Running upgrade/downgrade in a transaction

[Joes]

Hello,

I'm trying to run upgrade/downgrade statements in one transaction,
without luck so far.
Biggest problem - upgrade scripts use SQLA Engine and create new
connection object for each change statement.

Even if I'm binding metadata to connection, it does not work, as
ChangesetColumn.create() uses "protected" method '_run_visitor' from
Engine which are not present in Connection class (_run_visitor as an
example).
Engine._run_visitor is just a small wrapper which checks if
connection was passed in, if not - creates one through
contextual_connect() call.

Yes, I can pass connection explicitly for each column.create() call,
but it is very error prone and easy to forget. I would assume that
it's easy not to rely on _run_visitor() and create connection
manually, this way both Engine and Connection instances will work
properly. I can provide patch for that, if needed.

So, what are the plans (if any) to support single connection for
migration scripts?

Thanks,
Serge.

[Domen Kožar]

Hello Joes

It is a known restriction. It has high priority on my TODO list. I
have exams to finish until 4th of September, then I will probably go
fixing migrate.
Patches are also welcome:)

cheers, Domen

[Sergey Koval]

Hi,

 I played with it a bit and here's what I got: http://hg.in-square.net/sqlalchemy-migrate/rev/481c8fa97ce9

 Basically, for each script executed, it will create and destroy connection. Instead of passing engine to the script, it will pass connection object instead.
If script requires Engine for some reason, it is always accessible using conn.engine property. Also, connection and engine are generally exchangeable,
so in some cases (MockConnection which does not have connect/close) Engine can be passed instead.

 I also changed tests to use connection instead of engine. Problem is that I'm using Windows x64 and Python x64, which does not have bindings to anything except of SQLite. All tests pass for SQLite, except of shell tests, as it can't find migrate script. I will migrate to x86 Python and will try it with PostgreSQL.

 SQLite notice: transactions don't help at all - pysqlite (or sqlite3) always commits transaction for scheme modification queries, commits before every select query (or PRAGMA statement), etc. See more information here: http://docs.python.org/library/sqlite3.html#controlling-transactions. I was bashing head against the wall for last day, trying to figure out why table stays in the database after rollback, even though exactly same SQL works fine when executed from sqlite3 console tool.

P.S. My text editor automatically removes trailing spaces, so diff contains some lines that appear to have no visible changes.

Serge.