From 0e1c0989894f13b29a00c9504b887568a40d6648 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 7 Apr 2015 12:36:51 -0400 Subject: - Added a new option :paramref:`.EnvironmentContext.configure.literal_binds`, which will pass the ``literal_binds`` flag into the compilation of SQL constructs when using "offline" mode. This has the effect that SQL objects like inserts, updates, deletes as well as textual statements sent using ``text()`` will be compiled such that the dialect will attempt to render literal values "inline" automatically. Only a subset of types is typically supported; the :meth:`.Operations.inline_literal` construct remains as the construct used to force a specific literal representation of a value. The :paramref:`.EnvironmentContext.configure.literal_binds` flag is added to the "offline" section of the ``env.py`` files generated in new environments. fixes #255 - enhance the op_fixture as well as MigrationContext._stdout_connection() so that it uses the real DefaultImpl and MigrationContext fully in tests. --- alembic/ddl/impl.py | 19 +++++++- alembic/environment.py | 20 +++++++++ alembic/migration.py | 5 +-- alembic/operations.py | 10 +++++ alembic/templates/generic/env.py | 3 +- alembic/templates/multidb/env.py | 3 +- alembic/templates/pylons/env.py | 3 +- alembic/testing/fixtures.py | 95 +++++++++++++++++++++------------------- docs/build/changelog.rst | 18 ++++++++ tests/test_bulk_insert.py | 8 +++- tests/test_mysql.py | 2 +- tests/test_op.py | 38 ++++++++++++++++ 12 files changed, 170 insertions(+), 54 deletions(-) diff --git a/alembic/ddl/impl.py b/alembic/ddl/impl.py index c91f1f1..176079c 100644 --- a/alembic/ddl/impl.py +++ b/alembic/ddl/impl.py @@ -48,12 +48,22 @@ class DefaultImpl(with_metaclass(ImplMeta)): self.dialect = dialect self.connection = connection self.as_sql = as_sql + self.literal_binds = context_opts.get('literal_binds', False) + if self.literal_binds and not util.sqla_08: + util.warn("'literal_binds' flag not supported in SQLAlchemy 0.7") + self.literal_binds = False + self.output_buffer = output_buffer self.memo = {} self.context_opts = context_opts if transactional_ddl is not None: self.transactional_ddl = transactional_ddl + if self.literal_binds: + if not self.as_sql: + raise util.CommandError( + "Can't use literal_binds setting without as_sql mode") + @classmethod def get_by_dialect(cls, dialect): return _impls[dialect.name] @@ -95,8 +105,15 @@ class DefaultImpl(with_metaclass(ImplMeta)): if multiparams or params: # TODO: coverage raise Exception("Execution arguments not allowed with as_sql") + + if self.literal_binds and not isinstance( + construct, schema.DDLElement): + compile_kw = dict(compile_kwargs={"literal_binds": True}) + else: + compile_kw = {} + self.static_output(text_type( - construct.compile(dialect=self.dialect) + construct.compile(dialect=self.dialect, **compile_kw) ).replace("\t", " ").strip() + self.command_terminator) else: conn = self.connection diff --git a/alembic/environment.py b/alembic/environment.py index 45983d1..130a50f 100644 --- a/alembic/environment.py +++ b/alembic/environment.py @@ -296,6 +296,7 @@ class EnvironmentContext(object): compare_type=False, compare_server_default=False, render_item=None, + literal_binds=False, upgrade_token="upgrades", downgrade_token="downgrades", alembic_module_prefix="op.", @@ -365,6 +366,24 @@ class EnvironmentContext(object): object. :param output_encoding: when using ``--sql`` to generate SQL scripts, apply this encoding to the string output. + :param literal_binds: when using ``--sql`` to generate SQL + scripts, pass through the ``literal_binds`` flag to the compiler + so that any literal values that would ordinarily be bound + parameters are converted to plain strings. + + .. warning:: Dialects can typically only handle simple datatypes + like strings and numbers for auto-literal generation. Datatypes + like dates, intervals, and others may still require manual + formatting, typically using :meth:`.Operations.inline_literal`. + + .. note:: the ``literal_binds`` flag is ignored on SQLAlchemy + versions prior to 0.8 where this feature is not supported. + + .. versionadded:: 0.7.6 + + .. seealso:: + + :meth:`.Operations.inline_literal` :param starting_rev: Override the "starting revision" argument when using ``--sql`` mode. @@ -700,6 +719,7 @@ class EnvironmentContext(object): opts['sqlalchemy_module_prefix'] = sqlalchemy_module_prefix opts['alembic_module_prefix'] = alembic_module_prefix opts['user_module_prefix'] = user_module_prefix + opts['literal_binds'] = literal_binds if render_item is not None: opts['render_item'] = render_item if compare_type is not None: diff --git a/alembic/migration.py b/alembic/migration.py index a2241fd..9bd34ed 100644 --- a/alembic/migration.py +++ b/alembic/migration.py @@ -3,7 +3,7 @@ import sys from contextlib import contextmanager from sqlalchemy import MetaData, Table, Column, String, literal_column -from sqlalchemy import create_engine +from sqlalchemy.engine.strategies import MockEngineStrategy from sqlalchemy.engine import url as sqla_url from .compat import callable, EncodedIO @@ -333,8 +333,7 @@ class MigrationContext(object): def dump(construct, *multiparams, **params): self.impl._exec(construct) - return create_engine("%s://" % self.dialect.name, - strategy="mock", executor=dump) + return MockEngineStrategy.MockConnection(self.dialect, dump) @property def bind(self): diff --git a/alembic/operations.py b/alembic/operations.py index 485943e..83ccaa1 100644 --- a/alembic/operations.py +++ b/alembic/operations.py @@ -1197,6 +1197,12 @@ class Operations(object): See :meth:`.execute` for an example usage of :meth:`.inline_literal`. + The environment can also be configured to attempt to render + "literal" values inline automatically, for those simple types + that are supported by the dialect; see + :paramref:`.EnvironmentContext.configure.literal_binds` for this + more recently added feature. + :param value: The value to render. Strings, integers, and simple numerics should be supported. Other types like boolean, dates, etc. may or may not be supported yet by various @@ -1207,6 +1213,10 @@ class Operations(object): from the Python type of the value itself, as well as based on the context in which the value is used. + .. seealso:: + + :paramref:`.EnvironmentContext.configure.literal_binds` + """ return impl._literal_bindparam(None, value, type_=type_) diff --git a/alembic/templates/generic/env.py b/alembic/templates/generic/env.py index 280006d..058378b 100644 --- a/alembic/templates/generic/env.py +++ b/alembic/templates/generic/env.py @@ -36,7 +36,8 @@ def run_migrations_offline(): """ url = config.get_main_option("sqlalchemy.url") - context.configure(url=url, target_metadata=target_metadata) + context.configure( + url=url, target_metadata=target_metadata, literal_binds=True) with context.begin_transaction(): context.run_migrations() diff --git a/alembic/templates/multidb/env.py b/alembic/templates/multidb/env.py index ab37199..453b41c 100644 --- a/alembic/templates/multidb/env.py +++ b/alembic/templates/multidb/env.py @@ -67,7 +67,8 @@ def run_migrations_offline(): logger.info("Writing output to %s" % file_) with open(file_, 'w') as buffer: context.configure(url=rec['url'], output_buffer=buffer, - target_metadata=target_metadata.get(name)) + target_metadata=target_metadata.get(name), + literal_binds=True) with context.begin_transaction(): context.run_migrations(engine_name=name) diff --git a/alembic/templates/pylons/env.py b/alembic/templates/pylons/env.py index 70eea4e..5ad9fd5 100644 --- a/alembic/templates/pylons/env.py +++ b/alembic/templates/pylons/env.py @@ -46,7 +46,8 @@ def run_migrations_offline(): """ context.configure( - url=meta.engine.url, target_metadata=target_metadata) + url=meta.engine.url, target_metadata=target_metadata, + literal_binds=True) with context.begin_transaction(): context.run_migrations() diff --git a/alembic/testing/fixtures.py b/alembic/testing/fixtures.py index 4091388..ae25fd2 100644 --- a/alembic/testing/fixtures.py +++ b/alembic/testing/fixtures.py @@ -88,39 +88,9 @@ def capture_context_buffer(**kw): yield buf -def op_fixture(dialect='default', as_sql=False, naming_convention=None): - impl = _impls[dialect] - - class Impl(impl): - - def __init__(self, dialect, as_sql): - self.assertion = [] - self.dialect = dialect - self.as_sql = as_sql - # TODO: this might need to - # be more like a real connection - # as tests get more involved - if as_sql and self.dialect.name != 'default': - # act similarly to MigrationContext - def dump(construct, *multiparams, **params): - self._exec(construct) - - self.connection = create_engine( - "%s://" % self.dialect.name, - strategy="mock", executor=dump) - - else: - self.connection = mock.Mock(dialect=dialect) - - def _exec(self, construct, *args, **kw): - if isinstance(construct, string_types): - construct = text(construct) - assert construct.supports_execution - sql = text_type(construct.compile(dialect=self.dialect)) - sql = re.sub(r'[\n\t]', '', sql) - self.assertion.append( - sql - ) +def op_fixture( + dialect='default', as_sql=False, + naming_convention=None, literal_binds=False): opts = {} if naming_convention: @@ -130,32 +100,67 @@ def op_fixture(dialect='default', as_sql=False, naming_convention=None): "sqla 0.9.2 or greater") opts['target_metadata'] = MetaData(naming_convention=naming_convention) - class ctx(MigrationContext): + class buffer_(object): + def __init__(self): + self.lines = [] + + def write(self, msg): + msg = msg.strip() + msg = re.sub(r'[\n\t]', '', msg) + if as_sql: + # the impl produces soft tabs, + # so search for blocks of 4 spaces + msg = re.sub(r' ', '', msg) + msg = re.sub('\;\n*$', '', msg) + + self.lines.append(msg) + + def flush(self): + pass - def __init__(self, dialect='default', as_sql=False): - self.dialect = _get_dialect(dialect) - self.impl = Impl(self.dialect, as_sql) - self.opts = opts - self.as_sql = as_sql + buf = buffer_() + class ctx(MigrationContext): def clear_assertions(self): - self.impl.assertion[:] = [] + buf.lines[:] = [] def assert_(self, *sql): # TODO: make this more flexible about # whitespace and such - eq_(self.impl.assertion, list(sql)) + eq_(buf.lines, list(sql)) def assert_contains(self, sql): - for stmt in self.impl.assertion: + for stmt in buf.lines: if sql in stmt: return else: assert False, "Could not locate fragment %r in %r" % ( sql, - self.impl.assertion + buf.lines ) - context = ctx(dialect, as_sql) + + if as_sql: + opts['as_sql'] = as_sql + if literal_binds: + opts['literal_binds'] = literal_binds + ctx_dialect = _get_dialect(dialect) + if not as_sql: + def execute(stmt, *multiparam, **param): + if isinstance(stmt, string_types): + stmt = text(stmt) + assert stmt.supports_execution + sql = text_type(stmt.compile(dialect=ctx_dialect)) + + buf.write(sql) + + connection = mock.Mock(dialect=ctx_dialect, execute=execute) + else: + opts['output_buffer'] = buf + connection = None + context = ctx( + ctx_dialect, + connection, + opts) + alembic.op._proxy = Operations(context) return context - diff --git a/docs/build/changelog.rst b/docs/build/changelog.rst index 9b27cc7..dbfd323 100644 --- a/docs/build/changelog.rst +++ b/docs/build/changelog.rst @@ -6,6 +6,24 @@ Changelog .. changelog:: :version: 0.7.6 + .. change:: + :tags: feature, operations + :tickets: 255 + + Added a new option + :paramref:`.EnvironmentContext.configure.literal_binds`, which + will pass the ``literal_binds`` flag into the compilation of SQL + constructs when using "offline" mode. This has the effect that + SQL objects like inserts, updates, deletes as well as textual + statements sent using ``text()`` will be compiled such that the dialect + will attempt to render literal values "inline" automatically. + Only a subset of types is typically supported; the + :meth:`.Operations.inline_literal` construct remains as the construct + used to force a specific literal representation of a value. + The :paramref:`.EnvironmentContext.configure.literal_binds` flag + is added to the "offline" section of the ``env.py`` files generated + in new environments. + .. change:: :tags: bug, batch :tickets: 289 diff --git a/tests/test_bulk_insert.py b/tests/test_bulk_insert.py index 5121e90..2655630 100644 --- a/tests/test_bulk_insert.py +++ b/tests/test_bulk_insert.py @@ -165,15 +165,21 @@ class BulkInsertTest(TestBase): # doesn't have an IDENTITY column context.assert_( 'SET IDENTITY_INSERT ins_table ON', + 'GO', "INSERT INTO ins_table (id, v1, v2) " "VALUES (1, 'row v1', 'row v5')", + 'GO', "INSERT INTO ins_table (id, v1, v2) " "VALUES (2, 'row v2', 'row v6')", + 'GO', "INSERT INTO ins_table (id, v1, v2) " "VALUES (3, 'row v3', 'row v7')", + 'GO', "INSERT INTO ins_table (id, v1, v2) " "VALUES (4, 'row v4', 'row v8')", - 'SET IDENTITY_INSERT ins_table OFF' + 'GO', + 'SET IDENTITY_INSERT ins_table OFF', + 'GO', ) def test_bulk_insert_from_new_table(self): diff --git a/tests/test_mysql.py b/tests/test_mysql.py index 7ce71a9..2dc8838 100644 --- a/tests/test_mysql.py +++ b/tests/test_mysql.py @@ -167,7 +167,7 @@ class MySQLOpTest(TestBase): context = op_fixture('mysql') op.drop_constraint('primary', 't1', type_='primary') context.assert_( - "ALTER TABLE t1 DROP PRIMARY KEY " + "ALTER TABLE t1 DROP PRIMARY KEY" ) def test_drop_unique(self): diff --git a/tests/test_op.py b/tests/test_op.py index 307ac42..7d5f83e 100644 --- a/tests/test_op.py +++ b/tests/test_op.py @@ -819,3 +819,41 @@ class OpTest(TestBase): context = op_fixture('mssql') op.drop_index('ik_test', tablename='t1') context.assert_("DROP INDEX ik_test ON t1") + + +class SQLModeOpTest(TestBase): + @config.requirements.sqlalchemy_09 + def test_auto_literals(self): + context = op_fixture(as_sql=True, literal_binds=True) + from sqlalchemy.sql import table, column + from sqlalchemy import String, Integer + + account = table('account', + column('name', String), + column('id', Integer) + ) + op.execute( + account.update(). + where(account.c.name == op.inline_literal('account 1')). + values({'name': op.inline_literal('account 2')}) + ) + op.execute(text("update table set foo=:bar").bindparams(bar='bat')) + context.assert_( + "UPDATE account SET name='account 2' " + "WHERE account.name = 'account 1'", + "update table set foo='bat'" + ) + + def test_create_table_literal_binds(self): + context = op_fixture(as_sql=True, literal_binds=True) + + op.create_table( + "some_table", + Column('id', Integer, primary_key=True), + Column('st_id', Integer, ForeignKey('some_table.id')) + ) + + context.assert_( + "CREATE TABLE some_table (id INTEGER NOT NULL, st_id INTEGER, " + "PRIMARY KEY (id), FOREIGN KEY(st_id) REFERENCES some_table (id))" + ) \ No newline at end of file -- cgit v1.2.1