On Feb 22, 2013, at 12:04 AM, Rick Harris <[email protected]> wrote:
> Hi all,
>
> I recently ran into an issue where one of our `sqlalchemy-migrate` downgrade
> scripts was failing because it was trying to drop an index twice.
>
> The root cause appears to be that SQLAlchemy's `idx.drop()` call does not
> update the `indexes` set for the associated table.
>
> I've attached a test case which hopefully explains the situation a little
> better.
>
> My question is: is this expected behavior? Given that `index.create` mutates
> `table.indexes`, should we expect `index.drop` to do the inverse and remove
> it?
>
> My naive assumption that it should, but if that wrong, I'd love to hear why.
index.create() doesn't mutate table.indexes, just the creation of the index
does:
assert len(users.indexes) == 0
email_idx = sa.Index('idx_users_email', users.c.email)
assert len(users.indexes) == 1
the structure of table metadata (by that term we mean, the whole structure of
MetaData, Table, Column, Index, Constraint, etc.) is intended to be a fixed
structure with only "additive" mutability - that is, you build one up to fully
represent a particular schema. It then offers create()/drop() hooks at
various locations which provide the service of emitting DDL for a particular
part of the metadata to a particular database connection. However, the
metadata structure is not by any definition be "tied" to one particular
database. You can emit create()/drop() for many engines/connections and this
has no effect on the metadata structure itself - it only serves as a form that
can be rendered into some particular DDL.
This is why you'll see that the concept of saying "metadata.bind = engine" or
"MetaData(engine)" as it does in your test script is getting very hard to find
in the documentation; this is a convenience feature that I identified some
years ago as far more confusing to a lot of users than it is potentially useful
as it creates the impression that the MetaData structure represents some kind
of synchronized representation of a database; it doesn't. So I never use this
feature and I've been discouraging its use for years. You'll see some
exposition of this in the green note in this section:
http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html#connectionless-execution-implicit-execution
The sqlalchemy-migrate project tried to twist the MetaData structure into one
that represents a "live" database structure, such that adding/removing items
would emit ALTER statements and similar to a database; but this was never a
great way to do things and it's a key reason I eventually found the resources
to create the Alembic product which doesn't rely upon this pattern.
In general, the metadata structure has only extremely limited support for
non-additive mutability.
>
> Thanks!
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
> <test_drop_index.py>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
