static QueryTemplate SqlQueryNonTimed(this SqlExpr sqlExpr, bool arrayResults, string connName, out string[] inputs, out string[] outputs) { var allColumns = sqlExpr.resNdx.Keys; string[] colsNames; IEnumerable <AliasExpr> colsExprs; Expr[] orderBy; int i; if (sqlExpr.resNdx.TryGetValue(nameof(INS_OUTS_SEPARATOR), out i)) { inputs = allColumns.Take(i).ToArray(); outputs = allColumns.Skip(i + 1).ToArray(); colsNames = inputs.Union(outputs).ToArray(); var rs = sqlExpr.results; colsExprs = rs.Take(i).Union(rs.Skip(i + 1)); orderBy = rs.Take(i).Select(e => e.right).ToArray(); } else { inputs = new string[0]; outputs = allColumns.ToArray(); colsNames = allColumns.ToArray(); colsExprs = sqlExpr.results; orderBy = null; } var expr = sqlExpr.CreateQueryExpr(new ReferenceExpr("1=1{0}"), null, orderBy, null, colsNames); var lstColsExprs = colsExprs //.Where(a => a.alias != nameof(START_TIME) && a.alias != nameof(END_TIME)) .Select(ae => ae.expr.ToString()).ToArray(); return(QueryTemplate.Get(colsNames, lstColsExprs, null, expr.ToString(), sqlExpr, arrayResults, connName)); }
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)); }