Single table inheritance - column not mapped

[Kuba Dolecki]

Hi,

We've used joined table inheritance up to this point, but performance issues are making us explore single table inheritance. I'm running into a problem with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 0.16, and Flask 0.8. Here is the inheritance: 

class Community(BaseModel, db.Model):

   # Bar

class Group(Community):

    ###

        Group utilized joined table inheritance to extend Community

    ###

    id  = db.Column(db.Integer, db.ForeignKey('community.id'), primary_key=True, nullable=False)

    autocreated = db.Column(db.Boolean, nullable=False, default=False)

    @declared_attr

        def __mapper_args__(cls):

            name = cls._name()

            identity = {

                'polymorphic_on': case([

                    (cls.autocreated == True, "autocreated_group"),

                    (cls.autocreated == False, "group")

                ]),

                'polymorphic_identity': name

            }

            return identity

class AutocreatedGroup(Group):

    ###

        AutocreatedGroup extends Group through joined table inheritance

    ###

    id = db.Column(db.Integer, db.ForeignKey('group.id'), primary_key=True, \

        nullable=False)

    

    @declared_attr

    def __mapper_args__(cls):

        name = cls._name()

        identity = {

            'polymorphic_on': cls.autocreated_type,

            'polymorphic_identity': name

        }

        return identity

class TopicAutocreatedGroup(AutocreatedGroup):

    ###

        TopicAutocreatedGroup extends AutocreatedGroup through single table inheritance

    ###

    @declared_attr

    def __mapper_args__(cls):

        return {'polymorphic_identity': cls._name()}

    topic_created_from_id = db.Column(

        db.Integer,

        db.ForeignKey('topic.id'),

        nullable = True

    )

    topic_created_from = db.relation(

        "Topic",

        primaryjoin="Topic.id == TopicAutocreatedGroup.topic_created_from_id",

        backref=db.backref("topic_groups", uselist=True),

        uselist=False, lazy='subquery'

    )

Here is the stacktrace: 

Traceback (most recent call last):

  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1518, in __call__

    return self.wsgi_app(environ, start_response)

  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1506, in wsgi_app

    response = self.make_response(self.handle_exception(e))

  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1504, in wsgi_app

    response = self.full_dispatch_request()

  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1264, in full_dispatch_request

    rv = self.handle_user_exception(e)

  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1260, in full_dispatch_request

    rv = self.preprocess_request()

  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1387, in preprocess_request

    rv = func()

  File "/srv/franklin-api/api/helpers/login_manager.py", line 53, in _load_user

    user = User.for_auth_token(token)

  File "/srv/franklin-api/api/models/user.py", line 584, in for_auth_token

    user_for_token = cls.query.filter(cls.auth_token == auth_token).first()

  File "/srv/.env/lib/python2.6/site-packages/flask_sqlalchemy.py", line 394, in __get__

    mapper = orm.class_mapper(type)

  File "/srv/.env/lib/python2.6/site-packages/sqlalchemy/orm/util.py", line 660, in class_mapper

    mapperlib.configure_mappers()

  File "/srv/.env/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py", line 2255, in configure_mappers

    raise e

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers.  Original exception was: Class <class 'api.models.autocreated_group.TopicAutocreatedGroup'> does not have a mapped column named 'topic_created_from_id'

It appears the Columns inside classes that inherit through single table inheritance are not included in the mapper. Any idea of what may be going wrong?

Thank you,

Kuba

P.S. What should __mapper_args__ look like for AutocreatedGroup? Should "topic_created_from_id" be automatically included in the "excluded_properties" value? Currently, it's empty if I print it out. 

[Michael Bayer]

On Aug 15, 2012, at 10:45 AM, Kuba Dolecki wrote:

Hi,

We've used joined table inheritance up to this point, but performance issues are making us explore single table inheritance. I'm running into a problem with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 0.16, and Flask 0.8. Here is the inheritance: 

there are many things that are confusing about this model.

1.  I don't see any mention of __tablename__ anywhere.  Where is the table defined ?

2. You say this is single table inheritance, but I see each subclass has it's own primary key column, with a foreign key column pointing back to the superclass "table" (which isn't present).   This doesn't fit with single table inheritance at all.

3. There's a second re-definition of "polymorphic_on".   This isn't necessarily a mistake, as it is supported that loading a specific sub-hierarchy may have a different system of determining polymorphic identity - usually this applies to concrete schemes.  But with joined or single table inheritance it's extremely unusual, as the base table is what determines object identity in both of these cases.   If you load a collection of "community" rows, the ORM needs to decide from that base table alone what the subclass should be.   It won't see a sub-polymorphic-on unless the original query was against that subclass.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/l3a-3tBjaf4J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Kuba Dolecki]

You responded! Woot. Thanks. Here's some additional info:

1. __tablename__ is generated automatically based on the class name. Flask-SQLAlchemy does this for us. The __tablename__ for AutocreatedGroup and TopicAutocreatedGroup model is both "autocreated_group".

2. Sorry, I might have included too much code in my snippet. Group extends Community through joined table inheritance (primary key with foreign key to community). AutocreatedGroup extends Group in the same manner. The relationship of interest is between AutocreatedGroup and TopicAutocreatedGroup. TopicAutocreatedGroup does appear to inherit through a single table. 

3. Yeah, I'm redefining polymorphic_on for Group since it depends on a boolean switch. I'm doing the same for AutocreatedGroup since it depends on the name of the class. 

AutocreatedGroup and TopicAutocreatedGroup do appear to have all of the same columns of Group and Community. The AutocreatedGroup table does have the topic_created_from_id column, but that same column doesn't appear in the mapper. 

Thanks, again. I'm definitely not a SQL Alchemy expert, so your help is appreciated greatly. 

[Michael Bayer]

On Aug 15, 2012, at 11:41 AM, Kuba Dolecki wrote:

You responded! Woot. Thanks. Here's some additional info:

1. __tablename__ is generated automatically based on the class name. Flask-SQLAlchemy does this for us. The __tablename__ for AutocreatedGroup and TopicAutocreatedGroup model is both "autocreated_group".

<many questions here, removed once I read point #2>

2. Sorry, I might have included too much code in my snippet. Group extends Community through joined table inheritance (primary key with foreign key to community). AutocreatedGroup extends Group in the same manner. The relationship of interest is between AutocreatedGroup and TopicAutocreatedGroup. TopicAutocreatedGroup does appear to inherit through a single table. 

OH.   no, this wasn't *enough* code.  I always need to know the full expanse of classes and tables in play.  I'll recreate the full mapping now based on your descriptions here in order to reproduce.

3. Yeah, I'm redefining polymorphic_on for Group since it depends on a boolean switch. I'm doing the same for AutocreatedGroup since it depends on the name of the class. 

AutocreatedGroup and TopicAutocreatedGroup do appear to have all of the same columns of Group and Community. The AutocreatedGroup table does have the topic_created_from_id column, but that same column doesn't appear in the mapper. 

there is logic added to declarative that detects columns applied to a single table inheritance subclass, and moves them up to the superclass table, mapping it only for the subclass.  Due to the complex and deep chain of mappings here, this logic is likely failing.  let's create a test case and see.  (goes to do that).....OK yeah this isn't going to work right now and would require a major architectural change to the expression system in order to accommodate.   The declarative system adds the new column to the AutocreatedGroup.__table__ correctly.  However, AutocreatedGroup's "mapped" table is the join() of community->group->autocreated_group.  The collection of columns on this join() object is determined only once from the Table objects given to it.   Adding a new column to one of those Table objects after the fact is not affecting that collection so the column isn't mapped.

There's ways this might work but they are definitely destabilizing, I'l try to see if I can put something into 0.8 for that - basically I'd make derived selectables add an event listener to the selectable they're derived from, so that new column additions can propagate.

However, to make this work for now you'd need to declare topic_created_from_id on the AutocreatedGroup class directly.   A more involved workaround would be to delay the production of mappings on the hierarchy, though in 0.7 you need to do a slightly hacky approach for that (it's what's described in https://bitbucket.org/sqlalchemy/sqlalchemy/src/f47a971874b6/examples/declarative_reflection/declarative_reflection.py).

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/iUiRCq6w7bcJ.

[Kuba Dolecki]

Cool, I think the approach you outlined briefly works, and I look forward to hopefully seeing it in the next release. 

For now, I will just add the columns back to the AutocreatedGroup class. 

Again, thank you. I really appreciate your help. I'll make sure to make my emails in the future more explanatory according to your input.