/// <summary> /// Create definitions of loading functions from specified SQL query /// </summary> /// <param name="funcNamesPrefix">Optional prefix for functions names. If null or empty, first table from 'FROM' clause is used</param> /// <param name="actualityInDays">Used to restrict the minimum queried timestamp // :MIN_TIME = :BEG_TIME-actualityInDays</param> /// <param name="queryText">SQL query text. Only some subset of SQL is supported /// (all sources in FROM cluase must have aliases, all fields in SELECT must be specified with source aliases, subqueries is not tested, etc)</param> /// <returns>Enumeration of pairs (func_name, loading function definition)</returns> internal static IEnumerable <FuncDef> FuncDefsForSql(Preprocessing.SqlFuncPreprocessingCtx c) { var sql = SqlParse.Do(c.queryText, SqlExpr.Options.EmptyFromPossible); sql = c.PostProc(sql); if (sql == null) { yield break; } var actuality = TimeSpan.FromDays((c.actualityInDays < 0) ? Attr.defaultActualityDays : c.actualityInDays); if (string.IsNullOrEmpty(c.funcNamesPrefix)) { c.funcNamesPrefix = sql.sources[0].expr.ToString(); } // generate list of results bool timedQuery = false; bool withSeparator = false; ValueInfo[] resultsInfo; { int n = sql.results.Length; var lst = new List <ValueInfo>(n); for (int i = 0; i < n; i++) { var d = sql.results[i].alias;//.ToUpperInvariant(); if (d == nameof(START_TIME)) { timedQuery = timedQuery || i == 1; } else if (d == nameof(END_TIME) || d == nameof(END_TIME__DT)) { //if (d.Length == nameof(END_TIME).Length) // timedQuery = timedQuery || i == 2; } else if (d == nameof(INS_OUTS_SEPARATOR)) { withSeparator = true; } else { //if (d.Length > 30) // throw new Generator.Exception($"SQL identifier too long (max 30, but {d.Length} chars in \"{d}\")"); var vi = ValueInfo.Create(d, defaultLocation: c.DefaultLocationForValueInfo); int DL = vi.DescriptorLength(); if (DL > 30) { throw new Generator.Exception($"SQL identifier too long (max 30, but {DL} chars in \"{vi}\")"); } lst.Add(vi); } } resultsInfo = lst.ToArray(); } var dbConnName = c.tblAttrs.GetString(Attr.Tbl.DbConnName) ?? c.ldr.dbConnValueName; #region Some query with INS_OUTS_SEPARATOR column if (withSeparator || !timedQuery || (c.ldr.forKinds & DbFuncType.Raw) != 0) { // separator column present string[] inputs, outputs; var qt = SqlQueryNonTimed(sql, c.arrayResults, dbConnName, out inputs, out outputs); Fn func = FuncNonTimedQuery(qt); var colsNames = qt.colsNames.Where(s => s != nameof(START_TIME) && s != nameof(END_TIME) && s != nameof(END_TIME__DT)).ToList(); for (int i = inputs.Length - 1; i >= 0; i--) { if (!ValueInfo.IsID(inputs[i])) { colsNames.RemoveAt(i); } } var fd = new FuncDef(func, c.funcNamesPrefix /* + "_Values"*/, inputs.Length, inputs.Length, ValueInfo.CreateMany(inputs), ValueInfo.CreateMany(colsNames.ToArray()), FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain, c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value) ); fd.xtraAttrs.Add(nameof(QueryTemplate), qt); yield return(fd); yield break; } #endregion #region Range if ((c.ldr.forKinds & DbFuncType.TimeInterval) != 0) { var qt = SqlQueryTimed(sql, DbFuncType.TimeInterval, c.arrayResults, dbConnName); Fn func = FuncTimedRangeQuery(actuality, qt); var fd = new FuncDef(func, c.funcNamesPrefix + "_Range", 3, 3, ValueInfo.CreateMany(qt.colsNames[0], nameof(ValueInfo.A_TIME__XT), nameof(ValueInfo.B_TIME__XT)), resultsInfo, FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain, c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value) ); fd.xtraAttrs.Add(nameof(QueryTemplate), qt); yield return(fd); } #endregion #region Slice at AT_TIME if ((c.ldr.forKinds & DbFuncType.TimeSlice) != 0) { var qt = SqlQueryTimed(sql, DbFuncType.TimeSlice, c.arrayResults, dbConnName); Fn func = FuncTimedSliceQuery(actuality, qt); var fd = new FuncDef(func, c.funcNamesPrefix + "_Slice", 2, 2, ValueInfo.CreateMany(qt.colsNames[0], nameof(ValueInfo.At_TIME__XT)), resultsInfo, FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain, c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value) ); fd.xtraAttrs.Add(nameof(QueryTemplate), qt); yield return(fd); } #endregion #region Raw interval // START_TIME in range MIN_TIME .. MAX_TIME if ((c.ldr.forKinds & DbFuncType.TimeRawInterval) != 0) { var qt = SqlQueryTimed(sql, DbFuncType.TimeRawInterval, c.arrayResults, dbConnName); Fn func = FuncRawIntervalQuery(qt); var fd = new FuncDef(func, c.funcNamesPrefix + "_Raw", 3, 3, ValueInfo.CreateMany(qt.colsNames[0], "MIN_TIME__XT", "MAX_TIME__XT"), resultsInfo, FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain, c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value) ); fd.xtraAttrs.Add(nameof(QueryTemplate), qt); yield return(fd); } #endregion #region Insert rows function if ((c.ldr.forKinds & DbFuncType.Insert) != 0) { var qt = SqlCommandInsert(sql, dbConnName, c.DefaultLocationForValueInfo, out var outputs); //todo: Sql insert //Fn func = FuncInsert(qt); } #endregion }