2015-09-21 10:42 GMT-07:00 Sergi Pons Freixes <[email protected]>:
> > 2015-09-19 8:18 GMT-07:00 Mike Bayer <[email protected]>: > >> what is your search_path set to ? see >> http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path >> for background on this. It is highly recommended that search_path remain >> set on "public" and that you do *not* refer to the "public" name directly >> in any table definitions. Feel free to share a model that includes all >> the tables and constraints in question as well. >> >> > If I understood it correcty, we're not setting search_path. Sample with > pyramid's pshell: > -----------8<-------------- > $ pshell development.ini > In [1]: from notifications.models import * > > In [2]: Base.metadata.schema is None > Out[2]: True > > In [3]: Base.metadata.tables['notifications.email_attachment'].schema > Out[3]: 'notifications' > -----------8<-------------- > > We're only using "public" for the alembic_versions table, all the rest are > on the same schema ("notifications" in this case). > > Ok, I have a simpler test case. Just changing the schema from "public" to "notifications" triggers the issue. On the issue-free case, my nofications.email.models.py is: -----------8<-------------- from sqlalchemy import Column, Integer, ForeignKey from notifications.sqla_base import Base class t1(Base): __tablename__='t1' __table_args__ = {'schema': 'public'} def __init__(self): pass class t2(Base): __tablename__='t2' __table_args__ = {'schema': 'public'} t1id = Column('t1id', Integer, ForeignKey(t1.id)) def __init__(self, t1id=None): self.t1id = t1id -----------8<-------------- A first run of 'alembic -c development.ini revision -m "upgrade" --autogenerate' creates the tables as expected: -----------8<-------------- ... def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table('t1', sa.Column('fxt', sa.String(), nullable=True), sa.Column('id', sa.Integer(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='public' ) op.create_table('t2', sa.Column('t1id', sa.Integer(), nullable=True), sa.Column('foo', sa.Integer(), nullable=True), sa.Column('fxt', sa.String(), nullable=True), sa.Column('id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['t1id'], ['public.t1.id'], ), sa.PrimaryKeyConstraint('id'), schema='public' ) ### end Alembic commands ### ... -----------8<-------------- And a second run does nothing, also as expected: -----------8<-------------- ... def upgrade(): ### commands auto generated by Alembic - please adjust! ### pass ### end Alembic commands ### ... -----------8<-------------- But, when changing my model so that "__table_args__ = {'schema': 'notifications'}", the outputs of alembic are: First run (fine): -----------8<-------------- ... def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table('t1', sa.Column('fxt', sa.String(), nullable=True), sa.Column('id', sa.Integer(), nullable=False), sa.PrimaryKeyConstraint('id'), schema='notifications' ) op.create_table('t2', sa.Column('t1id', sa.Integer(), nullable=True), sa.Column('foo', sa.Integer(), nullable=True), sa.Column('fxt', sa.String(), nullable=True), sa.Column('id', sa.Integer(), nullable=False), sa.ForeignKeyConstraint(['t1id'], ['notifications.t1.id'], ), sa.PrimaryKeyConstraint('id'), schema='notifications' ) ### end Alembic commands ### ... -----------8<-------------- Second run (oops!): -----------8<-------------- ... def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.drop_constraint('t2_t1id_fkey', 't2', type_='foreignkey') op.create_foreign_key(None, 't2', 't1', ['t1id'], ['id'], source_schema='notifications', referent_schema='notifications') ### end Alembic commands ### ... -----------8<-------------- On both cases, env.py was: -----------8<-------------- from __future__ import with_statement from alembic import context from sqlalchemy import engine_from_config from pyramid.paster import setup_logging import notifications.models from notifications.sqla_base import DBSession, Base # this is the Alembic Config object, which provides # access to the values within the .ini file in use. config = context.config setup_logging(config.config_file_name) engine = engine_from_config(config.get_section('app:main'), 'sqlalchemy.') DBSession.configure(bind=engine) target_metadata = Base.metadata def run_migrations_offline(): ... def run_migrations_online(): connection = engine.connect() context.configure( connection=connection, target_metadata=target_metadata, include_schemas=True, version_table_schema='public' ) try: with context.begin_transaction(): context.run_migrations() finally: connection.close() if context.is_offline_mode(): run_migrations_offline() else: run_migrations_online() -----------8<-------------- Where notifications.models just has a "from notifications.email.models import *" statement, and notifications.sql_base.py: -----------8<-------------- from sqlalchemy.ext.declarative import declarative_base, declared_attr from sqlalchemy.orm import ( scoped_session, sessionmaker, ) from sqlalchemy import Column, Integer, String from zope.sqlalchemy import ZopeTransactionExtension DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) class NotificationsBase: @declared_attr def id(cls): return Column(Integer, primary_key=True) @declared_attr def fxt(cls): return Column(String, nullable=True) @classmethod def get_one(cls, **kwargs): query = DBSession.query(cls) for k, v in kwargs.items(): query = query.filter(getattr(cls, k) == v) return query.one() def __repr__(self): return "<%s id=%s>" % (self.__class__.__name__, self.id) Base = declarative_base(cls=NotificationsBase) -----------8<-------------- So, yes, it is definitely related with the schema. -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
