private static Fn FuncRawIntervalQuery(QueryTemplate qt)
{
return((IList args) =>
{
return (LazyAsync)(async ctx =>
{
var begTime = OPs.FromExcelDate(Convert.ToDouble(args[1]));
var endTime = OPs.FromExcelDate(Convert.ToDouble(args[2]));
var conn = (IDbConn)await ctx.GetValue(qt.connName);
var mq = qt.GetQuery(new object[] { args[0] }
, conn.dbms.TimeToSqlText(begTime)
, conn.dbms.TimeToSqlText(endTime)
);
if (mq == null)
{
return ValuesDictionary.Empties;
}
var cmd = new SqlCommandData()
{
Kind = CommandKind.Query,
ConvertMultiResultsToLists = qt.arrayResults
};
using (mq)
{
cmd.SqlText = mq.QueryText;
var res = await conn.ExecCmd(cmd, ctx.Cancellation);
if (((IList)res).Count == 0)
{
res = ValuesDictionary.Empties;
}
return res;
}
});
});
}
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));
}
public Monitored(QueryTemplate parent, string queryText)
{
sqt = parent;
qryTxt = queryText;
sw = new System.Diagnostics.Stopwatch();
sw.Start();
}
public static QueryTemplate Get(string[] colsNames, string[] colsExprs, string[] varsNames, string queryTemplateText, SqlExpr SrcSqlExpr, bool arrayResults, string connName)
{
QueryTemplate res;
lock (registry)
{
if (!registry.TryGetValue(queryTemplateText, out res))
{
res = new QueryTemplate(colsNames, colsExprs, varsNames, queryTemplateText, SrcSqlExpr, arrayResults, connName);
registry.Add(queryTemplateText, res);
}
}
return(res);
}
private static Fn FuncTimedSliceQuery(TimeSpan actuality, QueryTemplate qt)
{
return((IList args) =>
{
return (LazyAsync)(async ctx =>
{
var lst = args[0] as IList;
if (lst != null && lst.Count == 0)
{
return lst;
}
bool range = args.Count > 2;
var begTime = OPs.FromExcelDate(Convert.ToDouble(range ? args[2] : args[1]));
var minTime = range ? OPs.FromExcelDate(Convert.ToDouble(args[1])) : begTime - actuality;
var conn = (IDbConn)await ctx.GetValue(qt.connName);
var mq = qt.GetQuery(new object[] { args[0] }, conn.dbms.TimeToSqlText(minTime), conn.dbms.TimeToSqlText(begTime));
if (mq == null)
{
return ValuesDictionary.Empties;
}
var cmd = new SqlCommandData()
{
Kind = CommandKind.Query,
ConvertMultiResultsToLists = qt.arrayResults
};
using (mq)
{
cmd.SqlText = mq.QueryText;
var res = await conn.ExecCmd(cmd, ctx.Cancellation);
if (((IList)res).Count == 0)
{
res = ValuesDictionary.Empties;
}
return res;
}
});
});
}
private static Fn FuncNonTimedQuery(QueryTemplate qt)
{
return((IList args) =>
{
return (LazyAsync)(async ctx =>
{
var mq = qt.GetQuery(args);
if (mq == null)
{
return ValuesDictionary.Empties;
}
using (mq)
{
var res = await FuncDefs_DB.ExecQuery(ctx, mq.QueryText, qt.connName, qt.arrayResults);
if (((IList)res).Count == 0)
{
res = ValuesDictionary.Empties;
}
return res;
}
});
});
}
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));
}
static QueryTemplate SqlCommandInsert(this SqlExpr sql, string connName, string defaultLocation, out string[] outputs)
{
var secFrom = sql[SqlSectionExpr.Kind.From];
outputs = null;
if (secFrom == null || secFrom.args.Count > 1 || !(secFrom.args[0] is ReferenceExpr reTable))
{
// don't generate INSERT for multiple tables or for none
return(null);
}
if (
sql[SqlSectionExpr.Kind.GroupBy] != null ||
sql[SqlSectionExpr.Kind.OrderBy] != null ||
sql[SqlSectionExpr.Kind.Where] != null
)
{
// don't generate INSERT from complex query definition
return(null);
}
var colsNames = new List <string>();
var colsExprs = new List <string>();
var sb = new StringBuilder($"INSERT INTO {secFrom.args[0]} (");
{
bool firstCol = true;
foreach (var colExpr in sql[SqlSectionExpr.Kind.Select].args)
{
if (!(colExpr is AliasExpr ae))
{
return(null);
}
if (firstCol)
{
firstCol = false;
}
else
{
sb.Append(',');
}
if (ae.expr is ReferenceExpr re)
{
if (string.Compare(ae.alias, nameof(INS_OUTS_SEPARATOR)) == 0)
{
continue;
}
colsNames.Add(ae.alias);
colsExprs.Add(re.name);
sb.Append(re.name);
}
else
{
return(null);
}
}
sb.Append(") VALUES ");
}
outputs = new string[] { $"{reTable.name}_OBJ_{defaultLocation}_RowsInserted" };
var qt = QueryTemplate.Get(colsNames.ToArray(), colsExprs.ToArray(), null, sb.ToString(), sql, false, connName);
return(qt);
}