On Aug 10, 2011, at 6:56 AM, erikj wrote:
> Hi,
>
> I have a table and a view mapped to objects
> as such :
>
> class A(object):
> mapped to a table
> with a primary key column id
> with a onetomany attribute list_of_b, being a relation
> to all rows of B with a_id == self.id
>
> class B(object):
> mapped to a view
> with a column a_id being a reference to the id column of A
>
> Since B is mapped to a view, no updates should happen on B.
>
> So, in A, the list_of_b relation is configured with :
>
> passive_updates = True,
> passive_deletes = 'all',
> viewonly = True,
> cascade = False
>
> But whenever an object of A is deleted, sqlalchemy tries to update
> the a_id columns of all rows of B that were loaded in memory, which
> of course should not happen, since B is a view.
You should only need "viewonly=True", which disables any persistence activity
on the relationship. A.list_of_bs would not participate in the flush
including no foreign key sync operations.
A full test case demonstrating this behavior, using SQLite so that a "B" table
can be created with a non-enforcing FK, is attached. I thought perhaps
setting a backref might not propagate the "viewonly" but this also works as
expected. Check your configuration again to ensure it isn't doing something
else.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
list_of_b = relationship("B", viewonly=True, backref="a")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
# viewonly is set on both sides
assert A.list_of_b.property.viewonly == True
assert B.a.property.viewonly == True
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
# create three "B" rows. SQLite doesn't enforce
# foregin keys so we are OK.
e.execute(
B.__table__.insert(),
{'id':1, 'a_id':'1'},
{'id':2, 'a_id':'1'},
{'id':3, 'a_id':'1'},
)
s = Session(e)
# create A with al three "B"s as the collection
a = A(list_of_b = s.query(B).all())
assert len(a.list_of_b) == 3
s.add(a)
s.commit()
# DB has one A row, three B rows
assert e.execute(A.__table__.select()).fetchall() == [(1, )]
assert e.execute(B.__table__.select().order_by(B.__table__.c.id)).fetchall() == [(1, 1), (2, 1), (3, 1)]
# lazyload the bs, works
assert len(a.list_of_b) == 3
# delete the A
s.delete(a)
s.commit()
# no more A rows, three B rows
assert e.execute(A.__table__.select()).fetchall() == []
assert e.execute(B.__table__.select().order_by(B.__table__.c.id)).fetchall() == [(1, 1), (2, 1), (3, 1)]
