Support IF EXISTS/IF NOT EXISTS for DDL constructs

Added parameters :paramref:`_ddl.CreateTable.if_not_exists`,
:paramref:`_ddl.CreateIndex.if_not_exists`,
:paramref:`_ddl.DropTable.if_exists` and
:paramref:`_ddl.DropIndex.if_exists` to the :class:`_ddl.CreateTable`,
:class:`_ddl.DropTable`, :class:`_ddl.CreateIndex` and
:class:`_ddl.DropIndex` constructs which result in "IF NOT EXISTS" / "IF
EXISTS" DDL being added to the CREATE/DROP. These phrases are not accepted
by all databases and the operation will fail on a database that does not
support it as there is no similarly compatible fallback within the scope of
a single DDL statement.  Pull request courtesy Ramon Williams.

Fixes: #2843
Closes: #5663
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5663
Pull-request-sha: 748b8472345d96efb446e2a444fbe020b313669f

Change-Id: I6a2b1f697993ed49c31584f0a31887fb0a868ed3

2 files changed, 90 insertions, 9 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 9b90bf868..9c3d1e080 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3835,7 +3835,12 @@ class DDLCompiler(Compiled):
         text = "\nCREATE "
         if table._prefixes:
             text += " ".join(table._prefixes) + " "
-        text += "TABLE " + preparer.format_table(table) + " "
+
+        text += "TABLE "
+        if create.if_not_exists:
+            text += "IF NOT EXISTS "
+
+        text += preparer.format_table(table) + " "
 
         create_table_suffix = self.create_table_suffix(table)
         if create_table_suffix:
@@ -3935,7 +3940,10 @@ class DDLCompiler(Compiled):
         )
 
     def visit_drop_table(self, drop, **kw):
-        return "\nDROP TABLE " + self.preparer.format_table(drop.element)
+        text = "\nDROP TABLE "
+        if drop.if_exists:
+            text += "IF EXISTS "
+        return text + self.preparer.format_table(drop.element)
 
     def visit_drop_view(self, drop, **kw):
         return "\nDROP VIEW " + self.preparer.format_table(drop.element)
@@ -3959,7 +3967,12 @@ class DDLCompiler(Compiled):
             raise exc.CompileError(
                 "CREATE INDEX requires that the index have a name"
             )
-        text += "INDEX %s ON %s (%s)" % (
+
+        text += "INDEX "
+        if create.if_not_exists:
+            text += "IF NOT EXISTS "
+
+        text += "%s ON %s (%s)" % (
             self._prepared_index_name(index, include_schema=include_schema),
             preparer.format_table(
                 index.table, use_schema=include_table_schema
@@ -3980,9 +3993,11 @@ class DDLCompiler(Compiled):
             raise exc.CompileError(
                 "DROP INDEX requires that the index have a name"
             )
-        return "\nDROP INDEX " + self._prepared_index_name(
-            index, include_schema=True
-        )
+        text = "\nDROP INDEX "
+        if drop.if_exists:
+            text += "IF EXISTS "
+
+        return text + self._prepared_index_name(index, include_schema=True)
 
     def _prepared_index_name(self, index, include_schema=False):
         if index.table is not None:
diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py
index f1012292b..564b53c55 100644
--- a/lib/sqlalchemy/sql/ddl.py
+++ b/lib/sqlalchemy/sql/ddl.py
@@ -369,9 +369,13 @@ class _CreateDropBase(DDLElement):
 
     """
 
-    def __init__(self, element, bind=None):
+    def __init__(
+        self, element, bind=None, if_exists=False, if_not_exists=False
+    ):
         self.element = element
         self.bind = bind
+        self.if_exists = if_exists
+        self.if_not_exists = if_not_exists
 
     @property
     def stringify_dialect(self):
@@ -427,7 +431,11 @@ class CreateTable(_CreateDropBase):
     __visit_name__ = "create_table"
 
     def __init__(
-        self, element, bind=None, include_foreign_key_constraints=None
+        self,
+        element,
+        bind=None,
+        include_foreign_key_constraints=None,
+        if_not_exists=False,
     ):
         """Create a :class:`.CreateTable` construct.
 
@@ -442,8 +450,15 @@ class CreateTable(_CreateDropBase):
 
          .. versionadded:: 1.0.0
 
+        :param if_not_exists: if True, an IF NOT EXISTS operator will be
+         applied to the construct.
+
+         .. versionadded:: 1.4.0b2
+
         """
-        super(CreateTable, self).__init__(element, bind=bind)
+        super(CreateTable, self).__init__(
+            element, bind=bind, if_not_exists=if_not_exists
+        )
         self.columns = [CreateColumn(column) for column in element.columns]
         self.include_foreign_key_constraints = include_foreign_key_constraints
 
@@ -573,6 +588,23 @@ class DropTable(_CreateDropBase):
 
     __visit_name__ = "drop_table"
 
+    def __init__(self, element, bind=None, if_exists=False):
+        """Create a :class:`.DropTable` construct.
+
+        :param element: a :class:`_schema.Table` that's the subject
+         of the DROP.
+        :param on: See the description for 'on' in :class:`.DDL`.
+        :param bind: See the description for 'bind' in :class:`.DDL`.
+        :param if_exists: if True, an IF EXISTS operator will be applied to the
+         construct.
+
+         .. versionadded:: 1.4.0b2
+
+        """
+        super(DropTable, self).__init__(
+            element, bind=bind, if_exists=if_exists
+        )
+
 
 class CreateSequence(_CreateDropBase):
     """Represent a CREATE SEQUENCE statement."""
@@ -591,12 +623,46 @@ class CreateIndex(_CreateDropBase):
 
     __visit_name__ = "create_index"
 
+    def __init__(self, element, bind=None, if_not_exists=False):
+        """Create a :class:`.Createindex` construct.
+
+        :param element: a :class:`_schema.Index` that's the subject
+         of the CREATE.
+        :param on: See the description for 'on' in :class:`.DDL`.
+        :param bind: See the description for 'bind' in :class:`.DDL`.
+        :param if_not_exists: if True, an IF NOT EXISTS operator will be
+         applied to the construct.
+
+         .. versionadded:: 1.4.0b2
+
+        """
+        super(CreateIndex, self).__init__(
+            element, bind=bind, if_not_exists=if_not_exists
+        )
+
 
 class DropIndex(_CreateDropBase):
     """Represent a DROP INDEX statement."""
 
     __visit_name__ = "drop_index"
 
+    def __init__(self, element, bind=None, if_exists=False):
+        """Create a :class:`.DropIndex` construct.
+
+        :param element: a :class:`_schema.Index` that's the subject
+         of the DROP.
+        :param on: See the description for 'on' in :class:`.DDL`.
+        :param bind: See the description for 'bind' in :class:`.DDL`.
+        :param if_exists: if True, an IF EXISTS operator will be applied to the
+         construct.
+
+         .. versionadded:: 1.4.0b2
+
+        """
+        super(DropIndex, self).__init__(
+            element, bind=bind, if_exists=if_exists
+        )
+
 
 class AddConstraint(_CreateDropBase):
     """Represent an ALTER TABLE ADD CONSTRAINT statement."""