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

Commit25a5c0a

Browse files
committed
fix: implementGetTemplateAppInsights in dbmem
1 parent1c951d1 commit25a5c0a

File tree

3 files changed

+273
-90
lines changed

3 files changed

+273
-90
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 271 additions & 90 deletions
Original file line numberDiff line numberDiff line change
@@ -799,6 +799,92 @@ func least[T constraints.Ordered](a, b T) T {
799799
returnb
800800
}
801801

802+
func (q*FakeQuerier)getLatestWorkspaceAppByTemplateIDUserIDSlugNoLock(ctx context.Context,templateID,userID uuid.UUID,slugstring) (database.WorkspaceApp,error) {
803+
/*
804+
SELECT
805+
app.display_name,
806+
app.icon,
807+
app.slug
808+
FROM
809+
workspace_apps AS app
810+
JOIN
811+
workspace_agents AS agent
812+
ON
813+
agent.id = app.agent_id
814+
JOIN
815+
workspace_resources AS resource
816+
ON
817+
resource.id = agent.resource_id
818+
JOIN
819+
workspace_builds AS build
820+
ON
821+
build.job_id = resource.job_id
822+
JOIN
823+
workspaces AS workspace
824+
ON
825+
workspace.id = build.workspace_id
826+
WHERE
827+
-- Requires lateral join.
828+
app.slug = app_usage.key
829+
AND workspace.owner_id = tus.user_id
830+
AND workspace.template_id = tus.template_id
831+
ORDER BY
832+
app.created_at DESC
833+
LIMIT 1
834+
*/
835+
836+
varworkspaces []database.Workspace
837+
for_,w:=rangeq.workspaces {
838+
ifw.TemplateID!=templateID||w.OwnerID!=userID {
839+
continue
840+
}
841+
workspaces=append(workspaces,w)
842+
}
843+
slices.SortFunc(workspaces,func(a,b database.Workspace)int {
844+
ifa.CreatedAt.Before(b.CreatedAt) {
845+
return1
846+
}elseifa.CreatedAt.Equal(b.CreatedAt) {
847+
return0
848+
}
849+
return-1
850+
})
851+
852+
for_,workspace:=rangeworkspaces {
853+
854+
build,err:=q.getLatestWorkspaceBuildByWorkspaceIDNoLock(ctx,workspace.ID)
855+
iferr!=nil {
856+
continue
857+
}
858+
859+
resources,err:=q.getWorkspaceResourcesByJobIDNoLock(ctx,build.JobID)
860+
iferr!=nil {
861+
continue
862+
}
863+
varresourceIDs []uuid.UUID
864+
for_,resource:=rangeresources {
865+
resourceIDs=append(resourceIDs,resource.ID)
866+
}
867+
868+
agents,err:=q.getWorkspaceAgentsByResourceIDsNoLock(ctx,resourceIDs)
869+
iferr!=nil {
870+
continue
871+
}
872+
873+
for_,agent:=rangeagents {
874+
app,err:=q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, database.GetWorkspaceAppByAgentIDAndSlugParams{
875+
AgentID:agent.ID,
876+
Slug:slug,
877+
})
878+
iferr!=nil {
879+
continue
880+
}
881+
returnapp,nil
882+
}
883+
}
884+
885+
return database.WorkspaceApp{},sql.ErrNoRows
886+
}
887+
802888
func (*FakeQuerier)AcquireLock(_ context.Context,_int64)error {
803889
returnxerrors.New("AcquireLock must only be called within a transaction")
804890
}
@@ -2896,119 +2982,214 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
28962982
q.mutex.RLock()
28972983
deferq.mutex.RUnlock()
28982984

2899-
typeappKeystruct {
2900-
AccessMethodstring
2901-
SlugOrPortstring
2902-
Slugstring
2903-
DisplayNamestring
2904-
Iconstring
2985+
/*
2986+
WITH
2987+
*/
2988+
2989+
/*
2990+
app_insights AS (
2991+
SELECT
2992+
tus.user_id,
2993+
array_agg(DISTINCT tus.template_id)::uuid[] AS template_ids,
2994+
app_usage.key::text AS app_name,
2995+
COALESCE(wa.display_name, '') AS display_name,
2996+
COALESCE(wa.icon, '') AS icon,
2997+
(wa.slug IS NOT NULL)::boolean AS is_app,
2998+
LEAST(SUM(app_usage.value::int), 30) AS app_usage_mins
2999+
FROM
3000+
template_usage_stats AS tus, jsonb_each(app_usage_mins) AS app_usage
3001+
LEFT JOIN LATERAL (
3002+
-- Fetch the latest app info for each app based on slug and template.
3003+
SELECT
3004+
app.display_name,
3005+
app.icon,
3006+
app.slug
3007+
FROM
3008+
workspace_apps AS app
3009+
JOIN
3010+
workspace_agents AS agent
3011+
ON
3012+
agent.id = app.agent_id
3013+
JOIN
3014+
workspace_resources AS resource
3015+
ON
3016+
resource.id = agent.resource_id
3017+
JOIN
3018+
workspace_builds AS build
3019+
ON
3020+
build.job_id = resource.job_id
3021+
JOIN
3022+
workspaces AS workspace
3023+
ON
3024+
workspace.id = build.workspace_id
3025+
WHERE
3026+
-- Requires lateral join.
3027+
app.slug = app_usage.key
3028+
AND workspace.owner_id = tus.user_id
3029+
AND workspace.template_id = tus.template_id
3030+
ORDER BY
3031+
app.created_at DESC
3032+
LIMIT 1
3033+
) AS wa
3034+
ON
3035+
true
3036+
WHERE
3037+
tus.start_time >= @start_time::timestamptz
3038+
AND tus.end_time <= @end_time::timestamptz
3039+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
3040+
GROUP BY
3041+
tus.start_time, tus.user_id, app_usage.key::text, wa.display_name, wa.icon, wa.slug
3042+
),
3043+
*/
3044+
3045+
typeappInsightsGroupBystruct {
3046+
StartTime time.Time
3047+
UserID uuid.UUID
3048+
AppNamestring
3049+
DisplayNamestring
3050+
Iconstring
3051+
IsAppbool
29053052
}
2906-
typeuniqueKeystruct {
2907-
TemplateID uuid.UUID
2908-
UserID uuid.UUID
2909-
AgentID uuid.UUID
2910-
AppKeyappKey
3053+
typeappInsightsRowstruct {
3054+
appInsightsGroupBy
3055+
TemplateIDs []uuid.UUID
3056+
AppUsageMinsint64
29113057
}
2912-
2913-
appUsageIntervalsByUserAgentApp:=make(map[uniqueKey]map[time.Time]int64)
2914-
for_,s:=rangeq.workspaceAppStats {
2915-
// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
2916-
// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
2917-
// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
2918-
if!(((s.SessionStartedAt.After(arg.StartTime)||s.SessionStartedAt.Equal(arg.StartTime))&&s.SessionStartedAt.Before(arg.EndTime))||
2919-
(s.SessionEndedAt.After(arg.StartTime)&&s.SessionEndedAt.Before(arg.EndTime))||
2920-
(s.SessionStartedAt.Before(arg.StartTime)&& (s.SessionEndedAt.After(arg.EndTime)||s.SessionEndedAt.Equal(arg.EndTime)))) {
3058+
appInsightRows:=make(map[appInsightsGroupBy]appInsightsRow)
3059+
// FROM
3060+
for_,stat:=rangeq.templateUsageStats {
3061+
// WHERE
3062+
ifstat.StartTime.Before(arg.StartTime)||stat.StartTime.After(arg.EndTime) {
29213063
continue
29223064
}
2923-
2924-
w,err:=q.getWorkspaceByIDNoLock(ctx,s.WorkspaceID)
2925-
iferr!=nil {
2926-
returnnil,err
2927-
}
2928-
2929-
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,w.TemplateID) {
3065+
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,stat.TemplateID) {
29303066
continue
29313067
}
29323068

2933-
app,_:=q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, database.GetWorkspaceAppByAgentIDAndSlugParams{
2934-
AgentID:s.AgentID,
2935-
Slug:s.SlugOrPort,
2936-
})
3069+
// json_each
3070+
forappName,appUsage:=rangestat.AppUsageMins {
3071+
// LEFT JOIN LATERAL
3072+
app,_:=q.getLatestWorkspaceAppByTemplateIDUserIDSlugNoLock(ctx,stat.TemplateID,stat.UserID,appName)
29373073

2938-
key:=uniqueKey{
2939-
TemplateID:w.TemplateID,
2940-
UserID:s.UserID,
2941-
AgentID:s.AgentID,
2942-
AppKey:appKey{
2943-
AccessMethod:s.AccessMethod,
2944-
SlugOrPort:s.SlugOrPort,
2945-
Slug:app.Slug,
2946-
DisplayName:app.DisplayName,
2947-
Icon:app.Icon,
2948-
},
2949-
}
2950-
ifappUsageIntervalsByUserAgentApp[key]==nil {
2951-
appUsageIntervalsByUserAgentApp[key]=make(map[time.Time]int64)
3074+
// SELECT
3075+
key:=appInsightsGroupBy{
3076+
StartTime:stat.StartTime,
3077+
UserID:stat.UserID,
3078+
AppName:appName,
3079+
DisplayName:app.DisplayName,
3080+
Icon:app.Icon,
3081+
IsApp:app.Slug!="",
3082+
}
3083+
row,ok:=appInsightRows[key]
3084+
if!ok {
3085+
row=appInsightsRow{
3086+
appInsightsGroupBy:key,
3087+
}
3088+
}
3089+
row.TemplateIDs=append(row.TemplateIDs,stat.TemplateID)
3090+
row.AppUsageMins=least(row.AppUsageMins+appUsage,30)
3091+
appInsightRows[key]=row
29523092
}
3093+
}
29533094

2954-
t:=s.SessionStartedAt.Truncate(5*time.Minute)
2955-
ift.Before(arg.StartTime) {
2956-
t=arg.StartTime
2957-
}
2958-
fort.Before(s.SessionEndedAt)&&t.Before(arg.EndTime) {
2959-
appUsageIntervalsByUserAgentApp[key][t]=60// 1 minute.
2960-
t=t.Add(1*time.Minute)
2961-
}
3095+
/*
3096+
templates AS (
3097+
SELECT
3098+
app_name,
3099+
display_name,
3100+
icon,
3101+
is_app,
3102+
array_agg(DISTINCT template_id)::uuid[] AS template_ids
3103+
FROM
3104+
app_insights, unnest(template_ids) AS template_id
3105+
GROUP BY
3106+
app_name, display_name, icon, is_app
3107+
)
3108+
*/
3109+
3110+
typeappGroupBystruct {
3111+
AppNamestring
3112+
DisplayNamestring
3113+
Iconstring
3114+
IsAppbool
3115+
}
3116+
typetemplateRowstruct {
3117+
appGroupBy
3118+
TemplateIDs []uuid.UUID
29623119
}
29633120

2964-
appUsageTemplateIDs:=make(map[appKey]map[uuid.UUID]struct{})
2965-
appUsageUserIDs:=make(map[appKey]map[uuid.UUID]struct{})
2966-
appUsage:=make(map[appKey]int64)
2967-
foruniqueKey,usage:=rangeappUsageIntervalsByUserAgentApp {
2968-
for_,seconds:=rangeusage {
2969-
ifappUsageTemplateIDs[uniqueKey.AppKey]==nil {
2970-
appUsageTemplateIDs[uniqueKey.AppKey]=make(map[uuid.UUID]struct{})
2971-
}
2972-
appUsageTemplateIDs[uniqueKey.AppKey][uniqueKey.TemplateID]=struct{}{}
2973-
ifappUsageUserIDs[uniqueKey.AppKey]==nil {
2974-
appUsageUserIDs[uniqueKey.AppKey]=make(map[uuid.UUID]struct{})
3121+
templateRows:=make(map[appGroupBy]templateRow)
3122+
for_,row:=rangeappInsightRows {
3123+
key:=appGroupBy{
3124+
AppName:row.AppName,
3125+
DisplayName:row.DisplayName,
3126+
Icon:row.Icon,
3127+
IsApp:row.IsApp,
3128+
}
3129+
row,ok:=templateRows[key]
3130+
if!ok {
3131+
row=templateRow{
3132+
appGroupBy:key,
29753133
}
2976-
appUsageUserIDs[uniqueKey.AppKey][uniqueKey.UserID]=struct{}{}
2977-
appUsage[uniqueKey.AppKey]+=seconds
29783134
}
3135+
row.TemplateIDs=append(row.TemplateIDs,row.TemplateIDs...)
3136+
templateRows[key]=row
29793137
}
29803138

2981-
varrows []database.GetTemplateAppInsightsRow
2982-
forappKey,usage:=rangeappUsage {
2983-
templateIDs:=make([]uuid.UUID,0,len(appUsageTemplateIDs[appKey]))
2984-
fortemplateID:=rangeappUsageTemplateIDs[appKey] {
2985-
templateIDs=append(templateIDs,templateID)
2986-
}
2987-
slices.SortFunc(templateIDs,func(a,b uuid.UUID)int {
2988-
returnslice.Ascending(a.String(),b.String())
2989-
})
2990-
activeUserIDs:=make([]uuid.UUID,0,len(appUsageUserIDs[appKey]))
2991-
foruserID:=rangeappUsageUserIDs[appKey] {
2992-
activeUserIDs=append(activeUserIDs,userID)
3139+
/*
3140+
SELECT
3141+
t.template_ids,
3142+
array_agg(DISTINCT ai.user_id)::uuid[] AS active_user_ids,
3143+
ai.app_name AS slug_or_port,
3144+
ai.display_name,
3145+
ai.icon,
3146+
ai.is_app,
3147+
(SUM(ai.app_usage_mins) * 60)::bigint AS usage_seconds
3148+
FROM
3149+
app_insights AS ai
3150+
JOIN
3151+
templates AS t
3152+
ON
3153+
ai.app_name = t.app_name
3154+
AND ai.display_name = t.display_name
3155+
AND ai.icon = t.icon
3156+
AND ai.is_app = t.is_app
3157+
GROUP BY
3158+
t.template_ids, ai.app_name, ai.display_name, ai.icon, ai.is_app;
3159+
*/
3160+
3161+
typetemplateAppInsightsRowstruct {
3162+
TemplateIDs []uuid.UUID
3163+
ActiveUserIDs []uuid.UUID
3164+
UsageSecondsint64
3165+
}
3166+
groupedRows:=make(map[appGroupBy]templateAppInsightsRow)
3167+
for_,aiRow:=rangeappInsightRows {
3168+
key:=appGroupBy{
3169+
AppName:aiRow.AppName,
3170+
DisplayName:aiRow.DisplayName,
3171+
Icon:aiRow.Icon,
3172+
IsApp:aiRow.IsApp,
29933173
}
2994-
slices.SortFunc(activeUserIDs,func(a,b uuid.UUID)int {
2995-
returnslice.Ascending(a.String(),b.String())
2996-
})
3174+
row:=groupedRows[key]
3175+
row.TemplateIDs=append(row.TemplateIDs,aiRow.TemplateIDs...)
3176+
row.ActiveUserIDs=append(row.ActiveUserIDs,aiRow.UserID)
3177+
row.UsageSeconds+=aiRow.AppUsageMins*60
3178+
}
29973179

3180+
varrows []database.GetTemplateAppInsightsRow
3181+
fork,gr:=rangegroupedRows {
29983182
rows=append(rows, database.GetTemplateAppInsightsRow{
2999-
TemplateIDs:templateIDs,
3000-
ActiveUserIDs:activeUserIDs,
3001-
AccessMethod:appKey.AccessMethod,
3002-
SlugOrPort:appKey.SlugOrPort,
3003-
DisplayName:appKey.DisplayName,
3004-
Icon:appKey.Icon,
3005-
IsApp:appKey.Slug!="",
3006-
UsageSeconds:usage,
3183+
TemplateIDs:uniqueSortedUUIDs(gr.TemplateIDs),
3184+
ActiveUsers:int64(len(uniqueSortedUUIDs(gr.ActiveUserIDs))),
3185+
DisplayName:k.DisplayName,
3186+
SlugOrPort:k.AppName,
3187+
UsageSeconds:gr.UsageSeconds,
30073188
})
30083189
}
30093190

30103191
// NOTE(mafredri): Add sorting if we decide on how to handle PostgreSQL collations.
3011-
// ORDER BYaccess_method,slug_or_port, display_name, icon, is_app
3192+
// ORDER BY slug_or_port, display_name, icon, is_app
30123193
returnrows,nil
30133194
}
30143195

0 commit comments

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