def kv_store_get_prefix(connection, prefix_name, limit=None, offset=None):
assert isinstance(prefix_name, str)
clause, params = limit_offset_helper(limit,
offset,
order_by="keyName",
extra_params={"prefix": prefix_name})
with cursor_manager(connection) as c:
c.execute(
"""
SELECT keyName, value FROM keyValueStore WHERE keyName LIKE :prefix || '%'
""" + clause, params)
return [(r['keyName'], json_loads(r['value'])) for r in c.fetchall()]
def select_measurements_that_need_upload(connection, limit):
clause, params = limit_offset_helper(limit,
None,
order_by="measurementStartTime")
with cursor_manager(connection) as c:
c.execute(
"""
SELECT m.*
FROM measurement AS m
WHERE hasBeenUploaded=0
""" + clause, params)
return [Measurement.from_row(r) for r in c.fetchall()]
def select_stations_by_channel(connection,
channel_num,
limit=None,
offset=None):
clause, params = limit_offset_helper(
limit, offset, extra_params={'channelNum': channel_num})
with cursor_manager(connection) as c:
c.execute(
"""
SELECT DISTINCT s.* FROM station AS s
JOIN measurementStationMap AS map ON map.mapStationID = s.stationID
JOIN measurement AS m ON m.measurementID = map.mapMeasurementID
WHERE channel = :channelNum
""" + clause, params),
return [Station.from_row(r) for r in c.fetchall()]
def select_service_sets_by_channel(connection,
channel_num,
limit=None,
offset=None):
clause, params = limit_offset_helper(
limit, offset, extra_params={'channelNum': channel_num})
with cursor_manager(connection) as c:
c.execute(
"""
SELECT DISTINCT ss.* FROM serviceSet AS ss
LEFT JOIN associationStationServiceSetMap AS a ON ss.serviceSetID = a.associatedServiceSetID
LEFT JOIN infrastructureStationServiceSetMap AS m ON ss.serviceSetID = m.mapServiceSetID
JOIN measurement AS m2 ON a.measurementID = m2.measurementID OR m2.measurementID = m.measurementID
WHERE m2.channel = :channelNum
""" + clause, params),
return [ServiceSet.from_row(r) for r in c.fetchall()]
def select_latest_channel_measurements(connection,
channel_num,
limit=None,
offset=None):
clause, params = limit_offset_helper(
limit,
offset,
order_by="measurementStartTime DESC",
extra_params={"channelNum": channel_num})
with cursor_manager(connection) as c:
c.execute(
"""
SELECT * FROM measurement WHERE channel=:channelNum
""" + clause, params)
return [Measurement.from_row(r) for r in c.fetchall()]
def select_latest_channel_device_counts(connection,
channel_num,
limit=None,
offset=None):
clause, params = limit_offset_helper(
limit,
offset,
order_by="m.measurementStartTime DESC",
extra_params={"channelNum": channel_num})
with cursor_manager(connection) as c:
c.execute(
"""
SELECT m.measurementID, m.measurementStartTime, m.measurementEndTime,
m.measurementDuration, COUNT(DISTINCT map.mapStationID) AS stationCount
FROM measurement AS m
JOIN measurementStationMap AS map
ON m.measurementID = map.mapMeasurementID
WHERE channel=:channelNum
GROUP BY m.measurementID
""" + clause, params)
return [dict(r) for r in c.fetchall()]
def select_all_service_sets(connection, limit=None, offset=None):
clause, params = limit_offset_helper(limit, offset)
with cursor_manager(connection) as c:
c.execute("SELECT * FROM serviceSet " + clause, params)
return [ServiceSet.from_row(r) for r in c.fetchall()]
def kv_store_get_all(connection, limit=None, offset=None):
clause, params = limit_offset_helper(limit, offset, order_by="keyName")
with cursor_manager(connection) as c:
c.execute("SELECT keyName, value FROM keyValueStore " + clause, params)
return [(r['keyName'], json_loads(r['value'])) for r in c.fetchall()]
