drop column does not work on persistent sqlite databases

[GoogleCodeExporter]

On versions:
- sqlalchemy_migrate-0.7.1-py2.6.egg
- SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg

Problem:
- when I'm trying to drop a column, I get a OperationalError (no such table)
- on sqlite with a non-memory database

Detailed:
I attached a example script (example.py) which does
- create a persistent sqlite database (problem does not occur in temporary dbs 
with this example script)
- create a table
- remove a column with migrate.changeset.schema.drop_column

I get this error:
Traceback (most recent call last):
  File "example.py", line 26, in <module>
    gitolite.feedcenter.blubb.run()
  File "/Users/jone/projects/sqlalchemy/example.py", line 31, in run
    drop_column(table=mytable, column='foo', engine=engine)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/schema.py", line 48, in drop_column
    return table.drop_column(column, *p, **kw)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/schema.py", line 451, in drop_column
    column.drop(table=self, *p, **kw)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/schema.py", line 551, in drop
    engine._run_visitor(visitorcallable, self, connection, **kwargs)
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/base.py", line 2178, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/base.py", line 1857, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/ansisql.py", line 57, in traverse_single
    ret = super(AlterTableVisitor, self).traverse_single(elem)
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/sql/visitors.py", line 86, in traverse_single
    return meth(obj, **kw)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/databases/sqlite.py", line 94, in visit_column
    super(SQLiteColumnDropper,self).visit_column(column)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/databases/sqlite.py", line 57, in visit_column
    self.recreate_table(table,column,delta)
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/databases/sqlite.py", line 46, in recreate_table
    self.execute()
  File "/Users/jone/python/eggs/sqlalchemy_migrate-0.7.1-py2.6.egg/migrate/changeset/ansisql.py", line 46, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/base.py", line 1358, in execute
    params)
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/base.py", line 1535, in _execute_text
    statement, parameters
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/base.py", line 1599, in _execute_context
    context)
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/base.py", line 1592, in _execute_context
    context)
  File "/Users/jone/python/eggs/SQLAlchemy-0.7.1-py2.6-macosx-10.6-x86_64.egg/sqlalchemy/engine/default.py", line 325, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such table: mytable 
'INSERT INTO mytable SELECT id from migration_tmp' ()

The problem seems to be in 
migrate.changeset.databases.sqlite.SQLiteHelper.recreate_table :
- there the table is altered (renamed to migration_tmp)
- the changed table is created
- the data from migration_tmp is inserted . << here the connection does not 
know that the table was just created

A solution for the problem:
after table was created: close the connection, then reconnect (see sqlite.patch)
I don't know if this is a good thing to do it like that, but somehow the 
connection has to be flushed..

Cheers
jones

Original issue reported on code.google.com by tscho...@gmail.com on 12 Jul 2011 at 8:33

Attachments:

• example.py
• sqlite.patch

[GoogleCodeExporter]

Just hit the same one.

The weird part of it is that in my case, the command line (manage.py) works 
fine, upgrade, downgrade and test. The trouble only appear (but it always 
appear) from my module. Leading me t maybe a possible fix in my module

What my module does:

    try:
        migrate_api.db_version(db_uri, repo_path)
    except DatabaseNotControlledError:
        migrate_api.version_control(db_uri, repo_path, 0)
    migrate_api.upgrade(db_uri, repo_path)

I am doing something wrong over there ?

The trouble itself is that the table is ceated (table.create()) on another 
connection than the rest than the operation (ALTER and INSERT), and by the time 
the INSERT comes, the create() has not ben COMMITED (I guess.)

Original comment by al.yazdi on 26 Oct 2011 at 6:15

[GoogleCodeExporter]

Based on the patch from Jones, I built up the following one, which also works 
fine, and closes no connection.

http://code.google.com/r/alyazdi-patches/source/detail?r=e5bd2821eea8aac5c5bb240
83c27d62f680714b9

Original comment by al.yazdi on 26 Oct 2011 at 6:34

[GoogleCodeExporter]

This issue was closed by revision a6edab8d553b.

Original comment by jan.ditt...@gmail.com on 28 Oct 2011 at 10:27

• Changed state: Fixed