static void SqlFuncsToTextImpl(Generator.Ctx ctx, TextWriter wr, Func <ValueInfo, bool> filter)
{
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;
}
if (!f.resultsInfo.Any(filter))
{
// 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 queryTmpl = (QueryTemplate)objQT;
var sql = queryTmpl.SrcSqlExpr;
foreach (SqlSectionExpr sec in sql.args)
{
var args = sec.args;
var attrs = (sec.kind == SqlSectionExpr.Kind.Select) ? colAttrs : null;
bool fromNewLine = args.Count > 1 || attrs != null;
wr.Write(sec.sectionName);
if (fromNewLine)
{
wr.WriteLine();
}
for (int i = 0; i < args.Count; i++)
{
if (i > 0)
{
wr.WriteLine(',');
}
if (attrs != null)
{
Attr.FriendlyText(attrs[i], wr);
}
wr.Write(fromNewLine ? '\t' : ' ');
wr.Write(args[i]);
}
wr.WriteLine();
}
//wr.WriteLine(sql);
wr.WriteLine(';');
wr.WriteLine();
wr.WriteLine();
}
}
public static object FindDependencies(CallExpr ce, Generator.Ctx ctx)
{
var arg0 = Generator.Generate(ce.args[0], ctx);
if (OPs.KindOf(arg0) != ValueKind.Const || arg0 == null)
{
throw new SolverException("FindDependencies: list of source params must be constant and not null");
}
var arg1 = (ce.args.Count > 1) ? Generator.Generate(ce.args[1], ctx) : null;
if (OPs.KindOf(arg1) != ValueKind.Const)
{
throw new SolverException("FindDependencies: optional list of dependent params must be constant");
}
var arg2 = (ce.args.Count > 2) ? Generator.Generate(ce.args[2], ctx) : null;
if (OPs.KindOf(arg2) != ValueKind.Const)
{
throw new SolverException("FindDependencies: optional list of unusable params must be constant");
}
var srcPrms = ((IList)arg0).Cast <object>().Select(o => o.ToString()).ToArray();
var depPrms = (arg1 == null) ? null : ((IList)arg1).Cast <object>().Select(o => o.ToString()).ToArray();
var unusables = (arg2 == null)
? new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase)
: ((IList)arg2).Cast <object>().Select(o => o.ToString()).ToDictionary(s => s, StringComparer.OrdinalIgnoreCase);
var aliasOf = GetAliases(ctx);
var funcs = Dependecies.GetFuncInfos(ctx.GetFunc(null, 0), aliasOf, fi => !fi.inputs.Any(inp => unusables.ContainsKey(inp)));
var dependencies = Dependecies.Find(funcs, aliasOf, int.MaxValue, srcPrms, depPrms);
var resDict = new Dictionary <string, bool>(StringComparer.OrdinalIgnoreCase);
var resParams = (depPrms == null)
? dependencies.Keys.Except(srcPrms, StringComparer.OrdinalIgnoreCase)
: depPrms.Where(s => dependencies.ContainsKey(s));
var res = new Dictionary <string, object>(StringComparer.OrdinalIgnoreCase);
foreach (var prmName in resParams)
{
var deps = dependencies[prmName];
var srcs = srcPrms.Where(s => deps.ContainsKey(s)).ToArray();
if (srcs.Length > 0)
{
res.Add(prmName, srcs);
}
}
return(res);
}
static IEnumerable <FuncDef> DefineProjectionFuncsImpl(CallExpr ce, Generator.Ctx ctx, bool acceptVector)
{
var inpParams = ((IList)OPs.GetConstant(ctx, ce.args[0])).Cast <object>().Select(Convert.ToString).ToArray();
var inpParamsParts = inpParams.Select(s => s.Split('_')).ToArray();
var inpParamsMasks = inpParamsParts.Where(parts => parts.Any(string.IsNullOrEmpty)).ToArray();
var outParams = ((IList)OPs.GetConstant(ctx, ce.args[1])).Cast <object>().Select(Convert.ToString).ToArray();
var outParamsMasks = outParams.Select(s => Convert.ToString(s).Split('_')).ToArray();
var newFuncName = "@projfunc:" + ce.args[ce.args.Count - 1].ToString().Replace('(', '_').Replace(')', '_').Replace('"', '_').Replace('\'', '_');
#region Collect suitable parameters from all functions outputs
var suitableParams = new Dictionary <string, string[]>(StringComparer.OrdinalIgnoreCase);
foreach (var f in ctx.GetFunc(null, 0))
{
foreach (var prm in f.resultsInfo)
{
var prmParts = prm.Parts();
for (int i = 0; i < inpParamsMasks.Length; i++)
{
var maskParts = inpParamsMasks[i];
var details = GetDetailsIfMatched(prmParts, maskParts);
if (details == null)
{
continue;
}
if (!suitableParams.TryGetValue(details, out var suitables))
{
suitables = new string[inpParamsMasks.Length];
suitableParams.Add(details, suitables);
}
suitables[i] = details;
}
}
}
#endregion
#region Return FuncDef for each suitable params set
foreach (var suitable in suitableParams.Where(prms => prms.Value.All(s => s != null)))
{
var details = suitable.Key;
var inps = new string[inpParamsParts.Length];
for (int i = 0; i < inpParamsParts.Length; i++)
{
var ip = inpParamsParts[i];
if (ip.Any(string.IsNullOrEmpty))
{ // one or more parts masked
inps[i] = ParamFromMaskAndDetails(details, ip);
}
else
{
inps[i] = inpParams[i];
}
}
var outs = new string[outParams.Length];
for (int i = 0; i < outParamsMasks.Length; i++)
{
outs[i] = ParamFromMaskAndDetails(details, outParamsMasks[i]);
}
yield return(FuncDefs_Core.macroFuncImpl(
context: ctx,
nameForNewFunc: new ConstExpr(newFuncName + details),
inpsDescriptors: new ArrayExpr(inps.Select(ReferenceExpr.Create).Cast <Expr>().ToList()),
outsDescriptors: new ArrayExpr(outs.Select(ReferenceExpr.Create).Cast <Expr>().ToList()),
funcBody: ce.args[ce.args.Count - 1],
inputParameterToSubstitute: (ce.args.Count > 3) ? ce.args[2] : null,
funcAcceptVector: acceptVector
));
}
#endregion
}
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};");
}
}
}
