def GetTracks() -> pd.DataFrame:
select_statement = textwrap.dedent("""
SELECT DISTINCT track
FROM sessions
""")
with timescale.ConnectToDB() as conn:
return pd.io.sql.read_sql(select_statement, conn)['track']
def CleanupTimescale():
logging.info('Cleaning up Timescale')
with timescale.ConnectToDB() as conn:
NukeNonLiveData(conn)
NukeLapsWithNoDuration(conn)
NukeLapsByDuration(conn)
# Hanging deletions should probably come last.
NukeHangingLaps(conn)
NukeHangingSessions(conn)
conn.commit()
def GetPointsColumns() -> List[Text]:
select_statement = textwrap.dedent("""
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'points'
""")
with timescale.ConnectToDB() as conn:
with conn.cursor() as cursor:
cursor.execute(select_statement)
columns = [row[0] for row in cursor.fetchall()]
columns.remove('lat')
columns.remove('lon')
return columns
def AddPointPanel(self, title: Text, point_values: Tuple[Text],
y_axis_title: Text):
select_statement = textwrap.dedent("""
SELECT
time,
{columns},
laps.number::text
FROM points
JOIN laps ON laps.id=points.lap_id
WHERE $__timeFilter(time)
ORDER BY time
""")
query = sql.SQL(select_statement).format(columns=sql.SQL(',').join(
[sql.Identifier(col) for col in point_values]))
with timescale.ConnectToDB() as conn:
self.AddGraphPanel(title, query.as_string(conn), y_axis_title)
def GetSessions() -> pd.DataFrame:
select_statement = textwrap.dedent("""
SELECT
TO_CHAR((duration_ms || 'millisecond')::interval, 'MI:SS:MS') AS lap_time,
TO_CHAR(sessions.time AT TIME ZONE 'PDT', 'YYYY-MM-DD HH:MI:SS') as session_time,
laps.id,
laps.number AS lap_number,
track,
(count(points.time)::float / (duration_ms::float / 1000.0)) as points_per_second
FROM laps
JOIN points ON laps.id=points.lap_id
JOIN sessions ON laps.session_id=sessions.id
WHERE duration_ms IS NOT null
GROUP BY sessions.id, track, sessions.time, laps.id, laps.number, lap_time, laps.duration_ms
""")
with timescale.ConnectToDB() as conn:
return pd.io.sql.read_sql(select_statement, conn)
def GetTimeDelta(lap_ids: List[int]) -> pd.DataFrame:
select_statement = textwrap.dedent("""
SELECT
a.elapsed_distance_m,
b.elapsed_duration_ms - a.elapsed_duration_ms AS time_delta,
b.number AS lap_number
FROM (
SELECT
elapsed_duration_ms,
ROUND(CAST(elapsed_distance_m AS numeric), 0) as elapsed_distance_m
FROM points
JOIN laps ON laps.id=points.lap_id
WHERE lap_id = %(lap_id_a)s
) AS a INNER JOIN (
SELECT
elapsed_duration_ms,
ROUND(CAST(elapsed_distance_m AS numeric), 0) as elapsed_distance_m,
number
FROM points
JOIN laps ON laps.id=points.lap_id
WHERE lap_id = %(lap_id_b)s
) as b
ON a.elapsed_distance_m = b.elapsed_distance_m
""")
lap_id_a = lap_ids[0]
lap_dfs = []
with timescale.ConnectToDB() as conn:
for lap_id in lap_ids[1:]:
df = pd.io.sql.read_sql(select_statement,
conn,
params={
'lap_id_a': str(lap_id_a),
'lap_id_b': str(lap_id)
})
df['lap_id'] = lap_id
lap_dfs.append(df)
combined_df = pd.concat(lap_dfs)
combined_df.sort_values(by='elapsed_distance_m', inplace=True)
return combined_df
def GetLapsData(lap_ids: List[int], point_values: List[Text]) -> pd.DataFrame:
all_columns = GetPointsColumns()
# Only select columns that map to point_values.
columns = set(point_values).intersection(set(all_columns))
# Columns used for graph labels and should always be included.
columns.update(['elapsed_distance_m', 'number', 'lap_id'])
if ('front_brake_pressure_percentage' in point_values
or 'rear_brake_pressure_percentage' in point_values):
columns.update(
['front_brake_pressure_voltage', 'rear_brake_pressure_voltage'])
if 'gsum' in point_values:
columns.update(
['accelerometer_x', 'accelerometer_y', 'accelerometer_z'])
if 'racing_line' in point_values:
columns.update(['lat', 'lon'])
select_statement = textwrap.dedent("""
SELECT {columns}
FROM points
JOIN laps ON points.lap_id = laps.id
WHERE lap_id IN %(lap_ids)s
""")
query = sql.SQL(select_statement).format(
columns=sql.SQL(',').join([sql.Identifier(col) for col in columns]))
with timescale.ConnectToDB() as conn:
with conn.cursor() as cursor:
df = pd.io.sql.read_sql(
query.as_string(cursor),
conn,
params={'lap_ids': tuple(str(lap_id) for lap_id in lap_ids)})
df.sort_values(by='elapsed_distance_m', inplace=True)
df['front_brake_pressure_percentage'] = (
df['front_brake_pressure_voltage'] /
df['front_brake_pressure_voltage'].max())
df['rear_brake_pressure_percentage'] = (
df['rear_brake_pressure_voltage'] /
df['rear_brake_pressure_voltage'].max())
df['gsum'] = df['accelerometer_x'].abs() + df['accelerometer_y'].abs()
df.rename(columns={'number': 'lap_number'}, inplace=True)
return df
def GetLiveData(start_time: datetime.datetime, point_values: List[Text]):
all_columns = GetPointsColumns()
# Only select columns that map to point_values.
columns = set(point_values).intersection(set(all_columns))
# Columns used for graph labels and should always be included.
columns.update(['time', 'number', 'lap_id'])
select_statement = textwrap.dedent("""
SELECT {columns}
FROM points
JOIN laps ON points.lap_id = laps.id
WHERE
time > %(start_time)s
""")
query = sql.SQL(select_statement).format(
columns=sql.SQL(',').join([sql.Identifier(col) for col in columns]))
with timescale.ConnectToDB() as conn:
with conn.cursor() as cursor:
df = pd.io.sql.read_sql(query.as_string(cursor),
conn,
params={'start_time': start_time})
df.sort_values(by='time', inplace=True)
df.rename(columns={'number': 'lap_number'}, inplace=True)
return df
def SyncLocalData():
timescale_conn = timescale.ConnectToDB()
for filepath in GetLocalFiles():
logging.info('Syncing %s', filepath)
if not IsFileAlreadySynced(timescale_conn, filepath):
SyncFile(os.path.join(LAP_LOG_PATH, filepath))