const string sAtTime = ":AT_TIME"; // time of slice
static Expr Cond_TimeIntervalHalfOpen(QueryTimeInfo startTime, string sATime, string sBTime)
{ // sATime<startTime AND startTime<=sBTime
var cond = BinaryExpr.Create(ExprType.LogicalAnd,
new BinaryExpr(ExprType.LessThan, startTime.Time2Value(sATime), startTime.valueExpr),
new BinaryExpr(ExprType.LessThanOrEqual, startTime.valueExpr, startTime.Time2Value(sBTime)));
return(cond);
}
static Expr Cond_TimeSlice(QueryTimeInfo startTime, string sMinTime, string sAtTime)
{ // sMinTime<=startTime AND startTime<=sAtTime
var cond = new BinaryExpr(ExprType.LogicalAnd,
new BinaryExpr(ExprType.LessThanOrEqual, startTime.Time2Value(sMinTime), startTime.valueExpr),
new BinaryExpr(ExprType.LessThanOrEqual, startTime.valueExpr, startTime.Time2Value(sAtTime))
);
return(cond);
}
static Expr Cond_TimeSlice(QueryTimeInfo startTime, QueryTimeInfo endTime, string sMinTime)
{ // sMinTime<=startTime AND startTime<=sAtTime AND (endTime is null OR sAtTime<=endTime)
var cond_min = new BinaryExpr(ExprType.LessThanOrEqual, startTime.Time2Value(sMinTime), startTime.valueExpr);
var cond_0 = new BinaryExpr(ExprType.LessThanOrEqual, startTime.valueExpr, startTime.Time2Value(sAtTime));
var cond_1 = new BinaryExpr(ExprType.LogicalOr,
new SequenceExpr(endTime.valueExpr, new ReferenceExpr("IS NULL")),
new BinaryExpr(ExprType.LessThan, endTime.Time2Value(sAtTime), endTime.valueExpr)
);
var cond = BinaryExpr.Create(ExprType.LogicalAnd, cond_min, cond_0, cond_1);
return(cond);
}
public static QueryTimeInfo Get(Expr timeExpr)
{
var r = new QueryTimeInfo();
if (timeExpr == null)
{
return(r);
}
r.timeExpr = timeExpr;
TryExtractTimeFormatString(timeExpr, out r.valueExpr, out r.valueFmt);
return(r);
}
static QueryTemplate SqlQueryTimed(SqlExpr sqlExpr, DbFuncType queryKind, bool arrayResults, string connName)
{
var rn = sqlExpr.resNdx;
var rf = sqlExpr.resFields;
var rs = sqlExpr.results;
var idValueExpr = rf[0]; // first column of query must be an ID of subject
var idAlias = rs[0];
int iStartTimeField = rn[refStartTime.name];
var startTime = QueryTimeInfo.Get(rf[iStartTimeField]);
System.Diagnostics.Trace.Assert(startTime.timeExpr != null, "START_TIME column is not found");
QueryTimeInfo endTime;
{
int i;
endTime = QueryTimeInfo.Get(rn.TryGetValue(nameof(END_TIME), out i) ? rf[i] : null);
}
var orderBy = new Expr[] { idAlias.right, refStartTime };
Expr expr;
if (endTime.timeExpr != null && queryKind != DbFuncType.GetSchemaOnly)
{ // value has two timestamps - START_TIME and END_TIME
Expr cond;
switch (queryKind)
{
case DbFuncType.TimeInterval:
cond = Cond_TimeInterval(startTime, endTime, sMinTime); break;
case DbFuncType.TimeRawInterval:
cond = Cond_TimeInterval(startTime, endTime, sATime); break;
case DbFuncType.TimeSlice:
cond = Cond_TimeSlice(startTime, endTime, sMinTime); break;
default:
throw new NotSupportedException($"Unsupported DbFuncType value: {queryKind.ToString()}");
}
cond = new SequenceExpr(cond, new ReferenceExpr("{0}"));
expr = sqlExpr.CreateQueryExpr(cond, null, orderBy);
}
else
{ // value has only one timestamp - START_TIME
Expr cond_aggr = null, cond_simp = null;
switch (queryKind)
{
case DbFuncType.GetSchemaOnly:
break;
case DbFuncType.TimeRawInterval:
cond_simp = Cond_TimeIntervalHalfOpen(startTime, sATime, sBTime); break;
case DbFuncType.TimeInterval:
cond_aggr = Cond_TimeSlice(startTime, sMinTime, sATime);
cond_simp = Cond_TimeIntervalHalfOpen(startTime, sATime, sBTime); break;
case DbFuncType.TimeSlice:
cond_aggr = Cond_TimeSlice(startTime, sMinTime, sAtTime); break;
default:
throw new NotSupportedException($"Unsupported DbFuncType value: {queryKind.ToString()}");
}
if (cond_aggr != null)
{
var exprKeep = new CallExpr("KEEP", new SequenceExpr(new ReferenceExpr("DENSE_RANK LAST ORDER BY"), startTime.valueExpr));
cond_aggr = new SequenceExpr(cond_aggr, new ReferenceExpr("{0}"));
expr = sqlExpr.CreateQueryExpr(cond_aggr, idAlias, orderBy,
src =>
{
Expr res;
if (src.expr.Traverse(e => e)
.OfType <CallExpr>()
.Select(ce => ce.funcName.ToUpperInvariant())
.Where(fn => fn == "MIN" || fn == "MAX" || fn == "SUM" || fn == "AVG" || fn == "COUNT").Any())
{
res = src.expr;
}
else
{
res = new CallExpr("MAX", src.expr);
}
if (src.alias != nameof(START_TIME))
{
res = new SequenceExpr(res, exprKeep);
}
return(new AliasExpr(res, src.right));
});
}
else
{
expr = null;
}
if (cond_simp != null)
{
cond_simp = new SequenceExpr(cond_simp, new ReferenceExpr("{0}"));
var expr2 = sqlExpr.CreateQueryExpr(cond_simp, null, orderBy);
if (expr == null)
{
expr = expr2;
}
else
{
//expr = new SequenceExpr(expr, new ReferenceExpr("UNION ALL"), expr2);
var sqlA = ((MultiExpr)expr).args.Cast <SqlSectionExpr>();
//var order = sqlA.FirstOrDefault(s => s.kind == SqlSectionExpr.Kind.OrderBy);
var bodyA = sqlA.Where(s => s.kind != SqlSectionExpr.Kind.OrderBy);
//var sqlB = ((MultiExpr)expr2).args.Cast<SqlSectionExpr>();
//var orderB = sqlB.FirstOrDefault(s => s.kind == SqlSectionExpr.Kind.OrderBy);
//var bodyB = sqlB.Where(s => s.kind != SqlSectionExpr.Kind.OrderBy);
var lst = new List <Expr>();
lst.AddRange(bodyA);
lst.Add(new ReferenceExpr("UNION ALL"));
lst.Add(expr2);
expr = new SequenceExpr(lst);
}
}
else if (expr == null)
{
expr = sqlExpr.CreateQueryExpr();
}
}
string[] qryVars = null;
switch (queryKind)
{
case DbFuncType.TimeRawInterval:
qryVars = new string[] { sMinTime, sATime, sBTime }; // sMinTime here is dummy (not used really and specified only for unification)
break;
case DbFuncType.TimeInterval:
qryVars = new string[] { sMinTime, sATime, sBTime };
break;
case DbFuncType.TimeSlice:
qryVars = new string[] { sMinTime, sAtTime };
break;
case DbFuncType.GetSchemaOnly:
qryVars = new string[0];
break;
}
return(QueryTemplate.Get(
rs.Select(x => x.alias).ToArray(),
rs.Select(x => x.expr.ToString()).ToArray(),
qryVars, expr.ToString(),
sqlExpr, arrayResults, connName));
}
