Skip to content

Commit f56db1b

Browse files
EmyrkEmyrk
authored
feat: add user search query param on last_seen (coder#8139)
* feat: add sql filter for before/after on last_seen column
1 parent 97945ae commit f56db1b

File tree

10 files changed

+200
-33
lines changed

10 files changed

+200
-33
lines changed

‎coderd/database/dbfake/dbfake.go

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2688,6 +2688,26 @@ func (q *fakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams
26882688
users = usersFilteredByRole
26892689
}
26902690

2691+
if !params.LastSeenBefore.IsZero() {
2692+
usersFilteredByLastSeen := make([]database.User, 0, len(users))
2693+
for i, user := range users {
2694+
if user.LastSeenAt.Before(params.LastSeenBefore) {
2695+
usersFilteredByLastSeen = append(usersFilteredByLastSeen, users[i])
2696+
}
2697+
}
2698+
users = usersFilteredByLastSeen
2699+
}
2700+
2701+
if !params.LastSeenAfter.IsZero() {
2702+
usersFilteredByLastSeen := make([]database.User, 0, len(users))
2703+
for i, user := range users {
2704+
if user.LastSeenAt.After(params.LastSeenAfter) {
2705+
usersFilteredByLastSeen = append(usersFilteredByLastSeen, users[i])
2706+
}
2707+
}
2708+
users = usersFilteredByLastSeen
2709+
}
2710+
26912711
beforePageCount := len(users)
26922712

26932713
if params.OffsetOpt > 0 {

‎coderd/database/dbgen/dbgen.go

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -206,6 +206,15 @@ func User(t testing.TB, db database.Store, orig database.User) database.User {
206206
LoginType: takeFirst(orig.LoginType, database.LoginTypePassword),
207207
})
208208
require.NoError(t, err, "insert user")
209+
210+
if !orig.LastSeenAt.IsZero() {
211+
user, err = db.UpdateUserLastSeenAt(genCtx, database.UpdateUserLastSeenAtParams{
212+
ID: user.ID,
213+
LastSeenAt: orig.LastSeenAt,
214+
UpdatedAt: user.UpdatedAt,
215+
})
216+
require.NoError(t, err, "user last seen")
217+
}
209218
return user
210219
}
211220

‎coderd/database/querier_test.go

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -387,3 +387,61 @@ func TestQueuePosition(t *testing.T) {
387387
require.Equal(t, job.ProvisionerJob.ID, jobs[index].ID)
388388
}
389389
}
390+
391+
func TestUserLastSeenFilter(t *testing.T) {
392+
t.Parallel()
393+
if testing.Short() {
394+
t.SkipNow()
395+
}
396+
t.Run("Before", func(t *testing.T) {
397+
t.Parallel()
398+
sqlDB := testSQLDB(t)
399+
err := migrations.Up(sqlDB)
400+
require.NoError(t, err)
401+
db := database.New(sqlDB)
402+
ctx := context.Background()
403+
now := time.Now()
404+
405+
yesterday := dbgen.User(t, db, database.User{
406+
LastSeenAt: now.Add(time.Hour * -25),
407+
})
408+
today := dbgen.User(t, db, database.User{
409+
LastSeenAt: now,
410+
})
411+
lastWeek := dbgen.User(t, db, database.User{
412+
LastSeenAt: now.Add((time.Hour * -24 * 7) + (-1 * time.Hour)),
413+
})
414+
415+
beforeToday, err := db.GetUsers(ctx, database.GetUsersParams{
416+
LastSeenBefore: now.Add(time.Hour * -24),
417+
})
418+
require.NoError(t, err)
419+
database.ConvertUserRows(beforeToday)
420+
421+
requireUsersMatch(t, []database.User{yesterday, lastWeek}, beforeToday, "before today")
422+
423+
justYesterday, err := db.GetUsers(ctx, database.GetUsersParams{
424+
LastSeenBefore: now.Add(time.Hour * -24),
425+
LastSeenAfter: now.Add(time.Hour * -24 * 2),
426+
})
427+
require.NoError(t, err)
428+
requireUsersMatch(t, []database.User{yesterday}, justYesterday, "just yesterday")
429+
430+
all, err := db.GetUsers(ctx, database.GetUsersParams{
431+
LastSeenBefore: now.Add(time.Hour),
432+
})
433+
require.NoError(t, err)
434+
requireUsersMatch(t, []database.User{today, yesterday, lastWeek}, all, "all")
435+
436+
allAfterLastWeek, err := db.GetUsers(ctx, database.GetUsersParams{
437+
LastSeenAfter: now.Add(time.Hour * -24 * 7),
438+
})
439+
require.NoError(t, err)
440+
requireUsersMatch(t, []database.User{today, yesterday}, allAfterLastWeek, "after last week")
441+
})
442+
}
443+
444+
func requireUsersMatch(t testing.TB, expected []database.User, found []database.GetUsersRow, msg string) {
445+
t.Helper()
446+
require.ElementsMatch(t, expected, database.ConvertUserRows(found), msg)
447+
}

‎coderd/database/queries.sql.go

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

‎coderd/database/queries/users.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -181,6 +181,17 @@ WHERE
181181
rbac_roles && @rbac_role :: text[]
182182
ELSE true
183183
END
184+
-- Filter by last_seen
185+
AND CASE
186+
WHEN @last_seen_before :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
187+
last_seen_at <= @last_seen_before
188+
ELSE true
189+
END
190+
AND CASE
191+
WHEN @last_seen_after :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
192+
last_seen_at >= @last_seen_after
193+
ELSE true
194+
END
184195
-- End of filters
185196
ORDER BY
186197
-- Deterministic and consistent ordering of all users. This is to ensure consistent pagination.

‎coderd/httpapi/queryparams.go

Lines changed: 24 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -111,14 +111,35 @@ func (p *QueryParamParser) UUIDs(vals url.Values, def []uuid.UUID, queryParam st
111111
})
112112
}
113113

114-
func (p *QueryParamParser) Time(vals url.Values, def time.Time, queryParam string, format string) time.Time {
114+
func (p *QueryParamParser) Time(vals url.Values, def time.Time, queryParam, layout string) time.Time {
115+
return p.timeWithMutate(vals, def, queryParam, layout, nil)
116+
}
117+
118+
// Time uses the default time format of RFC3339Nano and always returns a UTC time.
119+
func (p *QueryParamParser) Time3339Nano(vals url.Values, def time.Time, queryParam string) time.Time {
120+
layout := time.RFC3339Nano
121+
return p.timeWithMutate(vals, def, queryParam, layout, func(term string) string {
122+
// All search queries are forced to lowercase. But the RFC format requires
123+
// upper case letters. So just uppercase the term.
124+
return strings.ToUpper(term)
125+
})
126+
}
127+
128+
func (p *QueryParamParser) timeWithMutate(vals url.Values, def time.Time, queryParam, layout string, mutate func(term string) string) time.Time {
115129
v, err := parseQueryParam(p, vals, func(term string) (time.Time, error) {
116-
return time.Parse(format, term)
130+
if mutate != nil {
131+
term = mutate(term)
132+
}
133+
t, err := time.Parse(layout, term)
134+
if err != nil {
135+
return time.Time{}, err
136+
}
137+
return t.UTC(), nil
117138
}, def, queryParam)
118139
if err != nil {
119140
p.Errors = append(p.Errors, codersdk.ValidationError{
120141
Field: queryParam,
121-
Detail: fmt.Sprintf("Query param %q must be a valid date format (%s): %s", queryParam, format, err.Error()),
142+
Detail: fmt.Sprintf("Query param %q must be a valid date format (%s): %s", queryParam, layout, err.Error()),
122143
})
123144
}
124145
return v

‎coderd/httpapi/queryparams_test.go

Lines changed: 3 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -69,13 +69,12 @@ func TestParseQueryParams(t *testing.T) {
6969

7070
t.Run("Time", func(t *testing.T) {
7171
t.Parallel()
72-
const layout = "2006-01-02"
7372

7473
expParams := []queryParamTestCase[time.Time]{
7574
{
7675
QueryParam: "date",
77-
Value: "2010-01-01",
78-
Expected: must(time.Parse(layout, "2010-01-01")),
76+
Value: "2023-01-16T00:00:00+12:00",
77+
Expected: time.Date(2023, 1, 15, 12, 0, 0, 0, time.UTC),
7978
},
8079
{
8180
QueryParam: "bad_date",
@@ -86,7 +85,7 @@ func TestParseQueryParams(t *testing.T) {
8685

8786
parser := httpapi.NewQueryParamParser()
8887
testQueryParams(t, expParams, parser, func(vals url.Values, def time.Time, queryParam string) time.Time {
89-
return parser.Time(vals, time.Time{}, queryParam, layout)
88+
return parser.Time3339Nano(vals, time.Time{}, queryParam)
9089
})
9190
})
9291

@@ -309,10 +308,3 @@ func testQueryParams[T any](t *testing.T, testCases []queryParamTestCase[T], par
309308
})
310309
}
311310
}
312-
313-
func must[T any](value T, err error) T {
314-
if err != nil {
315-
panic(err)
316-
}
317-
return value
318-
}

‎coderd/searchquery/search.go

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -59,9 +59,11 @@ func Users(query string) (database.GetUsersParams, []codersdk.ValidationError) {
5959

6060
parser := httpapi.NewQueryParamParser()
6161
filter := database.GetUsersParams{
62-
Search: parser.String(values, "", "search"),
63-
Status: httpapi.ParseCustomList(parser, values, []database.UserStatus{}, "status", httpapi.ParseEnum[database.UserStatus]),
64-
RbacRole: parser.Strings(values, []string{}, "role"),
62+
Search: parser.String(values, "", "search"),
63+
Status: httpapi.ParseCustomList(parser, values, []database.UserStatus{}, "status", httpapi.ParseEnum[database.UserStatus]),
64+
RbacRole: parser.Strings(values, []string{}, "role"),
65+
LastSeenAfter: parser.Time3339Nano(values, time.Time{}, "last_seen_after"),
66+
LastSeenBefore: parser.Time3339Nano(values, time.Time{}, "last_seen_before"),
6567
}
6668
parser.ErrorExcessParams(values)
6769
return filter, parser.Errors

‎coderd/users.go

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -199,12 +199,14 @@ func (api *API) users(rw http.ResponseWriter, r *http.Request) {
199199
}
200200

201201
userRows, err := api.Database.GetUsers(ctx, database.GetUsersParams{
202-
AfterID: paginationParams.AfterID,
203-
OffsetOpt: int32(paginationParams.Offset),
204-
LimitOpt: int32(paginationParams.Limit),
205-
Search: params.Search,
206-
Status: params.Status,
207-
RbacRole: params.RbacRole,
202+
AfterID: paginationParams.AfterID,
203+
Search: params.Search,
204+
Status: params.Status,
205+
RbacRole: params.RbacRole,
206+
LastSeenBefore: params.LastSeenBefore,
207+
LastSeenAfter: params.LastSeenAfter,
208+
OffsetOpt: int32(paginationParams.Offset),
209+
LimitOpt: int32(paginationParams.Limit),
208210
})
209211
if err != nil {
210212
httpapi.Write(ctx, rw, http.StatusInternalServerError, codersdk.Response{

‎coderd/users_test.go

Lines changed: 39 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@ import (
1818
"github.com/coder/coder/coderd/audit"
1919
"github.com/coder/coder/coderd/coderdtest"
2020
"github.com/coder/coder/coderd/database"
21+
"github.com/coder/coder/coderd/database/dbauthz"
2122
"github.com/coder/coder/coderd/rbac"
2223
"github.com/coder/coder/codersdk"
2324
"github.com/coder/coder/testutil"
@@ -1102,7 +1103,7 @@ func TestGetUser(t *testing.T) {
11021103
func TestUsersFilter(t *testing.T) {
11031104
t.Parallel()
11041105

1105-
client := coderdtest.New(t, &coderdtest.Options{IncludeProvisionerDaemon: true})
1106+
client, _, api := coderdtest.NewWithAPI(t, &coderdtest.Options{IncludeProvisionerDaemon: true})
11061107
first := coderdtest.CreateFirstUser(t, client)
11071108

11081109
ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitLong)
@@ -1111,6 +1112,13 @@ func TestUsersFilter(t *testing.T) {
11111112
firstUser, err := client.User(ctx, codersdk.Me)
11121113
require.NoError(t, err, "fetch me")
11131114

1115+
// Noon on Jan 18 is the "now" for this test for last_seen timestamps.
1116+
// All these values are equal
1117+
// 2023-01-18T12:00:00Z (UTC)
1118+
// 2023-01-18T07:00:00-05:00 (America/New_York)
1119+
// 2023-01-18T13:00:00+01:00 (Europe/Madrid)
1120+
// 2023-01-16T00:00:00+12:00 (Asia/Anadyr)
1121+
lastSeenNow := time.Date(2023, 1, 18, 12, 0, 0, 0, time.UTC)
11141122
users := make([]codersdk.User, 0)
11151123
users = append(users, firstUser)
11161124
for i := 0; i < 15; i++ {
@@ -1121,7 +1129,16 @@ func TestUsersFilter(t *testing.T) {
11211129
if i%3 == 0 {
11221130
roles = append(roles, "auditor")
11231131
}
1124-
userClient, _ := coderdtest.CreateAnotherUser(t, client, first.OrganizationID, roles...)
1132+
userClient, userData := coderdtest.CreateAnotherUser(t, client, first.OrganizationID, roles...)
1133+
// Set the last seen for each user to a unique day
1134+
// nolint:gocritic // Unit test
1135+
_, err := api.Database.UpdateUserLastSeenAt(dbauthz.AsSystemRestricted(ctx), database.UpdateUserLastSeenAtParams{
1136+
ID: userData.ID,
1137+
LastSeenAt: lastSeenNow.Add(-1 * time.Hour * 24 * time.Duration(i)),
1138+
UpdatedAt: time.Now(),
1139+
})
1140+
require.NoError(t, err, "set a last seen")
1141+
11251142
user, err := userClient.User(ctx, codersdk.Me)
11261143
require.NoError(t, err, "fetch me")
11271144

@@ -1262,6 +1279,26 @@ func TestUsersFilter(t *testing.T) {
12621279
return false
12631280
},
12641281
},
1282+
{
1283+
Name: "LastSeenBeforeNow",
1284+
Filter: codersdk.UsersRequest{
1285+
SearchQuery: `last_seen_before:"2023-01-16T00:00:00+12:00"`,
1286+
},
1287+
FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
1288+
return u.LastSeenAt.Before(lastSeenNow)
1289+
},
1290+
},
1291+
{
1292+
Name: "LastSeenLastWeek",
1293+
Filter: codersdk.UsersRequest{
1294+
SearchQuery: `last_seen_before:"2023-01-14T23:59:59Z" last_seen_after:"2023-01-08T00:00:00Z"`,
1295+
},
1296+
FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
1297+
start := time.Date(2023, 1, 8, 0, 0, 0, 0, time.UTC)
1298+
end := time.Date(2023, 1, 14, 23, 59, 59, 0, time.UTC)
1299+
return u.LastSeenAt.Before(end) && u.LastSeenAt.After(start)
1300+
},
1301+
},
12651302
}
12661303

12671304
for _, c := range testCases {

0 commit comments

Comments
 (0)