Performing tasks before insert, remove, etc. in flask-sqlalchemy

Question

I'm trying to implement a nested set model using Flask-SQLAlchemy.

I see this example using SQLAlchemy: http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/nested_sets/nested_sets.html

The magic seems to happen in their definition of before_insert:

@event.listens_for(Employee, "before_insert")
def before_insert(mapper, connection, instance):
    if not instance.parent:
        instance.left = 1
...

I'm definitely not an expert with nested sets, but as I understand them, an "add" requires some pre-work to figure out new values for "left" and "right" for both the new item to be added, as well as potentially for everything in the table. (Similarly for remove.) I suppose maybe I could just add that pre-work in the regular flow within my app, and not use the before_insert from the example.

I've been having trouble finding if there's a way for me to override the db.session.add from within the Flask-SQLAlchemy framework in a similar way. Or should I be doing something else here?

Answer 1

I ended up implementing nested intervals (instead of nested sets). I hope it's helpful to folks!

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    employee_name = db.Column(db.String(120))
    parent = db.Column(db.Integer, db.ForeignKey('employee.id'), index=True)
    # implements nested intervals with fractions
    created = db.Column(db.DateTime, index=True)
    left_num = db.Column(db.Integer, nullable=False)
    left_den = db.Column(db.Integer, nullable=False)
    right_num = db.Column(db.Integer, nullable=False)
    right_den = db.Column(db.Integer, nullable=False)
    level = db.Column(db.Integer, nullable=False)

    def __init__(self, employee_name, parent):
        self.created = datetime.datetime.now()
        self.employee_name = employee_name
        self.parent = parent

        # handle case of first addition
        if not parent:
            self.level = 1
            left_node_num = 0
            left_node_den = 1
            right_node_num = 1
            right_node_den = 1
        else:
            parent_employee = Employee.query.get(self.parent)
            # if the parent exists, set level to 1 more
            self.level = parent_employee.level + 1
            # find the most recent sibling
            most_recent_sibling = Employee.query\
                .filter_by(parent=self.parent)\
                .order_by(Employee.id.desc())\
                .first()
            if not most_recent_sibling:
                # if no sibling, use parent boundaries
                left_node_num = parent_employee.left_num
                left_node_den = parent_employee.left_den
            else:
                left_node_num = most_recent_sibling.right_num
                left_node_den = most_recent_sibling.right_den
            right_node_num = parent_employee.right_num
            right_node_den = parent_employee.right_den
        left_boundary = fractions.Fraction(numerator=left_node_num + right_node_num,
                                           denominator=left_node_den + right_node_den)
        right_boundary = fractions.Fraction(numerator=left_boundary.numerator + right_node_num,
                                            denominator=left_boundary.denominator + right_node_den)
        self.left_num = left_boundary.numerator
        self.left_den = left_boundary.denominator
        self.right_num = right_boundary.numerator
        self.right_den = right_boundary.denominator