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, 35 insertions, 0 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 75ac896e9..fdffe04bf 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -31,6 +31,7 @@ from sqlalchemy import exists
 from sqlalchemy import Float
 from sqlalchemy import ForeignKey
 from sqlalchemy import func
+from sqlalchemy import Index
 from sqlalchemy import Integer
 from sqlalchemy import intersect
 from sqlalchemy import join
@@ -4471,6 +4472,40 @@ class DDLTest(fixtures.TestBase, AssertsCompiledSQL):
             "PRIMARY KEY (a, b))",
         )
 
+    def test_create_table_exists(self):
+        m = MetaData()
+        t1 = Table("t1", m, Column("q", Integer))
+        self.assert_compile(
+            schema.CreateTable(t1, if_not_exists=True),
+            "CREATE TABLE IF NOT EXISTS t1 (q INTEGER)",
+        )
+
+    def test_drop_table_exists(self):
+        m = MetaData()
+        t1 = Table("t1", m, Column("q", Integer))
+        self.assert_compile(
+            schema.DropTable(t1, if_exists=True),
+            "DROP TABLE IF EXISTS t1",
+        )
+
+    def test_create_index_exists(self):
+        m = MetaData()
+        t1 = Table("t1", m, Column("q", Integer))
+        idx = Index("my_idx", t1.c.q)
+        self.assert_compile(
+            schema.CreateIndex(idx, if_not_exists=True),
+            "CREATE INDEX IF NOT EXISTS my_idx ON t1 (q)",
+        )
+
+    def test_drop_index_exists(self):
+        m = MetaData()
+        t1 = Table("t1", m, Column("q", Integer))
+        idx = Index("my_idx", t1.c.q)
+        self.assert_compile(
+            schema.DropIndex(idx, if_exists=True),
+            "DROP INDEX IF EXISTS my_idx",
+        )
+
     def test_create_table_suffix(self):
         class MyDialect(default.DefaultDialect):
             class MyCompiler(compiler.DDLCompiler):