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); }