def get_all_devices(db: Connection) -> List[Device]:
"""
Get all devices.
"""
query = select(DEVICES.c).select_from(DEVICES)
result = db.execute(query)
return [map_object(Device, row) for row in result]
def get_all_channels(db: Connection) -> List[Channel]:
"""
Get all channels.
"""
query = select(CHANNELS.c).select_from(CHANNELS)
result = db.execute(query)
return [map_object(Channel, row) for row in result]
def get_device_channels(db: Connection, device_id: int) -> List[Channel]:
"""
Get device channels.
"""
query = select(CHANNELS.c).select_from(CHANNELS).where(CHANNELS.c.device_id == device_id)
result = db.execute(query)
return [map_object(Channel, row) for row in result]
def get_notification(db: Connection,
notification_id: int) -> Optional[Notification]:
"""
Get notification by id.
"""
query = select(NOTIFICATIONS.c, NOTIFICATIONS.c.id == notification_id)
result = db.execute(query)
row = result.fetchone()
return map_object(Notification, row) if row else None
def get_pending_notifications(db: Connection,
user_id: int) -> List[Notification]:
"""
Get all pending notifications for specified user.
"""
query = select(
NOTIFICATIONS.c,
and_(NOTIFICATIONS.c.user_id == user_id,
NOTIFICATIONS.c.received.is_(None)))
result = db.execute(query)
return [map_object(Notification, row) for row in result]
def get_all_channels_ordered(db: Connection, user_id: int) -> List[Channel]:
"""
Get all channels sorted by specified user's order.
"""
query = select(CHANNELS.c)\
.select_from(CHANNELS.outerjoin(CHANNELS_ORDER, CHANNELS_ORDER.c.channel_id == CHANNELS.c.id))\
.where(or_(CHANNELS_ORDER.c.user_id == user_id, CHANNELS_ORDER.c.user_id.is_(None)))\
.order_by(func.isnull(CHANNELS_ORDER.c.order))\
.order_by(CHANNELS_ORDER.c.order)
result = db.execute(query)
return [map_object(Channel, row) for row in result]
def get_device(db: Connection, device_id: Union[int, str]) -> Optional[Device]:
"""
Get device by ID or UUID.
"""
if isinstance(device_id, int):
condition = (DEVICES.c.id == device_id)
elif isinstance(device_id, str):
condition = (DEVICES.c.uuid == func.unhex(device_id))
else:
return None
query = select(DEVICES.c).select_from(DEVICES).where(condition)
result = db.execute(query)
row = result.fetchone()
return map_object(Device, row) if row else None
def get_channel(db: Connection, channel_id: Union[int, str]) -> Optional[Channel]:
"""
Get channel by ID or UUID.
"""
if isinstance(channel_id, int):
condition = (CHANNELS.c.id == channel_id)
elif isinstance(channel_id, str):
condition = (CHANNELS.c.uuid == func.unhex(channel_id))
else:
return None
query = select(CHANNELS.c).select_from(CHANNELS).where(condition)
result = db.execute(query)
row = result.fetchone()
return map_object(Channel, row) if row else None