private string HistorySqlSnippet()
{
return(string.Format(
@"SELECT
ID = entity.ID,
EntityID = entity.ID,
ActiveUntil = CAST(NULL AS DateTime){5}
FROM
{0}.{1} entity
UNION ALL
SELECT
ID = history.ID,
EntityID = history.EntityID,
au.ActiveUntil{4}
FROM
{0}.{2} history
LEFT JOIN {0}.{3} au ON au.ID = history.ID",
SqlUtility.Identifier(Entity.Module.Name),
SqlUtility.Identifier(Entity.Name),
SqlUtility.Identifier(Dependency_ChangesEntity.Name),
SqlUtility.Identifier(Entity.Name + "_ChangesActiveUntil"),
SelectHistoryPropertiesTag.Evaluate(this),
SelectEntityPropertiesTag.Evaluate(this)));
}
private string AtTimeSqlSnippet()
{
return(string.Format(
@"RETURNS TABLE
AS
RETURN
SELECT
ID = history.EntityID,
ActiveUntil,
EntityID = history.EntityID{2}
FROM
{0}.{1} history
INNER JOIN
(
SELECT EntityID, Max_ActiveSince = MAX(ActiveSince)
FROM {0}.{1}
WHERE ActiveSince <= @ContextTime
GROUP BY EntityID
) last ON last.EntityID = history.EntityID AND last.Max_ActiveSince = history.ActiveSince",
SqlUtility.Identifier(Entity.Module.Name),
SqlUtility.Identifier(Entity.Name + "_History"),
SelectHistoryPropertiesTag.Evaluate(this)));
}