From 748b8472345d96efb446e2a444fbe020b313669f Mon Sep 17 00:00:00 2001 From: RamonWill Date: Wed, 21 Oct 2020 12:42:53 +0100 Subject: [PATCH] Support IF EXISTS/IF NOT EXISTS for DDL constructs --- doc/build/changelog/unreleased_14/2843.rst | 8 ++ lib/sqlalchemy/dialects/mssql/base.py | 3 + lib/sqlalchemy/dialects/mysql/base.py | 13 +++- lib/sqlalchemy/dialects/oracle/base.py | 2 + lib/sqlalchemy/dialects/postgresql/base.py | 9 +++ lib/sqlalchemy/dialects/sqlite/base.py | 10 ++- lib/sqlalchemy/engine/default.py | 3 + lib/sqlalchemy/sql/compiler.py | 27 +++++-- lib/sqlalchemy/sql/ddl.py | 90 ++++++++++++++++++++-- test/sql/test_constraints.py | 76 ++++++++++++++++++ 10 files changed, 226 insertions(+), 15 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/2843.rst diff --git a/doc/build/changelog/unreleased_14/2843.rst b/doc/build/changelog/unreleased_14/2843.rst new file mode 100644 index 0000000000..be1f6796a8 --- /dev/null +++ b/doc/build/changelog/unreleased_14/2843.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: schema, usecase + :tickets: 2843 + + The create/drop clause constructs :class:`CreateTable`, :class:`DropTable`, + :class:`CreateIndex` and :class:`DropIndex` now support the EXISTS + operator. If a dialect does not support the EXISTS operator then EXISTS + will be ignored. Pull request courtesy Ramon Williams. diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index c8f2b4ca3b..344b2859a6 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -2590,6 +2590,9 @@ class MSDialect(default.DefaultDialect): # T-SQL's actual default is -9223372036854775808 default_sequence_base = 1 + supports_exists_table = False + supports_exists_index = False + supports_native_boolean = False non_native_boolean_check_constraint = False supports_unicode_binds = True diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 77f65799c2..e61f189ae0 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2005,7 +2005,10 @@ class MySQLDDLCompiler(compiler.DDLCompiler): if index_prefix: text += index_prefix + " " - text += "INDEX %s ON %s " % (name, table) + text += "INDEX " + if create.if_not_exists and self.dialect.supports_exists_index: + text += "IF NOT EXISTS " + text += "%s ON %s " % (name, table) length = index.dialect_options[self.dialect.name]["length"] if length is not None: @@ -2055,8 +2058,11 @@ class MySQLDDLCompiler(compiler.DDLCompiler): def visit_drop_index(self, drop): index = drop.element + text = "\nDROP INDEX " + if drop.if_exists and self.dialect.supports_exists_index: + text += "IF EXISTS " - return "\nDROP INDEX %s ON %s" % ( + return text + "%s ON %s" % ( self._prepared_index_name(index, include_schema=False), self.preparer.format_table(index.table), ) @@ -2455,6 +2461,9 @@ class MySQLDialect(default.DefaultDialect): max_identifier_length = 255 max_index_name_length = 64 + supports_exists_table = True + supports_exists_index = False + supports_native_enum = True supports_sequences = False # default for MySQL ... diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 1f1f7501bd..ada0805ff2 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -1397,6 +1397,8 @@ class OracleExecutionContext(default.DefaultExecutionContext): class OracleDialect(default.DefaultDialect): name = "oracle" supports_alter = True + supports_exist_table = False + supports_exists_index = False supports_unicode_statements = False supports_unicode_binds = False max_identifier_length = 128 diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ea6921b2db..3ee421a89d 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2262,6 +2262,9 @@ class PGDDLCompiler(compiler.DDLCompiler): if concurrently: text += "CONCURRENTLY " + if create.if_not_exists and self.dialect.supports_exists_index: + text += "IF NOT EXISTS " + text += "%s ON %s " % ( self._prepared_index_name(index, include_schema=False), preparer.format_table(index.table), @@ -2345,6 +2348,9 @@ class PGDDLCompiler(compiler.DDLCompiler): if concurrently: text += "CONCURRENTLY " + if drop.if_exists and self.dialect.supports_exists_index: + text += "IF EXISTS " + text += self._prepared_index_name(index, include_schema=True) return text @@ -2759,6 +2765,9 @@ class PGDialect(default.DefaultDialect): max_identifier_length = 63 supports_sane_rowcount = True + supports_exists_table = True + supports_exists_index = True + supports_native_enum = True supports_native_boolean = True supports_smallserial = True diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 8ef35514ab..1ffe5cd6ac 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1239,7 +1239,13 @@ class SQLiteDDLCompiler(compiler.DDLCompiler): text = "CREATE " if index.unique: text += "UNIQUE " - text += "INDEX %s ON %s (%s)" % ( + + text += "INDEX " + + if create.if_not_exists and self.dialect.supports_exists_index: + text += "IF NOT EXISTS " + + text += "%s ON %s (%s)" % ( self._prepared_index_name(index, include_schema=True), preparer.format_table(index.table, use_schema=False), ", ".join( @@ -1447,6 +1453,8 @@ class SQLiteExecutionContext(default.DefaultExecutionContext): class SQLiteDialect(default.DefaultDialect): name = "sqlite" supports_alter = False + supports_exists_table = True + supports_exists_index = True supports_unicode_statements = True supports_unicode_binds = True supports_default_values = True diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 7f92271e97..7215a4380a 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -56,6 +56,9 @@ class DefaultDialect(interfaces.Dialect): supports_alter = True supports_comments = False inline_comments = False + + supports_exists_table = False + supports_exists_index = False use_setinputsizes = False # the first value we'd get for an autoincrement diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 10499975c7..41b5990b01 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -3838,7 +3838,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 and self.dialect.supports_exists_table: + text += "IF NOT EXISTS " + + text += preparer.format_table(table) + " " create_table_suffix = self.create_table_suffix(table) if create_table_suffix: @@ -3938,7 +3943,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 and self.dialect.supports_exists_table: + 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) @@ -3962,7 +3970,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 and self.dialect.supports_exists_index: + 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 @@ -3983,9 +3996,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 and self.dialect.supports_exists_index: + 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 5f3074cdc2..4ca4e65887 100644 --- a/lib/sqlalchemy/sql/ddl.py +++ b/lib/sqlalchemy/sql/ddl.py @@ -369,6 +369,34 @@ class _CreateDropBase(DDLElement): return False +class _CreateBase(_CreateDropBase): + """ + Base class for DDL constructs that represent CREATE with the EXISTS + operator. + + .. note:: If a dialect does not support the use of the EXISTS operator with + CREATE then it will be ignored when generating the CREATE + construct. + """ + + def __init__(self, element, if_not_exists=False, **kw): + self.if_not_exists = if_not_exists + super(_CreateBase, self).__init__(element, **kw) + + +class _DropBase(_CreateDropBase): + """ + Base class for DDL constructs that represent DROP with the EXISTS operator. + + .. note:: If a dialect does not support the use of the EXISTS operator with + DROP then it will be ignored when generating the DROP construct. + """ + + def __init__(self, element, if_exists=False, **kw): + self.if_exists = if_exists + super(_DropBase, self).__init__(element, **kw) + + class CreateSchema(_CreateDropBase): """Represent a CREATE SCHEMA statement. @@ -402,13 +430,17 @@ class DropSchema(_CreateDropBase): super(DropSchema, self).__init__(name, **kw) -class CreateTable(_CreateDropBase): +class CreateTable(_CreateBase): """Represent a CREATE TABLE statement.""" __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. @@ -423,8 +455,12 @@ class CreateTable(_CreateDropBase): .. versionadded:: 1.0.0 + :param if_not_exists: if True, an IF NOT EXISTS operator will be + applied to the construct. """ - 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 @@ -549,11 +585,25 @@ class CreateColumn(_DDLCompiles): self.element = element -class DropTable(_CreateDropBase): +class DropTable(_DropBase): """Represent a DROP TABLE statement.""" __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. + """ + super(DropTable, self).__init__( + element, bind=bind, if_exists=if_exists + ) + class CreateSequence(_CreateDropBase): """Represent a CREATE SEQUENCE statement.""" @@ -567,17 +617,45 @@ class DropSequence(_CreateDropBase): __visit_name__ = "drop_sequence" -class CreateIndex(_CreateDropBase): +class CreateIndex(_CreateBase): """Represent a CREATE INDEX statement.""" __visit_name__ = "create_index" + def __init__(self, element, bind=None, if_not_exists=False): + """Create a :class:`.Createindex` construct. -class DropIndex(_CreateDropBase): + :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. + """ + super(CreateIndex, self).__init__( + element, bind=bind, if_not_exists=if_not_exists + ) + + +class DropIndex(_DropBase): """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. + """ + super(DropIndex, self).__init__( + element, bind=bind, if_exists=if_exists + ) + class AddConstraint(_CreateDropBase): """Represent an ALTER TABLE ADD CONSTRAINT statement.""" diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py index 019409ba3d..a0b4ee2fe5 100644 --- a/test/sql/test_constraints.py +++ b/test/sql/test_constraints.py @@ -1340,3 +1340,79 @@ class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( schema.CreateIndex(constraint), "CREATE INDEX name ON tbl (a + 5)" ) + + @testing.skip_if("postgresql < 9.1") + def test_create_table_if_not_exists(self): + t, t2 = self._constraint_create_fixture() + + # If a dialect doesn't support EXISTS, EXISTS will be ignored silently. + if testing.db.dialect.supports_exists_table: + if testing.db.dialect.name == "mssql": + stmt = ( + "CREATE TABLE IF NOT EXISTS tbl " + "(a INTEGER NULL, b INTEGER NULL)" + ) + else: + stmt = "CREATE TABLE IF NOT EXISTS tbl (a INTEGER, b INTEGER)" + + else: + if testing.db.dialect.name == "mssql": + stmt = "CREATE TABLE tbl (a INTEGER NULL, b INTEGER NULL)" + else: + stmt = "CREATE TABLE tbl (a INTEGER, b INTEGER)" + + self.assert_compile( + schema.CreateTable(t, if_not_exists=True), + stmt, + dialect=testing.db.dialect, + ) + + @testing.skip_if("postgresql < 9.5") + def test_create_index_if_not_exists(self): + t, t2 = self._constraint_create_fixture() + idx = Index("idx_name", t.c.a) + + # If a dialect doesn't support EXISTS, EXISTS will be ignored silently. + if testing.db.dialect.supports_exists_index: + stmt = "CREATE INDEX IF NOT EXISTS idx_name ON tbl (a)" + else: + stmt = "CREATE INDEX idx_name ON tbl (a)" + self.assert_compile( + schema.CreateIndex(idx, if_not_exists=True), + stmt, + dialect=testing.db.dialect, + ) + + @testing.skip_if("postgresql < 8.2") + def test_drop_table_if_exists(self): + t, t2 = self._constraint_create_fixture() + + # If a dialect doesn't support EXISTS, EXISTS will be ignored silently. + if testing.db.dialect.supports_exists_table: + stmt = "DROP TABLE IF EXISTS tbl" + else: + stmt = "DROP TABLE tbl" + self.assert_compile( + schema.DropTable(t, if_exists=True), + stmt, + dialect=testing.db.dialect, + ) + + @testing.skip_if("postgresql < 8.2") + def test_drop_index_if_exists(self): + t, t2 = self._constraint_create_fixture() + idx = Index("idx_name", t.c.a) + + # If a dialect doesn't support EXISTS, EXISTS will be ignored silently. + if testing.db.dialect.supports_exists_index: + stmt = "DROP INDEX IF EXISTS idx_name" + else: + stmt = "DROP INDEX idx_name" + + if testing.db.dialect.name in ("mysql", "mariadb", "mssql"): + stmt += " ON tbl" + self.assert_compile( + schema.DropIndex(idx, if_exists=True), + stmt, + dialect=testing.db.dialect, + ) -- 2.47.3