Make if_exists and if_not_exists flags on ddl statements match compiler

Jesse-Bakker · zzzeek · commit b6eed88ef4eb · 2022-10-04T02:40:20.000Z
Added ``if_exists`` and ``if_not_exists`` parameters for all "Create" / "Drop" constructs including :class:`.CreateSequence`, :class:`.DropSequence`, :class:`.CreateIndex`, :class:`.DropIndex`, etc. allowing generic "IF EXISTS" / "IF NOT EXISTS" phrases to be rendered within DDL. Pull request courtesy Jesse Bakker. Fixes: #7354 Closes: #8492 Pull-request: #8492 Pull-request-sha: d107c6c Change-Id: I367e57b2d9216f5180bcc44e86ca6f3dc794e5ca
diff --git a/doc/build/changelog/unreleased_20/7354.rst b/doc/build/changelog/unreleased_20/7354.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 7354
+
+    Added ``if_exists`` and ``if_not_exists`` parameters for all "Create" /
+    "Drop" constructs including :class:`.CreateSequence`,
+    :class:`.DropSequence`, :class:`.CreateIndex`, :class:`.DropIndex`, etc.
+    allowing generic "IF EXISTS" / "IF NOT EXISTS" phrases to be rendered
+    within DDL. Pull request courtesy Jesse Bakker.
+
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
@@ -5396,12 +5396,16 @@ def visit_ddl(self, ddl, **kwargs):
         return self.sql_compiler.post_process_text(ddl.statement % context)
 
     def visit_create_schema(self, create, **kw):
-        schema = self.preparer.format_schema(create.element)
-        return "CREATE SCHEMA " + schema
+        text = "CREATE SCHEMA "
+        if create.if_not_exists:
+            text += "IF NOT EXISTS "
+        return text + self.preparer.format_schema(create.element)
 
     def visit_drop_schema(self, drop, **kw):
-        schema = self.preparer.format_schema(drop.element)
-        text = "DROP SCHEMA " + schema
+        text = "DROP SCHEMA "
+        if drop.if_exists:
+            text += "IF EXISTS "
+        text += self.preparer.format_schema(drop.element)
         if drop.cascade:
             text += " CASCADE"
         return text
@@ -5650,9 +5654,11 @@ def get_identity_options(self, identity_options):
         return " ".join(text)
 
     def visit_create_sequence(self, create, prefix=None, **kw):
-        text = "CREATE SEQUENCE %s" % self.preparer.format_sequence(
-            create.element
-        )
+        text = "CREATE SEQUENCE "
+        if create.if_not_exists:
+            text += "IF NOT EXISTS "
+        text += self.preparer.format_sequence(create.element)
+
         if prefix:
             text += prefix
         if create.element.start is None:
@@ -5663,7 +5669,10 @@ def visit_create_sequence(self, create, prefix=None, **kw):
         return text
 
     def visit_drop_sequence(self, drop, **kw):
-        return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element)
+        text = "DROP SEQUENCE "
+        if drop.if_exists:
+            text += "IF EXISTS "
+        return text + self.preparer.format_sequence(drop.element)
 
     def visit_drop_constraint(self, drop, **kw):
         constraint = drop.element
diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py
@@ -40,6 +40,7 @@
     from .schema import Constraint
     from .schema import ForeignKeyConstraint
     from .schema import SchemaItem
+    from .schema import Sequence
     from .schema import Table
     from ..engine.base import _CompiledCacheType
     from ..engine.base import Connection
@@ -434,12 +435,8 @@ class _CreateDropBase(ExecutableDDLElement):
     def __init__(
         self,
         element,
-        if_exists=False,
-        if_not_exists=False,
     ):
         self.element = self.target = element
-        self.if_exists = if_exists
-        self.if_not_exists = if_not_exists
         self._ddl_if = getattr(element, "_ddl_if", None)
 
     @property
@@ -457,7 +454,19 @@ def _create_rule_disable(self, compiler):
         return False
 
 
-class CreateSchema(_CreateDropBase):
+class _CreateBase(_CreateDropBase):
+    def __init__(self, element, if_not_exists=False):
+        super().__init__(element)
+        self.if_not_exists = if_not_exists
+
+
+class _DropBase(_CreateDropBase):
+    def __init__(self, element, if_exists=False):
+        super().__init__(element)
+        self.if_exists = if_exists
+
+
+class CreateSchema(_CreateBase):
     """Represent a CREATE SCHEMA statement.
 
     The argument here is the string name of the schema.
@@ -469,19 +478,14 @@ class CreateSchema(_CreateDropBase):
     def __init__(
         self,
         name,
-        quote=None,
-        if_exists=False,
         if_not_exists=False,
     ):
         """Create a new :class:`.CreateSchema` construct."""
 
-        self.quote = quote
-        self.element = name
-        self.if_exists = if_exists
-        self.if_not_exists = if_not_exists
+        super().__init__(element=name, if_not_exists=if_not_exists)
 
 
-class DropSchema(_CreateDropBase):
+class DropSchema(_DropBase):
     """Represent a DROP SCHEMA statement.
 
     The argument here is the string name of the schema.
@@ -493,22 +497,16 @@ class DropSchema(_CreateDropBase):
     def __init__(
         self,
         name,
-        quote=None,
         cascade=False,
         if_exists=False,
-        if_not_exists=False,
     ):
         """Create a new :class:`.DropSchema` construct."""
 
-        self.quote = quote
+        super().__init__(element=name, if_exists=if_exists)
         self.cascade = cascade
-        self.quote = quote
-        self.element = name
-        self.if_exists = if_exists
-        self.if_not_exists = if_not_exists
 
 
-class CreateTable(_CreateDropBase):
+class CreateTable(_CreateBase):
     """Represent a CREATE TABLE statement."""
 
     __visit_name__ = "create_table"
@@ -544,7 +542,7 @@ def __init__(
         self.include_foreign_key_constraints = include_foreign_key_constraints
 
 
-class _DropView(_CreateDropBase):
+class _DropView(_DropBase):
     """Semi-public 'DROP VIEW' construct.
 
     Used by the test suite for dialect-agnostic drops of views.
@@ -669,7 +667,7 @@ def __init__(self, element):
         self.element = element
 
 
-class DropTable(_CreateDropBase):
+class DropTable(_DropBase):
     """Represent a DROP TABLE statement."""
 
     __visit_name__ = "drop_table"
@@ -689,19 +687,25 @@ def __init__(self, element: Table, if_exists: bool = False):
         super().__init__(element, if_exists=if_exists)
 
 
-class CreateSequence(_CreateDropBase):
+class CreateSequence(_CreateBase):
     """Represent a CREATE SEQUENCE statement."""
 
     __visit_name__ = "create_sequence"
 
+    def __init__(self, element: Sequence, if_not_exists: bool = False):
+        super().__init__(element, if_not_exists=if_not_exists)
+
 
-class DropSequence(_CreateDropBase):
+class DropSequence(_DropBase):
     """Represent a DROP SEQUENCE statement."""
 
     __visit_name__ = "drop_sequence"
 
+    def __init__(self, element: Sequence, if_exists: bool = False):
+        super().__init__(element, if_exists=if_exists)
+
 
-class CreateIndex(_CreateDropBase):
+class CreateIndex(_CreateBase):
     """Represent a CREATE INDEX statement."""
 
     __visit_name__ = "create_index"
@@ -711,7 +715,6 @@ def __init__(self, element, if_not_exists=False):
 
         :param element: a :class:`_schema.Index` that's the subject
          of the CREATE.
-        :param on: See the description for 'on' in :class:`.DDL`.
         :param if_not_exists: if True, an IF NOT EXISTS operator will be
          applied to the construct.
 
@@ -721,7 +724,7 @@ def __init__(self, element, if_not_exists=False):
         super().__init__(element, if_not_exists=if_not_exists)
 
 
-class DropIndex(_CreateDropBase):
+class DropIndex(_DropBase):
     """Represent a DROP INDEX statement."""
 
     __visit_name__ = "drop_index"
@@ -731,7 +734,6 @@ def __init__(self, element, if_exists=False):
 
         :param element: a :class:`_schema.Index` that's the subject
          of the DROP.
-        :param on: See the description for 'on' in :class:`.DDL`.
         :param if_exists: if True, an IF EXISTS operator will be applied to the
          construct.
 
@@ -741,26 +743,26 @@ def __init__(self, element, if_exists=False):
         super().__init__(element, if_exists=if_exists)
 
 
-class AddConstraint(_CreateDropBase):
+class AddConstraint(_CreateBase):
     """Represent an ALTER TABLE ADD CONSTRAINT statement."""
 
     __visit_name__ = "add_constraint"
 
-    def __init__(self, element, *args, **kw):
-        super().__init__(element, *args, **kw)
+    def __init__(self, element):
+        super().__init__(element)
         element._create_rule = util.portable_instancemethod(
             self._create_rule_disable
         )
 
 
-class DropConstraint(_CreateDropBase):
+class DropConstraint(_DropBase):
     """Represent an ALTER TABLE DROP CONSTRAINT statement."""
 
     __visit_name__ = "drop_constraint"
 
-    def __init__(self, element, cascade=False, **kw):
+    def __init__(self, element, cascade=False, if_exists=False, **kw):
         self.cascade = cascade
-        super().__init__(element, **kw)
+        super().__init__(element, if_exists=if_exists, **kw)
         element._create_rule = util.portable_instancemethod(
             self._create_rule_disable
         )
diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py
@@ -765,6 +765,14 @@ def test_create_index_plain(self):
         i = Index("xyz", t.c.x)
         self.assert_compile(schema.CreateIndex(i), "CREATE INDEX xyz ON t (x)")
 
+    def test_create_index_if_not_exists(self):
+        t = Table("t", MetaData(), Column("x", Integer))
+        i = Index("xyz", t.c.x)
+        self.assert_compile(
+            schema.CreateIndex(i, if_not_exists=True),
+            "CREATE INDEX IF NOT EXISTS xyz ON t (x)",
+        )
+
     def test_drop_index_plain_unattached(self):
         self.assert_compile(
             schema.DropIndex(Index(name="xyz")), "DROP INDEX xyz"
@@ -775,6 +783,12 @@ def test_drop_index_plain(self):
             schema.DropIndex(Index(name="xyz")), "DROP INDEX xyz"
         )
 
+    def test_drop_index_if_exists(self):
+        self.assert_compile(
+            schema.DropIndex(Index(name="xyz"), if_exists=True),
+            "DROP INDEX IF EXISTS xyz",
+        )
+
     def test_create_index_schema(self):
         t = Table("t", MetaData(), Column("x", Integer), schema="foo")
         i = Index("xyz", t.c.x)
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
@@ -2620,9 +2620,17 @@ def test_create_drop_schema(self):
         self.assert_compile(
             schema.CreateSchema("sa_schema"), "CREATE SCHEMA sa_schema"
         )
+        self.assert_compile(
+            schema.CreateSchema("sa_schema", if_not_exists=True),
+            "CREATE SCHEMA IF NOT EXISTS sa_schema",
+        )
         self.assert_compile(
             schema.DropSchema("sa_schema"), "DROP SCHEMA sa_schema"
         )
+        self.assert_compile(
+            schema.DropSchema("sa_schema", if_exists=True),
+            "DROP SCHEMA IF EXISTS sa_schema",
+        )
         self.assert_compile(
             schema.DropSchema("sa_schema", cascade=True),
             "DROP SCHEMA sa_schema CASCADE",
diff --git a/test/sql/test_sequences.py b/test/sql/test_sequences.py
@@ -92,10 +92,20 @@ def test_create_drop_ddl(self):
             "CREATE SEQUENCE foo_seq START WITH 1 ORDER",
         )
 
+        self.assert_compile(
+            CreateSequence(Sequence("foo_seq"), if_not_exists=True),
+            "CREATE SEQUENCE IF NOT EXISTS foo_seq START WITH 1",
+        )
+
         self.assert_compile(
             DropSequence(Sequence("foo_seq")), "DROP SEQUENCE foo_seq"
         )
 
+        self.assert_compile(
+            DropSequence(Sequence("foo_seq"), if_exists=True),
+            "DROP SEQUENCE IF EXISTS foo_seq",
+        )
+
 
 class SequenceExecTest(fixtures.TestBase):
     __requires__ = ("sequences",)
