Issue #46 invalid

mysql.TINYINT(display_width=1) vs sa.Boolean()

dieselmachine avatardieselmachine created an issue

I'm generating diffs that are failing to equate these two types (in autogenerate._compare_columns). I understand the two are not strictly equivalent, but I found this note in the sqlalchemy comments (dialects/mysql/base.py):

"Note: following the usual MySQL conventions, TINYINT(1) columns reflected during Table(..., autoload=True) are treated as Boolean columns."

I am not sure whether the issue lies with sqlalchemy or alembic, but I did some grepping through the sqla code attempting to find where the tinyint was being cast to boolean and I was unable to find anything that looked relevant.

I'd like alembic to not detect these as changes during autogenerate, is there something I need to do in order to have this work?

Comments (2)

  1. dieselmachine

    After reading the discussion at https://groups.google.com/group/sqlalchemy/browse_thread/thread/b9c020804456f066 and reviewing the changeset at http://www.sqlalchemy.org/trac/changeset?reponame=&new=95ac46ca88eef268c54357e345a44fc5a930283a%40lib%2Fsqlalchemy%2Fdialects%2Fmysql%2Fbase.py&old=3d0efe038b8543a5ba78871860336346e7b17d96%40lib%2Fsqlalchemy%2Fdialects%2Fmysql%2Fbase.py I can see this behavior was intentional, so I guess it's not really a bug at all.

    You may want to scrub the related comment from the dialects/mysql/base.py file, as the information there is out of date and may lead people to expect unsupported behavior from the Inspector.

    The fix suggested in the linked discussion thread worked for me, and caused the tinyint != boolean ALTER statements to vanish from the migration. Thanks.

  2. Mike Bayer

    a more API-centric way of doing this is to use the column_reflect() event, new in 0.7. note the API for this event is changing in 0.8. Here's a demonstration:

    from sqlalchemy import *
    e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
    
    m = MetaData()
    t = Table("bool_table", m, 
        Column("mybool", Boolean)
    )
    m.drop_all(e)
    m.create_all(e)
    
    from sqlalchemy.dialects import mysql
    
    m2 = MetaData()
    t2 = Table("bool_table", m2, autoload=True, autoload_with=e)
    assert type(t2.c.mybool.type) is mysql.TINYINT
    
    from sqlalchemy import event
    
    @event.listens_for(Table, "column_reflect")
    # in 0.7, use this:
    def column_reflect(table, column_info):
    # in 0.8
    # def column_reflect(inspector, table, column_info):
        if type(column_info['type']) is mysql.TINYINT:
            column_info['type'] = Boolean()
    
    m3 = MetaData()
    t3 = Table("bool_table", m3, autoload=True, autoload_with=e)
    assert type(t3.c.mybool.type) is Boolean
    

    you might also use this approach to deal with your issue in #47.

  3. Log in to comment
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.