- restate sort_tables in terms of a more fine grained

sort_tables_and_constraints function.
- The DDL generation system of :meth:`.MetaData.create_all`
and :meth:`.Metadata.drop_all` has been enhanced to in most
cases automatically handle the case of mutually dependent
foreign key constraints; the need for the
:paramref:`.ForeignKeyConstraint.use_alter` flag is greatly
reduced.  The system also works for constraints which aren't given
a name up front; only in the case of DROP is a name required for
at least one of the constraints involved in the cycle.
fixes #3282

1 files changed, 181 insertions, 26 deletions

diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py
index c0b5806ac..604b5efeb 100644
--- a/test/sql/test_constraints.py
+++ b/test/sql/test_constraints.py
@@ -58,8 +58,77 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults):
                         )
         )
 
+    @testing.force_drop_names('a', 'b')
+    def test_fk_cant_drop_cycled_unnamed(self):
+        metadata = MetaData()
+
+        Table("a", metadata,
+              Column('id', Integer, primary_key=True),
+              Column('bid', Integer),
+              ForeignKeyConstraint(["bid"], ["b.id"])
+              )
+        Table(
+            "b", metadata,
+            Column('id', Integer, primary_key=True),
+            Column("aid", Integer),
+            ForeignKeyConstraint(["aid"], ["a.id"]))
+        metadata.create_all(testing.db)
+        if testing.db.dialect.supports_alter:
+            assert_raises_message(
+                exc.CircularDependencyError,
+                "Can't sort tables for DROP; an unresolvable foreign key "
+                "dependency exists between tables: a, b.  Please ensure "
+                "that the ForeignKey and ForeignKeyConstraint objects "
+                "involved in the cycle have names so that they can be "
+                "dropped using DROP CONSTRAINT.",
+                metadata.drop_all, testing.db
+            )
+        else:
+
+            with self.sql_execution_asserter() as asserter:
+                metadata.drop_all(testing.db, checkfirst=False)
+
+            asserter.assert_(
+                AllOf(
+                    CompiledSQL("DROP TABLE a"),
+                    CompiledSQL("DROP TABLE b")
+                )
+            )
+
+    @testing.provide_metadata
+    def test_fk_table_auto_alter_constraint_create(self):
+        metadata = self.metadata
+
+        Table("a", metadata,
+              Column('id', Integer, primary_key=True),
+              Column('bid', Integer),
+              ForeignKeyConstraint(["bid"], ["b.id"])
+              )
+        Table(
+            "b", metadata,
+            Column('id', Integer, primary_key=True),
+            Column("aid", Integer),
+            ForeignKeyConstraint(["aid"], ["a.id"], name="bfk"))
+        self._assert_cyclic_constraint(metadata, auto=True)
+
+    @testing.provide_metadata
+    def test_fk_column_auto_alter_constraint_create(self):
+        metadata = self.metadata
+
+        Table("a", metadata,
+              Column('id', Integer, primary_key=True),
+              Column('bid', Integer, ForeignKey("b.id")),
+              )
+        Table("b", metadata,
+              Column('id', Integer, primary_key=True),
+              Column("aid", Integer,
+                     ForeignKey("a.id", name="bfk")
+                     ),
+              )
+        self._assert_cyclic_constraint(metadata, auto=True)
+
     @testing.provide_metadata
-    def test_cyclic_fk_table_constraint_create(self):
+    def test_fk_table_use_alter_constraint_create(self):
         metadata = self.metadata
 
         Table("a", metadata,
@@ -75,7 +144,7 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults):
         self._assert_cyclic_constraint(metadata)
 
     @testing.provide_metadata
-    def test_cyclic_fk_column_constraint_create(self):
+    def test_fk_column_use_alter_constraint_create(self):
         metadata = self.metadata
 
         Table("a", metadata,
@@ -90,45 +159,104 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults):
               )
         self._assert_cyclic_constraint(metadata)
 
-    def _assert_cyclic_constraint(self, metadata):
-        assertions = [
-            CompiledSQL('CREATE TABLE b ('
+    def _assert_cyclic_constraint(self, metadata, auto=False):
+        table_assertions = []
+        if auto:
+            if testing.db.dialect.supports_alter:
+                table_assertions.append(
+                    CompiledSQL('CREATE TABLE b ('
+                                'id INTEGER NOT NULL, '
+                                'aid INTEGER, '
+                                'PRIMARY KEY (id)'
+                                ')'
+                                )
+                )
+            else:
+                table_assertions.append(
+                    CompiledSQL(
+                        'CREATE TABLE b ('
                         'id INTEGER NOT NULL, '
                         'aid INTEGER, '
+                        'PRIMARY KEY (id), '
+                        'CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)'
+                        ')'
+                    )
+                )
+
+            if testing.db.dialect.supports_alter:
+                table_assertions.append(
+                    CompiledSQL(
+                        'CREATE TABLE a ('
+                        'id INTEGER NOT NULL, '
+                        'bid INTEGER, '
                         'PRIMARY KEY (id)'
                         ')'
-                        ),
-            CompiledSQL('CREATE TABLE a ('
+                    )
+                )
+            else:
+                table_assertions.append(
+                    CompiledSQL(
+                        'CREATE TABLE a ('
                         'id INTEGER NOT NULL, '
                         'bid INTEGER, '
                         'PRIMARY KEY (id), '
                         'FOREIGN KEY(bid) REFERENCES b (id)'
                         ')'
-                        ),
-        ]
+                    )
+                )
+        else:
+            table_assertions.append(
+                CompiledSQL('CREATE TABLE b ('
+                            'id INTEGER NOT NULL, '
+                            'aid INTEGER, '
+                            'PRIMARY KEY (id)'
+                            ')'
+                            )
+            )
+            table_assertions.append(
+                CompiledSQL(
+                    'CREATE TABLE a ('
+                    'id INTEGER NOT NULL, '
+                    'bid INTEGER, '
+                    'PRIMARY KEY (id), '
+                    'FOREIGN KEY(bid) REFERENCES b (id)'
+                    ')'
+                )
+            )
+
+        assertions = [AllOf(*table_assertions)]
         if testing.db.dialect.supports_alter:
-            assertions.append(
+            fk_assertions = []
+            fk_assertions.append(
                 CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk '
                             'FOREIGN KEY(aid) REFERENCES a (id)')
             )
-        self.assert_sql_execution(
-            testing.db,
-            lambda: metadata.create_all(checkfirst=False),
-            *assertions
-        )
+            if auto:
+                fk_assertions.append(
+                    CompiledSQL('ALTER TABLE a ADD '
+                                'FOREIGN KEY(bid) REFERENCES b (id)')
+                )
+            assertions.append(AllOf(*fk_assertions))
+
+        with self.sql_execution_asserter() as asserter:
+            metadata.create_all(checkfirst=False)
+        asserter.assert_(*assertions)
 
-        assertions = []
         if testing.db.dialect.supports_alter:
-            assertions.append(CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'))
-        assertions.extend([
-            CompiledSQL("DROP TABLE a"),
-            CompiledSQL("DROP TABLE b"),
-        ])
-        self.assert_sql_execution(
-            testing.db,
-            lambda: metadata.drop_all(checkfirst=False),
-            *assertions
-        )
+            assertions = [
+                CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'),
+                CompiledSQL("DROP TABLE a"),
+                CompiledSQL("DROP TABLE b")
+            ]
+        else:
+            assertions = [AllOf(
+                CompiledSQL("DROP TABLE a"),
+                CompiledSQL("DROP TABLE b")
+            )]
+
+        with self.sql_execution_asserter() as asserter:
+            metadata.drop_all(checkfirst=False),
+        asserter.assert_(*assertions)
 
     @testing.requires.check_constraints
     @testing.provide_metadata
@@ -542,6 +670,33 @@ class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL):
             "REFERENCES tbl (a) MATCH SIMPLE"
         )
 
+    def test_create_table_omit_fks(self):
+        fkcs = [
+            ForeignKeyConstraint(['a'], ['remote.id'], name='foo'),
+            ForeignKeyConstraint(['b'], ['remote.id'], name='bar'),
+            ForeignKeyConstraint(['c'], ['remote.id'], name='bat'),
+        ]
+        m = MetaData()
+        t = Table(
+            't', m,
+            Column('a', Integer),
+            Column('b', Integer),
+            Column('c', Integer),
+            *fkcs
+        )
+        Table('remote', m, Column('id', Integer, primary_key=True))
+
+        self.assert_compile(
+            schema.CreateTable(t, include_foreign_key_constraints=[]),
+            "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER)"
+        )
+        self.assert_compile(
+            schema.CreateTable(t, include_foreign_key_constraints=fkcs[0:2]),
+            "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER, "
+            "CONSTRAINT foo FOREIGN KEY(a) REFERENCES remote (id), "
+            "CONSTRAINT bar FOREIGN KEY(b) REFERENCES remote (id))"
+        )
+
     def test_deferrable_unique(self):
         factory = lambda **kw: UniqueConstraint('b', **kw)
         self._test_deferrable(factory)