Hi Michael, hi *, here is another issue I ran into with SQLAlchemy. Basically, I am trying to map a filesystem like structure to SQL. Unfortunately, there is a difference in that the users can reorder the tree. I reduced my code to the attached example.
Mapping the structure worked quite good so far, until I noticed that I had duplicate entries in the same folder. So I added a unique constraint with the result that I can't overwrite the list of entries anymore: folder.children = list(folder.children) alone causes the problem. SQLAlchemy adds the 'new' entries first, which violates the unique constraint. Is there a way to do what I want without bigger changes to the code? For now, I will disable the unique constraint again and add some checks to the Python code. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
""" Example of using association_proxy with ordering_list and how to break it ;-) Basically, assigning the same entries again in a different order will violate the unique constraint below. Remove it and you see the reason: INSERT INTO folder_entry (folder_id, entry_id, entry_order) VALUES (?, ?, ?) (2, 4, 0) INSERT INTO folder_entry (folder_id, entry_id, entry_order) VALUES (?, ?, ?) (2, 3, 1) DELETE FROM folder_entry WHERE folder_entry.surrogate_key = ? ((2,), (3,)) The new entries are inserted first and the old entries are deleted last. """ from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.orderinglist import ordering_list from sqlalchemy.ext.associationproxy import association_proxy Base = declarative_base() class Entry(Base): __tablename__ = "entry" id = Column(Integer, primary_key=True) entry_type = Column(String) __mapper_args__ = {'polymorphic_on': entry_type} name = Column(String) class File(Entry): __tablename__ = "file" __mapper_args__ = {'polymorphic_identity': "file"} id = Column(Integer, ForeignKey("entry.id"), primary_key=True) content = Column(LargeBinary) class FolderEntry(Base): __tablename__ = "folder_entry" surrogate_key = Column(Integer, primary_key=True) folder_id = Column(Integer, ForeignKey("folder.id"), index=True, nullable=False) entry_id = Column(Integer, ForeignKey("entry.id"), nullable=False) entry_order = Column(Integer) entry = relation(Entry) __table_args__ = (UniqueConstraint("folder_id", "entry_id"), {}) def __init__(self, entry): u"""Constructor for association_proxy, which passes only association target.""" self.entry = entry class Folder(Entry): __tablename__ = "folder" __mapper_args__ = {'polymorphic_identity': "folder"} id = Column(Integer, ForeignKey("entry.id"), primary_key=True) children_relation = relation(FolderEntry, order_by=[FolderEntry.entry_order], cascade='save-update,merge,delete,delete-orphan', collection_class=ordering_list("entry_order")) children = association_proxy("children_relation", "entry") engine = create_engine("sqlite:///", echo=True) Base.metadata.create_all(engine) Session = sessionmaker(engine) session = Session() root = Folder(name="root") src = Folder(name="src") root.children = [src] src.children = [File(name="test.py", content="# Some content"), File(name="bar.py", content="# More content")] session.add(root) session.commit() src.children = list(src.children) session.commit()