Minor updates, including refactoring Device to capture parent-child relationship (device tree), along with variable inheritance

device-refactor.diff

diff --git a/craton/db/sqlalchemy/alembic/versions/ffdc1a500db1_craton_inventory_init.py b/craton/db/sqlalchemy/alembic/versions/ffdc1a500db1_craton_inventory_init.py
index 4032017..513476e 100644
--- a/craton/db/sqlalchemy/alembic/versions/ffdc1a500db1_craton_inventory_init.py
+++ b/craton/db/sqlalchemy/alembic/versions/ffdc1a500db1_craton_inventory_init.py
@@ -41,7 +41,7 @@ def upgrade():
         sa.Column('region_id', sa.Integer(), nullable=False),
         sa.Column('cell_id', sa.Integer(), nullable=True),
         sa.Column('project_id', sa.Integer(), nullable=False),
-        sa.PrimaryKeyConstraint('id')
+        sa.PrimaryKeyConstraint('id'),
         sa.ForeignKeyConstraint(['cell_id'], ['cells.id'], ),
         sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ),
         sa.ForeignKeyConstraint(['region_id'], ['regions.id'], ),
diff --git a/craton/db/sqlalchemy/models.py b/craton/db/sqlalchemy/models.py
index 13ab7a9..a3086ee 100644
--- a/craton/db/sqlalchemy/models.py
+++ b/craton/db/sqlalchemy/models.py
@@ -267,7 +267,7 @@ class Cell(Base, VariableMixin):
 
 
 class Device(Base, VariableMixin):
-    """Models descriptive data about a host"""
+    """Base class for Host, NetDevice, and other devices"""
     __tablename__ = 'devices'
     __table_args__ = (
         UniqueConstraint("region_id", "name",
@@ -282,6 +282,9 @@ class Device(Base, VariableMixin):
         Integer, ForeignKey('cells.id'), index=True, nullable=True)
     project_id = Column(
         Integer, ForeignKey('projects.id'), index=True, nullable=False)
+    access_secret_id = Column(Integer, ForeignKey('access_secrets.id'))
+    parent_id = Column(Integer, ForeignKey('devices.id'))
+
     ip_address = Column(IPAddressType, nullable=False)
     device_type = Column(String(255), nullable=False)
     # this means the host is "active" for administration
@@ -301,23 +304,43 @@ class Device(Base, VariableMixin):
         collection_class=lambda: SortedSet(key=attrgetter('label')))
     associated_labels = association_proxy('labels', 'label')
 
-    # many-to-one relationship to regions and cells
+    # many-to-one relationship to regions, cells, projects
     region = relationship('Region', back_populates='devices')
     cell = relationship('Cell', back_populates='devices')
     project = relationship('Project', back_populates='devices')
 
+    # many-to-one relationship to other devices, creating the device tree;
+    # construct both sides of the parent-children relationship, per
+    # http://docs.sqlalchemy.org/en/latest/orm/self_referential.html#adjacency-list-relationships
+    children = relationship(
+        'Device', backref=backref('parent', remote_side=[id]))
+    
+    @property
+    def ancestors(self):
+        lineage = []
+        ancestor = self.parent
+        while ancestor:
+            lineage.append(ancestor)
+            ancestor = ancestor.parent
+        return lineage
+
+    # optional many-to-one relationship to a host-specific secret
+    access_secret = relationship('AccessSecret', back_populates='devices')
+
     @property
     def resolved(self):
         """Provides a mapping that uses scope resolution for variables"""
         if self.cell:
             return ChainMap(
                 self.variables,
+                ChainMap(*[ancestor.variables for ancestor in self.ancestors]),
                 ChainMap(*[label.variables for label in self.labels]),
                 self.cell.variables,
                 self.region.variables)
         else:
             return ChainMap(
                 self.variables,
+                ChainMap(*[ancestor.variables for ancestor in self.ancestors]),
                 ChainMap(*[label.variables for label in self.labels]),
                 self.region.variables)
 
@@ -332,10 +355,6 @@ class Host(Device):
     __tablename__ = 'hosts'
     id = Column(Integer, ForeignKey('devices.id'), primary_key=True)
     hostname = Device.name
-    access_secret_id = Column(Integer, ForeignKey('access_secrets.id'))
-    parent_id = Column(Integer, ForeignKey('devices.id'))
-    # optional many-to-one relationship to a host-specific secret
-    access_secret = relationship('AccessSecret', back_populates='hosts')
 
     __mapper_args__ = {
         'polymorphic_identity': 'hosts',
@@ -355,18 +374,18 @@ class NetInterface(Base, VariableMixin):
     link = Column(String(255), nullable=True)
     cdp = Column(String(255), nullable=True)
     security = Column(String(255), nullable=True)
-    device_id = Column(Integer,
-                       ForeignKey('net_devices.id', ondelete="CASCADE"),
-                       nullable=False,
-                       primary_key=True)
-    network_id = Column(Integer,
-                        ForeignKey('networks.id'),
-                        primary_key=True,
-                        nullable=True)
-    name = Column(String(255), nullable=True)
 
-    network = relationship('Network', back_populates="net_devices")
-    net_device = relationship('NetDevice', back_populates="interfaces")
+    # many-to-many relationship between NetDevice and Network
+    device_id = Column(
+        Integer, ForeignKey('net_devices.id'), primary_key=True)
+    network_id = Column(
+        Integer, ForeignKey('networks.id'), primary_key=True)
+    network = relationship(
+        'Network', back_populates='net_devices',
+        cascade='all', lazy='joined')
+    net_device = relationship(
+        'NetDevice', back_populates='interfaces',
+        cascade='all', lazy='joined')
 
 
 class Network(Base, VariableMixin):
@@ -385,8 +404,7 @@ class Network(Base, VariableMixin):
     project_id = Column(
         Integer, ForeignKey('projects.id'), index=True, nullable=False)
 
-    net_devices = relationship("NetInterface",
-                               back_populates="network")
+    net_devices = relationship('NetInterface', back_populates='network')
     region = relationship('Region', back_populates='networks')
     cell = relationship('Cell', back_populates='networks')
     project = relationship('Project', back_populates='networks')
@@ -396,16 +414,12 @@ class NetDevice(Device):
     __tablename__ = 'net_devices'
     id = Column(Integer, ForeignKey('devices.id'), primary_key=True)
     hostname = Device.name
-    access_secret_id = Column(Integer, ForeignKey('access_secrets.id'))
-    parent_id = Column(Integer, ForeignKey('devices.id'))
-    access_secret = relationship('AccessSecret', back_populates='net_devices')
     # network device specific properties
     model_name = Column(String(255), nullable=True)
     os_version = Column(String(255), nullable=True)
     vlans = Column(JSONType)
 
-    interfaces = relationship('NetInterface',
-                              back_populates='net_device')
+    interfaces = relationship('NetInterface', back_populates='net_device')
 
     __mapper_args__ = {
         'polymorphic_identity': 'net_devices',
@@ -459,5 +473,4 @@ class AccessSecret(Base):
     id = Column(Integer, primary_key=True)
     cert = Column(Text)
 
-    hosts = relationship('Host', back_populates='access_secret')
-    net_devices = relationship('NetDevice', back_populates='access_secret')
+    devices = relationship('Device', back_populates='access_secret')

t-schema.py

# FIXME add some api queries, such as get all hosts in a tenant/region

from ipaddress import IPv4Address
from sqlalchemy import create_engine  # change to oslo_db
from sqlalchemy.orm import sessionmaker
from craton.db.sqlalchemy import models

engine = create_engine('mysql+pymysql://craton:craton@localhost/craton', echo=True)
models.Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()
project = models.Project(name='A Big Customer, Inc.')
user = models.User(username='jimbaker', project=project)
region = models.Region(project=project, name="region-1", variables={'region-level': {'region-variable': 42}})

session.add(user)
session.add(project)
session.add(region)

cell = models.Cell(region=region, project=project, name="cell-1")
cell.variables["cell-level"] = [{"num-hosts": 47, "cabinets": {"compute": [1,2,3], "data": [4,5,6]}}]
session.add(cell)

switch = models.NetDevice(
    region=region, cell=cell, project=project, device_type='cabinet_switch',
    name='some-switch',
    ip_address=IPv4Address(u'10.1.2.1'),
    active=True,
    variables = {'foo': 47, 'baz': [1,2,3]})
session.add(switch)

network1 = models.Network(
    region=region, project=project, name='network-1')
network2 = models.Network(
    region=region, project=project, name='network-2')
interface1 = models.NetInterface(name='interface-1', network=network1, net_device=switch)
interface2 = models.NetInterface(name='interface-2', network=network2, net_device=switch)
session.add(network1)
session.add(network2)
session.add(interface1)
session.add(interface2)

host = models.Host(
    region=region, cell=cell, project=project, device_type='fum',
    name='www1.example.com',
    ip_address=IPv4Address(u'10.1.2.101'), active=True,
    parent=switch)
host.variables["apple"] = "red"
host.variables["banana"] = "yellow"
host.variables["phase-1-facts"]={'apple': 1, 'banana': 2, 'numbers': [0, 1, 1, 2, 3, 5, 8]}
session.add(host)

session.commit()

container = models.Device(
    region=region, cell=cell, project=project, device_type='container',
    name='some-container',
    ip_address=IPv4Address(u'10.1.2.110'),
    active=True,
    parent=host)
container.variables["apple"] = "green"
container.variables["peach"] = "orange"
container.variables["phase-2-facts"]={'apple': 1, 'banana': 2, 'numbers': [0, 1, 1, 2, 3, 5, 8]}
container.variables['baz'] = [0, 1, 1, 2, 3, 5, 8]
session.add(container)

session.commit()