System Versioned Temporal tables looking up and listing
This StockItemsHistoryController has methods for retrieving a list of StockItems at a point in time specified by an optional query string parameter (if no value is provided the current time is assumed). To show how a temporal query can span multiple tables I included the [Purchasing].[suppliers] table which is also versioned.
http://localhost:36739/api/StockItemsHistory
[HttpGet]
public async Task<ActionResult<IAsyncEnumerable<Model.StockItemsHistoryListDtoV1>>> Get([FromQuery]DateTime? asAt)
{
IEnumerable<Model.StockItemsHistoryListDtoV1> response = null;
if (!asAt.HasValue)
{
asAt = DateTime.UtcNow;
}
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QueryAsync<Model.StockItemsHistoryListDtoV1>(sql: "[warehouse].[StockItemsHistoryStockItemsListAsAtV1]", param: new { asAt }, commandType: CommandType.StoredProcedure);
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving list of StockItems");
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemsListAsAtV1]
@AsAt DATETIME2(7)
AS
BEGIN
SELECT [StockItems].[StockItemID] as "ID"
,[StockItems].[StockItemName] as "Name"
,[StockItems].[UnitPrice]
,[StockItems].[RecommendedRetailPrice]
,[StockItems].[TaxRate]
,[StockItems].[CustomFields]
,[Suppliers].[SupplierID]
,[Suppliers].[SupplierName]
FROM [Warehouse].[StockItems] FOR SYSTEM_TIME AS OF @AsAt as StockItems
INNER JOIN [Purchasing].[Suppliers] FOR SYSTEM_TIME AS OF @AsAt as Suppliers ON (StockItems.SupplierID = [Suppliers].SupplierID)
END
The query also returns the custom fields (often what was changed in StockItem history), the supplier Id and Supplier name.
The detailed history of a StockItem can be queried to illustrate how the _Archive(history) table works
localhost:36739/api/StockItemsHistory/64/history
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemHistoryListV1]
@StockItemID int
AS
BEGIN
SELECT[StockItems_Archive].[StockItemID] as "ID"
,[StockItems_Archive].[StockItemName] as "Name"
,[StockItems_Archive].[UnitPrice]
,[StockItems_Archive].[RecommendedRetailPrice]
,[StockItems_Archive].[TaxRate]
,[StockItems_Archive].[CustomFields]
,[StockItems_Archive].[ValidFrom]
,[StockItems_Archive].[ValidTo]
FROM [Warehouse].[StockItems_Archive]
WHERE [StockItems_Archive].[StockItemID] = @StockItemId
ORDER BY [ValidFrom] DESC
END
[HttpGet("{id}/history")]
public async Task<ActionResult<IEnumerable<Model.StockItemHistoryListDtoV1>>> GetHistory([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id)
{
IEnumerable<Model.StockItemHistoryListDtoV1> response = null;
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QueryAsync<Model.StockItemHistoryListDtoV1>(sql: "[Warehouse].[StockItemsHistoryStockItemHistoryListV1]", param: new { StockItemID = id }, commandType: CommandType.StoredProcedure);
if (response == default)
{
logger.LogInformation("StockItem:{0} not found", id);
return this.NotFound($"StockItem:{id} not found");
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving up a StockItem with Id:{0}", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
The state of a StockItem plus the associated Supplier and PackageTypes tables can also be queried at a point in time (if no value is provided the current time is assumed).
http://localhost:36739/api/StockItemsHistory/64?AsAt=2021-06-18T01:21:07.0121476
[HttpGet("{id}")]
public async Task<ActionResult<Model.StockItemGetDtoV1>> Get([Range(1, int.MaxValue, ErrorMessage = "Stock item id must greater than 0")] int id, [FromQuery] DateTime? asAt)
{
Model.StockItemGetDtoV1 response = null;
if ( !asAt.HasValue)
{
asAt = DateTime.UtcNow;
}
try
{
using (SqlConnection db = new SqlConnection(this.connectionString))
{
response = await db.QuerySingleOrDefaultAsync<Model.StockItemGetDtoV1>(sql: "[Warehouse].[StockItemsHistoryStockItemLookupAsAtV1]", param: new { asAt, stockItemID=id }, commandType: CommandType.StoredProcedure);
if (response == default)
{
logger.LogInformation("StockItem:{0} not found", id);
return this.NotFound($"StockItem:{id} not found");
}
}
}
catch (SqlException ex)
{
logger.LogError(ex, "Retrieving StockItem with Id:{0}", id);
return this.StatusCode(StatusCodes.Status500InternalServerError);
}
return this.Ok(response);
}
ALTER PROCEDURE [Warehouse].[StockItemsHistoryStockItemLookupAsAtV1]
@StockItemID int,
@AsAt DATETIME2(7)
AS
BEGIN
SELECT[StockItem].[StockItemID] as "ID"
,[StockItem].[StockItemName] as "Name"
,[StockItem].[UnitPrice]
,[StockItem].[RecommendedRetailPrice]
,[StockItem].[TaxRate]
,[StockItem].[typicalWeightPerUnit]
,[StockItem].[QuantityPerOuter]
,[UnitPackage].[PackageTypeName] as "unitPackageName"
,[OuterPackage].[PackageTypeName] as "outerPackageName"
,[Supplier].[SupplierID]
,[Supplier].[SupplierName]
FROM [Warehouse].[StockItems] FOR SYSTEM_TIME AS OF @AsAt as StockItem
INNER JOIN[Warehouse].[PackageTypes] FOR SYSTEM_TIME AS OF @AsAt as UnitPackage ON ([StockItem].[UnitPackageID] = [UnitPackage].[PackageTypeID])
INNER JOIN[Warehouse].[PackageTypes] FOR SYSTEM_TIME AS OF @AsAt as OuterPackage ON ([StockItem].[OuterPackageID] = [OuterPackage].[PackageTypeID])
INNER JOIN[Purchasing].[Suppliers] FOR SYSTEM_TIME AS OF @AsAt as Supplier ON ([StockItem].SupplierID = Supplier.SupplierID)
WHERE[StockItem].[StockItemID] = @StockItemId
END
I found it was easy to miss the “FOR SYSTEM_TIME AS OF @AsAt” on the INNER JOINs.
......
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
| [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
[ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
DEFAULT constant_expression [WITH VALUES] ,
system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
[ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
DEFAULT constant_expression [WITH VALUES] ,
start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
]
PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
|
It is also possible to hide the start and end time columns which might be useful for when retrofitting this approach to a legacy application which uses SELECT * FROM … and might not handle the extra columns correctly.