Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2332d81

Browse files
authored
feat(coderd/database): usetemplate_usage_stats inGetUserActivityInsights query (#12672)
This PR updates the `GetUserActivityInsights` query to use rolled up `template_usage_stats` instead of raw agent and app stats.
1 parenta8ed689 commit2332d81

12 files changed

+246
-264
lines changed

‎coderd/database/dbauthz/dbauthz_test.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -939,7 +939,7 @@ func (s *MethodTestSuite) TestTemplate() {
939939
check.Args(database.GetUserLatencyInsightsParams{}).Asserts(rbac.ResourceTemplateInsights,rbac.ActionRead)
940940
}))
941941
s.Run("GetUserActivityInsights",s.Subtest(func(db database.Store,check*expects) {
942-
check.Args(database.GetUserActivityInsightsParams{}).Asserts(rbac.ResourceTemplateInsights,rbac.ActionRead)
942+
check.Args(database.GetUserActivityInsightsParams{}).Asserts(rbac.ResourceTemplateInsights,rbac.ActionRead).Errors(sql.ErrNoRows)
943943
}))
944944
s.Run("GetTemplateParameterInsights",s.Subtest(func(db database.Store,check*expects) {
945945
check.Args(database.GetTemplateParameterInsightsParams{}).Asserts(rbac.ResourceTemplateInsights,rbac.ActionRead)

‎coderd/database/dbmem/dbmem.go

Lines changed: 116 additions & 106 deletions
Original file line numberDiff line numberDiff line change
@@ -4119,7 +4119,7 @@ func (q *FakeQuerier) GetUnexpiredLicenses(_ context.Context) ([]database.Licens
41194119
returnresults,nil
41204120
}
41214121

4122-
func (q*FakeQuerier)GetUserActivityInsights(ctx context.Context,arg database.GetUserActivityInsightsParams) ([]database.GetUserActivityInsightsRow,error) {
4122+
func (q*FakeQuerier)GetUserActivityInsights(_ context.Context,arg database.GetUserActivityInsightsParams) ([]database.GetUserActivityInsightsRow,error) {
41234123
err:=validateDatabaseType(arg)
41244124
iferr!=nil {
41254125
returnnil,err
@@ -4128,130 +4128,140 @@ func (q *FakeQuerier) GetUserActivityInsights(ctx context.Context, arg database.
41284128
q.mutex.RLock()
41294129
deferq.mutex.RUnlock()
41304130

4131-
typeuniqueKeystruct {
4132-
TemplateID uuid.UUID
4133-
UserID uuid.UUID
4134-
}
4135-
4136-
combinedStats:=make(map[uniqueKey]map[time.Time]int64)
4137-
4138-
// Get application stats
4139-
for_,s:=rangeq.workspaceAppStats {
4140-
if!(((s.SessionStartedAt.After(arg.StartTime)||s.SessionStartedAt.Equal(arg.StartTime))&&s.SessionStartedAt.Before(arg.EndTime))||
4141-
(s.SessionEndedAt.After(arg.StartTime)&&s.SessionEndedAt.Before(arg.EndTime))||
4142-
(s.SessionStartedAt.Before(arg.StartTime)&& (s.SessionEndedAt.After(arg.EndTime)||s.SessionEndedAt.Equal(arg.EndTime)))) {
4143-
continue
4144-
}
4145-
4146-
w,err:=q.getWorkspaceByIDNoLock(ctx,s.WorkspaceID)
4147-
iferr!=nil {
4148-
returnnil,err
4149-
}
4150-
4151-
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,w.TemplateID) {
4152-
continue
4153-
}
4154-
4155-
key:=uniqueKey{
4156-
TemplateID:w.TemplateID,
4157-
UserID:s.UserID,
4158-
}
4159-
ifcombinedStats[key]==nil {
4160-
combinedStats[key]=make(map[time.Time]int64)
4161-
}
4131+
/*
4132+
WITH
4133+
*/
4134+
/*
4135+
deployment_stats AS (
4136+
SELECT
4137+
start_time,
4138+
user_id,
4139+
array_agg(template_id) AS template_ids,
4140+
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
4141+
LEAST(SUM(usage_mins), 30) AS usage_mins
4142+
FROM
4143+
template_usage_stats
4144+
WHERE
4145+
start_time >= @start_time::timestamptz
4146+
AND end_time <= @end_time::timestamptz
4147+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
4148+
GROUP BY
4149+
start_time, user_id
4150+
),
4151+
*/
41624152

4163-
t:=s.SessionStartedAt.Truncate(time.Minute)
4164-
ift.Before(arg.StartTime) {
4165-
t=arg.StartTime
4166-
}
4167-
fort.Before(s.SessionEndedAt)&&t.Before(arg.EndTime) {
4168-
combinedStats[key][t]=60
4169-
t=t.Add(1*time.Minute)
4170-
}
4153+
typedeploymentStatsGroupBystruct {
4154+
StartTime time.Time
4155+
UserID uuid.UUID
41714156
}
4172-
4173-
// Get session stats
4174-
for_,s:=rangeq.workspaceAgentStats {
4175-
ifs.CreatedAt.Before(arg.StartTime)||s.CreatedAt.Equal(arg.EndTime)||s.CreatedAt.After(arg.EndTime) {
4176-
continue
4177-
}
4178-
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,s.TemplateID) {
4157+
typedeploymentStatsRowstruct {
4158+
deploymentStatsGroupBy
4159+
TemplateIDs []uuid.UUID
4160+
UsageMinsint16
4161+
}
4162+
deploymentStatsRows:=make(map[deploymentStatsGroupBy]deploymentStatsRow)
4163+
for_,stat:=rangeq.templateUsageStats {
4164+
ifstat.StartTime.Before(arg.StartTime)||stat.EndTime.After(arg.EndTime) {
41794165
continue
41804166
}
4181-
ifs.ConnectionCount==0 {
4167+
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,stat.TemplateID) {
41824168
continue
41834169
}
4184-
4185-
key:=uniqueKey{
4186-
TemplateID:s.TemplateID,
4187-
UserID:s.UserID,
4188-
}
4189-
4190-
ifcombinedStats[key]==nil {
4191-
combinedStats[key]=make(map[time.Time]int64)
4170+
key:=deploymentStatsGroupBy{
4171+
StartTime:stat.StartTime,
4172+
UserID:stat.UserID,
41924173
}
4193-
4194-
ifs.SessionCountJetBrains>0||s.SessionCountVSCode>0||s.SessionCountReconnectingPTY>0||s.SessionCountSSH>0 {
4195-
t:=s.CreatedAt.Truncate(time.Minute)
4196-
combinedStats[key][t]=60
4174+
row,ok:=deploymentStatsRows[key]
4175+
if!ok {
4176+
row=deploymentStatsRow{
4177+
deploymentStatsGroupBy:key,
4178+
}
41974179
}
4180+
row.TemplateIDs=append(row.TemplateIDs,stat.TemplateID)
4181+
row.UsageMins=least(row.UsageMins+stat.UsageMins,30)
4182+
deploymentStatsRows[key]=row
41984183
}
41994184

4200-
// Use temporary maps for aggregation purposes
4201-
mUserIDTemplateIDs:=map[uuid.UUID]map[uuid.UUID]struct{}{}
4202-
mUserIDUsageSeconds:=map[uuid.UUID]int64{}
4203-
4204-
forkey,times:=rangecombinedStats {
4205-
ifmUserIDTemplateIDs[key.UserID]==nil {
4206-
mUserIDTemplateIDs[key.UserID]=make(map[uuid.UUID]struct{})
4207-
mUserIDUsageSeconds[key.UserID]=0
4208-
}
4209-
4210-
if_,ok:=mUserIDTemplateIDs[key.UserID][key.TemplateID];!ok {
4211-
mUserIDTemplateIDs[key.UserID][key.TemplateID]=struct{}{}
4212-
}
4185+
/*
4186+
template_ids AS (
4187+
SELECT
4188+
user_id,
4189+
array_agg(DISTINCT template_id) AS ids
4190+
FROM
4191+
deployment_stats, unnest(template_ids) template_id
4192+
GROUP BY
4193+
user_id
4194+
)
4195+
*/
42134196

4214-
for_,t:=rangetimes {
4215-
mUserIDUsageSeconds[key.UserID]+=t
4197+
typetemplateIDsRowstruct {
4198+
UserID uuid.UUID
4199+
TemplateIDs []uuid.UUID
4200+
}
4201+
templateIDs:=make(map[uuid.UUID]templateIDsRow)
4202+
for_,dsRow:=rangedeploymentStatsRows {
4203+
row,ok:=templateIDs[dsRow.UserID]
4204+
if!ok {
4205+
row=templateIDsRow{
4206+
UserID:row.UserID,
4207+
}
42164208
}
4209+
row.TemplateIDs=uniqueSortedUUIDs(append(row.TemplateIDs,dsRow.TemplateIDs...))
4210+
templateIDs[dsRow.UserID]=row
42174211
}
42184212

4219-
userIDs:=make([]uuid.UUID,0,len(mUserIDUsageSeconds))
4220-
foruserID:=rangemUserIDUsageSeconds {
4221-
userIDs=append(userIDs,userID)
4222-
}
4223-
sort.Slice(userIDs,func(i,jint)bool {
4224-
returnuserIDs[i].String()<userIDs[j].String()
4225-
})
4213+
/*
4214+
SELECT
4215+
ds.user_id,
4216+
u.username,
4217+
u.avatar_url,
4218+
t.ids::uuid[] AS template_ids,
4219+
(SUM(ds.usage_mins) * 60)::bigint AS usage_seconds
4220+
FROM
4221+
deployment_stats ds
4222+
JOIN
4223+
users u
4224+
ON
4225+
u.id = ds.user_id
4226+
JOIN
4227+
template_ids t
4228+
ON
4229+
ds.user_id = t.user_id
4230+
GROUP BY
4231+
ds.user_id, u.username, u.avatar_url, t.ids
4232+
ORDER BY
4233+
ds.user_id ASC;
4234+
*/
42264235

4227-
// Finally, select stats
42284236
varrows []database.GetUserActivityInsightsRow
4229-
4230-
for_,userID:=rangeuserIDs {
4231-
user,err:=q.getUserByIDNoLock(userID)
4232-
iferr!=nil {
4233-
returnnil,err
4234-
}
4235-
4236-
tids:=mUserIDTemplateIDs[userID]
4237-
templateIDs:=make([]uuid.UUID,0,len(tids))
4238-
forkey:=rangetids {
4239-
templateIDs=append(templateIDs,key)
4240-
}
4241-
sort.Slice(templateIDs,func(i,jint)bool {
4242-
returntemplateIDs[i].String()<templateIDs[j].String()
4243-
})
4244-
4245-
row:= database.GetUserActivityInsightsRow{
4246-
UserID:user.ID,
4247-
Username:user.Username,
4248-
AvatarURL:user.AvatarURL,
4249-
TemplateIDs:templateIDs,
4250-
UsageSeconds:mUserIDUsageSeconds[userID],
4237+
groupedRows:=make(map[uuid.UUID]database.GetUserActivityInsightsRow)
4238+
for_,dsRow:=rangedeploymentStatsRows {
4239+
row,ok:=groupedRows[dsRow.UserID]
4240+
if!ok {
4241+
user,err:=q.getUserByIDNoLock(dsRow.UserID)
4242+
iferr!=nil {
4243+
returnnil,err
4244+
}
4245+
row= database.GetUserActivityInsightsRow{
4246+
UserID:user.ID,
4247+
Username:user.Username,
4248+
AvatarURL:user.AvatarURL,
4249+
TemplateIDs:templateIDs[user.ID].TemplateIDs,
4250+
}
42514251
}
4252-
4252+
row.UsageSeconds+=int64(dsRow.UsageMins)*60
4253+
groupedRows[dsRow.UserID]=row
4254+
}
4255+
for_,row:=rangegroupedRows {
42534256
rows=append(rows,row)
42544257
}
4258+
iflen(rows)==0 {
4259+
returnnil,sql.ErrNoRows
4260+
}
4261+
slices.SortFunc(rows,func(a,b database.GetUserActivityInsightsRow)int {
4262+
returnslice.Ascending(a.UserID.String(),b.UserID.String())
4263+
})
4264+
42554265
returnrows,nil
42564266
}
42574267

‎coderd/database/querier.go

Lines changed: 5 additions & 5 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp