class Thing(db.Model):
"""The base class of all Devicehub resources.
This is a loose copy of
`schema.org's Thing class <https://schema.org/Thing>`_
using only needed fields.
"""
__abstract__ = True
updated = db.Column(db.TIMESTAMP(timezone=True),
nullable=False,
index=True,
server_default=db.text('CURRENT_TIMESTAMP'))
updated.comment = """The last time Devicehub recorded a change for
this thing.
"""
created = db.Column(db.TIMESTAMP(timezone=True),
nullable=False,
index=True,
server_default=db.text('CURRENT_TIMESTAMP'))
created.comment = """When Devicehub created this."""
def __init__(self, **kwargs) -> None:
# We need to set 'created' before sqlalchemy inits the class
# to be able to use sorted containers
self.created = kwargs.get('created', datetime.now(timezone.utc))
super().__init__(**kwargs)
class Path(db.Model):
id = db.Column(db.UUID(as_uuid=True),
primary_key=True,
server_default=db.text('gen_random_uuid()'))
lot_id = db.Column(db.UUID(as_uuid=True),
db.ForeignKey(Lot.id),
nullable=False)
lot = db.relationship(Lot,
backref=db.backref('paths',
lazy=True,
collection_class=set,
cascade=CASCADE_OWN),
primaryjoin=Lot.id == lot_id)
path = db.Column(LtreeType, nullable=False)
created = db.Column(db.TIMESTAMP(timezone=True),
server_default=db.text('CURRENT_TIMESTAMP'))
created.comment = """
When Devicehub created this.
"""
__table_args__ = (
# dag.delete_edge needs to disable internally/temporarily the unique constraint
db.UniqueConstraint(path,
name='path_unique',
deferrable=True,
initially='immediate'),
db.Index('path_gist', path, postgresql_using='gist'),
db.Index('path_btree', path, postgresql_using='btree'),
db.Index('lot_id_index', lot_id, postgresql_using='hash'))
def __init__(self, lot: Lot) -> None:
super().__init__(lot=lot)
self.path = UUIDLtree(lot.id)
@classmethod
def add(cls, parent_id: uuid.UUID, child_id: uuid.UUID):
"""Creates an edge between parent and child."""
db.session.execute(db.func.add_edge(str(parent_id), str(child_id)))
@classmethod
def delete(cls, parent_id: uuid.UUID, child_id: uuid.UUID):
"""Deletes the edge between parent and child."""
db.session.execute(db.func.delete_edge(str(parent_id), str(child_id)))
@classmethod
def has_lot(cls, parent_id: uuid.UUID, child_id: uuid.UUID) -> bool:
parent_id = UUIDLtree.convert(parent_id)
child_id = UUIDLtree.convert(child_id)
return bool(
db.session.execute(
"SELECT 1 from path where path ~ '*.{}.*.{}.*'".format(
parent_id, child_id)).first())
class LotDeviceDescendants(db.Model):
"""A view facilitating querying inclusion between devices and lots,
including components.
The view has 4 columns:
1. The ID of the device.
2. The ID of a lot containing the device.
3. The ID of the lot that directly contains the device.
4. If 1. is a component, the ID of the device that is inside the lot.
"""
_ancestor = Lot.__table__.alias(name='ancestor')
"""Ancestor lot table."""
_desc = Lot.__table__.alias()
"""Descendant lot table."""
lot_device = _desc \
.join(LotDevice, _desc.c.id == LotDevice.lot_id) \
.join(Path, _desc.c.id == Path.lot_id)
"""Join: Path -- Lot -- LotDevice"""
descendants = "path.path ~ (CAST('*.'|| replace(CAST({}.id as text), '-', '_') " \
"|| '.*' AS LQUERY))".format(_ancestor.name)
"""Query that gets the descendants of the ancestor lot."""
devices = db.select([
LotDevice.device_id,
_desc.c.id.label('parent_lot_id'),
_ancestor.c.id.label('ancestor_lot_id'), None
]).select_from(_ancestor).select_from(lot_device).where(
db.text(descendants))
# Components
_parent_device = Device.__table__.alias(name='parent_device')
"""The device that has the access to the lot."""
lot_device_component = lot_device \
.join(_parent_device, _parent_device.c.id == LotDevice.device_id) \
.join(Component, _parent_device.c.id == Component.parent_id)
"""Join: Path -- Lot -- LotDevice -- ParentDevice (Device) -- Component"""
components = db.select([
Component.id.label('device_id'),
_desc.c.id.label('parent_lot_id'),
_ancestor.c.id.label('ancestor_lot_id'),
LotDevice.device_id.label('device_parent_id'),
]).select_from(_ancestor).select_from(lot_device_component).where(
db.text(descendants))
__table__ = create_view('lot_device_descendants',
devices.union(components))
class ReportHash(db.Model):
"""Save the hash than is create when one report is download.
"""
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid4)
id.comment = """The identifier of the device for this database. Used only
internally for software; users should not use this.
"""
created = db.Column(db.TIMESTAMP(timezone=True),
nullable=False,
index=True,
server_default=db.text('CURRENT_TIMESTAMP'))
created.comment = """When Devicehub created this."""
hash3 = db.Column(CIText(), nullable=False)
hash3.comment = """The normalized name of the hash."""