Skip to content

Commit ddc8606

Browse files
mafredrimafredri
committed
add test for template insights
1 parent 2e51056 commit ddc8606

File tree

5 files changed

+249
-55
lines changed

5 files changed

+249
-55
lines changed

‎coderd/database/dbfake/dbfake.go

Lines changed: 113 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1917,22 +1917,127 @@ func (q *FakeQuerier) GetTemplateDAUs(_ context.Context, arg database.GetTemplat
19171917
return rs, nil
19181918
}
19191919

1920-
func (q *FakeQuerier) GetTemplateDailyInsights(ctx context.Context, arg database.GetTemplateDailyInsightsParams) ([]database.GetTemplateDailyInsightsRow, error) {
1920+
func (q *FakeQuerier) GetTemplateDailyInsights(_ context.Context, arg database.GetTemplateDailyInsightsParams) ([]database.GetTemplateDailyInsightsRow, error) {
19211921
err := validateDatabaseType(arg)
19221922
if err != nil {
19231923
return nil, err
19241924
}
19251925

1926-
panic("not implemented")
1926+
type dailyStat struct {
1927+
startTime, endTime time.Time
1928+
userSet map[uuid.UUID]struct{}
1929+
templateIDSet map[uuid.UUID]struct{}
1930+
}
1931+
dailyStats := []dailyStat{{arg.StartTime, arg.StartTime.AddDate(0, 0, 1), make(map[uuid.UUID]struct{}), make(map[uuid.UUID]struct{})}}
1932+
for dailyStats[len(dailyStats)-1].endTime.Before(arg.EndTime) {
1933+
dailyStats = append(dailyStats, dailyStat{dailyStats[len(dailyStats)-1].endTime, dailyStats[len(dailyStats)-1].endTime.AddDate(0, 0, 1), make(map[uuid.UUID]struct{}), make(map[uuid.UUID]struct{})})
1934+
}
1935+
if dailyStats[len(dailyStats)-1].endTime.After(arg.EndTime) {
1936+
dailyStats[len(dailyStats)-1].endTime = arg.EndTime
1937+
}
1938+
1939+
for _, s := range q.workspaceAgentStats {
1940+
if s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.Equal(arg.EndTime) || s.CreatedAt.After(arg.EndTime) {
1941+
continue
1942+
}
1943+
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, s.TemplateID) {
1944+
continue
1945+
}
1946+
if s.ConnectionCount == 0 {
1947+
continue
1948+
}
1949+
1950+
for _, ds := range dailyStats {
1951+
if s.CreatedAt.Before(ds.startTime) || s.CreatedAt.Equal(ds.endTime) || s.CreatedAt.After(ds.endTime) {
1952+
continue
1953+
}
1954+
ds.userSet[s.UserID] = struct{}{}
1955+
ds.templateIDSet[s.TemplateID] = struct{}{}
1956+
break
1957+
}
1958+
}
1959+
1960+
var result []database.GetTemplateDailyInsightsRow
1961+
for _, ds := range dailyStats {
1962+
templateIDs := make([]uuid.UUID, 0, len(ds.templateIDSet))
1963+
for templateID := range ds.templateIDSet {
1964+
templateIDs = append(templateIDs, templateID)
1965+
}
1966+
slices.SortFunc(templateIDs, func(a, b uuid.UUID) bool {
1967+
return a.String() < b.String()
1968+
})
1969+
result = append(result, database.GetTemplateDailyInsightsRow{
1970+
StartTime: ds.startTime,
1971+
EndTime: ds.endTime,
1972+
TemplateIDs: templateIDs,
1973+
ActiveUsers: int64(len(ds.userSet)),
1974+
})
1975+
}
1976+
return result, nil
19271977
}
19281978

1929-
func (q *FakeQuerier) GetTemplateInsights(ctx context.Context, arg database.GetTemplateInsightsParams) (database.GetTemplateInsightsRow, error) {
1979+
func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTemplateInsightsParams) (database.GetTemplateInsightsRow, error) {
19301980
err := validateDatabaseType(arg)
19311981
if err != nil {
19321982
return database.GetTemplateInsightsRow{}, err
19331983
}
19341984

1935-
panic("not implemented")
1985+
templateIDSet := make(map[uuid.UUID]struct{})
1986+
appUsageIntervalsByUser := make(map[uuid.UUID]map[time.Time]*database.GetTemplateInsightsRow)
1987+
for _, s := range q.workspaceAgentStats {
1988+
if s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.Equal(arg.EndTime) || s.CreatedAt.After(arg.EndTime) {
1989+
continue
1990+
}
1991+
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, s.TemplateID) {
1992+
continue
1993+
}
1994+
if s.ConnectionCount == 0 {
1995+
continue
1996+
}
1997+
1998+
templateIDSet[s.TemplateID] = struct{}{}
1999+
if appUsageIntervalsByUser[s.UserID] == nil {
2000+
appUsageIntervalsByUser[s.UserID] = make(map[time.Time]*database.GetTemplateInsightsRow)
2001+
}
2002+
t := s.CreatedAt.Truncate(5 * time.Minute)
2003+
if _, ok := appUsageIntervalsByUser[s.UserID][t]; !ok {
2004+
appUsageIntervalsByUser[s.UserID][t] = &database.GetTemplateInsightsRow{}
2005+
}
2006+
2007+
if s.SessionCountJetBrains > 0 {
2008+
appUsageIntervalsByUser[s.UserID][t].UsageJetbrainsSeconds = 300
2009+
}
2010+
if s.SessionCountVSCode > 0 {
2011+
appUsageIntervalsByUser[s.UserID][t].UsageVscodeSeconds = 300
2012+
}
2013+
if s.SessionCountReconnectingPTY > 0 {
2014+
appUsageIntervalsByUser[s.UserID][t].UsageReconnectingPtySeconds = 300
2015+
}
2016+
if s.SessionCountSSH > 0 {
2017+
appUsageIntervalsByUser[s.UserID][t].UsageSshSeconds = 300
2018+
}
2019+
}
2020+
2021+
templateIDs := make([]uuid.UUID, 0, len(templateIDSet))
2022+
for templateID := range templateIDSet {
2023+
templateIDs = append(templateIDs, templateID)
2024+
}
2025+
slices.SortFunc(templateIDs, func(a, b uuid.UUID) bool {
2026+
return a.String() < b.String()
2027+
})
2028+
result := database.GetTemplateInsightsRow{
2029+
TemplateIDs: templateIDs,
2030+
ActiveUsers: int64(len(appUsageIntervalsByUser)),
2031+
}
2032+
for _, intervals := range appUsageIntervalsByUser {
2033+
for _, interval := range intervals {
2034+
result.UsageJetbrainsSeconds += interval.UsageJetbrainsSeconds
2035+
result.UsageVscodeSeconds += interval.UsageVscodeSeconds
2036+
result.UsageReconnectingPtySeconds += interval.UsageReconnectingPtySeconds
2037+
result.UsageSshSeconds += interval.UsageSshSeconds
2038+
}
2039+
}
2040+
return result, nil
19362041
}
19372042

19382043
func (q *FakeQuerier) GetTemplateVersionByID(ctx context.Context, templateVersionID uuid.UUID) (database.TemplateVersion, error) {
@@ -2182,7 +2287,7 @@ func (q *FakeQuerier) GetUserCount(_ context.Context) (int64, error) {
21822287
return existing, nil
21832288
}
21842289

2185-
func (q *FakeQuerier) GetUserLatencyInsights(ctx context.Context, arg database.GetUserLatencyInsightsParams) ([]database.GetUserLatencyInsightsRow, error) {
2290+
func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.GetUserLatencyInsightsParams) ([]database.GetUserLatencyInsightsRow, error) {
21862291
err := validateDatabaseType(arg)
21872292
if err != nil {
21882293
return nil, err
@@ -2222,9 +2327,9 @@ func (q *FakeQuerier) GetUserLatencyInsights(ctx context.Context, arg database.G
22222327
var rows []database.GetUserLatencyInsightsRow
22232328
for userID, latencies := range latenciesByUserID {
22242329
sort.Float64s(latencies)
2225-
templateSet := seenTemplatesByUserID[userID]
2226-
templateIDs := make([]uuid.UUID, 0, len(templateSet))
2227-
for templateID := range templateSet {
2330+
templateIDSet := seenTemplatesByUserID[userID]
2331+
templateIDs := make([]uuid.UUID, 0, len(templateIDSet))
2332+
for templateID := range templateIDSet {
22282333
templateIDs = append(templateIDs, templateID)
22292334
}
22302335
slices.SortFunc(templateIDs, func(a, b uuid.UUID) bool {

‎coderd/database/queries.sql.go

Lines changed: 26 additions & 14 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 22 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -38,21 +38,27 @@ WITH d AS (
3838
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN ts.seconds ELSE 0 END AS usage_reconnecting_pty_seconds,
3939
CASE WHEN SUM(was.session_count_ssh) > 0 THEN ts.seconds ELSE 0 END AS usage_ssh_seconds
4040
FROM ts
41-
JOIN workspace_agent_stats was ON (was.created_at >= ts.from_ AND was.created_at < ts.to_)
42-
WHERE
43-
was.connection_count > 0
41+
JOIN workspace_agent_stats was ON (
42+
was.created_at >= ts.from_
43+
AND was.created_at < ts.to_
44+
AND was.connection_count > 0
4445
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
46+
)
4547
GROUP BY ts.from_, ts.to_, ts.seconds, was.user_id
48+
), template_ids AS (
49+
SELECT array_agg(DISTINCT template_id) AS ids
50+
FROM usage_by_user, unnest(template_ids) template_id
51+
WHERE template_id IS NOT NULL
4652
)
4753

4854
SELECT
55+
COALESCE((SELECT ids FROM template_ids), '{}')::uuid[] AS template_ids,
4956
COUNT(DISTINCT user_id) AS active_users,
50-
COALESCE(array_agg(DISTINCT template_id), '{}')::uuid[] AS template_ids,
5157
COALESCE(SUM(usage_vscode_seconds), 0)::bigint AS usage_vscode_seconds,
5258
COALESCE(SUM(usage_jetbrains_seconds), 0)::bigint AS usage_jetbrains_seconds,
5359
COALESCE(SUM(usage_reconnecting_pty_seconds), 0)::bigint AS usage_reconnecting_pty_seconds,
5460
COALESCE(SUM(usage_ssh_seconds), 0)::bigint AS usage_ssh_seconds
55-
FROM usage_by_user, unnest(template_ids) as template_id;
61+
FROM usage_by_user;
5662

5763
-- name: GetTemplateDailyInsights :many
5864
WITH d AS (
@@ -69,17 +75,23 @@ WITH d AS (
6975
was.user_id,
7076
array_agg(was.template_id) AS template_ids
7177
FROM ts
72-
LEFT JOIN workspace_agent_stats was ON (was.created_at >= ts.from_ AND was.created_at < ts.to_)
73-
WHERE
74-
was.connection_count > 0
78+
LEFT JOIN workspace_agent_stats was ON (
79+
was.created_at >= ts.from_
80+
AND was.created_at < ts.to_
81+
AND was.connection_count > 0
7582
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
83+
)
7684
GROUP BY ts.from_, ts.to_, was.user_id
85+
), template_ids AS (
86+
SELECT array_agg(DISTINCT template_id) AS ids
87+
FROM usage_by_day, unnest(template_ids) template_id
88+
WHERE template_id IS NOT NULL
7789
)
7890

7991
SELECT
8092
from_ AS start_time,
8193
to_ AS end_time,
82-
COUNT(DISTINCT user_id) AS active_users,
83-
array_agg(DISTINCT template_id)::uuid[] AS template_ids
94+
COALESCE((SELECT ids FROM template_ids), '{}')::uuid[] AS template_ids,
95+
COUNT(DISTINCT user_id) AS active_users
8496
FROM usage_by_day, unnest(template_ids) as template_id
8597
GROUP BY from_, to_;

‎coderd/insights.go

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -129,7 +129,6 @@ func (api *API) insightsUserLatency(rw http.ResponseWriter, r *http.Request) {
129129
for _, user := range users {
130130
userLatency, ok := usersWithLatencyByID[user.ID]
131131
if !ok {
132-
// TODO(mafredri): Other cases?
133132
// We only include deleted/inactive users if they were
134133
// active as part of the requested timeframe.
135134
if user.Deleted || user.Status != database.UserStatusActive {
@@ -262,31 +261,31 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
262261
ActiveUsers: usage.ActiveUsers,
263262
AppsUsage: []codersdk.TemplateAppUsage{
264263
{
265-
TemplateIDs: usage.TemplateIDs, // TODO(mafredri): Update query to return template IDs/app?
264+
TemplateIDs: usage.TemplateIDs,
266265
Type: codersdk.TemplateAppsTypeBuiltin,
267266
DisplayName: "Visual Studio Code",
268267
Slug: "vscode",
269268
Icon: "/icons/code.svg",
270269
Seconds: usage.UsageVscodeSeconds,
271270
},
272271
{
273-
TemplateIDs: usage.TemplateIDs, // TODO(mafredri): Update query to return template IDs/app?
272+
TemplateIDs: usage.TemplateIDs,
274273
Type: codersdk.TemplateAppsTypeBuiltin,
275274
DisplayName: "JetBrains",
276275
Slug: "jetbrains",
277276
Icon: "/icons/intellij.svg",
278277
Seconds: usage.UsageJetbrainsSeconds,
279278
},
280279
{
281-
TemplateIDs: usage.TemplateIDs, // TODO(mafredri): Update query to return template IDs/app?
280+
TemplateIDs: usage.TemplateIDs,
282281
Type: codersdk.TemplateAppsTypeBuiltin,
283282
DisplayName: "Web Terminal",
284283
Slug: "reconnecting-pty",
285284
Icon: "/icons/terminal.svg",
286285
Seconds: usage.UsageReconnectingPtySeconds,
287286
},
288287
{
289-
TemplateIDs: usage.TemplateIDs, // TODO(mafredri): Update query to return template IDs/app?
288+
TemplateIDs: usage.TemplateIDs,
290289
Type: codersdk.TemplateAppsTypeBuiltin,
291290
DisplayName: "SSH",
292291
Slug: "ssh",

0 commit comments

Comments
 (0)