SQLAlchemy Adjacency List Queries

Question

I'm attempting to create a simple Flask application that models network devices and their membership to arbitrarily-named domains (if it's relevant, the tool will be used to define MPLS LSP meshes between the devices. I'm using sqlite for dev and production will be postgres). The relationships should go as follows:

• domain to subdomain: one to many
• domain to device: one to many
• subdomain to domain: one to one
• device to domain: one to one

Here is my model:

class Device(db.Model):
    __tablename__ = 'device'
    id = db.Column(db.Integer, primary_key=True)
    hostname = db.Column(db.String(255), unique=True)
    mgmt_ip = db.Column(db.String(255), unique=True)
    snmp_comm = db.Column(db.String(255))
    domain_id = db.Column(db.Integer, db.ForeignKey('domain.id'))

    def __repr__(self):
        return '<Hostname %r>' % (self.hostname)

class Domain(db.Model):
    __tablename__ = 'domain'
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('domain.id'))
    name = db.Column(db.String(255), unique=True)
    children = db.relationship("Domain")
    devices = db.relationship("Device")

    def __repr__(self):
        return '<Domain %r>' % (self.name)

How can I structure my SQLAlchemy query to start at the device itself and recurse up the tree to get to the given root Domain (with no parents) in order to generate a list of devices in each domain up the tree? As an example:

from app import db
from app.models import Device, Domain

db.create_all()

d1 = Domain(name='mandatory')
db.session.add(d1)
db.session.commit()
d2 = Domain(name='metro_A', parent_id=1)
db.session.add(d2)
db.session.commit()
d3 = Domain(name='metro_B', parent_id=1)
db.session.add(d3)
db.session.commit()

dev1 = Device(hostname='switch_1', mgmt_ip='1.1.1.1', snmp_comm='public', domain_id=1)
dev2 = Device(hostname='switch_2', mgmt_ip='2.2.2.2', snmp_comm='public', domain_id=1)
dev3 = Device(hostname='switch_3', mgmt_ip='3.3.3.3', snmp_comm='public', domain_id=2)
dev4 = Device(hostname='switch_4', mgmt_ip='4.4.4.4', snmp_comm='public', domain_id=2)
dev5 = Device(hostname='switch_5', mgmt_ip='5.5.5.5', snmp_comm='public', domain_id=3)
dev6 = Device(hostname='switch_6', mgmt_ip='6.6.6.6', snmp_comm='public', domain_id=3)

db.session.add_all([dev1, dev2, dev3, dev4, dev5, dev6])

db.session.commit()

The goal here is, given switch_1 as an input, how do I get a list of other devices in its domain, plus the devices in its parent domain (and, if it applies in the real world, recurse until I reach its root domain)?

Answer 1

Traversing tree structures can be done using a recursive Common Table Expression in SQL. Given your goal to fetch the domain of a device and its possible parent domains, and then all the devices in those domains, you could start by creating a CTE for fetching the domains:

domain_alias = db.aliased(Domain)

# Domain of switch_1 has no parents, so for demonstration switch_6
# is a better target.
initial = db.session.query(Domain.id, Domain.parent_id).\
    join(Device).\
    filter_by(hostname='switch_6').\
    cte(recursive=True)

child = db.aliased(initial)

domain_query = initial.union(
    db.session.query(domain_alias.id, domain_alias.parent_id).
        join(child, child.c.parent_id == domain_alias.id))

And then just fetch the devices that are in the found domains:

db.session.query(Device).\
    join(domain_query, domain_query.c.id == Device.domain_id).\
    all()