C# TSqlObject.GetReferencing方法代码示例

        protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
        {
            //Get the indexes of the table
            var indexes = table.GetReferencing(Index.IndexedObject);
            if (indexes.Count() == 0)
                yield return new SqlRuleProblem(string.Format("The info table {0} has no index", name), table);

            //Ensure that one of them is effecively a clustered index
            var clusteredIndex = indexes.FirstOrDefault(i => i.GetProperty<bool>(Index.Clustered));
            if (clusteredIndex == null)
                yield return new SqlRuleProblem(string.Format("No clustered index for the info table {0}", name), table);
            else
            {
                //Ensure that this index is effectively unique
                var uniqueClusturedIndex = indexes.FirstOrDefault(i => i.GetProperty<bool>(Index.Clustered) && i.GetProperty<bool>(Index.Unique));
                if (uniqueClusturedIndex == null)
                    yield return new SqlRuleProblem(string.Format("Clustured index for the info table {0} is not unique ", name), table);
                else
                {
                    //Ensure that the clustered index is active only on the identity column
                    var columns = uniqueClusturedIndex.GetReferenced(Index.Columns);
                    if (columns.Count() > 1)
                        yield return new SqlRuleProblem(string.Format("The info table {0} has a clustered index but this index has more than one column.", name), table);

                    if (columns.Count(c => c.GetProperty<bool>(Column.IsIdentity)) == 0)
                        yield return new SqlRuleProblem(string.Format("The info table {0} has a clustered index but this index doesn't include the identity column.", name), table);
                }
            }
        }

        protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
        {
            //Get the indexes of the table
            var indexes = table.GetReferencing(Index.IndexedObject);
            if (indexes.Count() == 0)
                yield return new SqlRuleProblem(string.Format("The anchor table {0} has no index", name), table);

            //Ensure that one of them is effecively not a clustered index
            var nonClusturedIndexes = indexes.Where(i => !i.GetProperty<bool>(Index.Clustered) && i.GetProperty<bool>(Index.Unique));
            if (nonClusturedIndexes == null)
                yield return new SqlRuleProblem(string.Format("No existing non-clustered unique index for the anchor table {0}", name), table);
            else
            {
                //Ensure that at least one of them is name BK
                var bkIndexes = nonClusturedIndexes.Where(i => i.Name.Parts.Last().StartsWith(Configuration.Anchor.BusinessKeyPrefix));
                if (bkIndexes.Count()==0)
                    yield return new SqlRuleProblem(string.Format("None of the non-clustered unique indexes for the anchor table {0} are starting by BK_", name), table);
                else
                {
                    foreach (var bkIndex in bkIndexes)
                    {
                        //Ensure that the unique index is not active on the identity column
                        var columns = bkIndex.GetReferenced(Index.Columns).Where(c => c.GetProperty<bool>(Column.IsIdentity));
                        if (columns.Count()>0)
                            yield return new SqlRuleProblem(string.Format("The business key (non-clustered unique index) {1} for the anchor table {0} contains the identity column.", name, bkIndex.Name), table);

                        //By default SQL Server will include the indentity column (because this column should be the clustered index)
                        var includedColumns = bkIndex.GetReferenced(Index.IncludedColumns).Where(c => c.GetProperty<bool>(Column.IsIdentity));
                        if (includedColumns.Count() > 0)
                            yield return new SqlRuleProblem(string.Format("The business key (non-clustered unique index) {1} for the anchor table {0} includes the identity column.", name, bkIndex.Name), table);
                    }
                }
            }
        }

        protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
        {
            var indexes = table.GetReferencing(Index.IndexedObject);
            var firstTimeIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).First().Name.Parts.Last()
                                                    == Configuration.Link.IsFirst);
            if (firstTimeIndexes.Count() == 0)
                yield return new SqlRuleProblem(string.Format("No index where is-first column is '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);

            foreach (var fIndex in firstTimeIndexes)
            {
                var indexColumns = fIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key);
                var includedColumns = fIndex.GetReferenced(Index.IncludedColumns);
                var allColumns = indexColumns.Union(includedColumns);

                if (allColumns.Count() == 0)
                    yield return new SqlRuleProblem(string.Format("The time-based index {0} for link table {1} has no additional or included columns", fIndex.Name, name), table);
                yield return new SqlRuleProblem(string.Format("The time-based index {0} for link table {1} has no additional or included columns", fIndex.Name, name), table);
            }
        }

        protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
        {
            var indexes = table.GetReferencing(Index.IndexedObject);
            var timeBasedIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).First().Name.Parts.Last()
                                                    == Configuration.TimeBased.Key);
            if (timeBasedIndexes.Count() == 0)
                yield return new SqlRuleProblem(string.Format("No index where first column is '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);

            foreach (var tbIndex in timeBasedIndexes)
            {
                var unexpectedColumns = tbIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key);
                if (unexpectedColumns.Count()>0)
                {
                    yield return new SqlRuleProblem(
                            string.Format(
                                    "The time-based index '{0}' for link table '{1}' contains additional columns. Unexpected column{2} '{3}'"
                                    , tbIndex.Name
                                    , name
                                    , (unexpectedColumns.Count() == 1) ? " is " : "s are  "
                                    , string.Join("', '", unexpectedColumns.Select(c => c.Name.Parts.Last()))
                            ), table);
                }

                var idColumns = table.GetReferenced(Table.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key && c.Name.Parts.Last().EndsWith("Id"));
                var includedColumns = tbIndex.GetReferenced(Index.IncludedColumns);

                var missingColumns = idColumns.Except(includedColumns);
                if (missingColumns.Count()>0)
                {
                    yield return new SqlRuleProblem(
                            string.Format(
                                    "The time-based index '{0}' for link table '{1}' doesn't include some Id columns. Missing column{2} '{3}'"
                                    , tbIndex.Name
                                    , name
                                    , (missingColumns.Count() == 1) ? " is " : "s are  "
                                    , string.Join("', '", missingColumns.Select(c => c.Name.Parts.Last()))
                            ), table);
                }

            }
        }

        protected IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table, string columnName)
        {
            var indexes = table.GetReferencing(Index.IndexedObject);
            var lastIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).Last().Name.Parts.Last()
                                                    == columnName);
            if (lastIndexes.Count() == 0)
                yield return new SqlRuleProblem(string.Format("No index on the column '{0}' for link table {1}", columnName, name), table);

            var filteredIndexes = lastIndexes.Where(i => i.GetProperty<bool>(Index.FilterPredicate));
            if (filteredIndexes.Count() == 0)
                yield return new SqlRuleProblem(string.Format("An index exists on the column '{0}' for link table {0} but this index is not filtered.", columnName, name), table);

            foreach (var lastIndex in lastIndexes)
            {
                var indexColumns = lastIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != columnName);
                var includedColumns = lastIndex.GetReferenced(Index.IncludedColumns);
                var allColumns = indexColumns.Union(includedColumns);

                if (allColumns.Count() == 0)
                    yield return new SqlRuleProblem(string.Format("The index {0} for link table {1} has no additional column or included column.", lastIndex.Name, name), table);
            }
        }

        /// <summary>
        /// No Indexes of any kind are allowed on Views that reference a memory optimized table.
        /// </summary>
        private void ValidateViewHasNoIndexes(SqlRuleExecutionContext context, TSqlObject view, TSqlObject table, IList<SqlRuleProblem> problems)
        {
            foreach (TSqlObject index in view.GetReferencing(Index.IndexedObject))
            {
                string description = string.Format(CultureInfo.CurrentCulture,
                    RuleResources.ViewsOnMemoryOptimizedTable_IndexProblemDescription,
                    RuleUtils.GetElementName(context, index),
                    RuleUtils.GetElementName(context, view),
                    RuleUtils.GetElementName(context, table));
                TSqlFragment nameFragment = TsqlScriptDomUtils.LookupSchemaObjectName(index);

                // Note that nameFragment can be null - in this case the index's position information will be used.
                // This is just a little less precise than pointing to the position of the name for that index
                problems.Add(new SqlRuleProblem(description, index, nameFragment));

            }
        }