Exemple #1
0
        public void GetForeignKeys(SqlTable table)
        {
            var rows = _queryResult
                       .Where(r => DataDefinitionReaderHelper.SchemaAndTableNameEquals(r, table, "OWNER", "TABLE_NAME") && r.GetAs <string>("CONSTRAINT_TYPE") == "R");

            foreach (var row in rows)
            {
                var fkColumn = table.Columns[row.GetAs <string>("COLUMN_NAME")];

                // TODO how to query reference in a nother schema?
                var referencedSchema             = row.GetAs <string>("R_OWNER");
                var referencedTable              = row.GetAs <string>("REF_TABLE_NAME");
                var referencedSchemaAndTableName = new SchemaAndTableName(referencedSchema, referencedTable);
                var referencedColumn             = row.GetAs <string>("REF_COLUMN_NAME");
                var fkName = row.GetAs <string>("CONSTRAINT_NAME");

                var referencedSqlTableSchemaAndTableNameAsToStore = GenericDataDefinitionReader.GetSchemaAndTableNameAsToStore(referencedSchemaAndTableName, Executer.Generator.Context);

                var referencedSqlTable = table.DatabaseDefinition.GetTable(referencedSqlTableSchemaAndTableNameAsToStore);

                if (!table.Properties.OfType <ForeignKey>().Any(fk => fk.SqlTable.SchemaAndTableName == table.SchemaAndTableName && fk.ReferredTable.SchemaAndTableName == referencedSqlTableSchemaAndTableNameAsToStore && fk.Name == fkName))
                {
                    table.Properties.Add(new ForeignKey(table, referencedSqlTable, fkName));
                }

                var referencedSqlColumn = referencedSqlTable[referencedColumn];

                var fk = table.Properties.OfType <ForeignKey>().First(fk1 => fk1.ReferredTable.SchemaAndTableName == referencedSqlTableSchemaAndTableNameAsToStore && fk1.Name == fkName);
                fk.ForeignKeyColumns.Add(new ForeignKeyColumnMap(fkColumn, referencedSqlColumn));
            }
        }
        public void GetForeignKeys(SqlTable table)
        {
            var rows = QueryResult
                       .Where(r => DataDefinitionReaderHelper.SchemaAndTableNameEquals(r, table, "FK_CONSTRAINT_SCHEMA", "FK_TABLE_NAME"))
                       .OrderBy(row => row.GetAs <string>("FK_CONSTRAINT_NAME"))
                       .ThenBy(row => row.GetAs <int>("FK_ORDINAL_POSITION"));

            foreach (var row in rows)
            {
                var fkColumn = table.Columns[row.GetAs <string>("FK_COLUMN_NAME")];

                var referencedSchema             = row.GetAs <string>("REFERENCED_CONSTRAINT_SCHEMA");
                var referencedTable              = row.GetAs <string>("REFERENCED_TABLE_NAME");
                var referencedSchemaAndTableName = new SchemaAndTableName(referencedSchema, referencedTable);
                var referencedColumn             = row.GetAs <string>("REFERENCED_COLUMN_NAME");
                var fkName = row.GetAs <string>("FK_CONSTRAINT_NAME");

                var referencedSqlTableSchemaAndTableNameAsToStore = GenericDataDefinitionReader.GetSchemaAndTableNameAsToStore(referencedSchemaAndTableName, Executer.Generator.Context);

                var referencedSqlTable = table.DatabaseDefinition.GetTable(referencedSqlTableSchemaAndTableNameAsToStore);

                ForeignKey fk;

                if (row.GetAs <int>("FK_ORDINAL_POSITION") == 1)
                {
                    fk = new ForeignKey(table, referencedSqlTable, fkName);
                    table.Properties.Add(fk);

                    if (row.GetAs <int>("IsNotTrusted") == 1)
                    {
                        fk.SqlEngineVersionSpecificProperties.Add(new SqlEngineVersionSpecificProperty(MsSqlVersion.MsSql2016, "Nocheck", "true"));
                    }
                    else if (row.GetAs <int>("IsNotTrusted") == 0)
                    {
                        fk.SqlEngineVersionSpecificProperties.Add(new SqlEngineVersionSpecificProperty(MsSqlVersion.MsSql2016, "Nocheck", "false"));
                    }
                }
                else
                {
                    fk = table.Properties.OfType <ForeignKey>().First(fk1 => fk1.ReferredTable.SchemaAndTableName == referencedSqlTableSchemaAndTableNameAsToStore && fk1.Name == fkName);
                }

                var referencedSqlColumn = referencedSqlTable[referencedColumn];

                fk.ForeignKeyColumns.Add(new ForeignKeyColumnMap(fkColumn, referencedSqlColumn));
            }
        }