--List all workouts with their split times and distances nested in:
--first create three temp tables and a trigger that are needed to calculate split times for time/distance interval programs.
DROP TABLE IF EXISTS tmp_workout_interval;
DROP TABLE IF EXISTS tmp_interval_trackpoint;
DROP TABLE IF EXISTS tmp_interval_trackpoint_old;
CREATE TABLE tmp_workout_interval (
programId INTEGER,
intervalId INTEGER,
workoutId INTEGER,
intensity INTEGER,
duration INTEGER,
distance INTEGER,
PRIMARY KEY ( programId, intervalId, workoutId )
);
CREATE TABLE tmp_interval_trackpoint (
workoutId INTEGER,
intervalId INTEGER,
intensity INTEGER,
tpid INTEGER,
duration REAL,
distance REAL,
PRIMARY KEY ( workoutId, intervalId, tpid )
);
CREATE TABLE tmp_interval_trackpoint_old (
workoutId INTEGER,
intervalId INTEGER,
duration REAL,
distance REAL,
PRIMARY KEY ( workoutId, intervalId)
);
CREATE TRIGGER tmp_workout_interval
AFTER INSERT ON tmp_workout_interval
FOR EACH ROW
BEGIN
INSERT OR REPLACE INTO tmp_interval_trackpoint_old (workoutid, intervalid, duration, distance ) VALUES (NEW.workoutid, 0, 0,0)
;
INSERT INTO tmp_interval_trackpoint_old (workoutid, intervalid, duration, distance )
SELECT workoutid, intervalId, duration, distance
FROM tmp_interval_trackpoint
WHERE workoutId=NEW.workoutId AND
intervalId=NEW.intervalId-1
;
INSERT INTO tmp_interval_trackpoint (workoutid, intervalid, intensity, tpid, duration, distance )
SELECT
NEW.workoutId,
NEW.intervalId,
NEW.intensity,
t.tpid,
-- here is a bit of mathematics whereby either interval duration or distance must be 0, hence the MAX function.
-- the second argument in the MAX function also handles a possible residuum by knocking it off.
max(ito.duration + NEW.duration, t.duration - ((t.distance-ito.distance-NEW.distance/1000.0) / (t.speed/3600.0))),
max(ito.distance + NEW.distance/1000.0, t.distance- ((t.duration-ito.duration-NEW.duration) * t.speed/3600.0))
FROM trackpoint t, tmp_interval_trackpoint_old ito
WHERE
t.workoutId=NEW.workoutId AND
ito.workoutId=NEW.workoutID AND
ito.intervalId=NEW.intervalID-1 AND
t.timestamp=max(
(select min(t2.timestamp)
from trackpoint t2
where t2.workoutId = NEW.workoutId and
t2.speed > 0 and
t2.duration >= ito.duration + NEW.duration
),
(select min(t2.timestamp)
from trackpoint t2
where t2.workoutId = NEW.workoutId and
t2.speed > 0 and
t2.distance >= ito.distance + NEW.distance/1000.0
)
)
;
END
;
--The following Insert will engage the trigger for the temporary split time calculation:
INSERT INTO tmp_workout_interval
SELECT i.programId,
i.intervalId,
w.workoutId,
i.intensity,
i.duration,
i.distance
FROM interval i, interval_program ip, workout w
WHERE w.status>0 AND
i.programId=ip.programId AND
ip.uuid=w.ipUuid
ORDER BY w.workoutId, i.intervalId
;
-- And finally the query made up of four record groups (A,B,C,X):
-- first select main workout records:
SELECT workoutId,
'A (workout)' as grp_sort,
datetime(starttime/1000, 'unixepoch', 'localtime') as start_time,
datetime(end_time/1000, 'unixepoch', 'localtime') as end_time,
sport,
round(distance,2) as distance,
time(duration, 'unixepoch') as duration,
hrAvg as avg_heart_rate,
calories,
temperature,
hydration,
round(distance/duration*3600.0,2) as avg_speed,
time(duration/distance, 'unixepoch') as avg_pace,
time(max(0,(end_time-starttime)/1000-duration), 'unixepoch') as total_pause,
0.0 as split_distance,
time(0, 'unixepoch') as split_time,
time(0, 'unixepoch') as subtotal_pause,
'' as intensity
FROM workout w
WHERE status>0
UNION ALL
-- for basic non-interval workouts, add lap times:
SELECT w.workoutId,
'B (lap)' as grp_sort,
datetime(w.starttime/1000, 'unixepoch', 'localtime') as start_time,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
l.number as split_distance,
time(l.splittime, 'unixepoch') as split_time,
(select time(max(0,(t.timestamp-w.starttime)/1000-t.duration), 'unixepoch')
from trackpoint t
where t.workoutid=l.workoutid and
t.timestamp=(select min(t2.timestamp)
from trackpoint t2
where t2.workoutid=l.workoutid and
t2.distance>=l.number and
t2.instruction=4
)
) as subtotal_pause,
'' as intensity
FROM laptimes l, workout w
WHERE l.type=1 AND w.status>0 AND
w.workoutid=l.workoutid
UNION ALL
-- for interval workouts, add interval-program splits (time-only and distance-only programs are supported but no mixed programs)
SELECT w.workoutId,
'C (interval)' as grp_sort,
datetime(w.starttime/1000, 'unixepoch', 'localtime') as start_time,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
round(it.distance, 2) as split_distance,
time(it.duration, 'unixepoch') as split_time,
time(max(0,(t.timestamp-w.starttime)/1000.0-t.duration), 'unixepoch') as subtotal_pause,
it.intensity
FROM tmp_interval_trackpoint it, trackpoint t, workout w
WHERE it.workoutId=w.workoutId AND
it.tpid=t.tpid
UNION ALL
-- add a concluding split record to each workout:
SELECT w.workoutId,
'X (last split)' as grp_sort,
datetime(w.starttime/1000, 'unixepoch', 'localtime') as start_time,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
round(t.distance, 2) as split_distance,
time(t.duration, 'unixepoch') as split_time,
time(max(0,(t.timestamp-w.starttime)/1000.0-t.duration), 'unixepoch') as subtotal_pause,
'' as intensity
FROM workout w, trackpoint t
WHERE w.status>0 AND
t.workoutId=w.workoutId AND
t.timestamp=
(select max(t2.timestamp)
from trackpoint t2
where t2.workoutId = w.workoutId and t2.instruction=4
)
ORDER BY 3,1,2,15
;