On Apr 6, 2011, at 5:43 AM, farcat wrote: > Hello, > > I am experimenting with a pattern where records hold the table name > and record id of the next record in any other table, chaining records > in different tables. This works, but I can't figure out how to clean > op references to the next record in another table when I delete a > record (the pattern does not use foreign keys in the normal sense).
.. in that it doesn't use foreign keys. Since you're working against the relational database's supported patterns, you'd need to roll the deletion of related rows yourself. The pattern is also called a "polymorphic association" and I blogged about it years ago here: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ . > The code is: > > ===================================== > > from sqlalchemy import * > from sqlalchemy.orm.session import sessionmaker > from sqlalchemy.ext.declarative import declarative_base, > declared_attr, DeclarativeMeta > #--------------------------------------------------------------------------- > Base = declarative_base() > reg = dict() > engine = create_engine('sqlite:///:memory:', echo=False) > Session = sessionmaker(bind = engine) > #--------------------------------------------------------------------------- > > class chainmeta(DeclarativeMeta): > #--------------------------------------------------------------------------- > class Base(object): > session = Session() > @declared_attr > def __tablename__(cls): > return cls.__name__ > > id = Column(Integer, primary_key = True) > next_table = Column(String(64)) > next_id = Column(Integer) #in table with name stored in > next_table! > > def __init__(self, data, next = None): > self.data = data > self.prev = None > self.next = next > self.session.add(self) > self.session.flush() > > def _getnext(self): > if self.next_table and self.next_id: > > return > self.session.query(reg[self.next_table]).filter(self.next_id == > reg[self.next_table].id).one() > else: return None > > def _setnext(self, next): > if next: > if self.next: > self.next.prev = None > self.next_table = next.__tablename__ > self.next_id = next.id > next.prev = self > elif self.next: > self.next.prev = None > self.next_table = None > self.next_id = None > > def _delnext(self): > self.next.prev = None > self.next_table = None > self.next_id = None > > next = property(_getnext, _setnext, _delnext) > > def __repr__(self): > out = "type: " + type(self).__name__ + "[" > for name in self.__dict__: > out += name + ", " > out += "]" > return out > #--------------------------------------------------------------------------- > def __new__(mcls, name, coltype): > return DeclarativeMeta.__new__(mcls, name, (chainmeta.Base, > Base),{"data": Column(coltype, nullable = False)}) > def __init__(cls, name, coltype): > reg[name] = cls > return DeclarativeMeta.__init__(cls, name, (chainmeta.Base, > Base),{}) > #--------------------------------------------------------------------------- > if __name__ == '__main__': > Base.metadata.drop_all(engine) > session = chainmeta.Base.session = Session() > > Ni = chainmeta("Ni", Integer) > Nb = chainmeta("Nb", Boolean) > Nt = chainmeta("Nt", String(200)) > Base.metadata.create_all(engine) > > ni1 = Ni(5) > ni2 = Ni(12) > nb1 = Nb(True) > nb2 = Nb(False) > nt1 = Nt("text in nt1") > nt2 = Nt("text in nt2") > ni1.next = ni2 > ni2.next = nb1 > nb1.next = nb2 > nb2.next = nt1 > nt1.next = nt2 > nt2.next = ni1 #circular > print "OBJECTS" > n = ni1 > count = 0 > print "nexts: ................." > while n and count < 10: > print n.data > count += 1 > n = n.next > n = ni1 > count = 0 > print "prevs: ................." > while n and count < 10: > print n.data > count += 1 > n = n.prev > print > "-----------------------------------------------------------------------------------" > nts = session.query(Nt).all() > print "QUERIES" > for nt in nts: > print nt.data > print "+++++++++++++++++++++" > print session.query(Ni).filter(Ni.next_id == > nb1.id).first().data > > > ===================================== > > This might seem to have no reasonable us case, but it is something I > want to use in a more complicated pattern later on. Basically the > question is, how can I remove a record and have no next or prev > pointing to it in other objects or records (without adding some sort > of external controller)? > > Cheers, Lars > > > -- > 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. > -- 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.
