From b6eed88ef4ebb3fd7035b4e366bf6653ebb26d15 Mon Sep 17 00:00:00 2001 From: Jesse Bakker Date: Tue, 6 Sep 2022 16:00:10 -0400 Subject: [PATCH] Make if_exists and if_not_exists flags on ddl statements match compiler 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: https://github.com/sqlalchemy/sqlalchemy/pull/8492 Pull-request-sha: d107c6ce553bd430111607815f5b3938ffc4770c Change-Id: I367e57b2d9216f5180bcc44e86ca6f3dc794e5ca --- doc/build/changelog/unreleased_20/7354.rst | 10 ++++ lib/sqlalchemy/sql/compiler.py | 25 +++++--- lib/sqlalchemy/sql/ddl.py | 70 +++++++++++----------- test/sql/test_constraints.py | 14 +++++ test/sql/test_metadata.py | 8 +++ test/sql/test_sequences.py | 10 ++++ 6 files changed, 95 insertions(+), 42 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/7354.rst diff --git a/doc/build/changelog/unreleased_20/7354.rst b/doc/build/changelog/unreleased_20/7354.rst new file mode 100644 index 0000000000..dfbd0e8d79 --- /dev/null +++ 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 index c7e226fcc6..dd40bfe345 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -5396,12 +5396,16 @@ class DDLCompiler(Compiled): 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 @@ class DDLCompiler(Compiled): 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 @@ class DDLCompiler(Compiled): 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 index 3c7c674f50..e744f0c1de 100644 --- a/lib/sqlalchemy/sql/ddl.py +++ b/lib/sqlalchemy/sql/ddl.py @@ -40,6 +40,7 @@ if typing.TYPE_CHECKING: 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 @@ class _CreateDropBase(ExecutableDDLElement): 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 @@ class CreateTable(_CreateDropBase): 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 @@ class CreateColumn(BaseDDLElement): self.element = element -class DropTable(_CreateDropBase): +class DropTable(_DropBase): """Represent a DROP TABLE statement.""" __visit_name__ = "drop_table" @@ -689,19 +687,25 @@ class DropTable(_CreateDropBase): 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 @@ class CreateIndex(_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 if_not_exists: if True, an IF NOT EXISTS operator will be applied to the construct. @@ -721,7 +724,7 @@ class CreateIndex(_CreateDropBase): 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 @@ class DropIndex(_CreateDropBase): :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 @@ class DropIndex(_CreateDropBase): 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 index 462667bedc..b1b731d66f 100644 --- a/test/sql/test_constraints.py +++ b/test/sql/test_constraints.py @@ -765,6 +765,14 @@ class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL): 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 @@ class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL): 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 index 38255f9775..6d93cb234e 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -2620,9 +2620,17 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): 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 index 19f95c6619..457aeb960b 100644 --- a/test/sql/test_sequences.py +++ b/test/sql/test_sequences.py @@ -92,10 +92,20 @@ class SequenceDDLTest(fixtures.TestBase, testing.AssertsCompiledSQL): "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",) -- 2.47.2