Skip to content

Commit cacebb9

Browse files
mafredrimafredri
committed
fix query joins, add basic test for app usage
1 parent 98a835c commit cacebb9

File tree

3 files changed

+132
-22
lines changed

3 files changed

+132
-22
lines changed

‎coderd/database/queries.sql.go

+9-9
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

+9-9
Original file line numberDiff line numberDiff line change
@@ -99,9 +99,9 @@ WITH ts AS (
9999
(wa.slug IS NOT NULL)::boolean AS is_app
100100
FROM ts
101101
JOIN workspace_app_stats was ON (
102-
(was.session_started_at BETWEEN ts.from_ AND ts.to_)
103-
OR (was.session_ended_at BETWEEN ts.from_ AND ts.to_)
104-
OR (was.session_started_at < ts.from_ AND was.session_ended_at > ts.to_)
102+
(was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
103+
OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
104+
OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
105105
)
106106
JOIN workspaces w ON (
107107
w.id = was.workspace_id
@@ -116,9 +116,9 @@ WITH ts AS (
116116
WHERE
117117
-- We already handle timeframe in the join, but we use an additional
118118
-- check against a static timeframe to help speed up the query.
119-
(was.session_started_at BETWEEN @start_time AND @end_time)
120-
OR (was.session_ended_at BETWEEN @start_time AND @end_time)
121-
OR (was.session_started_at < @start_time AND was.session_ended_at > @end_time)
119+
(was.session_started_at >= @start_time AND was.session_started_at < @end_time)
120+
OR (was.session_ended_at > @start_time AND was.session_ended_at < @end_time)
121+
OR (was.session_started_at < @start_time AND was.session_ended_at >= @end_time)
122122
GROUP BY ts.from_, ts.to_, ts.seconds, w.template_id, was.user_id, was.agent_id, was.access_method, was.slug_or_port, wa.display_name, wa.icon, wa.slug
123123
)
124124

@@ -180,9 +180,9 @@ WITH ts AS (
180180
was.user_id
181181
FROM ts
182182
JOIN workspace_app_stats was ON (
183-
(was.session_started_at BETWEEN ts.from_ AND ts.to_)
184-
OR (was.session_ended_at BETWEEN ts.from_ AND ts.to_)
185-
OR (was.session_started_at < ts.from_ AND was.session_ended_at > ts.to_)
183+
(was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
184+
OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
185+
OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
186186
)
187187
JOIN workspaces w ON (
188188
w.id = was.workspace_id

‎coderd/insights_test.go

+114-4
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,9 @@ import (
1717
"cdr.dev/slog/sloggers/slogtest"
1818
"github.com/coder/coder/agent"
1919
"github.com/coder/coder/coderd/coderdtest"
20+
"github.com/coder/coder/coderd/database/dbauthz"
2021
"github.com/coder/coder/coderd/rbac"
22+
"github.com/coder/coder/coderd/workspaceapps"
2123
"github.com/coder/coder/codersdk"
2224
"github.com/coder/coder/codersdk/agentsdk"
2325
"github.com/coder/coder/provisioner/echo"
@@ -257,14 +259,19 @@ func TestTemplateInsights(t *testing.T) {
257259
thirdParameterOptionValue2 = "bbb"
258260
thirdParameterOptionName3 = "This is CCC"
259261
thirdParameterOptionValue3 = "ccc"
262+
263+
testAppSlug = "test-app"
264+
testAppName = "Test App"
265+
testAppIcon = "/icon.png"
266+
testAppURL = "http://127.1.0.1:65536" // Not used.
260267
)
261268

262269
logger := slogtest.Make(t, nil).Leveled(slog.LevelDebug)
263270
opts := &coderdtest.Options{
264271
IncludeProvisionerDaemon: true,
265272
AgentStatsRefreshInterval: time.Millisecond * 100,
266273
}
267-
client := coderdtest.New(t, opts)
274+
client, _, coderdAPI := coderdtest.NewWithAPI(t, opts)
268275

269276
user := coderdtest.CreateFirstUser(t, client)
270277
authToken := uuid.NewString()
@@ -287,7 +294,32 @@ func TestTemplateInsights(t *testing.T) {
287294
},
288295
},
289296
},
290-
ProvisionApply: echo.ProvisionApplyWithAgent(authToken),
297+
ProvisionApply: []*proto.Provision_Response{{
298+
Type: &proto.Provision_Response_Complete{
299+
Complete: &proto.Provision_Complete{
300+
Resources: []*proto.Resource{{
301+
Name: "example",
302+
Type: "aws_instance",
303+
Agents: []*proto.Agent{{
304+
Id: uuid.NewString(),
305+
Name: "dev",
306+
Auth: &proto.Agent_Token{
307+
Token: authToken,
308+
},
309+
Apps: []*proto.App{
310+
{
311+
Slug: testAppSlug,
312+
DisplayName: testAppName,
313+
Icon: testAppIcon,
314+
SharingLevel: proto.AppSharingLevel_OWNER,
315+
Url: testAppURL,
316+
},
317+
},
318+
}},
319+
}},
320+
},
321+
},
322+
}},
291323
})
292324
template := coderdtest.CreateTemplate(t, client, user.OrganizationID, version.ID)
293325
require.Empty(t, template.BuildTimeStats[codersdk.WorkspaceTransitionStart])
@@ -320,10 +352,70 @@ func TestTemplateInsights(t *testing.T) {
320352
// the day changes so that we get the relevant stats faster.
321353
y, m, d := time.Now().UTC().Date()
322354
today := time.Date(y, m, d, 0, 0, 0, 0, time.UTC)
355+
requestStartTime := today
356+
requestEndTime := time.Now().UTC().Truncate(time.Hour).Add(time.Hour)
323357

324358
ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitLong)
325359
defer cancel()
326360

361+
// TODO(mafredri): We should prefer to set up an app and generate
362+
// data by accessing it.
363+
// Insert entries within and outside timeframe.
364+
reporter := workspaceapps.NewStatsDBReporter(coderdAPI.Database, workspaceapps.DefaultStatsDBReporterBatchSize)
365+
err := reporter.Report(dbauthz.AsSystemRestricted(ctx), []workspaceapps.StatsReport{
366+
{
367+
UserID: user.UserID,
368+
WorkspaceID: workspace.ID,
369+
AgentID: resources[0].Agents[0].ID,
370+
AccessMethod: workspaceapps.AccessMethodPath,
371+
SlugOrPort: testAppSlug,
372+
SessionID: uuid.New(),
373+
// Outside report range.
374+
SessionStartedAt: requestStartTime.Add(-1 * time.Minute),
375+
SessionEndedAt: requestStartTime,
376+
Requests: 1,
377+
},
378+
{
379+
UserID: user.UserID,
380+
WorkspaceID: workspace.ID,
381+
AgentID: resources[0].Agents[0].ID,
382+
AccessMethod: workspaceapps.AccessMethodPath,
383+
SlugOrPort: testAppSlug,
384+
SessionID: uuid.New(),
385+
// One minute of usage (rounded up to 5 due to query intervals).
386+
// TODO(mafredri): We'll fix this in a future refactor so that it's
387+
// 1 minute increments instead of 5.
388+
SessionStartedAt: requestStartTime,
389+
SessionEndedAt: requestStartTime.Add(1 * time.Minute),
390+
Requests: 1,
391+
},
392+
{
393+
UserID: user.UserID,
394+
WorkspaceID: workspace.ID,
395+
AgentID: resources[0].Agents[0].ID,
396+
AccessMethod: workspaceapps.AccessMethodPath,
397+
SlugOrPort: testAppSlug,
398+
SessionID: uuid.New(),
399+
// Five additional minutes of usage.
400+
SessionStartedAt: requestStartTime.Add(10 * time.Minute),
401+
SessionEndedAt: requestStartTime.Add(15 * time.Minute),
402+
Requests: 1,
403+
},
404+
{
405+
UserID: user.UserID,
406+
WorkspaceID: workspace.ID,
407+
AgentID: resources[0].Agents[0].ID,
408+
AccessMethod: workspaceapps.AccessMethodPath,
409+
SlugOrPort: testAppSlug,
410+
SessionID: uuid.New(),
411+
// Outside report range.
412+
SessionStartedAt: requestEndTime,
413+
SessionEndedAt: requestEndTime.Add(1 * time.Minute),
414+
Requests: 1,
415+
},
416+
})
417+
require.NoError(t, err, "want no error inserting stats")
418+
327419
// Connect to the agent to generate usage/latency stats.
328420
conn, err := client.DialWorkspaceAgent(ctx, resources[0].Agents[0].ID, &codersdk.DialWorkspaceAgentOptions{
329421
Logger: logger.Named("client"),
@@ -362,8 +454,8 @@ func TestTemplateInsights(t *testing.T) {
362454
waitForAppSeconds := func(slug string) func() bool {
363455
return func() bool {
364456
req = codersdk.TemplateInsightsRequest{
365-
StartTime: today,
366-
EndTime: time.Now().UTC().Truncate(time.Hour).Add(time.Hour),
457+
StartTime: requestStartTime,
458+
EndTime: requestEndTime,
367459
Interval: codersdk.InsightsReportIntervalDay,
368460
}
369461
resp, err = client.TemplateInsights(ctx, req)
@@ -390,13 +482,31 @@ func TestTemplateInsights(t *testing.T) {
390482
assert.WithinDuration(t, req.StartTime, resp.Report.StartTime, 0)
391483
assert.WithinDuration(t, req.EndTime, resp.Report.EndTime, 0)
392484
assert.Equal(t, resp.Report.ActiveUsers, int64(1), "want one active user")
485+
var gotApps []codersdk.TemplateAppUsage
486+
// Check builtin apps usage.
393487
for _, app := range resp.Report.AppsUsage {
488+
if app.Type != codersdk.TemplateAppsTypeBuiltin {
489+
gotApps = append(gotApps, app)
490+
continue
491+
}
394492
if slices.Contains([]string{"reconnecting-pty", "ssh"}, app.Slug) {
395493
assert.Equal(t, app.Seconds, int64(300), "want app %q to have 5 minutes of usage", app.Slug)
396494
} else {
397495
assert.Equal(t, app.Seconds, int64(0), "want app %q to have 0 minutes of usage", app.Slug)
398496
}
399497
}
498+
// Check app usage.
499+
assert.Equal(t, gotApps, []codersdk.TemplateAppUsage{
500+
{
501+
TemplateIDs: []uuid.UUID{template.ID},
502+
Type: codersdk.TemplateAppsTypeApp,
503+
Slug: testAppSlug,
504+
DisplayName: testAppName,
505+
Icon: testAppIcon,
506+
Seconds: 300 + 300, // Two times 5 minutes of usage (actually 1 + 5, but see TODO above).
507+
},
508+
}, "want app usage to match")
509+
400510
// The full timeframe is <= 24h, so the interval matches exactly.
401511
require.Len(t, resp.IntervalReports, 1, "want one interval report")
402512
assert.WithinDuration(t, req.StartTime, resp.IntervalReports[0].StartTime, 0)

0 commit comments

Comments
 (0)