public static (string descr, string lookup) Combine(string name, string[] subsDescr, bool isPK)
{
if (name.IndexOf('_') < 0)
{
name = '_' + name;
}
var tmplParts = ValueInfo.FourParts(name);
var parts = (subsDescr == null)
? tmplParts
: Enumerable.Range(0, 4).Select(
j =>
{
var a = tmplParts[j];
var b = subsDescr[j];
switch ((ValueInfo.Part)j)
{
case ValueInfo.Part.Substance:
return((a == null || b == null) ? a ?? b : a.StartsWith(b) ? a : b.EndsWith(a) ? b : b + a);
case ValueInfo.Part.Quantity:
return((a == null || b == null) ? b ?? a : isPK?b: a);
default:
return(a ?? b);
}
}).ToArray();
var lookup = (parts.Length > 3) ? parts[3] ?? parts[1] : parts[1];
return(ValueInfo.FromParts(parts), lookup);
}
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 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;
}
});
}
