`migrate upgrade --preview_sql` raises error with database/dialect-specific migrations

*What steps will reproduce the problem?*

1. Create the following migration in 
src/migrations/versions/001_add_user_table_.py:

#START
import time, datetime
from sqlalchemy import *
from sqlalchemy.dialects.postgresql.base import UUID
from migrate import *

meta = MetaData()

user_table = Table('buildpunkt__%s'%'user', meta,
  Column('uuid', UUID(as_uuid=True), primary_key=True),
)

def upgrade(migrate_engine):
  # Upgrade operations go here. Don't create your own engine; bind
  # migrate_engine to your metadata
  meta.bind = migrate_engine
  user_table.create()

def downgrade(migrate_engine):
  # Operations to reverse the above upgrade go here.
  meta.bind = migrate_engine
  user_table.drop()
#END

2. Attempt an upgrade:

$ migrate upgrade --preview_sql 
postgresql+psycopg2://username:password@dbhost/dbname src/migrations

*What is the expected output?*

0 -> 1... 
done

*What do you see instead?*

0 -> 1... 
Traceback (most recent call last):
  File "migrate", line 9, in <module>
    load_entry_point('sqlalchemy-migrate==0.6', 'console_scripts', 'migrate')()
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/shell.py", line 203, in main
    ret = command_func(**kwargs)
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/api.py", line 185, in upgrade
    return _migrate(url, repository, version, upgrade=True, err=err, **opts)
  File "<string>", line 2, in _migrate
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine
    return f(*a, **kw)
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/api.py", line 350, in _migrate
    log.info(change.preview_sql(url, changeset.step, **opts))
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 118, in preview_sql
    return go(url, step, **args)
  File "<string>", line 2, in go
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine
    return f(*a, **kw)
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 115, in go
    self.run(engine, step)
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 140, in run
    script_func(engine)
  File "src/migrations/versions/001_add_user_table_.py", line 47, in upgrade
    user_table.create()
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/schema.py", line 440, in create
    bind.create(self, checkfirst=checkfirst)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 223, in create
    ddl.SchemaGenerator(self.dialect, self, **kwargs).traverse(entity)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 93, in traverse
    return traverse(obj, self.__traverse_options__, self._visitor_dict)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 204, in traverse
    return traverse_using(iterate(obj, opts), obj, visitors)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 198, in traverse_using
    meth(target)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 58, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/Users/admin/project/build/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 110, in <lambda>
    args['engine_arg_executor'] = lambda s, p = '': buf.write(str(s) + p)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/expression.py", line 1426, in __str__
    return unicode(self.compile()).encode('ascii', 'backslashreplace')
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/expression.py", line 1413, in compile
    compiler.compile()
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 702, in compile
    self.string = self.process(self.statement)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 715, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 54, in _compiler_dispatch
    return getter(visitor)(self, **kw)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1152, in visit_create_table
    first_pk=column.primary_key and not first_pk
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1251, in get_column_specification
    self.dialect.type_compiler.process(column.type)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 761, in process
    return type_._compiler_dispatch(self)
  File "/Users/admin/project/build/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 54, in _compiler_dispatch
    return getter(visitor)(self, **kw)
AttributeError: 'GenericTypeCompiler' object has no attribute 'visit_UUID'

*What version of the product are you using?*

Python=2.7.1
SQLAlchemy==0.6.6
sqlalchemy-migrate==0.6

*On what operating system?*

Mac OS X

*Please provide any additional information below.*

It appears that the '--preview_sql' uses a mock connection and generates 
generic SQL. It obviously then fails on code that expects a specific database 
or dialect feature, like PostgreSQL's UUID type. Wouldn't it be much cleaner 
(and easier) to have the '--preview_sql' option make the backend print the SQL 
as it is being generated? Why the extra step of doing a Mock compile with a 
generic backend?
Original issue reported on code.google.com by mafr...@gmail.com on 24 Feb 2011 at 8:35
qizhiyi / sqlalchemy-migrate

`migrate upgrade --preview_sql` raises error with database/dialect-specific migrations #114
