示例#1
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Procedure"/> class.
        /// </summary>
        /// <param name="prefix">The prefix used on stored procedure names.</param>
        public Procedure(dac.TSqlObject tSqlObject, string prefix, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
        {
            this.Prefix     = prefix ?? "";
            this.RawName    = tSqlObject.Name.Parts.Last();
            this.Name       = this.RawName.Substring(this.Prefix.Length);
            this.Parameters = tSqlObject.GetReferenced(dac.Procedure.Parameters).Select(x => new Parameter(x, primaryKeys, foreignKeys));

            TSqlFragment fragment;

            TSqlModelUtils.TryGetFragmentForAnalysis(tSqlObject, out fragment);
            var selectVisitor = new SelectVisitor();

            fragment.Accept(selectVisitor);

            var bodyColumnTypes = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
                                  .Where(x => x.ObjectType.Name == "Column")
                                  .GroupBy(bd => string.Join(".", bd.Name.Parts))
                                  .Select(grp => grp.First())
                                  .ToDictionary(
                key => string.Join(".", key.Name.Parts),
                val => new DataType
            {
                Map      = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).First().Name.Parts.Last()),
                Nullable = dac.Column.Nullable.GetValue <bool>(val)
            },
                StringComparer.InvariantCultureIgnoreCase);

            var unions  = selectVisitor.Nodes.OfType <BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
            var selects = selectVisitor.Nodes.OfType <QuerySpecification>().Concat(unions);

            this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
        }
示例#2
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Column" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the column.</param>
        /// <param name="tSqlTable">The table or view this column belongs to.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Column(dac.TSqlObject tSqlObject, dac.TSqlObject tSqlTable, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
        {
            this.Name = tSqlObject.Name.Parts.Last();
            var fullName = string.Join(".", tSqlObject.Name.Parts);

            this.IsPrimaryKey = primaryKeys.Any(p => string.Join(".", p.Name.Parts) == fullName);

            // Get relationships where this column is the child.
            IEnumerable <ForeignKeyConstraintDefinition> myForeignKeys;

            foreignKeys.TryGetValue(tSqlTable, out myForeignKeys);
            myForeignKeys            = myForeignKeys ?? Enumerable.Empty <ForeignKeyConstraintDefinition>();
            this.ParentRelationships = from f in myForeignKeys
                                       where f.Columns.Any(c => c.Value == this.Name)
                                       select new RelationshipIdentifier
            {
                TableOrView = f.ReferenceTableName.BaseIdentifier != null ? f.ReferenceTableName.BaseIdentifier.Value : null,
                Schema      = f.ReferenceTableName.SchemaIdentifier != null ? f.ReferenceTableName.SchemaIdentifier.Value : null,
                Database    = f.ReferenceTableName.DatabaseIdentifier != null ? f.ReferenceTableName.DatabaseIdentifier.Value : null,
                Columns     = f.ReferencedTableColumns.Select(c => c.Value)
            };
            this.IsForeignKey = this.ParentRelationships.Any();

            // Get relationships where this column is the parent.
            var childTables = foreignKeys.Where(f => f.Value.Any(v =>
                                                                 v.ReferenceTableName.BaseIdentifier.Value == tSqlTable.Name.Parts.Last() &&
                                                                 v.ReferencedTableColumns.Any(c => c.Value == this.Name)));

            this.ChildRelationships = from t in childTables
                                      from r in t.Value
                                      let tableParts = t.Key.Name.Parts.Count()
                                                       select new RelationshipIdentifier
            {
                TableOrView = t.Key.Name.Parts.Last(),
                Schema      = tableParts > 1 ? t.Key.Name.Parts.ElementAt(tableParts - 2) : null,
                Database    = tableParts > 2 ? t.Key.Name.Parts.ElementAt(tableParts - 3) : null,
                Columns     = r.Columns.Select(c => c.Value)
            };

            if (tSqlObject.ObjectType.Name == "TableTypeColumn")
            {
                var sqlDataTypeName = tSqlObject.GetReferenced(dac.TableTypeColumn.DataType).ToList().First().Name.Parts.Last();
                this.DataTypes  = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
                this.IsIdentity = dac.TableTypeColumn.IsIdentity.GetValue <bool>(tSqlObject);
                this.IsNullable = dac.TableTypeColumn.Nullable.GetValue <bool>(tSqlObject);
                this.Precision  = dac.TableTypeColumn.Precision.GetValue <int>(tSqlObject);
                this.Scale      = dac.TableTypeColumn.Scale.GetValue <int>(tSqlObject);
                this.Length     = dac.TableTypeColumn.Length.GetValue <int>(tSqlObject);
            }
            else
            {
                var sqlDataTypeName = tSqlObject.GetReferenced(dac.Column.DataType).ToList().First().Name.Parts.Last();
                this.DataTypes  = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
                this.IsIdentity = dac.Column.IsIdentity.GetValue <bool>(tSqlObject);
                this.IsNullable = dac.Column.Nullable.GetValue <bool>(tSqlObject);
                this.Precision  = dac.Column.Precision.GetValue <int>(tSqlObject);
                this.Scale      = dac.Column.Scale.GetValue <int>(tSqlObject);
                this.Length     = dac.Column.Length.GetValue <int>(tSqlObject);
            }
        }
示例#3
0
        private void SetProperties(dac.TSqlObject tSqlObject)
        {
            var sqlDataTypeName = tSqlObject.GetReferenced(dac.Column.DataType).ToList().First().Name.Parts.Last();

            this.DataTypes  = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
            this.IsIdentity = dac.Column.IsIdentity.GetValue <bool>(tSqlObject);
            this.IsNullable = dac.Column.Nullable.GetValue <bool>(tSqlObject);
            this.Precision  = dac.Column.Precision.GetValue <int>(tSqlObject);
            this.Scale      = dac.Column.Scale.GetValue <int>(tSqlObject);
            this.Length     = dac.Column.Length.GetValue <int>(tSqlObject);
        }
示例#4
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Table" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the table.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Table(dac.TSqlObject tSqlObject, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
        {
            // Get the name.
            this.Name = tSqlObject.Name.Parts.Last();

            // Get the columns
            var columns    = new List <Column>();
            var sqlColumns = tSqlObject.ObjectType.Name == "TableType" ? tSqlObject.GetReferenced(dac.TableType.Columns) : tSqlObject.GetReferenced(dac.Table.Columns);

            foreach (var sqlColumn in sqlColumns)
            {
                var column = new Column(sqlColumn, tSqlObject, primaryKeys, foreignKeys);
                columns.Add(column);
            }
            this.Columns = columns;
        }
示例#5
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Table" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the table.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Table(dac.TSqlObject tSqlObject)
        {
            // Get the name.
            this.Name = tSqlObject.Name.Parts.Last();

            // Get the columns
            var columns    = new List <Column>();
            var sqlColumns = tSqlObject.ObjectType.Name == "TableType" ? tSqlObject.GetReferenced(dac.TableType.Columns) : tSqlObject.GetReferenced(dac.Table.Columns);

            foreach (var sqlColumn in sqlColumns)
            {
                var column = new Column(sqlColumn);
                columns.Add(column);
            }
            this.Columns = columns;
        }
示例#6
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Column" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the column.</param>
        /// <param name="tSqlTable">The table or view this column belongs to.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Column(dac.TSqlObject tSqlObject)
        {
            this.Name = tSqlObject.Name.Parts.Last();

            if (tSqlObject.ObjectType.Name == "TableTypeColumn")
            {
                var sqlDataTypeName = tSqlObject.GetReferenced(dac.TableTypeColumn.DataType).ToList().First().Name.Parts.Last();
                this.DataTypes  = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
                this.IsIdentity = dac.TableTypeColumn.IsIdentity.GetValue <bool>(tSqlObject);
                this.IsNullable = dac.TableTypeColumn.Nullable.GetValue <bool>(tSqlObject);
                this.Precision  = dac.TableTypeColumn.Precision.GetValue <int>(tSqlObject);
                this.Scale      = dac.TableTypeColumn.Scale.GetValue <int>(tSqlObject);
                this.Length     = dac.TableTypeColumn.Length.GetValue <int>(tSqlObject);
            }
            else
            {
                dac.ColumnType metaType = tSqlObject.GetMetadata <dac.ColumnType>(dac.Column.ColumnType);

                switch (metaType)
                {
                case dac.ColumnType.Column:
                case dac.ColumnType.ColumnSet:
                    SetProperties(tSqlObject);
                    break;

                case dac.ColumnType.ComputedColumn:
                    // use the referenced column - this works for simple view referenced
                    // column but not for a computed expression like [Name] = [FirstName] + ' ' + [LastName]
                    var referenced = tSqlObject.GetReferenced().ToArray();
                    if (referenced.Length == 1)
                    {
                        var tSqlObjectReferenced = referenced[0];
                        SetProperties(tSqlObjectReferenced);
                    }
                    else
                    {
                        // TODO: how to get and evaluate the expression?
                    }
                    break;
                }
            }
        }
示例#7
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Parameter" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the parameter.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Parameter(dac.TSqlObject tSqlObject, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
        {
            this.Name     = tSqlObject.Name.Parts.Last().Trim('@');
            this.IsOutput = dac.Parameter.IsOutput.GetValue <bool>(tSqlObject);
            var dataType = tSqlObject.GetReferenced(dac.Parameter.DataType).ToList().FirstOrDefault();

            if (dataType == null)
            {
                this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.DotNetFrameworkType, "Object");
            }
            else if (dataType.ObjectType.Name == "TableType")
            {
                this.TableValue = new Table(dataType, primaryKeys, foreignKeys);
            }
            else
            {
                var sqlDataTypeName = dataType.Name.Parts.Last();
                this.DataTypes = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
            }
        }
示例#8
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Table" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the table.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Table(dac.TSqlObject tSqlObject)
        {
            if (tSqlObject == null)
            {
                throw new ArgumentNullException(nameof(tSqlObject));
            }

            // Get the name.
            this.Name = tSqlObject.Name.Parts.Last();

            // Get the columns
            var columns    = new List <Column>();
            var sqlColumns = tSqlObject.ObjectType.Name == "TableType" ? tSqlObject.GetReferenced(dac.TableType.Columns) : tSqlObject.GetReferenced(dac.Table.Columns);

            foreach (var sqlColumn in sqlColumns)
            {
                var column = new Column(sqlColumn);
                columns.Add(column);
            }

            this.Columns = columns;
        }
示例#9
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Procedure"/> class.
        /// </summary>
        /// <param name="prefix">The prefix used on stored procedure names.</param>
        public Procedure(dac.TSqlObject tSqlObject)
        {
            this.Name   = tSqlObject.Name.Parts.Last();
            this.Schema = tSqlObject.Name.Parts.First();

            var parser = new TSql150Parser(false);
            IList <ParseError> errors;
            var frag          = parser.Parse(new StringReader(tSqlObject.GetScript()), out errors);
            var selectVisitor = new SqlSharpener.SelectVisitor();

            frag.Accept(selectVisitor);

            var depends = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
                          .Where(x => x.ObjectType.Name == "Column")
                          .ToList();

            if (depends.Count() > 0)
            {
                var bodyColumnTypes = depends
                                      .GroupBy(bd => string.Join(".", bd.Name.Parts))
                                      .Select(grp => grp.First())
                                      .ToDictionary(
                    key => string.Join(".", key.Name.Parts),
                    val => new DataType
                {
                    Map      = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).FirstOrDefault()?.Name.Parts.Last()),
                    Nullable = dac.Column.Nullable.GetValue <bool>(val)
                },
                    StringComparer.InvariantCultureIgnoreCase);

                var unions  = selectVisitor.Nodes.OfType <BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
                var selects = selectVisitor.Nodes.OfType <QuerySpecification>().Concat(unions);

                this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
            }
        }
示例#10
0
        /// <summary>
        /// Initializes a new instance of the <see cref="Column" /> class.
        /// </summary>
        /// <param name="tSqlObject">The TSqlObject representing the column.</param>
        /// <param name="tSqlTable">The table or view this column belongs to.</param>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="foreignKeys">The foreign keys.</param>
        public Column(dac.TSqlObject tSqlObject, dac.TSqlObject tSqlTable, IEnumerable <dac.TSqlObject> primaryKeys, IDictionary <dac.TSqlObject, IEnumerable <ForeignKeyConstraintDefinition> > foreignKeys)
        {
            this.Name = tSqlObject.Name.Parts.Last();
            var fullName = string.Join(".", tSqlObject.Name.Parts);

            this.IsPrimaryKey = primaryKeys.Any(p => string.Join(".", p.Name.Parts) == fullName);

            // Get relationships where this column is the child.
            IEnumerable <ForeignKeyConstraintDefinition> myForeignKeys;

            foreignKeys.TryGetValue(tSqlTable, out myForeignKeys);
            myForeignKeys            = myForeignKeys ?? Enumerable.Empty <ForeignKeyConstraintDefinition>();
            this.ParentRelationships = from f in myForeignKeys
                                       where f.Columns.Any(c => c.Value == this.Name)
                                       select new RelationshipIdentifier
            {
                TableOrView = f.ReferenceTableName.BaseIdentifier != null ? f.ReferenceTableName.BaseIdentifier.Value : null,
                Schema      = f.ReferenceTableName.SchemaIdentifier != null ? f.ReferenceTableName.SchemaIdentifier.Value : null,
                Database    = f.ReferenceTableName.DatabaseIdentifier != null ? f.ReferenceTableName.DatabaseIdentifier.Value : null,
                Columns     = f.ReferencedTableColumns.Select(c => c.Value)
            };
            this.IsForeignKey = this.ParentRelationships.Any();

            // Get relationships where this column is the parent.
            var childTables = foreignKeys.Where(f => f.Value.Any(v =>
                                                                 v.ReferenceTableName.BaseIdentifier.Value == tSqlTable.Name.Parts.Last() &&
                                                                 v.ReferencedTableColumns.Any(c => c.Value == this.Name)));

            this.ChildRelationships = from t in childTables
                                      from r in t.Value
                                      let tableParts = t.Key.Name.Parts.Count()
                                                       select new RelationshipIdentifier
            {
                TableOrView = t.Key.Name.Parts.Last(),
                Schema      = tableParts > 1 ? t.Key.Name.Parts.ElementAt(tableParts - 2) : null,
                Database    = tableParts > 2 ? t.Key.Name.Parts.ElementAt(tableParts - 3) : null,
                Columns     = r.Columns.Select(c => c.Value)
            };

            if (tSqlObject.ObjectType.Name == "TableTypeColumn")
            {
                var sqlDataTypeName = tSqlObject.GetReferenced(dac.TableTypeColumn.DataType).ToList().First().Name.Parts.Last();
                this.DataTypes  = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, sqlDataTypeName);
                this.IsIdentity = dac.TableTypeColumn.IsIdentity.GetValue <bool>(tSqlObject);
                this.IsNullable = dac.TableTypeColumn.Nullable.GetValue <bool>(tSqlObject);
                this.Precision  = dac.TableTypeColumn.Precision.GetValue <int>(tSqlObject);
                this.Scale      = dac.TableTypeColumn.Scale.GetValue <int>(tSqlObject);
                this.Length     = dac.TableTypeColumn.Length.GetValue <int>(tSqlObject);
            }
            else
            {
                dac.ColumnType metaType = tSqlObject.GetMetadata <dac.ColumnType>(dac.Column.ColumnType);

                switch (metaType)
                {
                case dac.ColumnType.Column:
                case dac.ColumnType.ColumnSet:
                    SetProperties(tSqlObject);
                    break;

                case dac.ColumnType.ComputedColumn:
                    // use the referenced column - this works for simple view referenced
                    // column but not for a computed expression like [Name] = [FirstName] + ' ' + [LastName]
                    var referenced = tSqlObject.GetReferenced().ToArray();
                    if (referenced.Length == 1)
                    {
                        var tSqlObjectReferenced = referenced[0];
                        SetProperties(tSqlObjectReferenced);
                    }
                    else
                    {
                        // TODO: how to get and evaluate the expression?
                    }
                    break;
                }
            }
        }