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

1 files changed, 21 insertions, 6 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: