static void SqlFuncsToDDL_Impl(Generator.Ctx ctx, TextWriter wr, TextWriter drops, string locationCode) { var dictTypes = new Dictionary <string, ValInf>(StringComparer.OrdinalIgnoreCase); var tablesToDrop = new List <string>(); foreach (var f in ctx.GetFunc(null, 0)) { if (f.xtraAttrs == null || !f.xtraAttrs.TryGetValue(nameof(QueryTemplate), out var objQT)) { // no QueryTemplate = is not SQL-originated function continue; } var queryTmpl = (QueryTemplate)objQT; var sql = queryTmpl.SrcSqlExpr; var secFrom = sql[SqlSectionExpr.Kind.From]; if (secFrom.args.Count > 1) { // can't generate DDL for join continue; } if (f.resultsInfo.All(vi => vi.location != locationCode)) { // no any output parameter from specified location/origin/source continue; } Attr.TblAttrsFriendlyText(f.xtraAttrs, wr); var colAttrs = (IList <Dictionary <Attr.Col, object> >)f.xtraAttrs[nameof(Attr.Tbl._columns_attrs)]; var secSelect = sql[SqlSectionExpr.Kind.Select]; var tableName = secFrom.args[0].ToString(); tablesToDrop.Add(tableName); var extraDDL = new StringBuilder(); var initValues = new Dictionary <string, object>(); int nInitRows = 0; wr.WriteLine($"CREATE TABLE {tableName} ("); var columns = secSelect.args; // columns for (int i = 0; i < columns.Count; i++) { AliasExpr ae; // column is presented as pair of field name and globally unique alias of value { var colExpr = columns[i]; ae = colExpr as AliasExpr; // Skip improper fields if (ae == null) { if (colExpr is ReferenceExpr re) { ae = new AliasExpr(re, re); } else { wr.WriteLine($"--???\t{colExpr}"); continue; } } switch (ae.left.nodeType) { case ExprType.Reference: break; case ExprType.Constant: wr.WriteLine($"--\t{ae.left}\t{ae.right}"); continue; default: wr.WriteLine($"--???\t{ae.left}\t{ae.right}"); continue; } } var attrs = colAttrs[i] ?? Attr.Empty; var fieldName = ae.left.ToString();//.ToUpperInvariant(); var fieldAlias = ValueInfo.WithoutParts(ae.right.ToString(), ValueInfo.Part.Location); var descr = attrs.Get(Attr.Col.Description); string type, trail; { bool isPK = attrs.GetBool(Attr.Col.PK, false); bool notNull = isPK || attrs.GetBool(Attr.Col.NotNull, false); var curr = new ValInf() { sqlType = attrs.GetString(Attr.Col.Type) }; var initVals = attrs.Get(Attr.Col.InitValues); if (initVals != null) { initValues.Add(fieldName, initVals); if (initVals is IList lst) { nInitRows = Math.Max(lst.Count, nInitRows); } else if (nInitRows == 0) { nInitRows = 1; } } ValInf valInfByLookupAttr = null; if (attrs.TryGet(Attr.Col.Lookup, out var objLookup)) { var masked = ValueInfo.OverrideByMask(fieldAlias, objLookup.ToString()); if (!dictTypes.TryGetValue(masked, out valInfByLookupAttr)) { var parts = ValueInfo.FourParts(masked); parts[3] = null; // remove 'units' part masked = ValueInfo.FromParts(parts); valInfByLookupAttr = dictTypes[masked]; } } // Descriptor 'type' in form '_QUANTITY__UNIT' // remove substance and location parts string sDescrType = ValueInfo.WithoutParts(fieldAlias, ValueInfo.Part.Substance, ValueInfo.Part.Location); ValInf valInfExact = null, valInfByType = null; if (valInfByLookupAttr != null || dictTypes.TryGetValue(fieldAlias, out valInfExact) || dictTypes.TryGetValue(sDescrType, out valInfByType)) { var vi = valInfByLookupAttr ?? valInfExact ?? valInfByType; if (vi.pkTable != null) { if (isPK) { if (valInfExact != null) { wr.WriteLine($"--WARNING! Value named '{fieldAlias}' is already used as PK in table '{vi.pkTable}'"); } } else { // create foreign key constraint var hash = $"{tableName}:{fieldAlias}".GetHashCode().ToString("X").Substring(0, 4); var sPK = vi.pkTable.Split('_')[0]; if (sPK.StartsWith(tableName)) { sPK = sPK.Substring(tableName.Length); } var fk = string.Format("fk_{0}_{1}", (tableName + '_' + sPK).DeLowerVowel(22), hash ); #region XRef comment (cross reference information) { f.xtraAttrs.TryGetValue(nameof(Attr.Tbl.Substance), out var substance); f.xtraAttrs.TryGetValue(nameof(Attr.Tbl.Description), out var tableDescr); var tDesc = Attr.OneLineText(tableDescr); var fDesc = Attr.OneLineText(descr); extraDDL.AppendLine($"--XRef\t{vi.pkTable}\t{substance}\t{tDesc}\t{tableName}\t{fieldName}\t{fDesc}"); } #endregion extraDDL.AppendLine($"ALTER TABLE {tableName} ADD CONSTRAINT {fk} FOREIGN KEY ({fieldName}) REFERENCES {vi.pkTable};"); } } if (curr.sqlType == null) { curr = new ValInf() { firstValue = vi.firstValue, pkField = vi.pkField, pkTable = vi.pkTable, sqlType = vi.sqlType } } ; else { if (curr.sqlType != vi.sqlType && valInfExact != null) { wr.WriteLine($"--WARNING! Type mismatch for value named '{fieldAlias}', first declaration has type '{vi.sqlType}'"); } curr.firstValue = vi.firstValue; curr.pkTable = vi.pkTable; curr.pkField = vi.pkField; } } if (valInfByLookupAttr == null && valInfExact == null) { bool noTypeFound = curr.sqlType == null; if (noTypeFound) { var info = ValueInfo.Create(fieldAlias, true); curr.sqlType = info?.quantity.DefaultDimensionUnit.Name ?? fieldAlias; wr.WriteLine($"--WARNING! No SQL-type inferenced for value named '{fieldAlias}' of type '{sDescrType}'"); } if (curr.sqlType != null) { if (isPK) { curr.pkTable = tableName; curr.pkField = fieldName; if (initVals is IList lst) { curr.firstValue = lst[0]; } else { curr.firstValue = initVals; } } if (noTypeFound == false) { if (isPK) { dictTypes.Add(fieldAlias, curr); } if (!dictTypes.ContainsKey(sDescrType)) { dictTypes.Add(sDescrType, new ValInf() { sqlType = curr.sqlType }); } } } } object defVal = attrs.Get(Attr.Col.Default); if (isPK) { trail = " NOT NULL PRIMARY KEY"; } else if (notNull) { var def = attrs.Get(Attr.Col.Default) ?? curr.firstValue; if (def != null) { trail = $" DEFAULT {new ConstExpr(def)} NOT NULL"; } else { trail = " NOT NULL"; } } else { trail = null; } type = curr.sqlType; } var typeArgs = attrs.GetString(Attr.Col.TypeArgs); if (!string.IsNullOrEmpty(typeArgs)) { typeArgs = '(' + typeArgs + ')'; } wr.WriteLine($"\t{fieldName} {type}{typeArgs}{trail},\t--{fieldAlias}\t{Attr.OneLineText(descr)}"); } wr.WriteLine(')'); wr.WriteLine(';'); wr.WriteLine(); if (extraDDL.Length > 0) { wr.WriteLine(extraDDL); wr.WriteLine(); extraDDL.Clear(); } if (nInitRows > 0) { var fields = string.Join(", ", initValues.Keys); for (int i = 0; i < nInitRows; i++) { var vals = initValues.Values.Select(v => { if (v is IList lst) { return((i < lst.Count) ? lst[i] : null); } else { return(v); } }) .Select(v => new ConstExpr(v)) .Select(v => ((v.value is string) ? "N" : null) + v.ToString()); var values = string.Join(", ", vals); wr.WriteLine($"INSERT INTO {tableName} ({fields}) VALUES ({values});"); } wr.WriteLine(); #region How to add MS SQL descriptions example //declare @CurrentUser sysname; //select @CurrentUser = user_name(); //execute sp_addextendedproperty 'MS_Description', 'Табле комент', 'user', @CurrentUser, 'table', 'PipeSysType_CL'; //execute sp_addextendedproperty 'MS_Description', 'This is the column comment', 'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'ID' } #endregion } } if (drops != null) { tablesToDrop.Reverse(); foreach (var tableName in tablesToDrop) { drops.WriteLine($"DROP TABLE {tableName};"); } } }
/// <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 }
/// <summary> /// Create field processing func /// </summary> internal Func <Expr, Dictionary <Attr.Col, object>, Expr> ModifyFieldExpr(SqlFuncPreprocessingCtx src, string targetDescrMask) { var maskPartsOnlyLocation = new string[4] { null, null, src.DefaultLocationForValueInfo, null }; string[] maskParts; if (targetDescrMask == null) { maskParts = maskPartsOnlyLocation; } else { maskParts = ValueInfo.FourParts(targetDescrMask); if (maskParts[2] == null) { maskParts[2] = src.DefaultLocationForValueInfo; } } string subst(string name, Dictionary <Attr.Col, object> attrs) { switch (name) { case nameof(START_TIME): src.isTimed = true; return(null); case nameof(END_TIME): case nameof(END_TIME__DT): case nameof(INS_OUTS_SEPARATOR): return(null); } bool isFixedAlias = attrs.GetBool(Attr.Col.FixedAlias); bool isPK = attrs.GetBool(Attr.Col.PK); var nameDescr = Combine(name, isFixedAlias ? maskPartsOnlyLocation : maskParts, isPK).descr; if (nameDescr.Length > 30) { throw new Generator.Exception($"Alias is too long: {nameDescr}, {nameDescr.Length}>30"); } string lkupDescr; if (attrs != null && attrs.TryGetValue(Attr.Col.Lookup, out var lookup)) { lkupDescr = ValueInfo.OverrideByMask(name, lookup.ToString()); } else { lkupDescr = nameDescr; lookup = null; } var vi = ValueInfo.Create(lkupDescr, true);//, src.ldr.defaultLocationForValueInfo); if (vi == null) { return(nameDescr); } //nameDescr = vi.ToString(); string lkupTable = null; if (templates.TryGetValue(vi.unit.Name, out var fields)) { // when templated by 'units', remove 'units' part var parts = ValueInfo.FourParts(lkupDescr); parts[2] = parts[3] = null; // remove location and units from lookup table name lkupTable = ValueInfo.FromParts(parts); parts = ValueInfo.FourParts(nameDescr); parts[3] = null; // remove units, needed only for templating purposes nameDescr = ValueInfo.FromParts(parts); } else if (templates.TryGetValue(vi.quantity.Name, out fields)) { lkupTable = lkupDescr; var parts = ValueInfo.FourParts(lkupDescr); parts[2] = null; // remove LOCATION part from lookup table name lkupTable = ValueInfo.FromParts(parts); } else if (lookup != null) { throw new KeyNotFoundException($"Can't found template table named '{vi.unit}' or {vi.quantity.Name} for lookup attribute '{lkupDescr}' of '{name}'"); } if (!isPK && fields.sql != null) { if (!extraFuncs.ContainsKey(lkupTable)) { AddExtraFunc(lkupTable, () => NewCodeLookupDict(src, fields, lkupTable, attrs)); } } return(nameDescr); } return((arg, attrs) => { string p = null; switch (arg.nodeType) { case ExprType.Alias: var ae = (AliasExpr)arg; if ((p = subst(ae.alias, attrs)) == null) { return arg; } return new AliasExpr(ae.expr, new ReferenceExpr(p)); case ExprType.Sequence: var args = ((SequenceExpr)arg).args; int n = args.Count; if ((p = subst(args[n - 1].ToString(), attrs)) == null) { return arg; } return new SequenceExpr(args.Take(n - 1).Concat(new[] { new ReferenceExpr(p) }).ToList()); case ExprType.Reference: var re = (ReferenceExpr)arg; if ((p = subst(re.name, attrs)) == null) { return arg; } return new AliasExpr(re, new ReferenceExpr(p)); default: return arg; } }); }