/// <summary>
        /// Internal API constructor intended for Implementation classes, instead use <see cref="DiscoveredTable.DiscoverRelationships"/> instead.
        /// </summary>
        /// <param name="fkName"></param>
        /// <param name="pkTable"></param>
        /// <param name="fkTable"></param>
        /// <param name="deleteRule"></param>
        public DiscoveredRelationship(string fkName, DiscoveredTable pkTable, DiscoveredTable fkTable, CascadeRule deleteRule)
        {
            Name            = fkName;
            PrimaryKeyTable = pkTable;
            ForeignKeyTable = fkTable;
            CascadeDelete   = deleteRule;

            Keys = new Dictionary <DiscoveredColumn, DiscoveredColumn>();
        }
Exemplo n.º 2
0
        public OperationBuilderSurface <CreateForeignKeyConstraintOperation> ForeignKey(string name,
                                                                                        Func <TColumns, IEnumerable <ConstraintColumnDefinition> > dependentColumns, string principalTable,
                                                                                        string principalSchema = null, string principalCatalog = null,
                                                                                        IEnumerable <string> principalColumns = null,
                                                                                        CascadeRule onUpdate = CascadeRule.None,
                                                                                        CascadeRule onDelete = CascadeRule.None)
        {
            var constraintColumnDefinitions = dependentColumns(_instance).ToList();

            principalColumns = principalColumns ?? constraintColumnDefinitions.Select(x => x.Column);
            var op = new CreateForeignKeyConstraintOperation
            {
                Name           = _operation.Name.MakeSubObjectName(name),
                PrincipalTable = new ObjectName {
                    Catalog = principalCatalog ?? _operation.Name.Catalog, Schema = principalSchema ?? _operation.Name.Schema, Name = principalTable
                },

                OnDelete         = onDelete,
                OnUpdate         = onUpdate,
                DependentColumns = constraintColumnDefinitions,
                PrincipalColumns = principalColumns.Select(x => new ConstraintColumnDefinition {
                    Column = x
                }).ToList(),
            };

            _operation.Constraints.Add(op);
            return(new OperationBuilderSurface <CreateForeignKeyConstraintOperation>(op));
        }
Exemplo n.º 3
0
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
                                                                       IManagedTransaction transaction = null)
        {
            string sql = $@"select c.constraint_name
    , x.table_schema as foreign_table_schema
    , x.table_name as foreign_table_name
    , x.column_name as foreign_column_name
    , y.table_schema 
    , y.table_name 
    , y.column_name 
    , delete_rule
from information_schema.referential_constraints c
join information_schema.key_column_usage x
    on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
    on y.ordinal_position = x.position_in_unique_constraint
    and y.constraint_name = c.unique_constraint_name
where 
    y.table_name='{table.GetRuntimeName()}' AND
    y.table_schema='{table.Schema??PostgreSqlSyntaxHelper.DefaultPostgresSchema}'
order by c.constraint_name, x.ordinal_position";


            Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();

            using (var cmd = table.GetCommand(sql, connection, transaction?.Transaction))
            {
                //fill data table to avoid multiple active readers
                using (DataTable dt = new DataTable())
                {
                    using (var da = new NpgsqlDataAdapter((NpgsqlCommand)cmd))
                        da.Fill(dt);

                    foreach (DataRow r in dt.Rows)
                    {
                        var fkName = r["constraint_name"].ToString();

                        DiscoveredRelationship current;

                        //could be a 2+ columns foreign key?
                        if (toReturn.ContainsKey(fkName))
                        {
                            current = toReturn[fkName];
                        }
                        else
                        {
                            var pkDb        = table.Database.GetRuntimeName();
                            var pkSchema    = r["table_schema"].ToString();
                            var pkTableName = r["table_name"].ToString();

                            var fkDb        = pkDb;
                            var fkSchema    = r["foreign_table_schema"].ToString();
                            var fkTableName = r["foreign_table_name"].ToString();

                            var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName, pkSchema);
                            var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName, fkSchema);

                            CascadeRule deleteRule = CascadeRule.Unknown;

                            var deleteRuleString = r["delete_rule"].ToString();

                            if (deleteRuleString == "CASCADE")
                            {
                                deleteRule = CascadeRule.Delete;
                            }
                            else if (deleteRuleString == "NO ACTION")
                            {
                                deleteRule = CascadeRule.NoAction;
                            }
                            else if (deleteRuleString == "RESTRICT")
                            {
                                deleteRule = CascadeRule.NoAction;
                            }
                            else if (deleteRuleString == "SET NULL")
                            {
                                deleteRule = CascadeRule.SetNull;
                            }
                            else if (deleteRuleString == "SET DEFAULT")
                            {
                                deleteRule = CascadeRule.SetDefault;
                            }

                            current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                            toReturn.Add(current.Name, current);
                        }

                        current.AddKeys(r["column_name"].ToString(), r["foreign_column_name"].ToString(), transaction);
                    }
                }
            }

            return(toReturn.Values.ToArray());
        }
Exemplo n.º 4
0
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null)
        {
            var toReturn = new Dictionary <string, DiscoveredRelationship>();

            string sql = "exec sp_fkeys @pktable_name = @table, @pktable_qualifier=@database, @pktable_owner=@schema";

            using (DbCommand cmd = table.GetCommand(sql, connection))
            {
                if (transaction != null)
                {
                    cmd.Transaction = transaction.Transaction;
                }

                var p = cmd.CreateParameter();
                p.ParameterName = "@table";
                p.Value         = table.GetRuntimeName();
                p.DbType        = DbType.String;
                cmd.Parameters.Add(p);

                p = cmd.CreateParameter();
                p.ParameterName = "@schema";
                p.Value         = table.Schema ?? "dbo";
                p.DbType        = DbType.String;
                cmd.Parameters.Add(p);

                p = cmd.CreateParameter();
                p.ParameterName = "@database";
                p.Value         = table.Database.GetRuntimeName();
                p.DbType        = DbType.String;
                cmd.Parameters.Add(p);

                using (DataTable dt = new DataTable())
                {
                    var da = table.Database.Server.GetDataAdapter(cmd);
                    da.Fill(dt);

                    foreach (DataRow r in dt.Rows)
                    {
                        var fkName = r["FK_NAME"].ToString();

                        DiscoveredRelationship current;

                        //could be a 2+ columns foreign key?
                        if (toReturn.ContainsKey(fkName))
                        {
                            current = toReturn[fkName];
                        }
                        else
                        {
                            var pkdb        = r["PKTABLE_QUALIFIER"].ToString();
                            var pkschema    = r["PKTABLE_OWNER"].ToString();
                            var pktableName = r["PKTABLE_NAME"].ToString();

                            var pktable = table.Database.Server.ExpectDatabase(pkdb).ExpectTable(pktableName, pkschema);

                            var fkdb        = r["FKTABLE_QUALIFIER"].ToString();
                            var fkschema    = r["FKTABLE_OWNER"].ToString();
                            var fktableName = r["FKTABLE_NAME"].ToString();

                            var fktable = table.Database.Server.ExpectDatabase(fkdb).ExpectTable(fktableName, fkschema);

                            var         deleteRuleInt = Convert.ToInt32(r["DELETE_RULE"]);
                            CascadeRule deleteRule    = CascadeRule.Unknown;


                            if (deleteRuleInt == 0)
                            {
                                deleteRule = CascadeRule.Delete;
                            }
                            else if (deleteRuleInt == 1)
                            {
                                deleteRule = CascadeRule.NoAction;
                            }
                            else if (deleteRuleInt == 2)
                            {
                                deleteRule = CascadeRule.SetNull;
                            }
                            else if (deleteRuleInt == 3)
                            {
                                deleteRule = CascadeRule.SetDefault;
                            }


                            /*
                             * https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql?view=sql-server-2017
                             *
                             * 0=CASCADE changes to foreign key.
                             * 1=NO ACTION changes if foreign key is present.
                             * 2 = set null
                             * 3 = set default*/

                            current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                            toReturn.Add(current.Name, current);
                        }

                        current.AddKeys(r["PKCOLUMN_NAME"].ToString(), r["FKCOLUMN_NAME"].ToString(), transaction);
                    }
                }
            }

            return(toReturn.Values.ToArray());
        }
Exemplo n.º 5
0
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null)
        {
            string sql = @"SELECT 
u.CONSTRAINT_NAME,
u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME,
u.REFERENCED_TABLE_SCHEMA,
u.REFERENCED_TABLE_NAME,
u.REFERENCED_COLUMN_NAME,
c.DELETE_RULE
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME
WHERE
  u.REFERENCED_TABLE_SCHEMA = @db AND
  u.REFERENCED_TABLE_NAME = @tbl";

            var cmd = new MySqlCommand(sql, (MySqlConnection)connection);

            var p = new MySqlParameter("@db", MySqlDbType.String);

            p.Value = table.Database.GetRuntimeName();
            cmd.Parameters.Add(p);

            p       = new MySqlParameter("@tbl", MySqlDbType.String);
            p.Value = table.GetRuntimeName();
            cmd.Parameters.Add(p);

            Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();

            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    var fkName = r["CONSTRAINT_NAME"].ToString();

                    DiscoveredRelationship current;

                    //could be a 2+ columns foreign key?
                    if (toReturn.ContainsKey(fkName))
                    {
                        current = toReturn[fkName];
                    }
                    else
                    {
                        var pkDb        = r["REFERENCED_TABLE_SCHEMA"].ToString();
                        var pkTableName = r["REFERENCED_TABLE_NAME"].ToString();

                        var fkDb        = r["TABLE_SCHEMA"].ToString();
                        var fkTableName = r["TABLE_NAME"].ToString();

                        var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
                        var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);

                        //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
                        var deleteRuleString = r["DELETE_RULE"].ToString();

                        CascadeRule deleteRule = CascadeRule.Unknown;

                        if (deleteRuleString == "CASCADE")
                        {
                            deleteRule = CascadeRule.Delete;
                        }
                        else if (deleteRuleString == "NO ACTION")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "RESTRICT")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "SET NULL")
                        {
                            deleteRule = CascadeRule.SetNull;
                        }
                        else if (deleteRuleString == "SET DEFAULT")
                        {
                            deleteRule = CascadeRule.SetDefault;
                        }

                        current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                        toReturn.Add(current.Name, current);
                    }

                    current.AddKeys(r["REFERENCED_COLUMN_NAME"].ToString(), r["COLUMN_NAME"].ToString(), transaction);
                }
            }

            return(toReturn.Values.ToArray());
        }
Exemplo n.º 6
0
        public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
                                                                       IManagedTransaction transaction = null)
        {
            string sql = @"
SELECT a.table_name
     , a.column_name
     , a.constraint_name
     , c.owner
     , c.delete_rule
     , c.r_owner
     , c_pk.table_name      r_table_name
     , c_pk.constraint_name r_pk
     , cc_pk.column_name    r_column_name
  FROM all_cons_columns a
  JOIN all_constraints  c       ON (a.owner                 = c.owner                   AND a.constraint_name   = c.constraint_name     )
  JOIN all_constraints  c_pk    ON (c.r_owner               = c_pk.owner                AND c.r_constraint_name = c_pk.constraint_name  )
  JOIN all_cons_columns cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            AND cc_pk.position = a.position)
 WHERE c.constraint_type = 'R'
AND  UPPER(c.r_owner) =  UPPER(:DatabaseName)
AND  UPPER(c_pk.table_name) =  UPPER(:TableName)";


            var cmd = new OracleCommand(sql, (OracleConnection)connection);

            var p = new OracleParameter(":DatabaseName", OracleDbType.Varchar2);

            p.Value = table.Database.GetRuntimeName();
            cmd.Parameters.Add(p);

            p       = new OracleParameter(":TableName", OracleDbType.Varchar2);
            p.Value = table.GetRuntimeName();
            cmd.Parameters.Add(p);

            Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();

            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    var fkName = r["constraint_name"].ToString();

                    DiscoveredRelationship current;

                    //could be a 2+ columns foreign key?
                    if (toReturn.ContainsKey(fkName))
                    {
                        current = toReturn[fkName];
                    }
                    else
                    {
                        var pkDb        = r["r_owner"].ToString();
                        var pkTableName = r["r_table_name"].ToString();

                        var fkDb        = r["owner"].ToString();
                        var fkTableName = r["table_name"].ToString();

                        var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
                        var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);

                        //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
                        var deleteRuleString = r["delete_rule"].ToString();

                        CascadeRule deleteRule = CascadeRule.Unknown;

                        if (deleteRuleString == "CASCADE")
                        {
                            deleteRule = CascadeRule.Delete;
                        }
                        else if (deleteRuleString == "NO ACTION")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "RESTRICT")
                        {
                            deleteRule = CascadeRule.NoAction;
                        }
                        else if (deleteRuleString == "SET NULL")
                        {
                            deleteRule = CascadeRule.SetNull;
                        }
                        else if (deleteRuleString == "SET DEFAULT")
                        {
                            deleteRule = CascadeRule.SetDefault;
                        }

                        current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                        toReturn.Add(current.Name, current);
                    }

                    current.AddKeys(r["r_column_name"].ToString(), r["column_name"].ToString(), transaction);
                }
            }

            return(toReturn.Values.ToArray());
        }
 public static OperationBuilderSurface <CreateForeignKeyConstraintOperation> AddForeignKey(this IMigrationBuilder builder,
                                                                                           string keyName,
                                                                                           string dependentTable,
                                                                                           string dependentColumn,
                                                                                           string principalTable,
                                                                                           string dependentSchema  = null,
                                                                                           string dependentCatalog = null,
                                                                                           string principalCatalog = null,
                                                                                           string principalSchema  = null,
                                                                                           string principalColumn  = null,
                                                                                           CascadeRule onUpdate    = CascadeRule.None,
                                                                                           CascadeRule onDelete    = CascadeRule.None)
 {
     return(AddForeignKey(builder, keyName, dependentTable, new[] { dependentColumn }, principalTable, new [] { principalColumn }, dependentSchema, dependentCatalog, principalCatalog, principalSchema));
 }
        public static OperationBuilderSurface <CreateForeignKeyConstraintOperation> AddForeignKey(this IMigrationBuilder builder,
                                                                                                  string keyName,
                                                                                                  string dependentTable,
                                                                                                  IEnumerable <string> dependentColumns,
                                                                                                  string principalTable,
                                                                                                  IEnumerable <string> principalColumns,
                                                                                                  string dependentSchema  = null,
                                                                                                  string dependentCatalog = null,
                                                                                                  string principalCatalog = null,
                                                                                                  string principalSchema  = null,

                                                                                                  CascadeRule onUpdate = CascadeRule.None,
                                                                                                  CascadeRule onDelete = CascadeRule.None)
        {
            var op = new CreateForeignKeyConstraintOperation
            {
                Name           = new SubObjectName(dependentCatalog, dependentSchema, dependentTable, keyName),
                PrincipalTable = new ObjectName {
                    Catalog = principalCatalog ?? dependentCatalog, Schema = principalSchema ?? dependentSchema, Name = principalTable
                },

                OnDelete         = onDelete,
                OnUpdate         = onUpdate,
                DependentColumns = dependentColumns.Select(x => new ConstraintColumnDefinition {
                    Column = x
                }).ToList(),
                PrincipalColumns = principalColumns.Select(x => new ConstraintColumnDefinition {
                    Column = x
                }).ToList(),
            };

            builder.AddOperation(op);
            return(new OperationBuilderSurface <CreateForeignKeyConstraintOperation>(op));
        }