Skip to content

chore: refactor audit page to use window function for count #5133

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.

Sign up for GitHub

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 15 commits into from
Nov 21, 2022
Merged

chore: refactor audit page to use window function for count #5133

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
15 commits
Select commit Hold shift + click to select a range
00342fb
Move count query to window function
presleyp Nov 15, 2022
31e8ae5
Unpack count and update types
presleyp Nov 15, 2022
2363361
Remove count endpoint
presleyp Nov 15, 2022
7363a70
Update tests, wip
presleyp Nov 16, 2022
04efd25
Fix tests
presleyp Nov 16, 2022
b40a80e
Update frontend, wip
presleyp Nov 16, 2022
1650803
Merge branch 'main' into window-audit/presleyp
presleyp Nov 16, 2022
fb46f46
Remove space
presleyp Nov 16, 2022
82d62c7
Fix frontend test
presleyp Nov 17, 2022
1797b58
Merge branch 'main' into window-audit/presleyp
presleyp Nov 17, 2022
0757395
Don't hang on error
presleyp Nov 18, 2022
eec1a61
Handle no results
presleyp Nov 18, 2022
0752245
Don't omit count
presleyp Nov 18, 2022
67d3944
Merge branch 'main' into window-audit/presleyp
presleyp Nov 18, 2022
8769f62
Fix frontend tests
presleyp Nov 18, 2022
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
Next Next commit
Move count query to window function
  • Loading branch information
@presleyp
presleyp committed Nov 15, 2022
commit 00342fb7d80ef6c31ed54e50e8fb3643abd01ebb
1 change: 0 additions & 1 deletion coderd/database/querier.go
View file
Open in desktop

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

88 changes: 4 additions & 84 deletions coderd/database/queries.sql.go
View file
Open in desktop

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

58 changes: 2 additions & 56 deletions coderd/database/queries/auditlogs.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -8,7 +8,8 @@ SELECT
users.created_at AS user_created_at,
users.status AS user_status,
users.rbac_roles AS user_roles,
users.avatar_url AS user_avatar_url
users.avatar_url AS user_avatar_url,
COUNT(audit_logs.*) OVER() AS count
FROM
audit_logs
LEFT JOIN
Expand Down Expand Up @@ -69,61 +70,6 @@ LIMIT
OFFSET
$2;

-- name: GetAuditLogCount :one
SELECT
COUNT(*) as count
FROM
audit_logs
WHERE
-- Filter resource_type
CASE
WHEN @resource_type :: text != '' THEN
resource_type = @resource_type :: resource_type
ELSE true
END
-- Filter resource_id
AND CASE
WHEN @resource_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
resource_id = @resource_id
ELSE true
END
-- Filter by resource_target
AND CASE
WHEN @resource_target :: text != '' THEN
resource_target = @resource_target
ELSE true
END
-- Filter action
AND CASE
WHEN @action :: text != '' THEN
action = @action :: audit_action
ELSE true
END
-- Filter by username
AND CASE
WHEN @username :: text != '' THEN
user_id = (SELECT id from users WHERE users.username = @username )
ELSE true
END
-- Filter by user_email
AND CASE
WHEN @email :: text != '' THEN
user_id = (SELECT id from users WHERE users.email = @email )
ELSE true
END
-- Filter by date_from
AND CASE
WHEN @date_from :: timestamp with time zone != '0001-01-01 00:00:00' THEN
"time" >= @date_from
ELSE true
END
-- Filter by date_to
AND CASE
WHEN @date_to :: timestamp with time zone != '0001-01-01 00:00:00' THEN
"time" <= @date_to
ELSE true
END;

-- name: InsertAuditLog :one
INSERT INTO
audit_logs (
Expand Down