[sqlalchemy] Re: doubly-linked list
Jonathan LaCour wrote: I am attempting to model a doubly-linked list, as follows: ... seems to do the trick. I had tried using backref's earlier, but it was failing because I was specifying a remote_side keyword argument to the backref(), which was making it blow up with cycle detection exceptions for some reason. Oops, spoke too soon! Here is a test case which shows something quite odd. I create some elements, link them together, and then walk the relations forward and backward, printing out the results. All seems fine. Then, I update the order of the linked list, and print them out forward, and they work okay, but when I print things out in reverse order, its all screwy. Any ideas? from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite:///') metadata = MetaData(engine) Session = scoped_session( sessionmaker(bind=engine, autoflush=True, transactional=True) ) task_table = Table('task', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode), Column('next_task_id', Integer, ForeignKey('task.id')), Column('previous_task_id', Integer, ForeignKey('task.id')) ) class Task(object): def __init__(self, **kw): for key, value in kw.items(): setattr(self, key, value) def __repr__(self): return 'Task :: %s' % self.name Session.mapper(Task, task_table, properties={ 'next_task' : relation( Task, primaryjoin=task_table.c.next_task_id==task_table.c.id, uselist=False, remote_side=task_table.c.id, backref=backref( 'previous_task', primaryjoin=task_table.c.previous_task_id==task_table.c.id, uselist=False ) ), }) if __name__ == '__main__': metadata.create_all() t1 = Task(name=u'Item One') t2 = Task(name=u'Item Two') t3 = Task(name=u'Item Three') t4 = Task(name=u'Item Four') t5 = Task(name=u'Item Five') t6 = Task(name=u'Item Six') t1.next_task = t2 t2.next_task = t3 t3.next_task = t4 t4.next_task = t5 t5.next_task = t6 Session.commit() Session.clear() print '-' * 80 task = Task.query.filter_by(name=u'Item One').one() while task is not None: print task task = task.next_task print '-' * 80 print '-' * 80 task = Task.query.filter_by(name=u'Item Six').one() while task is not None: print task task = task.previous_task print '-' * 80 Session.clear() t1 = Task.query.filter_by(name=u'Item One').one() t2 = Task.query.filter_by(name=u'Item Two').one() t3 = Task.query.filter_by(name=u'Item Three').one() t4 = Task.query.filter_by(name=u'Item Four').one() t5 = Task.query.filter_by(name=u'Item Five').one() t6 = Task.query.filter_by(name=u'Item Six').one() t1.next_task = t5 t5.next_task = t2 t4.next_task = t6 Session.commit() Session.clear() print '-' * 80 task = Task.query.filter_by(name=u'Item One').one() while task is not None: print task task = task.next_task print '-' * 80 print '-' * 80 task = Task.query.filter_by(name=u'Item Six').one() while task is not None: print task task = task.previous_task print '-' * 80 -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: doubly-linked list
On Jan 11, 2008 7:57 PM, Jonathan LaCour [EMAIL PROTECTED] wrote: Jonathan LaCour wrote: I am attempting to model a doubly-linked list, as follows: ... seems to do the trick. I had tried using backref's earlier, but it was failing because I was specifying a remote_side keyword argument to the backref(), which was making it blow up with cycle detection exceptions for some reason. Oops, spoke too soon! Here is a test case which shows something quite odd. I create some elements, link them together, and then walk the relations forward and backward, printing out the results. All seems fine. Then, I update the order of the linked list, and print them out forward, and they work okay, but when I print things out in reverse order, its all screwy. Any ideas? I believe you need either the only next_task_id or the only previous_task_id, but not both, since one can be calculated from another. Something like the following: task_table = Table('task', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode), Column('next_task_id', Integer, unique=True, ForeignKey('task.id')), ) Session.mapper(Task, task_table, properties={ 'next_task' : relation( Task, uselist=False, remote_side=task_table.c.id, backref=backref('previous_task', uselist=False), ), }) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: doubly-linked list
Jonathan LaCour wrote: I am attempting to model a doubly-linked list, as follows: Replying to myself: task_table = Table('task', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode), Column('next_task_id', Integer, ForeignKey('task.id')), Column('previous_task_id', Integer, ForeignKey('task.id')) ) class Task(object): def __init__(self, **kw): for key, value in kw.items(): setattr(self, key, value) def __repr__(self): return 'Task :: %s' % self.name mapper(Task, task_table, properties={ 'next_task' : relation( Task, primaryjoin=task_table.c.next_task_id==task_table.c.id, uselist=False, remote_side=task_table.c.id, backref=backref( 'previous_task', primaryjoin=task_table.c.previous_task_id==task_table.c.id, uselist=False ) ), }) ... seems to do the trick. I had tried using backref's earlier, but it was failing because I was specifying a remote_side keyword argument to the backref(), which was making it blow up with cycle detection exceptions for some reason. -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: doubly-linked list
All of the crazy mappings today are blowing my mind, so I'll point you to an old unit test with a doubly linked list: http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/test/orm/inheritance/poly_linked_list.py the above uses just a single foreign key (but we can still traverse bi- directionally of course). Im not sure what the double foreign key approach here is for. wouldnt the previous task of one task be the next task for another with just one FK relation ? in the relational model, a single FK is automatically bidirectional, unlike a reference in a programming language. On Jan 11, 2008, at 11:57 AM, Jonathan LaCour wrote: Jonathan LaCour wrote: I am attempting to model a doubly-linked list, as follows: ... seems to do the trick. I had tried using backref's earlier, but it was failing because I was specifying a remote_side keyword argument to the backref(), which was making it blow up with cycle detection exceptions for some reason. Oops, spoke too soon! Here is a test case which shows something quite odd. I create some elements, link them together, and then walk the relations forward and backward, printing out the results. All seems fine. Then, I update the order of the linked list, and print them out forward, and they work okay, but when I print things out in reverse order, its all screwy. Any ideas? from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite:///') metadata = MetaData(engine) Session = scoped_session( sessionmaker(bind=engine, autoflush=True, transactional=True) ) task_table = Table('task', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode), Column('next_task_id', Integer, ForeignKey('task.id')), Column('previous_task_id', Integer, ForeignKey('task.id')) ) class Task(object): def __init__(self, **kw): for key, value in kw.items(): setattr(self, key, value) def __repr__(self): return 'Task :: %s' % self.name Session.mapper(Task, task_table, properties={ 'next_task' : relation( Task, primaryjoin=task_table.c.next_task_id==task_table.c.id, uselist=False, remote_side=task_table.c.id, backref=backref( 'previous_task', primaryjoin=task_table.c.previous_task_id==task_table.c.id, uselist=False ) ), }) if __name__ == '__main__': metadata.create_all() t1 = Task(name=u'Item One') t2 = Task(name=u'Item Two') t3 = Task(name=u'Item Three') t4 = Task(name=u'Item Four') t5 = Task(name=u'Item Five') t6 = Task(name=u'Item Six') t1.next_task = t2 t2.next_task = t3 t3.next_task = t4 t4.next_task = t5 t5.next_task = t6 Session.commit() Session.clear() print '-' * 80 task = Task.query.filter_by(name=u'Item One').one() while task is not None: print task task = task.next_task print '-' * 80 print '-' * 80 task = Task.query.filter_by(name=u'Item Six').one() while task is not None: print task task = task.previous_task print '-' * 80 Session.clear() t1 = Task.query.filter_by(name=u'Item One').one() t2 = Task.query.filter_by(name=u'Item Two').one() t3 = Task.query.filter_by(name=u'Item Three').one() t4 = Task.query.filter_by(name=u'Item Four').one() t5 = Task.query.filter_by(name=u'Item Five').one() t6 = Task.query.filter_by(name=u'Item Six').one() t1.next_task = t5 t5.next_task = t2 t4.next_task = t6 Session.commit() Session.clear() print '-' * 80 task = Task.query.filter_by(name=u'Item One').one() while task is not None: print task task = task.next_task print '-' * 80 print '-' * 80 task = Task.query.filter_by(name=u'Item Six').one() while task is not None: print task task = task.previous_task print '-' * 80 -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: doubly-linked list
Michael Bayer wrote: All of the crazy mappings today are blowing my mind, so I'll point you to an old unit test with a doubly linked list: Yeah, trust me, it was blowing my mind as well, so I elected not to go this direction anyway. You're also correct that there isn't _really_ a need to maintain the previous anyway, since it can be implied from the next a heck of a lot easier. The simplest solution is usually the best, and this one definitely wasn't very simple! On to other ideas... -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---