On Thu, Sep 4, 2014 at 3:30 PM, Michael Bayer <mike...@zzzcomputing.com>
wrote:
>
> On Sep 4, 2014, at 5:37 PM, Ken Lareau <klar...@tagged.com> wrote:
>
> So I have a few tables as follows (abbreviated for unnecessary columns):
>
> class Project(Base):
> __tablename__ = 'projects'
>
> id = Column(u'project_id', INTEGER(), primary_key=True)
>
> applications = relationship(
> 'AppDefinition',
> secondary=lambda: Base.metadata.tables['project_package'],
> passive_deletes=True,
> lazy=False,
> )
>
> package_definitions = relationship(
> 'PackageDefinition',
> secondary=lambda: Base.metadata.tables['project_package'],
> passive_deletes=True,
> )
>
>
> class PackageDefinition(Base):
> __tablename__ = 'package_definitions'
>
> id = Column(u'pkg_def_id', INTEGER(), primary_key=True)
>
> applications = relationship(
> 'AppDefinition',
> secondary=lambda: Base.metadata.tables['project_package'],
> passive_deletes=True,
> )
>
> projects = relationship(
> 'Project',
> secondary=lambda: Base.metadata.tables['project_package'],
> passive_deletes=True,
> )
>
>
> class AppDefinition(Base):
> __tablename__ = 'app_definitions'
>
> id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
>
> package_definitions = relationship(
> 'PackageDefinition',
> secondary=lambda: Base.metadata.tables['project_package'],
> passive_deletes=True,
> )
>
> projects = relationship(
> 'Project',
> secondary=lambda: Base.metadata.tables['project_package'],
> passive_deletes=True,
> )
>
>
> class ProjectPackage(Base):
> __tablename__ = 'project_package'
>
> project_id = Column(
> INTEGER(),
> ForeignKey('projects.project_id', ondelete='cascade'),
> primary_key=True
> )
> pkg_def_id = Column(
> INTEGER(),
> ForeignKey('package_definitions.pkg_def_id', ondelete='cascade'),
> primary_key=True
> )
> app_id = Column(
> SMALLINT(display_width=6),
> ForeignKey('app_definitions.AppID', ondelete='cascade'),
> primary_key=True
> )
>
> app_definition = relationship('AppDefinition', uselist=False)
> package_definition = relationship('PackageDefinition', uselist=False)
> project = relationship('Project', uselist=False)
>
>
> this model has serious issues, though it can be made to hobble along with
> some small changes. The “three way association table” is not a great
> pattern in the first place. Additionally, using that same table as
> “secondary” in all those places is clearly the issue here, and they don’t
> even have back_populates between them, which are used in many-to-many in
> order to try avoid “double deleting”. But that wouldn’t solve the issue
> here anyway.
>
As I feared, I figured this model would be frowned upon. :) Note that this
model (or the underlying database)
is not set in stone; if you have suggestions on a better way to link the
three main tables, I would love to hear
it (and we did add back_populates (somehow missed them during the
restructuring of the models), but as
you said, didn't help in this case).
>
> In this case, you have mapped to the project_package table directly;
> you’re using the association object pattern. The note at the end of
> http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
> applies here - you absolutely should have viewonly=True on all of those
> relationship() directives that refer to this association table as the
> “secondary” table. The ORM otherwise has no way to know what changes
> should result in what deletions where.
>
Will add the 'viewonly' option as suggested and see if that helps, but as
mentioned above, if you can suggest
a better model for this case, please let me know!
--
- Ken Lareau
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
