Re: [sqlalchemy] passive_deletes/updates with sqlite
On 09/13/2010 05:49 PM, Michael Bayer wrote:
On Sep 13, 2010, at 11:16 AM, alex bodnaru wrote:
hope my approach isn't too simplist, but onetomany is usually implemented in
rdbms by an manytoone column or a few of them, with or without ri clauses:
thus,
a foreign key or an index.
conversely, a manytoone relation has an implicit onetomany one (or an
explicit
onetoone).
if you read what I wrote, I was explaining, that we architecturally choose not
to generate the implicit reverse direction when it isn't specified by the
user. And that this decision is not too controversial since Hibernate made
the same one.
the example i've given with elixir (look at the sql echo) shows the onetomany
updates the foreign key to null, not knowing they wouldn't be found in the
cascading delete. i'm searching the exact point elixir should give the
passive_deletes to the sa relation, thus to force it to give it to the right
side of it.
right - Elixir has a more abstracted layer of user configuration, which is
basically what you're asking SQLAlchemy to build into it. Id rather make
things simpler on the inside, not more magical.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
well i found the problem (it's in my code). i was hilariously dealing with
something more magical in sa then in elixir: i've used backref from sa instead
of inverse from elixir. for this specific case it's simply my error to do this,
since passive_deletes is an argument to be passed to an existing relation, but
the magic i usually wanted to achieve with backref was auto-creating a onetomany
relation to complement a manytoone one, especially when i don't wish to touch
the file of the parent entity.
btw, to achieve this same magic with elixir i've made in the past an elixir
extension, that was rejected by elixir people, that pointed me to backref.
best regards and thanks again,
alex
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey,
create_engine, ForeignKeyConstraint)
from sqlalchemy.orm import (mapper, relationship, sessionmaker)
from elixir import *
from sqlalchemy.interfaces import PoolListener
class SQLiteFKListener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.execute('PRAGMA foreign_keys = ON;')
engine = create_engine(sqlite:///:memory:, echo=True, listeners=[SQLiteFKListener()])
metadata.bind = engine
class MyClass(Entity):
using_options(tablename='mytable')
id = Field(Integer, primary_key=True, autoincrement=True)
name = Field(String(20))
children = OneToMany('MyOtherClass', passive_deletes=True)
def __repr__(self):
return 'MyClass %s, %s' % (None if self.id is None else str(self.id), self.name)
class MyOtherClass(Entity):
using_options(tablename='myothertable')
id = Field(Integer, primary_key=True, autoincrement=True)
name = Field(String(20))
parent = ManyToOne('MyClass', inverse='children', colname='parent_id', ondelete=cascade)
def __repr__(self):
return 'MyOtherClass %s, %s, %s' % (None if self.parent_id is None else str(self.parent_id), None if self.id is None else str(self.id), self.name)
setup_all()
create_all()
alex = MyClass(name='alex')
pisi = MyClass(name='pisi')
print alex, pisi
#session.commit()
session.flush()
print alex, pisi
alexdagan = MyOtherClass(parent=alex, name='dagan')
alexshaked = MyOtherClass(parent=alex, name='shaked')
pisidagan = MyOtherClass(parent=pisi, name='dagan')
pisishaked = MyOtherClass(parent=pisi, name='shaked')
#session.commit()
session.flush()
shaked1 = session.query(MyOtherClass).filter_by(parent_id=1, name=u'shaked')
session.delete(alex)
#session.commit()
session.flush()
for my in session.query(MyClass).all():
print my
for my in session.query(MyOtherClass).all():
print my
Re: [sqlalchemy] passive_deletes/updates with sqlite
On Sep 12, 2010, at 11:02 PM, alex bodnaru wrote: thanks a lot michael. indeed it works, but it seems counter-intuitive a little since passive_* should in my opinion be on the side of the on * cascade it describes. anyway, it's great, and hope to make it work with elixir too. If you said session.delete(my_other_object), no action is needed if it references some particular my_object.It is only if you session.delete(my_object) that SQLAlchemy needs to do something - and for that, it must load those objects which reference my_object. It does this simply by using the one-to-many collection on my_object which states how to load my_other_objects with an association.So the one-to-many collection is what's needed here - if you only specified the other direction, SQLAlchemy would need to derive the forwards collection from it and apply it to the o2m parent class internally. Right now the internals are simple such that loading is only possible in the direction in which the relationship() was configured. It is certainly possible to alter relationship() to be able to load collections or associations in both directions so that a many-to-one relationship could transparently load from the other direction, though internally this would require doing pretty much what backref does explicitly - placing some linkage to the relationship on the target class of the many-to-one which could react to deletions of that target class. So the current implementation is much simpler than that, so the passive_updates flag goes onto the side of the relationship that ultimately needs it, so that SQLAlchemy doesn't need to generate the backref which is easy enough for the user to specify, and in very rare conditions may even be configured differently than the reverse direction. The original design of cascade is taken from Hibernate, probably the most widely used object relational tool in history. They too tend to define ORM-level information about the foreign key, including the CASCADE attributes on the primary key side: http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-declaration-key http://docs.jboss.org/hibernate/stable/core/reference/en/html/collections.html#collections-mapping -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On 09/13/2010 08:32 AM, Michael Bayer wrote: On Sep 12, 2010, at 11:02 PM, alex bodnaru wrote: thanks a lot michael. indeed it works, but it seems counter-intuitive a little since passive_* should in my opinion be on the side of the on * cascade it describes. anyway, it's great, and hope to make it work with elixir too. If you said session.delete(my_other_object), no action is needed if it references some particular my_object.It is only if you session.delete(my_object) that SQLAlchemy needs to do something - and for that, it must load those objects which reference my_object. It does this simply by using the one-to-many collection on my_object which states how to load my_other_objects with an association.So the one-to-many collection is what's needed here - if you only specified the other direction, SQLAlchemy would need to derive the forwards collection from it and apply it to the o2m parent class internally. Right now the internals are simple such that loading is only possible in the direction in which the relationship() was configured. It is certainly possible to alter relationship() to be able to load collections or associations in both directions so that a many-to-one relationship could transparently load from the other direction, though internally this would require doing pretty much what backref does explicitly - placing some linkage to the relationship on the target class of the many-to-one which could react to deletions of that target class. So the current implementation is much simpler than that, so the passive_updates flag goes onto the side of the relationship that ultimately needs it, so that SQLAlchemy doesn't need to generate the backref which is easy enough for the user to specify, and in very rare conditions may even be configured differently than the reverse direction. The original design of cascade is taken from Hibernate, probably the most widely used object relational tool in history. They too tend to define ORM-level information about the foreign key, including the CASCADE attributes on the primary key side: http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-declaration-key http://docs.jboss.org/hibernate/stable/core/reference/en/html/collections.html#collections-mapping -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. hope my approach isn't too simplist, but onetomany is usually implemented in rdbms by an manytoone column or a few of them, with or without ri clauses: thus, a foreign key or an index. conversely, a manytoone relation has an implicit onetomany one (or an explicit onetoone). hereinafter, anything you would define on one end, at least in terms of ri, should be at the end it needs to be there. the example i've given with elixir (look at the sql echo) shows the onetomany updates the foreign key to null, not knowing they wouldn't be found in the cascading delete. i'm searching the exact point elixir should give the passive_deletes to the sa relation, thus to force it to give it to the right side of it. best regards, alex -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On Sep 13, 2010, at 11:16 AM, alex bodnaru wrote: hope my approach isn't too simplist, but onetomany is usually implemented in rdbms by an manytoone column or a few of them, with or without ri clauses: thus, a foreign key or an index. conversely, a manytoone relation has an implicit onetomany one (or an explicit onetoone). if you read what I wrote, I was explaining, that we architecturally choose not to generate the implicit reverse direction when it isn't specified by the user. And that this decision is not too controversial since Hibernate made the same one. the example i've given with elixir (look at the sql echo) shows the onetomany updates the foreign key to null, not knowing they wouldn't be found in the cascading delete. i'm searching the exact point elixir should give the passive_deletes to the sa relation, thus to force it to give it to the right side of it. right - Elixir has a more abstracted layer of user configuration, which is basically what you're asking SQLAlchemy to build into it. Id rather make things simpler on the inside, not more magical. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive_deletes/updates with sqlite
cant speak for Elixir, but your passive_deletes is on the wrong side in your declarative example. The correlations between classical and declarative relationship patterns are now individually contrasted at http://www.sqlalchemy.org/docs/orm/relationships.html#basic-relational-patterns . On Sep 11, 2010, at 10:58 PM, alex bodnaru wrote: On 09/11/2010 04:31 AM, alex wrote: On 09/10/2010 05:41 PM, Michael Bayer wrote: On Sep 10, 2010, at 4:13 AM, alex wrote: hello friends, as sqlite has recently introduced support for on delete/update cascade, i'd like to implement the passive_* functionality for this driver too. please give me a hint where to start. passive_delete and passive_update are database agnostic and only apply to what effects the ORM can expect from the underlying schema, just use them normally. thanks a lot for your response michael. i spent a little time to make a more isolated test case, and it works with sa+orm, but not with elixir, that seems to forget to pass the passive_deletes. i'm further inquiring there, in a hope to make a patch. best regards, alex one further step took me closer to the problem. it happens with declarative too. i'm attaching 3 scripts for comparison: one made with regular orm, one with elixir and one with declarative. the same problem with elixir and declarative. haven't tested on other rdbms. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. cascade_declarative.pycascade_elixir.pycascade_orm.py -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On 09/12/2010 04:27 PM, Michael Bayer wrote: cant speak for Elixir, but your passive_deletes is on the wrong side in your declarative example. The correlations between classical and declarative relationship patterns are now individually contrasted at http://www.sqlalchemy.org/docs/orm/relationships.html#basic-relational-patterns . On Sep 11, 2010, at 10:58 PM, alex bodnaru wrote: On 09/11/2010 04:31 AM, alex wrote: On 09/10/2010 05:41 PM, Michael Bayer wrote: On Sep 10, 2010, at 4:13 AM, alex wrote: hello friends, as sqlite has recently introduced support for on delete/update cascade, i'd like to implement the passive_* functionality for this driver too. please give me a hint where to start. passive_delete and passive_update are database agnostic and only apply to what effects the ORM can expect from the underlying schema, just use them normally. thanks a lot for your response michael. i spent a little time to make a more isolated test case, and it works with sa+orm, but not with elixir, that seems to forget to pass the passive_deletes. i'm further inquiring there, in a hope to make a patch. best regards, alex one further step took me closer to the problem. it happens with declarative too. i'm attaching 3 scripts for comparison: one made with regular orm, one with elixir and one with declarative. the same problem with elixir and declarative. haven't tested on other rdbms. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. cascade_declarative.pycascade_elixir.pycascade_orm.py thanks a lot michael. indeed it works, but it seems counter-intuitive a little since passive_* should in my opinion be on the side of the on * cascade it describes. anyway, it's great, and hope to make it work with elixir too. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On 09/12/2010 04:27 PM, Michael Bayer wrote:
cant speak for Elixir, but your passive_deletes is on the wrong side in
your declarative example.
The correlations between classical and declarative relationship patterns are
now individually contrasted at
http://www.sqlalchemy.org/docs/orm/relationships.html#basic-relational-patterns
.
On Sep 11, 2010, at 10:58 PM, alex bodnaru wrote:
On 09/11/2010 04:31 AM, alex wrote:
On 09/10/2010 05:41 PM, Michael Bayer wrote:
On Sep 10, 2010, at 4:13 AM, alex wrote:
hello friends,
as sqlite has recently introduced support for on delete/update cascade,
i'd like to implement the passive_* functionality for this driver too.
please give me a hint where to start.
passive_delete and passive_update are database agnostic and only apply to
what effects the ORM can expect from the underlying schema, just use them
normally.
thanks a lot for your response michael.
i spent a little time to make a more isolated test case, and it works with
sa+orm, but not with elixir, that seems to forget to pass the
passive_deletes.
i'm further inquiring there, in a hope to make a patch.
best regards,
alex
one further step took me closer to the problem. it happens with declarative
too.
i'm attaching 3 scripts for comparison: one made with regular orm, one with
elixir and one with declarative. the same problem with elixir and
declarative.
haven't tested on other rdbms.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
cascade_declarative.pycascade_elixir.pycascade_orm.py
btw, the fixed code.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey,
create_engine, ForeignKeyConstraint)
from sqlalchemy.orm import scoped_session, sessionmaker, relation
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.interfaces import PoolListener
class SQLiteFKListener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.execute('PRAGMA foreign_keys = ON;')
engine = create_engine(sqlite:///:memory:, echo=True, listeners=[SQLiteFKListener()])
Base = declarative_base(bind=engine)
class MyClass(Base):
__tablename__ = 'mytable'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50))
children = relation('MyOtherClass', backref='parent', passive_deletes=True)
def __repr__(self):
return 'MyClass %s, %s' % (None if self.id is None else str(self.id), self.name)
class MyOtherClass(Base):
__tablename__ = 'myothertable'
id = Column(Integer, primary_key=True, autoincrement=True)
parent_id = Column(Integer, ForeignKey('mytable.id', ondelete=CASCADE))
name = Column(String(50))
def __repr__(self):
return 'MyOtherClass %s, %s, %s' % (None if self.parent_id is None else str(self.parent_id), None if self.id is None else str(self.id), self.name)
Base.metadata.create_all(engine)
session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
alex = MyClass(name='alex')
pisi = MyClass(name='pisi')
session.add_all([
alex, pisi
])
print alex, pisi
session.flush()
session.add_all([
MyOtherClass(parent=alex, name='dagan'),
MyOtherClass(parent=alex, name='shaked'),
MyOtherClass(parent=pisi, name='dagan'),
MyOtherClass(parent=pisi, name='shaked'),
])
session.flush()
shaked1 = session.query(MyOtherClass).filter_by(parent_id=1, name=u'shaked')
session.delete(alex)
session.flush()
for my in session.query(MyClass).all():
print my
for my in session.query(MyOtherClass).all():
print my
Re: [sqlalchemy] passive_deletes/updates with sqlite
On 09/11/2010 04:31 AM, alex wrote:
On 09/10/2010 05:41 PM, Michael Bayer wrote:
On Sep 10, 2010, at 4:13 AM, alex wrote:
hello friends,
as sqlite has recently introduced support for on delete/update cascade,
i'd like to implement the passive_* functionality for this driver too.
please give me a hint where to start.
passive_delete and passive_update are database agnostic and only apply to
what effects the ORM can expect from the underlying schema, just use them
normally.
thanks a lot for your response michael.
i spent a little time to make a more isolated test case, and it works with
sa+orm, but not with elixir, that seems to forget to pass the passive_deletes.
i'm further inquiring there, in a hope to make a patch.
best regards,
alex
one further step took me closer to the problem. it happens with declarative too.
i'm attaching 3 scripts for comparison: one made with regular orm, one with
elixir and one with declarative. the same problem with elixir and declarative.
haven't tested on other rdbms.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey,
create_engine, ForeignKeyConstraint)
from sqlalchemy.orm import scoped_session, sessionmaker, relation
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(sqlite:///:memory:, echo=True)
Base = declarative_base(bind=engine)
class MyClass(Base):
__tablename__ = 'mytable'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50))
#children = relation('MyOtherClass', backref='parent')
def __repr__(self):
return 'MyClass %s, %s' % (None if self.id is None else str(self.id), self.name)
class MyOtherClass(Base):
__tablename__ = 'myothertable'
id = Column(Integer, primary_key=True, autoincrement=True)
parent_id = Column(Integer, ForeignKey('mytable.id', ondelete=CASCADE))
parent = relation('MyClass', backref='children', passive_deletes=True)
name = Column(String(50))
def __repr__(self):
return 'MyOtherClass %s, %s, %s' % (None if self.parent_id is None else str(self.parent_id), None if self.id is None else str(self.id), self.name)
Base.metadata.create_all(engine)
session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
alex = MyClass(name='alex')
pisi = MyClass(name='pisi')
session.add_all([
alex, pisi
])
print alex, pisi
session.flush()
session.add_all([
MyOtherClass(parent=alex, name='dagan'),
MyOtherClass(parent=alex, name='shaked'),
MyOtherClass(parent=pisi, name='dagan'),
MyOtherClass(parent=pisi, name='shaked'),
])
session.flush()
shaked1 = session.query(MyOtherClass).filter_by(parent_id=1, name=u'shaked')
session.delete(alex)
session.flush()
for my in session.query(MyClass).all():
print my
for my in session.query(MyOtherClass).all():
print my
from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey,
create_engine, ForeignKeyConstraint)
from sqlalchemy.orm import (mapper, relationship, sessionmaker)
from elixir import *
engine = create_engine(sqlite:///:memory:, echo=True)
metadata.bind = engine
connection = engine.connect()
connection.execute('PRAGMA foreign_keys = ON;')
class MyClass(Entity):
using_options(tablename='mytable')
id = Field(Integer, primary_key=True, autoincrement=True)
name = Field(String(20))
#children = OneToMany('MyOtherClass', ondelete=CASCADE, passive_deletes=True)
def __repr__(self):
return 'MyClass %s, %s' % (None if self.id is None else str(self.id), self.name)
class MyOtherClass(Entity):
using_options(tablename='myothertable')
id = Field(Integer, primary_key=True, autoincrement=True)
name = Field(String(20))
parent = ManyToOne('MyClass', backref='children', colname='parent_id', ondelete=cascade, passive_deletes=True)
def __repr__(self):
return 'MyOtherClass %s, %s, %s' % (None if self.parent_id is None else str(self.parent_id), None if self.id is None else str(self.id), self.name)
setup_all()
create_all()
alex = MyClass(name='alex')
pisi = MyClass(name='pisi')
print alex, pisi
#session.commit()
session.flush()
print alex, pisi
alexdagan = MyOtherClass(parent=alex, name='dagan')
alexshaked = MyOtherClass(parent=alex, name='shaked')
pisidagan = MyOtherClass(parent=pisi, name='dagan')
pisishaked = MyOtherClass(parent=pisi, name='shaked')
#session.commit()
session.flush()
shaked1 = session.query(MyOtherClass).filter_by(parent_id=1, name=u'shaked')
[sqlalchemy] passive_deletes/updates with sqlite
hello friends, as sqlite has recently introduced support for on delete/update cascade, i'd like to implement the passive_* functionality for this driver too. please give me a hint where to start. best regards, alex -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On Sep 10, 2010, at 4:13 AM, alex wrote: hello friends, as sqlite has recently introduced support for on delete/update cascade, i'd like to implement the passive_* functionality for this driver too. please give me a hint where to start. passive_delete and passive_update are database agnostic and only apply to what effects the ORM can expect from the underlying schema, just use them normally. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
