Nested Set Model and SQLAlchemy -- Adding New Nodes

Question

How should new nodes be added with SQLAlchemy to a tree implemented using the Nested Set Model?

class Category(Base):
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column(String(128), nullable=False)
    lft = Column(Integer, nullable=False, unique=True)
    rgt = Column(Integer, nullable=False, unique=True)

I would need a trigger on the table to assign lft and rgt for the new node and update all other affected nodes, but what is the best way to define the position of the node? I can pass the parent_id of the new node to the constructor, but how would I then communicate the parent_id to the trigger?

Answer 1

You might want to look at the nested sets example in the examples directory of SQLAlchemy. This implements the model at the Python level.

Doing it at the database level with triggers would need some way to communicate the desired parent, either as an extra column or as a stored procedure.