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

chore: populate connectionlog count using a separate query#18629

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 ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
ethanndickson merged 1 commit intomainfromethan/populate-connection-log-count
Jul 15, 2025
Merged
Show file tree
Hide file tree
Changes fromall 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
chore: populate connectionlog count using a separate query
  • Loading branch information
@ethanndickson
ethanndickson committedJul 15, 2025
commite10a5cffbc39f7fc33e3dacbc795c94173f9a19c
19 changes: 17 additions & 2 deletionscoderd/database/dbauthz/dbauthz.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -1353,15 +1353,26 @@ func (q *querier) CountAuditLogs(ctx context.Context, arg database.CountAuditLog
if err == nil {
return q.db.CountAuditLogs(ctx, arg)
}

prep, err := prepareSQLFilter(ctx, q.auth, policy.ActionRead, rbac.ResourceAuditLog.Type)
if err != nil {
return 0, xerrors.Errorf("(dev error) prepare sql filter: %w", err)
}

return q.db.CountAuthorizedAuditLogs(ctx, arg, prep)
}

func (q *querier) CountConnectionLogs(ctx context.Context, arg database.CountConnectionLogsParams) (int64, error) {
// Just like the actual query, shortcut if the user is an owner.
err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceConnectionLog)
if err == nil {
return q.db.CountConnectionLogs(ctx, arg)
}
prep, err := prepareSQLFilter(ctx, q.auth, policy.ActionRead, rbac.ResourceConnectionLog.Type)
if err != nil {
return 0, xerrors.Errorf("(dev error) prepare sql filter: %w", err)
}
return q.db.CountAuthorizedConnectionLogs(ctx, arg, prep)
}

func (q *querier) CountInProgressPrebuilds(ctx context.Context) ([]database.CountInProgressPrebuildsRow, error) {
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceWorkspace.All()); err != nil {
return nil, err
Expand DownExpand Up@@ -5392,3 +5403,7 @@ func (q *querier) CountAuthorizedAuditLogs(ctx context.Context, arg database.Cou
func (q *querier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg database.GetConnectionLogsOffsetParams, _ rbac.PreparedAuthorized) ([]database.GetConnectionLogsOffsetRow, error) {
return q.GetConnectionLogsOffset(ctx, arg)
}

func (q *querier) CountAuthorizedConnectionLogs(ctx context.Context, arg database.CountConnectionLogsParams, _ rbac.PreparedAuthorized) (int64, error) {
return q.CountConnectionLogs(ctx, arg)
}
36 changes: 36 additions & 0 deletionscoderd/database/dbauthz/dbauthz_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -406,6 +406,42 @@ func (s *MethodTestSuite) TestConnectionLogs() {
LimitOpt: 10,
}, emptyPreparedAuthorized{}).Asserts(rbac.ResourceConnectionLog, policy.ActionRead)
}))
s.Run("CountConnectionLogs", s.Subtest(func(db database.Store, check *expects) {
ws := createWorkspace(s.T(), db)
_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
Type: database.ConnectionTypeSsh,
WorkspaceID: ws.ID,
OrganizationID: ws.OrganizationID,
WorkspaceOwnerID: ws.OwnerID,
})
_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
Type: database.ConnectionTypeSsh,
WorkspaceID: ws.ID,
OrganizationID: ws.OrganizationID,
WorkspaceOwnerID: ws.OwnerID,
})
check.Args(database.CountConnectionLogsParams{}).Asserts(
rbac.ResourceConnectionLog, policy.ActionRead,
).WithNotAuthorized("nil")
}))
s.Run("CountAuthorizedConnectionLogs", s.Subtest(func(db database.Store, check *expects) {
ws := createWorkspace(s.T(), db)
_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
Type: database.ConnectionTypeSsh,
WorkspaceID: ws.ID,
OrganizationID: ws.OrganizationID,
WorkspaceOwnerID: ws.OwnerID,
})
_ = dbgen.ConnectionLog(s.T(), db, database.UpsertConnectionLogParams{
Type: database.ConnectionTypeSsh,
WorkspaceID: ws.ID,
OrganizationID: ws.OrganizationID,
WorkspaceOwnerID: ws.OwnerID,
})
check.Args(database.CountConnectionLogsParams{}, emptyPreparedAuthorized{}).Asserts(
rbac.ResourceConnectionLog, policy.ActionRead,
)
}))
}

func (s *MethodTestSuite) TestFile() {
Expand Down
2 changes: 1 addition & 1 deletioncoderd/database/dbauthz/setup_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -318,7 +318,7 @@ func hasEmptyResponse(values []reflect.Value) bool {
}
}

// Special case for int64, as it's the return type for countquery.
// Special case for int64, as it's the return type for countqueries.
if r.Kind() == reflect.Int64 {
if r.Int() == 0 {
return true
Expand Down
14 changes: 14 additions & 0 deletionscoderd/database/dbmetrics/querymetrics.go
View file
Open in desktop

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

30 changes: 30 additions & 0 deletionscoderd/database/dbmock/dbmock.go
View file
Open in desktop

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

48 changes: 48 additions & 0 deletionscoderd/database/modelqueries.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -614,6 +614,7 @@ func (q *sqlQuerier) CountAuthorizedAuditLogs(ctx context.Context, arg CountAudi

type connectionLogQuerier interface {
GetAuthorizedConnectionLogsOffset(ctx context.Context, arg GetConnectionLogsOffsetParams, prepared rbac.PreparedAuthorized) ([]GetConnectionLogsOffsetRow, error)
CountAuthorizedConnectionLogs(ctx context.Context, arg CountConnectionLogsParams, prepared rbac.PreparedAuthorized) (int64, error)
}

func (q *sqlQuerier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg GetConnectionLogsOffsetParams, prepared rbac.PreparedAuthorized) ([]GetConnectionLogsOffsetRow, error) {
Expand DownExpand Up@@ -700,6 +701,53 @@ func (q *sqlQuerier) GetAuthorizedConnectionLogsOffset(ctx context.Context, arg
return items, nil
}

func (q *sqlQuerier) CountAuthorizedConnectionLogs(ctx context.Context, arg CountConnectionLogsParams, prepared rbac.PreparedAuthorized) (int64, error) {
authorizedFilter, err := prepared.CompileToSQL(ctx, regosql.ConvertConfig{
VariableConverter: regosql.ConnectionLogConverter(),
})
if err != nil {
return 0, xerrors.Errorf("compile authorized filter: %w", err)
}
filtered, err := insertAuthorizedFilter(countConnectionLogs, fmt.Sprintf(" AND %s", authorizedFilter))
if err != nil {
return 0, xerrors.Errorf("insert authorized filter: %w", err)
}

query := fmt.Sprintf("-- name: CountAuthorizedConnectionLogs :one\n%s", filtered)
rows, err := q.db.QueryContext(ctx, query,
arg.OrganizationID,
arg.WorkspaceOwner,
arg.WorkspaceOwnerID,
arg.WorkspaceOwnerEmail,
arg.Type,
arg.UserID,
arg.Username,
arg.UserEmail,
arg.ConnectedAfter,
arg.ConnectedBefore,
arg.WorkspaceID,
arg.ConnectionID,
arg.Status,
)
if err != nil {
return 0, err
}
defer rows.Close()
var count int64
for rows.Next() {
if err := rows.Scan(&count); err != nil {
return 0, err
}
}
if err := rows.Close(); err != nil {
return 0, err
}
if err := rows.Err(); err != nil {
return 0, err
}
return count, nil
}

func insertAuthorizedFilter(query string, replaceWith string) (string, error) {
if !strings.Contains(query, authorizedQueryPlaceholder) {
return "", xerrors.Errorf("query does not contain authorized replace string, this is not an authorized query")
Expand Down
13 changes: 13 additions & 0 deletionscoderd/database/modelqueries_internal_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -76,6 +76,19 @@ func TestAuditLogsQueryConsistency(t *testing.T) {
}
}

// Same as TestAuditLogsQueryConsistency, but for connection logs.
func TestConnectionLogsQueryConsistency(t *testing.T) {
t.Parallel()

getWhereClause := extractWhereClause(getConnectionLogsOffset)
require.NotEmpty(t, getWhereClause, "getConnectionLogsOffset query should have a WHERE clause")

countWhereClause := extractWhereClause(countConnectionLogs)
require.NotEmpty(t, countWhereClause, "countConnectionLogs query should have a WHERE clause")

require.Equal(t, getWhereClause, countWhereClause, "getConnectionLogsOffset and countConnectionLogs queries should have the same WHERE clause")
}

// extractWhereClause extracts the WHERE clause from a SQL query string
func extractWhereClause(query string) string {
// Find WHERE and get everything after it
Expand Down
1 change: 1 addition & 0 deletionscoderd/database/querier.go
View file
Open in desktop

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

95 changes: 95 additions & 0 deletionscoderd/database/querier_test.go
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -2168,6 +2168,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
require.NoError(t, err)
// Then: No logs returned
require.Len(t, logs, 0, "no logs should be returned")
// And: The count matches the number of logs returned
count, err := authDb.CountConnectionLogs(memberCtx, database.CountConnectionLogsParams{})
require.NoError(t, err)
require.EqualValues(t, len(logs), count)
})

t.Run("SiteWideAuditor", func(t *testing.T) {
Expand All@@ -2186,6 +2190,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
require.NoError(t, err)
// Then: All logs are returned
require.ElementsMatch(t, connectionOnlyIDs(allLogs), connectionOnlyIDs(logs))
// And: The count matches the number of logs returned
count, err := authDb.CountConnectionLogs(siteAuditorCtx, database.CountConnectionLogsParams{})
require.NoError(t, err)
require.EqualValues(t, len(logs), count)
})

t.Run("SingleOrgAuditor", func(t *testing.T) {
Expand All@@ -2205,6 +2213,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
require.NoError(t, err)
// Then: Only the logs for the organization are returned
require.ElementsMatch(t, orgConnectionLogs[orgID], connectionOnlyIDs(logs))
// And: The count matches the number of logs returned
count, err := authDb.CountConnectionLogs(orgAuditCtx, database.CountConnectionLogsParams{})
require.NoError(t, err)
require.EqualValues(t, len(logs), count)
})

t.Run("TwoOrgAuditors", func(t *testing.T) {
Expand All@@ -2225,6 +2237,10 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
require.NoError(t, err)
// Then: All logs for both organizations are returned
require.ElementsMatch(t, append(orgConnectionLogs[first], orgConnectionLogs[second]...), connectionOnlyIDs(logs))
// And: The count matches the number of logs returned
count, err := authDb.CountConnectionLogs(multiOrgAuditCtx, database.CountConnectionLogsParams{})
require.NoError(t, err)
require.EqualValues(t, len(logs), count)
})

t.Run("ErroneousOrg", func(t *testing.T) {
Expand All@@ -2243,9 +2259,71 @@ func TestGetAuthorizedConnectionLogsOffset(t *testing.T) {
require.NoError(t, err)
// Then: No logs are returned
require.Len(t, logs, 0, "no logs should be returned")
// And: The count matches the number of logs returned
count, err := authDb.CountConnectionLogs(userCtx, database.CountConnectionLogsParams{})
require.NoError(t, err)
require.EqualValues(t, len(logs), count)
})
}

func TestCountConnectionLogs(t *testing.T) {
t.Parallel()
ctx := testutil.Context(t, testutil.WaitLong)

db, _ := dbtestutil.NewDB(t)

orgA := dbfake.Organization(t, db).Do()
userA := dbgen.User(t, db, database.User{})
tplA := dbgen.Template(t, db, database.Template{OrganizationID: orgA.Org.ID, CreatedBy: userA.ID})
wsA := dbgen.Workspace(t, db, database.WorkspaceTable{OwnerID: userA.ID, OrganizationID: orgA.Org.ID, TemplateID: tplA.ID})

orgB := dbfake.Organization(t, db).Do()
userB := dbgen.User(t, db, database.User{})
tplB := dbgen.Template(t, db, database.Template{OrganizationID: orgB.Org.ID, CreatedBy: userB.ID})
wsB := dbgen.Workspace(t, db, database.WorkspaceTable{OwnerID: userB.ID, OrganizationID: orgB.Org.ID, TemplateID: tplB.ID})

// Create logs for two different orgs.
for i := 0; i < 20; i++ {
dbgen.ConnectionLog(t, db, database.UpsertConnectionLogParams{
OrganizationID: wsA.OrganizationID,
WorkspaceOwnerID: wsA.OwnerID,
WorkspaceID: wsA.ID,
Type: database.ConnectionTypeSsh,
})
}
for i := 0; i < 10; i++ {
dbgen.ConnectionLog(t, db, database.UpsertConnectionLogParams{
OrganizationID: wsB.OrganizationID,
WorkspaceOwnerID: wsB.OwnerID,
WorkspaceID: wsB.ID,
Type: database.ConnectionTypeSsh,
})
}

// Count with a filter for orgA.
countParams := database.CountConnectionLogsParams{
OrganizationID: orgA.Org.ID,
}
totalCount, err := db.CountConnectionLogs(ctx, countParams)
require.NoError(t, err)
require.Equal(t, int64(20), totalCount)

// Get a paginated result for the same filter.
getParams := database.GetConnectionLogsOffsetParams{
OrganizationID: orgA.Org.ID,
LimitOpt: 5,
OffsetOpt: 10,
}
logs, err := db.GetConnectionLogsOffset(ctx, getParams)
require.NoError(t, err)
require.Len(t, logs, 5)

// The count with the filter should remain the same, independent of pagination.
countAfterGet, err := db.CountConnectionLogs(ctx, countParams)
require.NoError(t, err)
require.Equal(t, int64(20), countAfterGet)
}

func TestConnectionLogsOffsetFilters(t *testing.T) {
t.Parallel()
ctx := testutil.Context(t, testutil.WaitLong)
Expand DownExpand Up@@ -2484,7 +2562,24 @@ func TestConnectionLogsOffsetFilters(t *testing.T) {
t.Parallel()
logs, err := db.GetConnectionLogsOffset(ctx, tc.params)
require.NoError(t, err)
count, err := db.CountConnectionLogs(ctx, database.CountConnectionLogsParams{
OrganizationID: tc.params.OrganizationID,
WorkspaceOwner: tc.params.WorkspaceOwner,
Type: tc.params.Type,
UserID: tc.params.UserID,
Username: tc.params.Username,
UserEmail: tc.params.UserEmail,
ConnectedAfter: tc.params.ConnectedAfter,
ConnectedBefore: tc.params.ConnectedBefore,
WorkspaceID: tc.params.WorkspaceID,
ConnectionID: tc.params.ConnectionID,
Status: tc.params.Status,
WorkspaceOwnerID: tc.params.WorkspaceOwnerID,
WorkspaceOwnerEmail: tc.params.WorkspaceOwnerEmail,
})
require.NoError(t, err)
require.ElementsMatch(t, tc.expectedLogIDs, connectionOnlyIDs(logs))
require.Equal(t, len(tc.expectedLogIDs), int(count), "CountConnectionLogs should match the number of returned logs (no offset or limit)")
})
}
}
Expand Down
Loading
Loading
[8]ページ先頭
©2009-2025 Movatter.jp