class UserInventory(db.Model):
"""Relationship between users and their inventories."""
__table_args__ = {'schema': 'common'}
user_id = db.Column(db.UUID(as_uuid=True),
db.ForeignKey(User.id),
primary_key=True)
inventory_id = db.Column(db.Unicode(),
db.ForeignKey(Inventory.id),
primary_key=True)
class LotDevice(db.Model):
device_id = db.Column(db.BigInteger, db.ForeignKey(Device.id), primary_key=True)
lot_id = db.Column(UUID(as_uuid=True), db.ForeignKey(Lot.id), primary_key=True)
created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
author_id = db.Column(UUID(as_uuid=True),
db.ForeignKey(User.id),
nullable=False,
default=lambda: g.user.id)
author = db.relationship(User, primaryjoin=author_id == User.id)
author_id.comment = """The user that put the device in the lot."""
class Document(Thing):
"""This represent a generic document."""
id = Column(BigInteger, Sequence('device_seq'), primary_key=True)
id.comment = """The identifier of the device for this database. Used only
internally for software; users should not use this.
"""
document_type = Column(Unicode(STR_SM_SIZE), nullable=False)
date = Column(db.DateTime, nullable=True)
date.comment = """The date of document, some documents need to have one date
"""
id_document = Column(CIText(), nullable=True)
id_document.comment = """The id of one document like invoice so they can be linked."""
owner_id = db.Column(UUID(as_uuid=True),
db.ForeignKey(User.id),
nullable=False,
default=lambda: g.user.id)
owner = db.relationship(User, primaryjoin=owner_id == User.id)
file_name = Column(db.CIText(), nullable=False)
file_name.comment = """This is the name of the file when user up the document."""
file_hash = Column(db.CIText(), nullable=False)
file_hash.comment = """This is the hash of the file produced from frontend."""
url = db.Column(URL(), nullable=True)
url.comment = """This is the url where resides the document."""
def __str__(self) -> str:
return '{0.file_name}'.format(self)
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 Session(Thing):
__table_args__ = {'schema': 'common'}
id = Column(BigInteger, Sequence('device_seq'), primary_key=True)
expired = Column(BigInteger, default=0)
token = Column(UUID(as_uuid=True),
default=uuid4,
unique=True,
nullable=False)
type = Column(IntEnum(SessionType),
default=SessionType.Internal,
nullable=False)
user_id = db.Column(db.UUID(as_uuid=True), db.ForeignKey(User.id))
user = db.relationship(User,
backref=db.backref('sessions',
lazy=True,
collection_class=set),
collection_class=set)
def __str__(self) -> str:
return '{0.token}'.format(self)
class DeviceSearch(db.Model):
"""Temporary table that stores full-text device documents.
It provides methods to auto-run
"""
device_id = db.Column(db.BigInteger,
db.ForeignKey(Device.id, ondelete='CASCADE'),
primary_key=True)
device = db.relationship(Device, primaryjoin=Device.id == device_id)
properties = db.Column(TSVECTOR, nullable=False)
tags = db.Column(TSVECTOR)
devicehub_ids = db.Column(TSVECTOR)
__table_args__ = (
# todo to add concurrency this should be commited separately
# see https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#indexes-with-concurrently
db.Index('properties gist', properties, postgresql_using='gist'),
db.Index('tags gist', tags, postgresql_using='gist'),
db.Index('devicehub_ids gist', devicehub_ids, postgresql_using='gist'),
{
'prefixes': ['UNLOGGED']
# Only for temporal tables, can cause table to empty on turn on
})
@classmethod
def update_modified_devices(cls, session: db.Session):
"""Updates the documents of the devices that are part of a
modified action, or tag in the passed-in session.
This method is registered as a SQLAlchemy listener in the
Devicehub class.
"""
devices_to_update = set()
for model in chain(session.new, session.dirty):
if isinstance(model, Action):
if isinstance(model, ActionWithMultipleDevices):
devices_to_update |= model.devices
elif isinstance(model, ActionWithOneDevice):
devices_to_update.add(model.device)
if model.parent:
devices_to_update.add(model.parent)
devices_to_update |= model.components
elif isinstance(model, Tag) and model.device:
devices_to_update.add(model.device)
# this flush is controversial:
# see https://groups.google.com/forum/#!topic/sqlalchemy/hBzfypgPfYo
# todo probably should replace it with what the solution says
session.flush()
for device in (d for d in devices_to_update
if not isinstance(d, Component)):
cls.set_device_tokens(session, device)
@classmethod
def set_all_devices_tokens_if_empty(cls, session: db.Session):
"""Generates the search docs if the table is empty.
This can happen if Postgres' shut down unexpectedly, as
it deletes unlogged tables as ours.
"""
if not DeviceSearch.query.first():
cls.regenerate_search_table(session)
@classmethod
def regenerate_search_table(cls, session: db.Session):
"""Deletes and re-computes all the search table."""
DeviceSearch.query.delete()
for device in Device.query:
if not isinstance(device, Component):
cls.set_device_tokens(session, device)
@classmethod
def set_device_tokens(cls, session: db.Session, device: Device):
"""(Re)Generates the device search tokens."""
assert not isinstance(device, Component)
tokens = [(str(device.id), search.Weight.A),
(inflection.humanize(device.type), search.Weight.B),
(Device.model, search.Weight.B),
(Device.manufacturer, search.Weight.C),
(Device.serial_number, search.Weight.A)]
if device.manufacturer:
# todo this has to be done using a dictionary
manufacturer = device.manufacturer.lower()
if 'asus' in manufacturer:
tokens.append(('asus', search.Weight.B))
if 'hewlett' in manufacturer or 'hp' in manufacturer or 'h.p' in manufacturer:
tokens.append(('hp', search.Weight.B))
tokens.append(('h.p', search.Weight.C))
tokens.append(('hewlett', search.Weight.C))
tokens.append(('packard', search.Weight.C))
if isinstance(device, Computer):
# Aggregate the values of all the components of pc
Comp = aliased(Component)
tokens.extend((
(db.func.string_agg(db.cast(Comp.id, db.TEXT),
' '), search.Weight.D),
(db.func.string_agg(Comp.model, ' '), search.Weight.C),
(db.func.string_agg(Comp.manufacturer, ' '), search.Weight.D),
(db.func.string_agg(Comp.serial_number, ' '), search.Weight.B),
(db.func.string_agg(Comp.type, ' '), search.Weight.B),
('Computer', search.Weight.C),
('PC', search.Weight.C),
))
properties = session \
.query(search.Search.vectorize(*tokens)) \
.filter(Device.id == device.id)
if isinstance(device, Computer):
# Join to components
properties = properties \
.outerjoin(Comp, Computer.components) \
.group_by(Device.id)
tags = session.query(
search.Search.vectorize(
(db.func.string_agg(Tag.id, ' '), search.Weight.A),
(db.func.string_agg(Tag.secondary, ' '), search.Weight.A),
(db.func.string_agg(Organization.name, ' '),
search.Weight.B))).filter(Tag.device_id == device.id).join(
Tag.org)
devicehub_ids = session.query(
search.Search.vectorize((db.func.string_agg(
Device.devicehub_id, ' '), search.Weight.A), )).filter(
Device.devicehub_id == device.devicehub_id)
# Note that commit flushes later
# todo see how to get rid of the one_or_none() by embedding those as subqueries
# I don't like this but I want the 'on_conflict_on_update' thingie
device_document = dict(properties=properties.one_or_none(),
tags=tags.one_or_none(),
devicehub_ids=devicehub_ids.one_or_none())
insert = postgresql.insert(DeviceSearch.__table__) \
.values(device_id=device.id, **device_document) \
.on_conflict_do_update(constraint='device_search_pkey', set_=device_document)
session.execute(insert)
class Deliverynote(Thing):
id = db.Column(UUID(as_uuid=True),
primary_key=True) # uuid is generated on init by default
document_id = db.Column(CIText(), nullable=False)
creator_id = db.Column(UUID(as_uuid=True),
db.ForeignKey(User.id),
nullable=False,
default=lambda: g.user.id)
creator = db.relationship(User, primaryjoin=creator_id == User.id)
supplier_email = db.Column(CIText(),
db.ForeignKey(User.email),
nullable=False,
default=lambda: g.user.email)
supplier = db.relationship(
User, primaryjoin=lambda: Deliverynote.supplier_email == User.email)
receiver_address = db.Column(CIText(),
db.ForeignKey(User.email),
nullable=False,
default=lambda: g.user.email)
receiver = db.relationship(
User, primaryjoin=lambda: Deliverynote.receiver_address == User.email)
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
date.comment = 'The date the DeliveryNote initiated'
amount = db.Column(db.Integer,
check_range('amount', min=0, max=100),
default=0)
# The following fields are supposed to be 0:N relationships
# to SnapshotDelivery entity.
# At this stage of implementation they will treated as a
# comma-separated string of the devices expexted/transfered
expected_devices = db.Column(JSONB, nullable=False)
# expected_devices = db.Column(db.ARRAY(JSONB, dimensions=1), nullable=False)
transferred_devices = db.Column(db.ARRAY(db.Integer, dimensions=1),
nullable=True)
transfer_state = db.Column(IntEnum(TransferState),
default=TransferState.Initial,
nullable=False)
transfer_state.comment = TransferState.__doc__
lot_id = db.Column(UUID(as_uuid=True),
db.ForeignKey(Lot.id),
nullable=False)
lot = db.relationship(Lot,
backref=db.backref('deliverynote',
uselist=False,
lazy=True),
lazy=True,
primaryjoin=Lot.id == lot_id)
def __init__(self, document_id: str, amount: str, date,
supplier_email: str, expected_devices: Iterable,
transfer_state: TransferState) -> None:
"""Initializes a delivery note
"""
super().__init__(id=uuid.uuid4(),
document_id=document_id,
amount=amount,
date=date,
supplier_email=supplier_email,
expected_devices=expected_devices,
transfer_state=transfer_state)
@property
def type(self) -> str:
return self.__class__.__name__
@property
def url(self) -> urlutils.URL:
"""The URL where to GET this action."""
return urlutils.URL(url_for_resource(Deliverynote, item_id=self.id))
def delete(self):
"""Deletes the deliverynote.
This method removes the delivery note.
"""
db.session.delete(self)
def __repr__(self) -> str:
return '<Deliverynote {0.document_id}>'.format(self)
class Lot(Thing):
id = db.Column(UUID(as_uuid=True), primary_key=True) # uuid is generated on init by default
name = db.Column(CIText(), nullable=False)
description = db.Column(CIText())
description.comment = """A comment about the lot."""
closed = db.Column(db.Boolean, default=False, nullable=False)
closed.comment = """A closed lot cannot be modified anymore."""
devices = db.relationship(Device,
backref=db.backref('lots', lazy=True, collection_class=set),
secondary=lambda: LotDevice.__table__,
lazy=True,
collection_class=set)
"""The **children** devices that the lot has.
Note that the lot can have more devices, if they are inside
descendant lots.
"""
parents = db.relationship(lambda: Lot,
viewonly=True,
lazy=True,
collection_class=set,
secondary=lambda: LotParent.__table__,
primaryjoin=lambda: Lot.id == LotParent.child_id,
secondaryjoin=lambda: LotParent.parent_id == Lot.id,
cascade='refresh-expire', # propagate changes outside ORM
backref=db.backref('children',
viewonly=True,
lazy=True,
cascade='refresh-expire',
collection_class=set)
)
"""The parent lots."""
all_devices = db.relationship(Device,
viewonly=True,
lazy=True,
collection_class=set,
secondary=lambda: LotDeviceDescendants.__table__,
primaryjoin=lambda: Lot.id == LotDeviceDescendants.ancestor_lot_id,
secondaryjoin=lambda: LotDeviceDescendants.device_id == Device.id)
"""All devices, including components, inside this lot and its
descendants.
"""
amount = db.Column(db.Integer, check_range('amount', min=0, max=100), default=0)
owner_id = db.Column(UUID(as_uuid=True),
db.ForeignKey(User.id),
nullable=False,
default=lambda: g.user.id)
owner = db.relationship(User, primaryjoin=owner_id == User.id)
transfer_state = db.Column(IntEnum(TransferState), default=TransferState.Initial, nullable=False)
transfer_state.comment = TransferState.__doc__
receiver_address = db.Column(CIText(),
db.ForeignKey(User.email),
nullable=False,
default=lambda: g.user.email)
receiver = db.relationship(User, primaryjoin=receiver_address == User.email)
def __init__(self, name: str, closed: bool = closed.default.arg,
description: str = None) -> None:
"""Initializes a lot
:param name:
:param closed:
"""
super().__init__(id=uuid.uuid4(), name=name, closed=closed, description=description)
Path(self) # Lots have always one edge per default.
@property
def type(self) -> str:
return self.__class__.__name__
@property
def url(self) -> urlutils.URL:
"""The URL where to GET this action."""
return urlutils.URL(url_for_resource(Lot, item_id=self.id))
@property
def descendants(self):
return self.descendantsq(self.id)
@property
def is_temporary(self):
return False if self.trade else True
@classmethod
def descendantsq(cls, id):
_id = UUIDLtree.convert(id)
return (cls.id == Path.lot_id) & Path.path.lquery(exp.cast('*.{}.*'.format(_id), LQUERY))
@classmethod
def roots(cls):
"""Gets the lots that are not under any other lot."""
return cls.query.join(cls.paths).filter(db.func.nlevel(Path.path) == 1)
def add_children(self, *children):
"""Add children lots to this lot.
This operation is highly costly as it forces refreshing
many models in session.
"""
for child in children:
if isinstance(child, Lot):
Path.add(self.id, child.id)
db.session.refresh(child)
else:
assert isinstance(child, uuid.UUID)
Path.add(self.id, child)
# We need to refresh the models involved in this operation
# outside the session / ORM control so the models
# that have relationships to this model
# with the cascade 'refresh-expire' can welcome the changes
db.session.refresh(self)
def remove_children(self, *children):
"""Remove children lots from this lot.
This operation is highly costly as it forces refreshing
many models in session.
"""
for child in children:
if isinstance(child, Lot):
Path.delete(self.id, child.id)
db.session.refresh(child)
else:
assert isinstance(child, uuid.UUID)
Path.delete(self.id, child)
db.session.refresh(self)
def delete(self):
"""Deletes the lot.
This method removes the children lots and children
devices orphan from this lot and then marks this lot
for deletion.
"""
self.remove_children(*self.children)
db.session.delete(self)
def _refresh_models_with_relationships_to_lots(self):
session = db.Session.object_session(self)
for model in session:
if isinstance(model, (Device, Lot, Path)):
session.expire(model)
def __contains__(self, child: Union['Lot', Device]):
if isinstance(child, Lot):
return Path.has_lot(self.id, child.id)
elif isinstance(child, Device):
device = db.session.query(LotDeviceDescendants) \
.filter(LotDeviceDescendants.device_id == child.id) \
.filter(LotDeviceDescendants.ancestor_lot_id == self.id) \
.one_or_none()
return device
else:
raise TypeError('Lot only contains devices and lots, not {}'.format(child.__class__))
def __repr__(self) -> str:
return '<Lot {0.name} devices={0.devices!r}>'.format(self)
class TradeDocument(Thing):
"""This represent a document involved in a trade action.
Every document is added to a lot.
When this lot is converted in one trade, the action trade is added to the document
and the action trade need to be confirmed for the both users of the trade.
This confirmation can be revoked and this revoked need to be ConfirmRevoke for have
some efect.
This documents can be invoices or list of devices or certificates of erasure of
one disk.
Like a Devices one document have actions and is possible add or delete of one lot
if this lot don't have a trade
The document is saved in the database
"""
id = Column(BigInteger, Sequence('device_seq'), primary_key=True)
id.comment = """The identifier of the device for this database. Used only
internally for software; users should not use this.
"""
# type = Column(Unicode(STR_SM_SIZE), nullable=False)
date = Column(db.DateTime)
date.comment = """The date of document, some documents need to have one date
"""
id_document = Column(CIText())
id_document.comment = """The id of one document like invoice so they can be linked."""
description = Column(db.CIText())
description.comment = """A description of document."""
owner_id = db.Column(UUID(as_uuid=True),
db.ForeignKey(User.id),
nullable=False,
default=lambda: g.user.id)
owner = db.relationship(User, primaryjoin=owner_id == User.id)
lot_id = db.Column(UUID(as_uuid=True),
db.ForeignKey('lot.id'),
nullable=False)
lot = db.relationship('Lot',
backref=backref('documents',
lazy=True,
cascade=CASCADE_OWN,
**_sorted_documents),
primaryjoin='TradeDocument.lot_id == Lot.id')
lot.comment = """Lot to which the document is associated"""
file_name = Column(db.CIText())
file_name.comment = """This is the name of the file when user up the document."""
file_hash = Column(db.CIText())
file_hash.comment = """This is the hash of the file produced from frontend."""
url = db.Column(URL())
url.comment = """This is the url where resides the document."""
weight = db.Column(db.Float(nullable=True))
weight.comment = """This is the weight of one container than this document express."""
__table_args__ = (
db.Index('document_id', id, postgresql_using='hash'),
# db.Index('type_doc', type, postgresql_using='hash')
)
@property
def actions(self) -> list:
"""All the actions where the device participated, including:
1. Actions performed directly to the device.
2. Actions performed to a component.
3. Actions performed to a parent device.
Actions are returned by descending ``created`` time.
"""
return sorted(self.actions_docs, key=lambda x: x.created)
@property
def trading(self):
"""The trading state, or None if no Trade action has
ever been performed to this device. This extract the posibilities for to do"""
confirm = 'Confirm'
need_confirm = 'Need Confirmation'
double_confirm = 'Document Confirmed'
revoke = 'Revoke'
revoke_pending = 'Revoke Pending'
confirm_revoke = 'Document Revoked'
ac = self.last_action_trading()
if not ac:
return
if ac.type == 'ConfirmRevokeDocument':
# can to do revoke_confirmed
return confirm_revoke
if ac.type == 'RevokeDocument':
if ac.user == g.user:
# can todo revoke_pending
return revoke_pending
else:
# can to do confirm_revoke
return revoke
if ac.type == 'ConfirmDocument':
if ac.user == self.owner:
if self.owner == g.user:
# can to do revoke
return confirm
else:
# can to do confirm
return need_confirm
else:
# can to do revoke
return double_confirm
@property
def total_weight(self):
"""Return all weight than this container have."""
weight = self.weight or 0
for x in self.actions:
if not x.type == 'MoveOnDocument' or not x.weight:
continue
if self == x.container_from:
continue
weight += x.weight
return weight
def last_action_trading(self):
"""which is the last action trading"""
with suppress(StopIteration, ValueError):
actions = copy.copy(self.actions)
actions.sort(key=lambda x: x.created)
t_trades = [
'Trade', 'Confirm', 'ConfirmRevokeDocument', 'RevokeDocument',
'ConfirmDocument'
]
return next(e for e in reversed(actions) if e.t in t_trades)
def _warning_actions(self, actions):
"""Show warning actions"""
return sorted(ev for ev in actions if ev.severity >= Severity.Warning)
def __lt__(self, other):
return self.id < other.id
def __str__(self) -> str:
return '{0.file_name}'.format(self)