示例#1
0
            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);
            }
示例#2
0
        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};");
                }
            }
        }
示例#3
0
            /// <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;
                    }
                });
            }