def test_scalar_table_valued(self, assets_transactions, connection):
stmt = select(
assets_transactions.c.id,
func.jsonb_each(assets_transactions.c.contents,
type_=JSONB).scalar_table_valued("key"),
func.jsonb_each(assets_transactions.c.contents,
type_=JSONB).scalar_table_valued("value"),
)
eq_(
connection.execute(stmt).all(),
[(1, "k1", "v1"), (2, "k2", "v2"), (3, "k3", "v3")],
)
def list_device_last_capability_data(device_id):
device = Device.query \
.with_entities(Device.deviceID, Device.productID) \
.filter(Device.id == device_id) \
.first_or_404()
events_query = db.session \
.query(DeviceEventLatest.msgTime, DeviceEventLatest.streamID,
column('key').label('dataPointID'), column('value')) \
.select_from(DeviceEventLatest, func.jsonb_each(DeviceEventLatest.data)) \
.filter(DeviceEventLatest.deviceID == device.deviceID) \
.filter(DeviceEventLatest.dataType == 1)
# filter data point
data_point_id = request.args.get('dataPointID')
if data_point_id:
events_query = events_query.filter(column('key') == data_point_id)
events_result = events_query.all()
events = []
for event in events_result:
event_dict = {}
for key in event.keys():
event_dict[key] = getattr(event, key)
events.append(event_dict)
data_points = _get_data_points([device.productID])
records = _get_capability_data(events, data_points)
return jsonify(records)
def list_device_capability_data(device_id):
validate_time_range()
device = Device.query \
.join(Product, Product.productID == Device.productID) \
.with_entities(Device.deviceID, Device.productID, Product.cloudProtocol) \
.filter(Device.id == device_id) \
.first_or_404()
events_query = db.session \
.query(DeviceEvent.msgTime, DeviceEvent.streamID,
column('key').label('dataPointID'), column('value')) \
.select_from(DeviceEvent, func.jsonb_each(DeviceEvent.data)) \
.filter(DeviceEvent.deviceID == device.deviceID) \
.filter(DeviceEvent.dataType == 1)
# filter data point
data_point_id = request.args.get('dataPointID')
if data_point_id:
events_query = events_query.filter(column('key') == data_point_id)
events = events_query.pagination()
data_points = _get_data_points([device.productID])
events['items'] = _get_capability_data(events['items'], data_points)
return jsonify(events)
def list_last_data_charts(device_id):
device = Device.query \
.with_entities(Device.deviceID, Device.tenantID, Device.productID) \
.filter(Device.id == device_id).first_or_404()
latest_device_events = db.session \
.query(DeviceEventLatest.msgTime, DeviceEventLatest.streamID,
column('key').label('dataPointID'), column('value')) \
.select_from(DeviceEventLatest, func.jsonb_each(DeviceEventLatest.data)) \
.filter(DeviceEventLatest.dataType == 1,
DeviceEventLatest.tenantID == device.tenantID,
DeviceEventLatest.deviceID == device.deviceID).all()
records = []
stream_point_info = _query_stream_points(device.productID)
for _key, _info in stream_point_info.items():
record = {
'streamID': _key.split(':')[0],
'dataPointID': _key.split(':')[1],
'chartName': f"{_info['streamName']}/{_info['dataPointName']}",
'chartData': None
}
for device_event in latest_device_events:
event_dict = device_event_to_dict(device_event)
event_key = f"{event_dict['streamID']}:{event_dict['dataPointID']}"
if _key == event_key:
record['chartData'] = {
'time': event_dict['msgTime'],
'value': event_dict['value']
}
else:
continue
records.append(record)
return jsonify(records)
def test_table_valued(self, assets_transactions, connection):
jb = func.jsonb_each(assets_transactions.c.contents).table_valued(
"key", "value")
stmt = select(assets_transactions.c.id, jb.c.key,
jb.c.value).join(jb, true())
eq_(
connection.execute(stmt).all(),
[(1, "k1", "v1"), (2, "k2", "v2"), (3, "k3", "v3")],
)
def get(self):
"""
---
description: Collects valid annotation origin/key pairs to filter on for scanning
responses:
200:
content:
application/json:
schema:
allOf:
- $ref: '#/components/schemas/Success'
- type: object
properties:
data:
type: object
description: |
An object in which each key is an annotation origin, and
the values are arrays of { key: value_type } objects
"""
# This query gets the origin/keys present in the accessible annotaions
# for an Obj, as well as the data type for the values for each key.
# This information is used to generate the front-end form for selecting
# filters to apply on the auto-annotations column on the scanning page.
# For example, if given that an annotation field is numeric we should
# have min/max fields on the form.
annotations = func.jsonb_each(Annotation.data).table_valued(
"key", "value")
# Objs are read-public, so no need to check that annotations belong to an unreadable obj
# Instead, just check for annotation group membership
q = (Annotation.query_records_accessible_by(
self.current_user, columns=[Annotation.origin]).add_columns(
annotations.c.key,
func.jsonb_typeof(
annotations.c.value).label("type")).outerjoin(
annotations, literal(True)).distinct())
# Restructure query results so that records are grouped by origin in a
# nice, nested dictionary
results = q.all()
grouped = defaultdict(list)
keys_seen = defaultdict(set)
for annotation in results:
if annotation.key not in keys_seen[annotation.origin]:
grouped[annotation.origin].append(
{annotation.key: annotation.type})
keys_seen[annotation.origin].add(annotation.key)
return self.success(data=grouped)
def list_device_charts(device_id):
device = Device.query \
.with_entities(Device.deviceID, Device.tenantID, Device.productID) \
.filter(Device.id == device_id).first_or_404()
query = db.session \
.query(DeviceEvent.msgTime, DeviceEvent.streamID,
column('key').label('dataPointID'), column('value')) \
.select_from(DeviceEvent, func.jsonb_each(DeviceEvent.data)) \
.filter(DeviceEvent.dataType == 1,
DeviceEvent.tenantID == device.tenantID,
DeviceEvent.deviceID == device.deviceID)
device_events = _filter_request_args(query).all()
records = _handle_device_events(device_events, device.productID)
return jsonify(records)
def list_devices_capability_data():
"""
List the latest capability data of each device under group or gateway
"""
group_id = request.args.get("groupIntID")
gateway_id = request.args.get("gatewayIntID")
if group_id:
group = Group.query.with_entities(Group.groupID) \
.filter(Group.id == group_id).first_or_404()
devices_query = Device.query.with_entities(Device.deviceID, Device.productID) \
.join(GroupDevice, GroupDevice.c.deviceIntID == Device.id) \
.filter(GroupDevice.c.groupID == group.groupID)
elif gateway_id:
devices_query = Device.query.with_entities(Device.deviceID, Device.productID) \
.join(EndDevice, EndDevice.id == Device.id) \
.filter(EndDevice.gateway == gateway_id)
else:
raise ParameterInvalid()
# search by device name
device_name = request.args.get('deviceName_like')
if device_name:
devices_query = devices_query.filter(
Device.deviceName.ilike(f'%{device_name}%'))
devices = devices_query.all()
devices_uid = [device.deviceID for device in devices]
devices_product_uid = [device.productID for device in devices]
events_query = db.session \
.query(DeviceEventLatest.msgTime, DeviceEventLatest.streamID, DeviceEventLatest.deviceID,
Device.deviceName, Device.id.label('deviceIntID'),
column('key').label('dataPointID'), column('value')) \
.select_from(DeviceEventLatest, func.jsonb_each(DeviceEventLatest.data)) \
.join(Device, Device.deviceID == DeviceEventLatest.deviceID) \
.filter(DeviceEventLatest.deviceID.in_(devices_uid)) \
.filter(DeviceEventLatest.dataType == 1)
# filter by data point
data_point_id = request.args.get('dataPointID')
if data_point_id:
events_query = events_query.filter(column('key') == data_point_id)
events = events_query.pagination()
data_points = _get_data_points(devices_product_uid)
events['items'] = _get_capability_data(events['items'], data_points)
return jsonify(events)
def _select(self, columns):
"""
Generates a select statement for this table.
[columns] is a list of column names that will be returned in the data, columns in the
table that aren't in the list will not be retrieved. If [columns] is None or empty,
then all columns are retrieved.
"""
data_column = self._table.c.data
if columns:
data_column = select(
[func.jsonb_object_agg(Column('key'), Column('value'))
], ).select_from(func.jsonb_each(self._table.c.data), ).where(
Column('key').in_(columns), ).label('data', )
return select([self._table.c.key, data_column], ).select_from(
self._table, )
