def get_measurement(self, system_uid, measurement, created_at):
conn = self.getDBConn()
cursor = conn.cursor()
table_name = get_measurement_table_name(measurement, system_uid)
# Declare and initialize the payload
payload = {'created_at': '', 'value': ''}
try:
# Retrieve records in time order, descending (most recent first)
query = "SELECT * FROM %s WHERE time = %%s " % table_name
cursor.execute(query, [created_at])
data = cursor.fetchone()
if not data or len(data) == 0:
raise ValueError("No data found for the given creation time.")
payload['created_at'] = data[0].strftime('%Y-%m-%d %H:%M:%S')
payload['value'] = float(data[1])
except Exception as e:
print e
return {'error': str(e)}
finally:
cursor.close()
conn.close()
return payload
def create_measurement_query(system, measurements, time_ranges):
query = ""
for i in range(0, len(measurements)):
query += "select \'" + measurements[i] + "\'," + measurements[i] + ".time as time," \
+ measurements[i] + ".value as value from " \
+ get_measurement_table_name(measurements[i], system) \
+ " " + measurements[i] + " where "
if time_ranges:
for j in range(0, len(time_ranges)):
start_time = "'" + str(time_ranges[j][0]) + "'"
end_time = "'" + str(time_ranges[j][1]) + "'"
if j == len(time_ranges) - 1:
query += "( time > " + start_time + " and time < " + end_time + ")"
else:
query += "( time > " + start_time + " and time < " + end_time + ") or"
else:
query += "1=1"
if i == len(measurements) - 1:
query += " order by time "
else:
query += " union "
return query
def get_system_measurement(self, system_uid, measurement_id):
# Encode the measurement_id
measurement_id_encoded = measurement_id.encode('utf-8')
# Check if the encoded value is a valid number
is_given_measurement_id_digit = measurement_id_encoded.isdigit()
# If the given measurement_id is a valid number, convert it to an integer,
# Else it is an invalid measurement_id
if is_given_measurement_id_digit:
measurement_id_encoded_int = int(measurement_id_encoded)
else:
return json.dumps({'error': 'Invalid measurement id'})
# Fetch the measurement information of all the measurements
measurement_info = self.mea.get_all_measurement_info()
# List that stores all the measurement ids
measurement_id_list = []
# For each measurement in the measure_info, fetch the measurement id and
# append to the measurement_id_list
for each_measurement in measurement_info:
measurement_id_info = each_measurement[0]
measurement_id_list.append(measurement_id_info)
# If the given measurement_id (encoded) in present in the measurement_id_list,
# then it is a valid id. Else it is an invalid id.
if measurement_id_encoded_int in measurement_id_list:
# Fetch the name of the measurement
measurement = self.mea.get_measurement_name(measurement_id)
else:
return json.dumps({'error': 'Invalid measurement id'})
if 'error' in measurement:
return json.dumps(measurement)
# Number of latest recorded to be returned
# Light: 7
# All other measurements: 1
if measurement[0] == 'light':
num_of_records = 7
else:
num_of_records = 1
# Create the name of the table
table_name = get_measurement_table_name(measurement[0], system_uid)
# Get the latest value recorded in that table
result = self.mea.get_latest_value(table_name, num_of_records)
if 'error' in result:
return json.dumps(result)
values = []
for result_temp in result:
measurement_value = decimal.Decimal(result_temp[1])
if measurement_value.is_normal():
normalized_measurement_value = measurement_value
else:
normalized_measurement_value = measurement_value.normalize()
normalized_measurement_value_reduced_decimal = "%.2f" % normalized_measurement_value
values_temp = {
'time': str(result_temp[0]),
'value': str(normalized_measurement_value_reduced_decimal)
}
values.append(values_temp)
obj = {'system_uid': system_uid, 'records': values}
return json.dumps(obj)
def get_system_measurements(self, system_uid):
"""Retrieve the latest measurements of the specified system.
Returns a Python dictionary"""
# Fetch names of all the measurements
names = self.mea.get_all_measurement_names()
# Create a list to store the name, latest time and value of all the measurements
x = []
# For each measurement
for measurement_name, full_name in names:
# Fetch the name of the measurement using regular expression
if measurement_name != 'time':
# As each measurement of a system has a table on it's own,
# we need to create the name of each table.
table_name = get_measurement_table_name(
measurement_name, system_uid)
num_of_records = 1
# Get the latest value stored in the table
value = self.mea.get_latest_value(table_name, num_of_records)
if 'error' in value:
return value
# Append the value to the latest_value[] list
if len(value) == 1:
value_temp = value[0]
measurement_value = decimal.Decimal(value_temp[1])
if measurement_value.is_normal():
normalized_measurement_value = measurement_value
else:
normalized_measurement_value = measurement_value.normalize(
)
normalized_measurement_value_reduced_decimal = "%.2f" % normalized_measurement_value
temp = {
'name':
measurement_name,
'full_name':
full_name,
'time':
str(value_temp[0]),
'value':
str(normalized_measurement_value_reduced_decimal),
'updated_at':
value_temp[2].strftime('%Y-%m-%d %H:%M:%S')
if value_temp[2] else None
}
else:
temp = {
'name': measurement_name,
'full_name': full_name,
'time': None,
'value': None,
'updated_at': None
}
x.append(temp)
return {'system_uid': str(system_uid), 'measurements': x}
def get_all_measurements(self, system_uid, measurement, page):
conn = self.getDBConn()
cursor = conn.cursor()
# Prep some values for the query
items_per_page = 20
page = int(page)
start = items_per_page * (page - 1)
table_name = get_measurement_table_name(measurement, system_uid)
# Declare and initialize the payload
payload = {'data': []}
try:
# Retrieve records in time order, descending (most recent first)
query = "SELECT * FROM %s ORDER BY time DESC LIMIT %%s OFFSET %%s " % table_name
cursor.execute(query, (items_per_page, start))
result = list(cursor.fetchall())
# Figure out total number of pages. We return this with every request
total_count = self.get_data_count(system_uid, measurement)
total_pages = int(floor(total_count['count'] / items_per_page) + 1)
# Error if page not in range
if total_pages < page:
raise ValueError(
'The provided page number ' + str(page) +
' is out of range. There allowable range is [1,%d]' %
total_pages)
# Add the page count
payload['total_pages'] = total_pages
# Loop through the query result, coerce the result strings to datetime and float
# Turn the results into dicts of form {time: <datetime>, value: <float>}
for r in result:
updated_at = r[2].strftime(
'%Y-%m-%d %H:%M:%S') if r[2] else None
payload['data'].append({
'time':
r[0].strftime('%Y-%m-%d %H:%M:%S'),
'value':
float(r[1]),
'updated_at':
updated_at
})
except Exception as e:
print e
return {'error': str(e)}
finally:
cursor.close()
conn.close()
return payload
def update_existing_measurement(self, system_uid, measurement, data):
conn = self.getDBConn()
cursor = conn.cursor()
table_name = get_measurement_table_name(measurement, system_uid)
try:
query = "UPDATE %s SET time=%%s, value=%%s, updated_at=%%s WHERE time=%%s" % table_name
result = cursor.execute(query, (data['time'], data['value'],
data['updated_at'], data['time']))
conn.commit()
except Exception as e:
print e
return {'error': str(e)}
finally:
cursor.close()
conn.close()
return 'Successfully updated ' + str(result) + ' records(s)!'
def get_data_count(self, system_uid, measurement):
conn = self.getDBConn()
cursor = conn.cursor()
table_name = get_measurement_table_name(measurement, system_uid)
payload = {}
try:
query = "SELECT COUNT(*) FROM %s" % table_name
cursor.execute(query)
result = cursor.fetchone()
payload['count'] = result[0]
except Exception as e:
return {'error': e.args[1]}
finally:
cursor.close()
conn.close()
return payload
def create_system(self, system):
conn = self.dbconn()
cursor = conn.cursor()
userID = system['userID']
name = system['name']
systemUID = str(uuid.uuid1().hex)
startDate = system['startDate']
techniqueID = system['techniqueID']
location = system['location']
locationLat = location['lat']
locationLng = location['lng']
gbMedia = system['gbMedia']
crops = system['crops']
organisms = system['organisms']
status = system['status']
systemID = 0
query1 = (
'INSERT INTO systems (user_id, name, system_uid, start_date, aqx_technique_id, location_lat, location_lng, status)'
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)')
values1 = (userID, name, systemUID, startDate, techniqueID, locationLat, locationLng, status)
query2 = 'INSERT INTO system_gb_media (system_id,gb_media_id) VALUES (%s, %s)'
query3 = 'INSERT INTO system_aquatic_organisms VALUES (%s, %s, %s)'
query4 = 'INSERT INTO system_crops VALUES (%s, %s, %s)'
# note: database table column sys_status_id is set to 100 on default
query5 = 'INSERT INTO system_status (system_uid, start_time, end_time) VALUES (%s, %s, "2030-12-31 23:59:59")'
values5 = (systemUID, startDate)
# MySQL unfortunately has versions supporting different non-primary key timestamps
# our production system (which runs on CentOS with ancient MySQL) does not accept
# the default value, the newer development system requires a default value
create_single_query1 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP)'
create_single_query2 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP default current_timestamp)'
# create multi table if the measurement type is marked as such
create_multi_query1 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP, foreign_key integer not null)'
create_multi_query2 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP default current_timestamp, foreign_key integer not null)'
try:
cursor.execute('select name, multi_table from measurement_types where id < 100000')
meas_types = {name: multi_table for name, multi_table in cursor.fetchall()}
cursor.execute(query1, values1)
systemID = cursor.lastrowid
for medium in gbMedia:
cursor.execute(query2, (systemID, medium['ID']))
for organism in organisms:
if organism['ID'] > 0:
cursor.execute(query3, (systemID, organism['ID'], organism['count']))
for crop in crops:
if crop['ID'] > 0:
cursor.execute(query4, (systemID, crop['ID'], crop['count']))
cursor.execute(query5, values5)
# make sure the query is creating the table in either production system
# or dev system format
for name, multi_table in meas_types.items():
table_name = get_measurement_table_name(name, systemUID)
try:
if multi_table is None:
cursor.execute(create_single_query1 % table_name)
else:
cursor.execute(create_multi_query1 % table_name)
except:
if multi_table is None:
cursor.execute(create_single_query2 % table_name)
else:
cursor.execute(create_multi_query2 % table_name)
conn.commit()
finally:
cursor.close()
conn.close()
return {'userID': userID, 'systemID': systemID, 'systemUID': systemUID}