Skip to content

Commit cb74f29

Browse files
mafredrimafredri
committed
fix: implement GetTemplateInsightsByInterval in dbmem
1 parent 4daf99b commit cb74f29

File tree

1 file changed

+71
-64
lines changed

1 file changed

+71
-64
lines changed

‎coderd/database/dbmem/dbmem.go

+71-64
Original file line numberDiff line numberDiff line change
@@ -3407,7 +3407,7 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
34073407
return row, nil
34083408
}
34093409

3410-
func (q *FakeQuerier) GetTemplateInsightsByInterval(ctx context.Context, arg database.GetTemplateInsightsByIntervalParams) ([]database.GetTemplateInsightsByIntervalRow, error) {
3410+
func (q *FakeQuerier) GetTemplateInsightsByInterval(_ context.Context, arg database.GetTemplateInsightsByIntervalParams) ([]database.GetTemplateInsightsByIntervalRow, error) {
34113411
err := validateDatabaseType(arg)
34123412
if err != nil {
34133413
return nil, err
@@ -3416,82 +3416,89 @@ func (q *FakeQuerier) GetTemplateInsightsByInterval(ctx context.Context, arg dat
34163416
q.mutex.RLock()
34173417
defer q.mutex.RUnlock()
34183418

3419-
type statByInterval struct {
3420-
startTime, endTime time.Time
3421-
userSet map[uuid.UUID]struct{}
3422-
templateIDSet map[uuid.UUID]struct{}
3423-
}
3419+
/*
3420+
WITH
3421+
ts AS (
3422+
SELECT
3423+
d::timestamptz AS from_,
3424+
CASE
3425+
WHEN (d::timestamptz + (@interval_days::int || ' day')::interval) <= @end_time::timestamptz
3426+
THEN (d::timestamptz + (@interval_days::int || ' day')::interval)
3427+
ELSE @end_time::timestamptz
3428+
END AS to_
3429+
FROM
3430+
-- Subtract 1 microsecond from end_time to avoid including the next interval in the results.
3431+
generate_series(@start_time::timestamptz, (@end_time::timestamptz) - '1 microsecond'::interval, (@interval_days::int || ' day')::interval) AS d
3432+
)
34243433
3425-
statsByInterval := []statByInterval{{arg.StartTime, arg.StartTime.AddDate(0, 0, int(arg.IntervalDays)), make(map[uuid.UUID]struct{}), make(map[uuid.UUID]struct{})}}
3426-
for statsByInterval[len(statsByInterval)-1].endTime.Before(arg.EndTime) {
3427-
statsByInterval = append(statsByInterval, statByInterval{statsByInterval[len(statsByInterval)-1].endTime, statsByInterval[len(statsByInterval)-1].endTime.AddDate(0, 0, int(arg.IntervalDays)), make(map[uuid.UUID]struct{}), make(map[uuid.UUID]struct{})})
3428-
}
3429-
if statsByInterval[len(statsByInterval)-1].endTime.After(arg.EndTime) {
3430-
statsByInterval[len(statsByInterval)-1].endTime = arg.EndTime
3431-
}
3434+
SELECT
3435+
ts.from_ AS start_time,
3436+
ts.to_ AS end_time,
3437+
array_remove(array_agg(DISTINCT tus.template_id), NULL)::uuid[] AS template_ids,
3438+
COUNT(DISTINCT tus.user_id) AS active_users
3439+
FROM
3440+
ts
3441+
LEFT JOIN
3442+
template_usage_stats AS tus
3443+
ON
3444+
tus.start_time >= ts.from_
3445+
AND tus.end_time <= ts.to_
3446+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
3447+
GROUP BY
3448+
ts.from_, ts.to_;
3449+
*/
34323450

3433-
for _, s := range q.workspaceAgentStats {
3434-
if s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.Equal(arg.EndTime) || s.CreatedAt.After(arg.EndTime) {
3435-
continue
3436-
}
3437-
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, s.TemplateID) {
3438-
continue
3439-
}
3440-
if s.ConnectionCount == 0 {
3441-
continue
3451+
type interval struct {
3452+
From time.Time
3453+
To time.Time
3454+
}
3455+
var ts []interval
3456+
for d := arg.StartTime; d.Before(arg.EndTime); d = d.AddDate(0, 0, int(arg.IntervalDays)) {
3457+
to := d.AddDate(0, 0, int(arg.IntervalDays))
3458+
if to.After(arg.EndTime) {
3459+
to = arg.EndTime
34423460
}
3461+
ts = append(ts, interval{From: d, To: to})
3462+
}
34433463

3444-
for _, ds := range statsByInterval {
3445-
if s.CreatedAt.Before(ds.startTime) || s.CreatedAt.Equal(ds.endTime) || s.CreatedAt.After(ds.endTime) {
3464+
type grouped struct {
3465+
TemplateIDs map[uuid.UUID]struct{}
3466+
UserIDs map[uuid.UUID]struct{}
3467+
}
3468+
groupedByInterval := make(map[interval]grouped)
3469+
for _, tus := range q.templateUsageStats {
3470+
for _, t := range ts {
3471+
if tus.StartTime.Before(t.From) || tus.EndTime.After(t.To) {
34463472
continue
34473473
}
3448-
ds.userSet[s.UserID] = struct{}{}
3449-
ds.templateIDSet[s.TemplateID] = struct{}{}
3450-
}
3451-
}
3452-
3453-
for _, s := range q.workspaceAppStats {
3454-
w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
3455-
if err != nil {
3456-
return nil, err
3457-
}
3458-
3459-
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, w.TemplateID) {
3460-
continue
3461-
}
3462-
3463-
for _, ds := range statsByInterval {
3464-
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
3465-
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
3466-
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
3467-
if !(((s.SessionStartedAt.After(ds.startTime) || s.SessionStartedAt.Equal(ds.startTime)) && s.SessionStartedAt.Before(ds.endTime)) ||
3468-
(s.SessionEndedAt.After(ds.startTime) && s.SessionEndedAt.Before(ds.endTime)) ||
3469-
(s.SessionStartedAt.Before(ds.startTime) && (s.SessionEndedAt.After(ds.endTime) || s.SessionEndedAt.Equal(ds.endTime)))) {
3474+
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, tus.TemplateID) {
34703475
continue
34713476
}
3472-
3473-
ds.userSet[s.UserID] = struct{}{}
3474-
ds.templateIDSet[w.TemplateID] = struct{}{}
3477+
g, ok := groupedByInterval[t]
3478+
if !ok {
3479+
g = grouped{
3480+
TemplateIDs: make(map[uuid.UUID]struct{}),
3481+
UserIDs: make(map[uuid.UUID]struct{}),
3482+
}
3483+
}
3484+
g.TemplateIDs[tus.TemplateID] = struct{}{}
3485+
g.UserIDs[tus.UserID] = struct{}{}
3486+
groupedByInterval[t] = g
34753487
}
34763488
}
34773489

3478-
var result []database.GetTemplateInsightsByIntervalRow
3479-
for _, ds := range statsByInterval {
3480-
templateIDs := make([]uuid.UUID, 0, len(ds.templateIDSet))
3481-
for templateID := range ds.templateIDSet {
3482-
templateIDs = append(templateIDs, templateID)
3490+
var rows []database.GetTemplateInsightsByIntervalRow
3491+
for _, t := range ts { // Ordered by interval.
3492+
row := database.GetTemplateInsightsByIntervalRow{
3493+
StartTime: t.From,
3494+
EndTime: t.To,
34833495
}
3484-
slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
3485-
return slice.Ascending(a.String(), b.String())
3486-
})
3487-
result = append(result, database.GetTemplateInsightsByIntervalRow{
3488-
StartTime: ds.startTime,
3489-
EndTime: ds.endTime,
3490-
TemplateIDs: templateIDs,
3491-
ActiveUsers: int64(len(ds.userSet)),
3492-
})
3496+
row.TemplateIDs = uniqueSortedUUIDs(maps.Keys(groupedByInterval[t].TemplateIDs))
3497+
row.ActiveUsers = int64(len(groupedByInterval[t].UserIDs))
3498+
rows = append(rows, row)
34933499
}
3494-
return result, nil
3500+
3501+
return rows, nil
34953502
}
34963503

34973504
func (q *FakeQuerier) GetTemplateInsightsByTemplate(_ context.Context, arg database.GetTemplateInsightsByTemplateParams) ([]database.GetTemplateInsightsByTemplateRow, error) {

0 commit comments

Comments
 (0)