def update_flight(id, flight):
"""
Updates a flight containing the ID of the flight and the name of the flight
Parameters
----------
flight_id : integer
flight : string
"""
if id is not None and flight is not None:
flights = Table('flights')
update_flight_query = Query.update(flights)
if flight.user_id is not None:
update_flight_query = update_flight_query.set(
flights.user_id, flight.user_id)
if flight.flight_name is not None:
update_flight_query = update_flight_query.set(
flights.flight_name, flight.flight_name)
if flight.manual_notes is not None:
update_flight_query = update_flight_query.set(
flights.manual_notes, flight.manual_notes)
if flight.address is not None:
update_flight_query = update_flight_query.set(
flights.address, flight.address)
if flight.field_name is not None:
update_flight_query = update_flight_query.set(
flights.field_name, flight.field_name)
if flight.crop_name is not None:
update_flight_query = update_flight_query.set(
flights.crop_name, flight.crop_name)
if flight.average_latitude is not None:
update_flight_query = update_flight_query.set(
flights.average_latitude, flight.average_latitude)
if flight.average_longitude is not None:
update_flight_query = update_flight_query.set(
flights.average_longitude, flight.average_longitude)
if flight.average_altitude is not None:
update_flight_query = update_flight_query.set(
flights.average_altitude, flight.average_altitude)
if flight.flight_start_time is not None:
update_flight_query = update_flight_query.set(
flights.flight_start_time, flight.flight_start_time)
if flight.flight_end_time is not None:
update_flight_query = update_flight_query.set(
flights.flight_end_time, flight.flight_end_time)
if flight.hardware_make is not None:
update_flight_query = update_flight_query.set(
flights.hardware_make, flight.hardware_make)
if flight.hardware_model is not None:
update_flight_query = update_flight_query.set(
flights.hardware_model, flight.hardware_model)
if flight.privacy is not None:
update_flight_query = update_flight_query.set(
flights.privacy, flight.privacy)
update_flight_query = update_flight_query.where(flights.id.isin([id]))
dao_tools.execute(update_flight_query)
def delete_images(image_ids):
"""
Deletes all the images in the database containing the passed ids
Parameters
----------
image_ids : list of int
The ids of the images to delete
"""
if image_ids is not None:
images = Table('images')
delete_images_query = Query.from_(images).delete().where(images.id.isin(image_ids))
dao_tools.execute(delete_images_query)
def delete_flight(flight_id):
"""
Deletes a flight containing the passed id
Parameters
----------
flight_id : integer
"""
if flight_id is not None:
flights = Table('flights')
delete_flight_query = Query.from_(flights).delete().where(
flights.id.isin([flight_id]))
dao_tools.execute(delete_flight_query)
def delete_shared_flight(flight_id):
"""
Deletes all shared_flights containing the passed flight_id
Parameters
----------
flight_id : integer
"""
if flight_id is not None:
shared_flights = Table('shared_flights')
delete_shared_flight_query = Query.from_(
shared_flights).delete().where(
shared_flights.flight_id.isin([flight_id]))
dao_tools.execute(delete_shared_flight_query)
def update_flight(flight_derived_md, flight_id, flight_name):
flights = Table('flights')
update_flight_query = Query.update(flights)
if flight_derived_md.average_latitude is not None:
update_flight_query = update_flight_query.set(
flights.average_latitude, flight_derived_md.average_latitude)
if flight_derived_md.average_longitude is not None:
update_flight_query = update_flight_query.set(
flights.average_longitude, flight_derived_md.average_longitude)
if flight_derived_md.average_altitude is not None:
update_flight_query = update_flight_query.set(
flights.average_altitude, flight_derived_md.average_altitude)
if flight_derived_md.flight_start_time is not None:
update_flight_query = update_flight_query.set(
flights.flight_start_time, flight_derived_md.flight_start_time)
if flight_derived_md.flight_end_time is not None:
update_flight_query = update_flight_query.set(
flights.flight_end_time, flight_derived_md.flight_end_time)
if flight_derived_md.hardware_make is not None:
update_flight_query = update_flight_query.set(
flights.hardware_make, flight_derived_md.hardware_make)
if flight_derived_md.hardware_model is not None:
update_flight_query = update_flight_query.set(
flights.hardware_model, flight_derived_md.hardware_model)
update_flight_query = update_flight_query.set(flights.privacy, 1)
return dao_tools.execute(
update_flight_query.set(flights.flight_name,
trim_name(flight_name)).where(
flights.id.isin([flight_id])))
def update_user_api_key(id, api_key):
"""
Updates the user's API key based on the passed user id
Parameters
----------
id : integer
api_key : string or None
"""
if id is not None:
users = Table('users')
select_user_query = Query.update(users)
select_user_query = select_user_query.set(users.api_key, api_key)
select_user_query = select_user_query.where(users.id.isin([id]))
dao_tools.execute(select_user_query)
def update_user(id, password, role, force_reset):
"""
Updates user data to passed data in the SQL database
Parameters
----------
id : integer
password : string
role : integer
force_reset : integer or None
"""
if id is not None and (password is not None or role is not None):
users = Table('users')
update_user_query = Query.update(users)
if password is not None:
update_user_query = update_user_query.set(users.password, password)
if role is not None:
update_user_query = update_user_query.set(users.role, role)
if force_reset is not None:
update_user_query = update_user_query.set(users.force_reset,
force_reset)
update_user_query = update_user_query.where(users.id.isin([id]))
dao_tools.execute(update_user_query)
def select_users_shared_flight_ids(user_id):
"""
Selects flight ids based on the passed user id
Parameters
----------
user_id : integer
Returns
-------
list[tuple]
Query results based on incoming parameters.
NOTE: This will return None for queries that return no results.
"""
shared_flights = Table('shared_flights')
shared_flights_flight_query = Query.from_(shared_flights).select(
'flight_id').where(
shared_flights.user_id.isin(0 if user_id is None else [user_id]))
return dao_tools.execute(shared_flights_flight_query)
def select_users(select_columns, ids, email, password, role, force_reset,
api_key):
"""
Selects users based on the passed parameters
Parameters
----------
select_columns : string or None
ids : integer
email: string
password : string
role : integer
force_reset : integer or None
api_key : string or None
Returns
-------
list[tuple]
Query results based on incoming parameters.
NOTE: This will return None for queries that return no results.
"""
users = Table('users')
select_users_query = Query.from_(users).select(
'*' if select_columns is None else select_columns)
if ids is not None:
select_users_query = select_users_query.where(users.id.isin(ids))
if email is not None:
select_users_query = select_users_query.where(users.email.isin([email
]))
if password is not None:
select_users_query = select_users_query.where(
users.password.isin([password]))
if role is not None:
select_users_query = select_users_query.where(users.role.isin([role]))
if force_reset is not None:
select_users_query = select_users_query.where(
users.force_reset.isin([force_reset]))
if api_key is not None:
select_users_query = select_users_query.where(
users.api_key.isin([api_key]))
return dao_tools.execute(select_users_query)
def select_images(image_ids, user_ids, flight_ids, extensions, datetime_range,
latitude_range, longitude_range, altitude_range, make,
model):
"""
General purpose image selection method built to cover a broad demand of queries.
Every parameter can be None and list values can accept any number of elements including 0.
This was built to flesh out image selections in the manager without having to write complex queries.
Parameters
----------
image_ids : list[int]
The optional list of image ids
user_ids : list[int]
The optional list of user ids
flight_ids : list[int]
The optional list of flight ids
extensions : list[str]
The optional list of image format extensions
datetime_range : objects.range
The optional range of datetimes
latitude_range : objects.range
The optional range of latitudes
longitude_range : objects.range
The optional range of longitudes
altitude_range : objects.range
The optional range of altitudes
make : str
The optional hardware make
NOTE: Uses a LIKE comparision, full hardware make is not necessary, case IN-sensitive
model : str
The optional hardware model
NOTE: Uses a LIKE comparision, full hardware model is not necessary, case IN-sensitive
Returns
-------
list[tuple]
Query results based on incoming parameters.
NOTE: This will return None for all SELECT queries that return no results.
"""
images = Table('images')
select_image_query = Query.from_(images).select('*')
if image_ids is not None and len(image_ids) > 0:
select_image_query = select_image_query.where(
images.id.isin(image_ids))
if user_ids is not None and len(user_ids) > 0:
select_image_query = select_image_query.where(
images.user_id.isin(user_ids))
if flight_ids is not None and len(flight_ids) > 0:
select_image_query = select_image_query.where(
images.flight_id.isin(flight_ids))
if extensions is not None and len(extensions) > 0:
select_image_query = select_image_query.where(
images.image_extension.isin(extensions))
if datetime_range is not None:
select_image_query = select_image_query.where(
images.datetime >= datetime_range.begin).where(
images.datetime <= datetime_range.end)
if latitude_range is not None:
select_image_query = select_image_query.where(
images.latitude >= latitude_range.begin).where(
images.latitude <= latitude_range.end)
if longitude_range is not None:
select_image_query = select_image_query.where(
images.longitude >= longitude_range.begin).where(
images.longitude <= longitude_range.end)
if altitude_range is not None:
select_image_query = select_image_query.where(
images.altitude >= altitude_range.begin).where(
images.altitude <= altitude_range.end)
if make is not None:
select_image_query = select_image_query.where(
images.hardware_make.like('%' + make + '%'))
if model is not None:
select_image_query = select_image_query.where(
images.hardware_model.like('%' + model + '%'))
return dao_tools.execute(select_image_query.get_sql(quote_char=None))
def insert_images(image_records):
dao_tools.execute(insert_images_query, image_records)
def select_images(select_columns, image_ids, user_ids, flight_ids, directory_location, extensions, datetime_range, latitude_range, longitude_range, altitude_range, make, model, md5_hash):
"""
General purpose image selection method built to cover a broad demand of queries.
Every parameter can be None and list values can accept any number of elements including 0.
This was built to flesh out image selections in the manager without having to write complex queries.
NOTE. If all the parameters are empty this will return nothing
Parameters
----------
select_columns : string or None
The optional comma delimited string of columns to select on
NOTE: if this value is None, all (*) columns will be selected
image_ids : list[int]
The optional list of image ids
user_ids : list[int]
The optional list of user ids
flight_ids : list[int]
The optional list of flight ids
directory_location : string
The location of the image
extensions : list[str]
The optional list of image format extensions
datetime_range : objects.range
The optional range of datetimes
latitude_range : objects.range
The optional range of latitudes
longitude_range : objects.range
The optional range of longitudes
altitude_range : objects.range
The optional range of altitudes
make : str
The optional hardware make
NOTE: Uses a LIKE comparision, full hardware make is not necessary, case IN-sensitive
model : str
The optional hardware model
NOTE: Uses a LIKE comparision, full hardware model is not necessary, case IN-sensitive
md5_hash : string
The hash of the image
Returns
-------
list[tuple]
Query results based on incoming parameters.
NOTE: This will return None for queries that return no results.
"""
images = Table('images')
select_image_query = Query.from_(images).select('*' if select_columns is None else select_columns)
added = False
if image_ids is not None:
added = True
select_image_query = select_image_query.where(images.id.isin(image_ids))
if user_ids is not None:
added = True
select_image_query = select_image_query.where(images.user_id.isin(user_ids))
if flight_ids is not None:
added = True
select_image_query = select_image_query.where(images.flight_id.isin(flight_ids))
if directory_location is not None:
added = True
select_image_query = select_image_query.where(images.directory_location.isin([directory_location]))
if extensions is not None:
added = True
select_image_query = select_image_query.where(images.image_extension.isin(extensions))
if datetime_range is not None:
added = True
select_image_query = select_image_query.where(images.datetime >= datetime_range.begin).where(images.datetime <= datetime_range.end)
if latitude_range is not None:
added = True
select_image_query = select_image_query.where(images.latitude >= latitude_range.begin).where(images.latitude <= latitude_range.end)
if longitude_range is not None:
added = True
select_image_query = select_image_query.where(images.longitude >= longitude_range.begin).where(images.longitude <= longitude_range.end)
if altitude_range is not None:
added = True
select_image_query = select_image_query.where(images.altitude >= altitude_range.begin).where(images.altitude <= altitude_range.end)
if make is not None:
added = True
select_image_query = select_image_query.where(images.hardware_make.like('%' + make + '%'))
if model is not None:
added = True
select_image_query = select_image_query.where(images.hardware_model.like('%' + model + '%'))
if md5_hash is not None:
added = True
select_image_query = select_image_query.where(images.md5_hash.isin([md5_hash]))
if added:
return dao_tools.execute(select_image_query)
else:
return []
def insert_shared_flights(shared_flights_records):
return dao_tools.execute(insert_shared_flights_query,
shared_flights_records)
def insert_users(users_records):
return dao_tools.execute(insert_users_query, users_records)
def select_flights(select_columns, flight_ids, user_ids, flight_name,
manual_notes, address, field_name, crop_name,
start_datetime_range, end_datetime_range, latitude_range,
longitude_range, altitude_range, make, model):
"""
General purpose flight selection method built to cover a broad demand of queries.
Every parameter can be None and list values can accept any number of elements including 0.
This was built to flesh out flight selections in the manager without having to write complex queries.
Parameters
----------
select_columns : string or None
The optional comma delimited string of columns to select on
NOTE: if this value is None, all (*) columns will be selected
flight_ids : list[int]
The optional list of flight ids
user_ids : list[int]
The optional list of user ids
flight_name : str
The optional flight_name
NOTE: Uses a LIKE comparision, full flight_name is not necessary, case IN-sensitive
manual_notes : str
The optional manual_notes
NOTE: Uses a LIKE comparision, full manual_notes is not necessary, case IN-sensitive
address : str
The optional address
NOTE: Uses a LIKE comparision, full address is not necessary, case IN-sensitive
field_name : str
The optional field_name
NOTE: Uses a LIKE comparision, full field_name is not necessary, case IN-sensitive
crop_name : str
The optional crop_name
NOTE: Uses a LIKE comparision, full crop_name is not necessary, case IN-sensitive
start_datetime_range : objects.range
The optional range of datetimes for the start of the flight
end_datetime_range : objects.range
The optional range of datetimes for the end of the flight
latitude_range : objects.range
The optional range of latitudes
longitude_range : objects.range
The optional range of longitudes
altitude_range : objects.range
The optional range of altitudes
make : str
The optional hardware make
NOTE: Uses a LIKE comparision, full hardware make is not necessary, case IN-sensitive
model : str
The optional hardware model
NOTE: Uses a LIKE comparision, full hardware model is not necessary, case IN-sensitive
Returns
-------
list[tuple]
Query results based on incoming parameters.
NOTE: This will return None for queries that return no results.
"""
flights = Table('flights')
select_flights_query = Query.from_(flights).select(
'*' if select_columns is None else select_columns)
if flight_ids is not None:
select_flights_query = select_flights_query.where(
flights.id.isin(flight_ids))
if user_ids is not None:
select_flights_query = select_flights_query.where(
flights.user_id.isin(user_ids))
if flight_name is not None:
select_flights_query = select_flights_query.where(
flights.flight_name.like('%' + flight_name + '%'))
if manual_notes is not None:
select_flights_query = select_flights_query.where(
flights.manual_notes.like('%' + manual_notes + '%'))
if address is not None:
select_flights_query = select_flights_query.where(
flights.address.like('%' + address + '%'))
if field_name is not None:
select_flights_query = select_flights_query.where(
flights.field_name.like('%' + field_name + '%'))
if crop_name is not None:
select_flights_query = select_flights_query.where(
flights.crop_name.like('%' + crop_name + '%'))
if start_datetime_range is not None:
select_flights_query = select_flights_query.where(
flights.flight_start_time >= start_datetime_range.begin).where(
flights.flight_start_time <= start_datetime_range.end)
if end_datetime_range is not None:
select_flights_query = select_flights_query.where(
flights.flight_end_time >= end_datetime_range.begin).where(
flights.flight_end_time <= end_datetime_range.end)
if latitude_range is not None:
select_flights_query = select_flights_query.where(
flights.average_latitude >= latitude_range.begin).where(
flights.average_latitude <= latitude_range.end)
if longitude_range is not None:
select_flights_query = select_flights_query.where(
flights.average_longitude >= longitude_range.begin).where(
flights.average_longitude <= longitude_range.end)
if altitude_range is not None:
select_flights_query = select_flights_query.where(
flights.average_altitude >= altitude_range.begin).where(
flights.average_altitude <= altitude_range.end)
if make is not None:
select_flights_query = select_flights_query.where(
flights.hardware_make.like('%' + make + '%'))
if model is not None:
select_flights_query = select_flights_query.where(
flights.hardware_model.like('%' + model + '%'))
return dao_tools.execute(select_flights_query)
def insert_flights(flight_records):
return dao_tools.execute(insert_flights_query, flight_records)