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:[email protected]
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 [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.
"""
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()
