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};");
}
}
}
