def add_vehicle_txn(session, longitude, latitude, battery, vehicle_info):
"""
Insert a row into the vehicles table, and one into location_history.
Arguments:
session {.Session} -- The active session for the database connection.
longitude {Float} -- Longitude of the vehicle.
Latitude {Float} -- Latitude of the vehicle.
battery {Int} -- Battery percantage remaining.
vehicle_info {dict} -- Information on the vehicle's info.
Returns:
{dict} -- The vehicle's new UUID and the location_history row's new
UUID as {'vehicle_id': <UUID>, 'location_history_id': <UUID>}
"""
vehicle_id = uuid4()
current_time = func.now()
location_history_id = uuid4()
new_vehicle_row = Vehicle(id=str(vehicle_id),
in_use=False,
vehicle_info=vehicle_info,
battery=battery)
new_location_history_row = LocationHistory(id=str(location_history_id),
vehicle_id=str(vehicle_id),
longitude=longitude,
latitude=latitude,
ts=current_time)
session.add(new_vehicle_row)
session.flush() # can't let the next row get inserted first.
session.add(new_location_history_row)
return {"vehicle_id": str(vehicle_id),
"location_history_id": str(location_history_id)}
def add_vehicle_txn(session, vehicle_type, longitude, latitude, battery):
"""
Insert a row into the vehicles table, and one into location_history.
Does the equivalent of:
# BEGIN;
#
# INSERT INTO vehicles (id, battery, in_use, vehicle_type)
# VALUES (<vehicle_id>, 12, false, 'scooter')
# );
#
# INSERT INTO location_history (id, vehicle_id, ts, longitude, latitude)
# VALUES (<uuid>, <vehicle_id>, now(), <longitude>, <latitude>);
#
# COMMIT;
Arguments:
session {.Session} -- The active session for the database connection.
vehicle_type {String} -- The vehicle's type.
longitude {Float} -- Longitude of the vehicle.
Latitude {Float} -- Latitude of the vehicle.
battery {Int} -- Battery percantage remaining.
Returns:
{dict} -- The vehicle's new UUID and the location_history row's new
UUID as {'vehicle_id': <UUID>, 'location_history_id': <UUID>}
"""
vehicle_id = uuid4()
current_time = func.now()
location_history_id = uuid4()
new_vehicle_row = Vehicle(id=str(vehicle_id),
in_use=False,
vehicle_type=vehicle_type,
battery=battery)
new_location_history_row = LocationHistory(id=str(location_history_id),
vehicle_id=str(vehicle_id),
longitude=longitude,
latitude=latitude,
ts=current_time)
session.add(new_vehicle_row)
session.flush() # can't let the next row get inserted first.
session.add(new_location_history_row)
return {
"vehicle_id": str(vehicle_id),
"location_history_id": str(location_history_id)
}
def start_ride_txn(session, vehicle_id, user_email):
"""
Start a vehicle ride (or continue if the vehicle is already in use).
Arguments:
session {.Session} -- The active session for the database connection.
vehicle_id {String} -- The vehicle's `id` column.
"""
# find the row where we want to start the ride.
# SELECT * FROM vehicles WHERE id = <vehicle_id> AND in_use = false
# LIMIT 1;
vehicle = session.query(Vehicle).filter(Vehicle.id == vehicle_id). \
filter(Vehicle.in_use == False).first()
if vehicle is None:
return None
# SELECT * FROM location_history WHERE vehicle_id = <vehicle_id>
# ORDER BY ts DESC LIMIT 1;
last_chx = session.query(LocationHistory). \
filter(LocationHistory.vehicle_id ==
vehicle_id). \
order_by(LocationHistory.ts.desc()). \
first()
new_location_history_id = str(uuid4())
new_timestamp = func.now()
new_location_history_row = LocationHistory(id=new_location_history_id,
vehicle_id=vehicle_id,
longitude=last_chx.longitude,
latitude=last_chx.latitude,
ts=new_timestamp)
new_ride_id = str(uuid4())
new_ride_row = Ride(id=new_ride_id,
vehicle_id=vehicle_id,
user_email=user_email,
start_ts=new_timestamp,
end_ts=None)
# UPDATE vehicles SET in_use = true WHERE vehicles.id = <vehicle_id>
vehicle.in_use = True
vehicle.last_checkin = func.now()
session.add(new_location_history_row)
session.flush()
session.add(new_ride_row)
return True # Just making it explicit that this worked.
def end_ride_txn(session, ride_id, new_longitude, new_latitude,
new_battery):
"""
Update a row of the rides table, and update a row of the vehicles table.
Arguments:
session {.Session} -- The active session for the database connection.
vehicle_id {String} -- The vehicle's `id` column
new_longitude {Float} -- The longitude where the ride ended
new_latitude {Float} -- The latitude where the ride ended
new_battery {Integer} -- The vehicle's battery % when the ride ended
Returns:
{Boolean} -- True if the ride ended.
"""
ride = session.query(Ride).filter(Ride.id == ride_id).first()
if ride is None:
return False
# find the vehicle
vehicle = session.query(Vehicle).filter(Vehicle.id == ride.vehicle_id). \
filter(Vehicle.in_use == True).first()
if vehicle is None:
return False
# Prepare the new row for vehicle_history
ride_end_time = str(func.now())
new_history_entry = LocationHistory(id=str(uuid4()),
vehicle_id=ride.vehicle_id,
longitude=new_longitude,
latitude=new_latitude,
ts=ride_end_time)
# Perform writes to end the ride
ride.end_ts = ride_end_time
vehicle.in_use = False
vehicle.battery = new_battery
session.add(new_history_entry)
return True # Make it explicit that this worked.
def add_vehicle_txn(session, longitude, latitude, battery, vehicle_info):
"""
Insert a row into the vehicles table, and one into location_history.
Arguments:
session {.Session} -- The active session for the database connection.
longitude {Float} -- Longitude of the vehicle.
Latitude {Float} -- Latitude of the vehicle.
battery {Int} -- Battery percantage remaining.
vehicle_info {dict} -- Information on the vehicle's info.
Returns:
{dict} -- The vehicle's new UUID and the location_history row's new
UUID as {'vehicle_id': <UUID>, 'location_history_id': <UUID>}
"""
vehicle_id = uuid4()
current_time = func.now()
location_history_id = uuid4()
# LAB: MODIFY THE FOLLOWING TO INCLUDE YOUR NEW JSON COLUMN
# ALL YOU NEED TO DO IS INCLUDE VEHICLE_INFO IN `new_vehicle_row`.
new_vehicle_row = Vehicle(id=str(vehicle_id),
in_use=False,
battery=battery)
new_location_history_row = LocationHistory(id=str(location_history_id),
# vehicle_id=str(vehicle_id),
vehicle_json=new_vehicle_row.vehicle_info,
longitude=longitude,
latitude=latitude,
ts=current_time)
session.add(new_vehicle_row)
# https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.flush
session.flush() # can't let the next row get inserted first.
session.add(new_location_history_row)
return {"vehicle_id": str(vehicle_id),
"location_history_id": str(location_history_id)}
