public SqlSectionExpr Postprocess()
{
switch (sectionName)
{
case "SELECT":
case "FROM":
var lst = new Expr[args.Count];
for (int i = 0; i < args.Count; i++)
{
var expr = args[i];
var seq = expr as SequenceExpr;
Expr item;
if (seq != null && seq.args.Count > 1)
{
item = AliasExpr.AsAlias(seq.args) ?? expr;
}
else
{
item = expr;
}
lst[i] = item;
}
var res = new SqlSectionExpr(sectionName, lst);
return(res);
default:
return(this);
}
}
public bool IsGroupedBy(Dictionary <string, bool> dictGroupBy, AliasExpr r)
{
if (dictGroupBy == null)
{
return(false);
}
else
{
return(dictGroupBy.ContainsKey(r.alias) || dictGroupBy.ContainsKey(r.expr.ToString()));
}
}
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};");
}
}
}
static IList <Expr> RestructAsSqlSelect(IList <Expr> lst)
{
var seq = lst[0] as SequenceExpr;
if (seq == null)
{
return(lst);
}
var ref0 = (seq.args.Count > 0) ? seq.args[0] as ReferenceExpr : null;
if (ref0 == null || string.Compare(ref0.name, "SELECT", StringComparison.InvariantCultureIgnoreCase) != 0)
{
return(lst);
}
string sectionName = null;
string sectionPartialName = string.Empty;
var res = new List <Expr>(lst.Count);
var sectionItems = new List <Expr>(lst.Count);
foreach (var item in lst)
{
var se = item as SequenceExpr;
if (se == null)
{
se = new SequenceExpr(item);
}
var argItems = new List <Expr>();
foreach (var exprArg in se.args)
{
var expr = exprArg;
var r = expr as ReferenceExpr;
CallExpr ce = null;
if (r == null)
{
ce = expr as CallExpr;
if (ce != null && ce.funcName.Length > 0)
{
r = new ReferenceExpr(ce.funcName);
}
}
if (r != null)
{
var s = r.name.ToUpperInvariant();
switch (s)
{
case "SELECT":
case "FROM":
case "WHERE":
case "BY":
case "USING":
if (argItems.Count > 0)
{
moveArgsToSection(sectionName, sectionItems, argItems);
}
if (sectionName != null)
{
var after = new SqlSectionExpr(sectionName, sectionItems.ToArray()).Postprocess();
if (after == null)
{
return(null);
}
res.Add(after);
}
else if (sectionItems.Count > 0)
{
res.AddRange(sectionItems);
}
sectionItems.Clear();
if (s == "BY")
{
sectionName = sectionPartialName + ' ' + s;
sectionPartialName = string.Empty;
}
else
{
System.Diagnostics.Trace.Assert(sectionPartialName.Length == 0, "Unknown SQL clause");
sectionName = s;
}
break;
case "JOIN":
{
System.Diagnostics.Trace.Assert(argItems.Count > 0, "No expressions before JOIN");
var ae = AliasExpr.AsAlias(argItems);
if (ae != null)
{
argItems.Clear();
argItems.Add(ae);
}
if (sectionPartialName.Length > 0)
{
argItems.Add(new ReferenceExpr(sectionPartialName));
}
argItems.Add(new ReferenceExpr(s));
if (ae == null)
{
var tmp = new SequenceExpr(argItems.ToArray());
argItems.Clear();
argItems.Add(tmp);
}
sectionPartialName = string.Empty;
}
break;
case "ON":
{
int i = argItems.Count - 1;
var subseq = new List <Expr>();
while (i >= 0)
{
var exp = argItems[i];
var re = exp as ReferenceExpr;
if (re != null && re.name == "JOIN")
{
break;
}
argItems.RemoveAt(i);
i--;
subseq.Insert(0, exp);
}
if (subseq.Count > 0)
{
argItems.Add((Expr)AliasExpr.AsAlias(subseq) ?? new SequenceExpr(subseq));
}
argItems.Add(new ReferenceExpr(s));
}
break;
case "ORDER":
case "GROUP":
case "INNER":
case "OUTER":
case "LEFT":
case "RIGHT":
case "CROSS":
case "FULL":
if (sectionPartialName.Length == 0)
{
sectionPartialName = s;
}
else
{
sectionPartialName += ' ' + s;
}
break;
default:
if (ce == null)
{
argItems.Add(r);
}
r = null;
break;
}
if (ce == null)
{
continue;
}
if (r != null)
{
expr = new CallExpr(string.Empty, ce.args);
ce = null;
}
}
if (ce != null && ce.funcName == string.Empty)
{
// no need to modify possible aliases in nested queries
var items = RestructAsSqlSelect(ce.args);
if (items != ce.args)
{
argItems.Add(CallExpr.Eval(new SequenceExpr(items)));
}
}
else
{
argItems.Add(expr);
}
}
moveArgsToSection(sectionName, sectionItems, argItems);
}
if (sectionName != null)
{
var after = new SqlSectionExpr(sectionName, sectionItems.ToArray()).Postprocess();
if (after == null)
{
return(null);
}
res.Add(after);
}
else if (sectionItems.Count > 0)
{
res.AddRange(sectionItems);
}
return(res.ToArray());
}
public Expr CreateQueryExpr(Expr andCondition = null, AliasExpr groupBy = null, Expr[] orderBy = null,
Func <AliasExpr, AliasExpr> resultModifier = null, params string[] resultsNames)
{
var sections = new List <Expr>(args.Count);
// select
Expr[] toSelect;
var gba = (groupBy == null) ? null : groupBy.alias;
// "group by" preprocessing
Expr newGroupBySection;
Dictionary <string, bool> presentInGroupBy = null;
{
var currGroupBy = this[SqlSectionExpr.Kind.GroupBy];
if (currGroupBy != null)
{
newGroupBySection = currGroupBy;
presentInGroupBy = GetPresenseDict(currGroupBy.args);
}
else if (groupBy != null)
{
newGroupBySection = new SqlSectionExpr(SqlSectionExpr.Kind.GroupBy, groupBy.expr);
presentInGroupBy = GetPresenseDict(new Expr[] { groupBy });
}
else
{
newGroupBySection = null;
}
}
// "select"
bool resultsModified = false;
if (resultsNames.Length == 0)
{
if (resultModifier != null)
{
toSelect = new Expr[results.Length];
for (int i = toSelect.Length - 1; i >= 0; i--)
{
var r = results[i];
var s = IsGroupedBy(presentInGroupBy, r) ? r : resultModifier(r);
if (r != s)
{
resultsModified = true;
}
toSelect[i] = s;
}
}
else
{
toSelect = results;
}
}
else
{
resultModifier = resultModifier ?? AsIs;
toSelect = new Expr[resultsNames.Length];
resultsModified = true;
for (int i = toSelect.Length - 1; i >= 0; i--)
{
var r = results[resNdx[resultsNames[i]]];
toSelect[i] = IsGroupedBy(presentInGroupBy, r) ? r : resultModifier(r);
}
}
if (resultsModified)
{
sections.Add(new SqlSectionExpr(SqlSectionExpr.Kind.Select, toSelect));
}
else
{
sections.Add(this[SqlSectionExpr.Kind.Select]);
}
// from
sections.Add(this[SqlSectionExpr.Kind.From]);
// where
if (andCondition != null)
{
sections.Add(new SqlSectionExpr(SqlSectionExpr.Kind.Where, (condition == null) ? andCondition : new BinaryExpr(ExprType.LogicalAnd, condition, andCondition)));
}
else if (condition != null)
{
sections.Add(this[SqlSectionExpr.Kind.Where]);
}
// group by
if (newGroupBySection != null)
{
sections.Add(newGroupBySection);
}
// order by
var thisOrderBy = this[SqlSectionExpr.Kind.OrderBy];
if (orderBy != null && orderBy.Length > 0)
{
if (thisOrderBy != null)
{
var fromQuery = thisOrderBy.args.Where(e =>
{
var s = e.ToString();
return(presentInGroupBy == null || presentInGroupBy.ContainsKey(s));
}).ToArray();
var presentInOrderBy = GetPresenseDict(fromQuery);
orderBy = fromQuery.Concat(orderBy.Where(e =>
{
var s = e.ToString();
return(!presentInOrderBy.ContainsKey(s));
})).ToArray();
}
sections.Add(new SqlSectionExpr(SqlSectionExpr.Kind.OrderBy, orderBy));
}
else if (thisOrderBy != null)
{
sections.Add(this[SqlSectionExpr.Kind.OrderBy]);
}
//for (int i = 3; i < sql.Length; i++)
// if (sql[i] != null)
// sections.Add(sql[i]);
return(new SequenceExpr(sections)); // todo
}
static AliasExpr AsIs(AliasExpr ae)
{
return(ae);
}
public SqlExpr(IList <Expr> sections, Options opts)
: base(sections)
{
foreach (SqlSectionExpr s in sections)
{
sql[(int)s.kind] = s;
}
SqlSectionExpr section;
// FROM: scan sources
section = this[SqlSectionExpr.Kind.From];
if (section == null)
{
if (!opts.HasFlag(Options.EmptyFromPossible))
{
throw new Generator.Exception($"new SqlExpr(...): nonempty FROM section expected");
}
section = new SqlSectionExpr(SqlSectionExpr.Kind.From);
}
IDictionary <string, int> srcAlias2Ndx;
{
var items = section.args;
var srcs = new List <AliasExpr>(items.Count);
srcAlias2Ndx = new Dictionary <string, int>(items.Count, StringComparer.OrdinalIgnoreCase);
int i = 0;
foreach (var item in items)
{
var ae = item as AliasExpr;
if (ae != null)
{
srcAlias2Ndx[ae.alias] = i++;
srcs.Add(ae);
continue;
}
var sq = item as SequenceExpr;
if (sq != null)
{
foreach (var it in sq.args)
{
var tmp = it as AliasExpr;
if (tmp != null)
{
srcAlias2Ndx[tmp.alias] = i++;
srcs.Add(tmp);
}
}
}
else
{
var tmp = new AliasExpr(item, item);
srcAlias2Ndx[tmp.alias] = i++;
srcs.Add(tmp);
}
}
//System.Diagnostics.Trace.Assert(i <= 32, "No more than 32 sources in FROM supported");
sources = srcs.ToArray();
}
// SELECT: scan results
section = this[SqlSectionExpr.Kind.Select];
{
var items = section.args;
int n = items.Count;
//System.Diagnostics.Trace.Assert(n <= 64, "No more than 64 result columns in SELECT supported");
results = new AliasExpr[n];
resFields = new Expr[n];
resNdx = new Dictionary <string, int>(n, StringComparer.OrdinalIgnoreCase);
//resultUsesSrcs = new uint[n];
for (int i = 0; i < n; i++)
{
var aliasExpr = items[i] as AliasExpr;
if (aliasExpr != null)
{ // item in form "expression alias"
Expr resField = null;
foreach (var srcAlias in aliasExpr.expr.Traverse <BinaryExpr>(e =>
{
var be = e as BinaryExpr;
if (be != null && be.nodeType == ExprType.Fluent)
{
return(be);
}
else
{
return(null);
}
}))
{
if (srcAlias == null)
{
continue;
}
int j;
if (srcAlias2Ndx.TryGetValue(srcAlias.left.ToString(), out j)) // source alias may be to the left of point
{
//resultUsesSrcs[i] |= (uint)(1 << j);
resField = srcAlias;
}
}
//System.Diagnostics.Trace.Assert(resField != null, "No field reference found");
resFields[i] = resField ?? aliasExpr.expr;
results[i] = aliasExpr;
resNdx.Add(aliasExpr.alias, i);
continue;
}
var binExpr = items[i] as BinaryExpr;
if (binExpr != null)
{
if (binExpr.nodeType == ExprType.Fluent)
{ // item in form "tablename.fieldname"
resFields[i] = binExpr;
results[i] = new AliasExpr(binExpr, binExpr.right);
resNdx.Add(binExpr.right.ToString(), i);
continue;
}
}
else
{
var tmp = items[i] as ReferenceExpr;
if (tmp != null)
{ // item in form "fieldname"
resFields[i] = tmp;
results[i] = new AliasExpr(tmp, tmp);
resNdx.Add(tmp.ToString(), i);
continue;
}
}
throw new ArgumentException("SELECTed expression must be a simple field reference or have alias", items[i].ToString());
}
}
// WHERE: scan conditions
section = this[SqlSectionExpr.Kind.Where];
if (section != null)
{
var items = section.args;
System.Diagnostics.Trace.Assert(items.Count == 1, "Only one condition expression in WHERE supported");
condition = items[0];
}
}