From 8782469b789585d3f0c3a642f0bb9519816f6b11 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Thu, 7 May 2020 17:13:35 -0400
Subject: Warn when sorted_tables is not actually sorting

A warning is emitted when making use of the :attr:`.MetaData.sorted_tables`
attribute as well as the :func:`_schema.sort_tables` function, and the
given tables cannot be correctly sorted due to a cyclic dependency between
foreign key constraints. In this case, the functions will no longer sort
the involved tables by foreign key, and a warning will be emitted. Other
tables that are not part of the cycle will still be returned in dependency
order. Previously, the sorted_table routines would return a collection that
would unconditionally omit all foreign keys when a cycle was detected, and
no warning was emitted.

Fixes: #5316
Change-Id: I14f72ccf39cb568bc77e8da16d0685718b2b9960
---
 test/sql/test_metadata.py | 87 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 87 insertions(+)

(limited to 'test/sql')

diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 36f308073..07f5b80db 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -611,6 +611,93 @@ class MetaDataTest(fixtures.TestBase, ComparesTables):
         a.add_is_dependent_on(b)
         eq_(meta.sorted_tables, [b, c, d, a, e])
 
+    def test_fks_deterministic_order(self):
+        meta = MetaData()
+        a = Table("a", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        b = Table("b", meta, Column("foo", Integer))
+        c = Table("c", meta, Column("foo", Integer))
+        d = Table("d", meta, Column("foo", Integer))
+        e = Table("e", meta, Column("foo", Integer, ForeignKey("c.foo")))
+
+        eq_(meta.sorted_tables, [b, c, d, a, e])
+
+    def test_cycles_fks_warning_one(self):
+        meta = MetaData()
+        a = Table("a", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        b = Table("b", meta, Column("foo", Integer, ForeignKey("d.foo")))
+        c = Table("c", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        d = Table("d", meta, Column("foo", Integer, ForeignKey("c.foo")))
+        e = Table("e", meta, Column("foo", Integer))
+
+        with testing.expect_warnings(
+            "Cannot correctly sort tables; there are unresolvable cycles "
+            'between tables "b, c, d", which is usually caused by mutually '
+            "dependent foreign key constraints.  "
+            "Foreign key constraints involving these tables will not be "
+            "considered"
+        ):
+            eq_(meta.sorted_tables, [b, c, d, e, a])
+
+    def test_cycles_fks_warning_two(self):
+        meta = MetaData()
+        a = Table("a", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        b = Table("b", meta, Column("foo", Integer, ForeignKey("a.foo")))
+        c = Table("c", meta, Column("foo", Integer, ForeignKey("e.foo")))
+        d = Table("d", meta, Column("foo", Integer))
+        e = Table("e", meta, Column("foo", Integer, ForeignKey("d.foo")))
+
+        with testing.expect_warnings(
+            "Cannot correctly sort tables; there are unresolvable cycles "
+            'between tables "a, b", which is usually caused by mutually '
+            "dependent foreign key constraints.  "
+            "Foreign key constraints involving these tables will not be "
+            "considered"
+        ):
+            eq_(meta.sorted_tables, [a, b, d, e, c])
+
+    def test_cycles_fks_fks_delivered_separately(self):
+        meta = MetaData()
+        a = Table("a", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        b = Table("b", meta, Column("foo", Integer, ForeignKey("a.foo")))
+        c = Table("c", meta, Column("foo", Integer, ForeignKey("e.foo")))
+        d = Table("d", meta, Column("foo", Integer))
+        e = Table("e", meta, Column("foo", Integer, ForeignKey("d.foo")))
+
+        results = schema.sort_tables_and_constraints(
+            sorted(meta.tables.values(), key=lambda t: t.key)
+        )
+        results[-1] = (None, set(results[-1][-1]))
+        eq_(
+            results,
+            [
+                (a, set()),
+                (b, set()),
+                (d, {fk.constraint for fk in d.foreign_keys}),
+                (e, {fk.constraint for fk in e.foreign_keys}),
+                (c, {fk.constraint for fk in c.foreign_keys}),
+                (
+                    None,
+                    {fk.constraint for fk in a.foreign_keys}.union(
+                        fk.constraint for fk in b.foreign_keys
+                    ),
+                ),
+            ],
+        )
+
+    def test_cycles_fks_usealter(self):
+        meta = MetaData()
+        a = Table("a", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        b = Table(
+            "b",
+            meta,
+            Column("foo", Integer, ForeignKey("d.foo", use_alter=True)),
+        )
+        c = Table("c", meta, Column("foo", Integer, ForeignKey("b.foo")))
+        d = Table("d", meta, Column("foo", Integer, ForeignKey("c.foo")))
+        e = Table("e", meta, Column("foo", Integer))
+
+        eq_(meta.sorted_tables, [b, e, a, c, d])
+
     def test_nonexistent(self):
         assert_raises(
             tsa.exc.NoSuchTableError,
-- 
cgit v1.2.1