[sqlalchemy] Multiple delete attempts of same rows with a star schema configuration

[Ken Lareau]

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)


If I select a row from the projects table and try to delete it, when I try
to commit
the session I get something like this:

[INFO] DELETE FROM project_package WHERE project_package.project_id = ? AND
project_package.app_id = ?
[INFO] (1L, 1L)
[INFO] DELETE FROM project_package WHERE project_package.project_id = ? AND
project_package.pkg_def_id = ?
[INFO] (1L, 1L)
[INFO] ROLLBACK

At the time of the delete, the project_package table simply has this:

[('project_id', 1L), ('pkg_def_id', 1L), ('app_id', 1L)]

Obviously the combination of the "ondelete='cascade'" settings in the
project_package table and the 'passive_deletes' settings in the
relationships
is what's triggering this, but after try many different searches on Google,
I'm
not finding the right solution to fix this; obviously I only want it to
attempt to
delete the row once, but each relationship in project is triggering its own
delete which is "bad".

So... help?  Am I missing something simple here, or is my model not good
enough to do the right thing?

-- 
- 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.