Skip to content

feat: User pagination using offsets #1062

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 25 commits into from
Apr 22, 2022
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
16 changes: 2 additions & 14 deletions Makefile
Original file line number Diff line number Diff line change
Expand Up @@ -15,7 +15,7 @@ coderd/database/dump.sql: $(wildcard coderd/database/migrations/*.sql)
.PHONY: coderd/database/dump.sql

# Generates Go code for querying the database.
coderd/database/generate: fmt/sql coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
coderd/database/generate: coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
coderd/database/generate.sh
.PHONY: coderd/database/generate

Expand All @@ -34,22 +34,10 @@ else
endif
.PHONY: fmt/prettier

fmt/sql: $(wildcard coderd/database/queries/*.sql)
for fi in coderd/database/queries/*.sql; do \
npx sql-formatter \
--language postgresql \
--lines-between-queries 2 \
--tab-indent \
$$fi \
--output $$fi; \
done

sed -i 's/@ /@/g' ./coderd/database/queries/*.sql

fmt/terraform: $(wildcard *.tf)
terraform fmt -recursive

fmt: fmt/prettier fmt/sql fmt/terraform
fmt: fmt/prettier fmt/terraform
.PHONY: fmt

gen: coderd/database/generate peerbroker/proto provisionersdk/proto provisionerd/proto apitypings/generate
Expand Down
23 changes: 15 additions & 8 deletions coderd/coderd.go
Original file line number Diff line number Diff line change
Expand Up @@ -35,13 +35,17 @@ type Options struct {
Pubsub database.Pubsub

AgentConnectionUpdateFrequency time.Duration
AWSCertificates awsidentity.Certificates
AzureCertificates x509.VerifyOptions
GoogleTokenValidator *idtoken.Validator
ICEServers []webrtc.ICEServer
SecureAuthCookie bool
SSHKeygenAlgorithm gitsshkey.Algorithm
TURNServer *turnconn.Server
// APIRateLimit is the minutely throughput rate limit per user or ip.
// Setting a rate limit <0 will disable the rate limiter across the entire
// app. Specific routes may have their own limiters.
APIRateLimit int
AWSCertificates awsidentity.Certificates
AzureCertificates x509.VerifyOptions
GoogleTokenValidator *idtoken.Validator
ICEServers []webrtc.ICEServer
SecureAuthCookie bool
SSHKeygenAlgorithm gitsshkey.Algorithm
TURNServer *turnconn.Server
}

// New constructs the Coder API into an HTTP handler.
Expand All @@ -52,6 +56,9 @@ func New(options *Options) (http.Handler, func()) {
if options.AgentConnectionUpdateFrequency == 0 {
options.AgentConnectionUpdateFrequency = 3 * time.Second
}
if options.APIRateLimit == 0 {
options.APIRateLimit = 512
}
api := &api{
Options: options,
}
Expand All @@ -61,7 +68,7 @@ func New(options *Options) (http.Handler, func()) {
r.Use(
chitrace.Middleware(),
// Specific routes can specify smaller limits.
httpmw.RateLimitPerMinute(512),
httpmw.RateLimitPerMinute(options.APIRateLimit),
debugLogRequest(api.Logger),
)
r.Get("/", func(w http.ResponseWriter, r *http.Request) {
Expand Down
2 changes: 2 additions & 0 deletions coderd/coderdtest/coderdtest.go
Original file line number Diff line number Diff line change
Expand Up @@ -55,6 +55,7 @@ type Options struct {
AzureCertificates x509.VerifyOptions
GoogleTokenValidator *idtoken.Validator
SSHKeygenAlgorithm gitsshkey.Algorithm
APIRateLimit int
}

// New constructs an in-memory coderd instance and returns
Expand Down Expand Up @@ -125,6 +126,7 @@ func New(t *testing.T, options *Options) *codersdk.Client {
GoogleTokenValidator: options.GoogleTokenValidator,
SSHKeygenAlgorithm: options.SSHKeygenAlgorithm,
TURNServer: turnServer,
APIRateLimit: options.APIRateLimit,
})
t.Cleanup(func() {
cancelFunc()
Expand Down
66 changes: 64 additions & 2 deletions coderd/database/databasefake/databasefake.go
Original file line number Diff line number Diff line change
Expand Up @@ -3,6 +3,7 @@ package databasefake
import (
"context"
"database/sql"
"sort"
"strings"
"sync"

Expand Down Expand Up @@ -164,11 +165,72 @@ func (q *fakeQuerier) GetUserCount(_ context.Context) (int64, error) {
return int64(len(q.users)), nil
}

func (q *fakeQuerier) GetUsers(_ context.Context) ([]database.User, error) {
func (q *fakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams) ([]database.User, error) {
q.mutex.RLock()
defer q.mutex.RUnlock()

return q.users, nil
users := q.users
// Database orders by created_at
sort.Slice(users, func(i, j int) bool {
if users[i].CreatedAt.Equal(users[j].CreatedAt) {
// Technically the postgres database also orders by uuid. So match
// that behavior
return users[i].ID.String() < users[j].ID.String()
}
return users[i].CreatedAt.Before(users[j].CreatedAt)
})

if params.AfterUser != uuid.Nil {
found := false
for i := range users {
if users[i].ID == params.AfterUser {
// We want to return all users after index i.
if i+1 >= len(users) {
return []database.User{}, nil
}
users = users[i+1:]
found = true
break
}
}

// If no users after the time, then we return an empty list.
if !found {
return []database.User{}, nil
}
}

if params.Search != "" {
tmp := make([]database.User, 0, len(users))
for i, user := range users {
if strings.Contains(user.Email, params.Search) {
tmp = append(tmp, users[i])
} else if strings.Contains(user.Username, params.Search) {
tmp = append(tmp, users[i])
} else if strings.Contains(user.Name, params.Search) {
tmp = append(tmp, users[i])
}
}
users = tmp
}

if params.OffsetOpt > 0 {
if int(params.OffsetOpt) > len(users)-1 {
return []database.User{}, nil
}
users = users[params.OffsetOpt:]
}

if params.LimitOpt > 0 {
if int(params.LimitOpt) > len(users) {
params.LimitOpt = int32(len(users))
}
users = users[:params.LimitOpt]
}
tmp := make([]database.User, len(users))
copy(tmp, users)

return tmp, nil
}

func (q *fakeQuerier) GetWorkspacesByTemplateID(_ context.Context, arg database.GetWorkspacesByTemplateIDParams) ([]database.Workspace, error) {
Expand Down
2 changes: 1 addition & 1 deletion coderd/database/querier.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

54 changes: 52 additions & 2 deletions coderd/database/queries.sql.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

40 changes: 39 additions & 1 deletion coderd/database/queries/users.sql
Original file line number Diff line number Diff line change
Expand Up @@ -56,4 +56,42 @@ WHERE
SELECT
*
FROM
users;
users
WHERE
CASE
-- This allows using the last element on a page as effectively a cursor.
-- This is an important option for scripts that need to paginate without
-- duplicating or missing data.
WHEN @after_user :: uuid != '00000000-00000000-00000000-00000000' THEN (
-- The pagination cursor is the last user of the previous page.
-- The query is ordered by the created_at field, so select all
-- users after the cursor. We also want to include any users
-- that share the created_at (super rare).
created_at >= (
SELECT
created_at
FROM
users
WHERE
id = @after_user
)
-- Omit the cursor from the final.
AND id != @after_user
)
ELSE true
END
AND CASE
WHEN @search :: text != '' THEN (
email LIKE concat('%', @search, '%')
OR username LIKE concat('%', @search, '%')
OR 'name' LIKE concat('%', @search, '%')
)
ELSE true
END
ORDER BY
-- Deterministic and consistent ordering of all users, even if they share
-- a timestamp. This is to ensure consistent pagination.
(created_at, id) ASC OFFSET @offset_opt
LIMIT
-- A null limit means "no limit", so -1 means return all
NULLIF(@limit_opt :: int, -1);
6 changes: 6 additions & 0 deletions coderd/httpmw/ratelimit.go
Original file line number Diff line number Diff line change
Expand Up @@ -13,6 +13,12 @@ import (
// RateLimitPerMinute returns a handler that limits requests per-minute based
// on IP, endpoint, and user ID (if available).
func RateLimitPerMinute(count int) func(http.Handler) http.Handler {
// -1 is no rate limit
if count <= 0 {
return func(handler http.Handler) http.Handler {
return handler
}
}
return httprate.Limit(
count,
1*time.Minute,
Expand Down
Loading