- 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.

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
@@ -7,6 +7,24 @@ 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