have dialect-specific TypeEngines support tranferrence between backends (reflection / tometadata)

[Former user]

Hi,

just tried to convert a SQL-Server-DB to SQLite using SQLAlchemy 0.5.3 / 0.5.4 and PyODBC 2.1.5 using the following code:

import sqlalchemy as sa

engine1 = sa.create_engine(connstr, encoding="utf-8", echo=True) # MSSql Server
engine2 = sa.create_engine(connstr2, encoding="utf-8", echo=True) # Sqlite db

meta1 = sa.MetaData(bind=engine1) # gives a deprecation warning (see below)
meta2 = sa.MetaData(bind=engine2)

meta1.reflect() # takes a while but works well

for table in meta1.tables.values():
    table.tometadata(meta2)

# no (real) complaints so far
# but this one makes some trouble
meta2.create_all()

Deprecation Warning

C:\Python26\lib\site-packages\sqlalchemy\databases\mssql.py:1068: DeprecationWar
ning: object.__new__() takes no parameters
  return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs)

Traceback thrown by create_all()

see attached file

[Mike Bayer]

as yet we have never tried to support the use case of transferring types between backends. For the case here, it would be possible, although for most non-trivial cases it would not, for such types as PG's INTERVAL, UUID or MySQL's BIT type. The work involved would require TypeEngine objects that are specific to some database to specify a "fallback" type when being converted to some other engine...or perhaps this can be automated within TypeEngine.dialect_impl() (that may make this easier than the "2 day" severity given).

there's been a major refactoring of types in 0.6 so any big changes like this are targeted there. of particular interest is the fact that 0.6 will in many cases rely upon the generic types (i.e. String, DateTime) a lot more than the DB-specific ones, which have been greatly reduced (although not for MS and MySQL, whose types have tons of DB-specific options).

as a workaround for now you might want to iterate through the columns of your reflected Table objects and change them to be "generic" types, i.e. MSString becomes String, etc.

[Mike Bayer]

there's really nothing to be done here, and the traceback attached does not seem to be against anything still currently present.

if we try to emit CREATE TABLE given a type object that's for the wrong database, it just treats the type as the "generic":

from sqlalchemy import *
from sqlalchemy.dialects import mysql, mssql
from sqlalchemy import schema

m = MetaData()

t = Table(
    't', m, Column('x', mysql.TIME())
)

print schema.CreateTable(t).compile(dialect=mssql.dialect())

that is, it doesn't try to instantiate mssql.TIME() since we aren't starting with the base sqltypes.TIME.

For custom translations, again there are ways to rewrite the tables, including doing it within the column_reflect() event. It's not something we can support as "built in" to all types since there are so many ambiguous cases.