NHibernate HQL Generator支持SQL Server 2016时态表 [英] NHibernate HQL Generator to support SQL Server 2016 temporal tables
问题描述
我正在尝试在NHibernate 4.x中实现对SQL Server 2016时态表的基本支持.想法是从
I am trying to implement basic support for SQL Server 2016 temporal tables in NHibernate 4.x. The idea is to alter SQL statement from
SELECT * FROM Table t0
到
SELECT * FROM Table FOR SYSTEM_TIME AS OF '2018-01-16 00:00:00' t0
您可以在SQL Server 2016中找到有关时态表的更多信息
You can find more info about temporal tables in SQL Server 2016 here
不幸的是,我没有找到在表名及其别名之间插入FOR FOR SYSTEM_TIME AS OF '...'
语句的任何方法.我不确定自定义方言是否支持此功能.我目前唯一可行的解决方案是将FOR SYSTEM_TIME
语句附加到额外的WHERE
中,并且我的输出SQL看起来像这样
Unfortunately, I've not found any way to insert FOR FOR SYSTEM_TIME AS OF '...'
statement between table name and its alias. I'm not sure if custom dialects supports this. The only working solution I have for now is to append FOR SYSTEM_TIME
statement within extra WHERE
and my output SQL looks like this
SELECT * FROM Table t0 WHERE FOR SYSTEM_TIME AS OF '2018-01-16 00:00:00'=1
为此,我实现了生成器和方言,如下所示:
To do so, I have implemented generator and dialect as follows:
public static class AuditableExtensions
{
public static bool AsOf(this IAuditable entity, DateTime date)
{
return true;
}
public static IQueryable<T> Query<T>(this ISession session, DateTime asOf) where T : IAuditable
{
return session.Query<T>().Where(x => x.AsOf(asOf));
}
}
public class ForSystemTimeGenerator : BaseHqlGeneratorForMethod
{
public static readonly string ForSystemTimeAsOfString = "FOR SYSTEM_TIME AS OF";
public ForSystemTimeGenerator()
{
SupportedMethods = new[]
{
ReflectionHelper.GetMethod(() => AuditableExtensions.AsOf(null, DateTime.MinValue))
};
}
public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject,
ReadOnlyCollection<Expression> arguments,
HqlTreeBuilder treeBuilder,
IHqlExpressionVisitor visitor)
{
return treeBuilder.BooleanMethodCall(nameof(AuditableExtensions.AsOf), new[]
{
visitor.Visit(arguments[1]).AsExpression()
});
}
}
public class MsSql2016Dialect : MsSql2012Dialect
{
public MsSql2016Dialect()
{
RegisterFunction(nameof(AuditableExtensions.AsOf), new SQLFunctionTemplate(
NHibernateUtil.Boolean,
$"{ForSystemTimeGenerator.ForSystemTimeAsOfString} ?1?2=1"));
}
}
任何人都可以提供任何更好的方法或示例,以便继续使用并在表名及其别名之间插入FOR SYSTEM_TIME AS OF
语句吗?目前,我唯一能看到的解决方案是在SessionInterceptor
中的OnPrepareStatement
中更改SQL,但是我相信有一些更好的方法...
Can anyone provide any better approach or samples I could use to move forward and insert FOR SYSTEM_TIME AS OF
statement between table name and its alias? At this moment the only solution I can see is to alter SQL in OnPrepareStatement
in SessionInterceptor
but I believe there is some better approach...
推荐答案
The example in section 19.1 shows how to use temporal tabvles:
首先定义一个过滤器:
<filter-def name="effectiveDate">
<filter-param name="asOfDate" type="date"/>
</filter-def>
然后将其附加到课程:
<class name="Employee" table="Employee For System_Time All" ...>
...
<many-to-one name="Department" column="dept_id" class="Department"/>
<property name="EffectiveStartDate" type="date" column="eff_start_dt"/>
<property name="EffectiveEndDate" type="date" column="eff_end_dt"/>
...
<!--
Note that this assumes non-terminal records have an eff_end_dt set to
a max db date for simplicity-sake
-->
<filter name="effectiveDate"
condition=":asOfDate BETWEEN eff_start_dt and eff_end_dt"/>
</class>
然后,您需要在会话上启用过滤器:
Then you need to enable the filter on the session:
ISession session = ...;
session.EnableFilter("effectiveDate").SetParameter("asOfDate", DateTime.Today);
var results = session.CreateQuery("from Employee as e where e.Salary > :targetSalary")
.SetInt64("targetSalary", 1000000L)
.List<Employee>();
希望这可以使人们入门.
Hope this gets people started.
这篇关于NHibernate HQL Generator支持SQL Server 2016时态表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!