[sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
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.
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
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.
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.
--
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.
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
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.
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Sep 4, 2014, at 6:58 PM, Ken Lareau klar...@tagged.com wrote:
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).
A row in the 3-way table means that for this project_id, and pkg_def_id, and
app_id, that's a single allowed value, so that's star schema, OK (though they
call star schema denormalized - i don't exactly understand why, if you make
all the columns part of the primary key). But then you have functions that
give you lists of things based on two-column slices of that table, in order to
relate different dimensions to each other; projects-app_definitions, means you
can have Project (X) - AppDefnition(Y) any number of times, for every value of
pkg_def_id.I guess that's the awkward part here. Those aren't really
collection-based relationships so much as analytical queries across dimension
tables. They are valid queries but aren't really object/relational - when
we map a class to another with a many-to-many collection in between, that means
something more specific.
So maybe its just, object relational mapping assumes certain patterns are
mappable to objects and collections, and 3-way associations aren't quite in
that pattern :).
--
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.
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Thu, Sep 4, 2014 at 4:35 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Sep 4, 2014, at 6:58 PM, Ken Lareau klar...@tagged.com wrote:
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).
A row in the 3-way table means that for this project_id, and pkg_def_id,
and app_id, that’s a single allowed value, so that’s star schema, OK
(though they call star schema “denormalized” - i don’t exactly understand
why, if you make all the columns part of the primary key). But then you
have functions that give you lists of things based on two-column slices of
that table, in order to relate different dimensions to each other;
projects-app_definitions, means you can have Project (X) -
AppDefnition(Y) any number of times, for every value of pkg_def_id.I
guess that’s the awkward part here. Those aren’t really collection-based
“relationships” so much as analytical queries across dimension tables.
They are valid queries but aren’t really “object/relational” - when we map
a class to another with a many-to-many collection in between, that means
something more specific.
So maybe its just, object relational mapping assumes certain patterns are
mappable to objects and collections, and 3-way associations aren’t quite in
that pattern :).
Hmm, understood, though the main reason for these relationships were to
avoid having to make
DB queries within the main application code (pushing them out into the
models) and simplify the
logic; if you had seen what our application code looked like before, you
might shudder even more
than over what we're doing here. :)
Is there a way to essentially allow something like 'for app in
project.applications:' without having
to make an explicit query to the DB first, but avoiding the awkwardness of
the current setup?
--
- Ken Lareau
--
You received this message because you are
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Sep 4, 2014, at 7:50 PM, Ken Lareau klar...@tagged.com wrote: Is there a way to essentially allow something like 'for app in project.applications:' without having to make an explicit query to the DB first, but avoiding the awkwardness of the current setup? just use the viewonly=True and we can all go home :) I'm not too upset about it -- 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.
Re: [sqlalchemy] Multiple delete attempts of same rows with a star schema configuration
On Thu, Sep 4, 2014 at 4:54 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 4, 2014, at 7:50 PM, Ken Lareau klar...@tagged.com wrote: Is there a way to essentially allow something like 'for app in project.applications:' without having to make an explicit query to the DB first, but avoiding the awkwardness of the current setup? just use the viewonly=True and we can all go home :) I’m not too upset about it Got it... the change worked fine, thanks for all the help. :) -- - 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.
