143 lines
3.6 KiB
SQL
143 lines
3.6 KiB
SQL
-- name: CreateUser :exec
|
|
INSERT INTO sys_users (account, username, password_hash, status, avatar_id)
|
|
VALUES ($1, $2, $3, $4, $5);
|
|
|
|
-- name: GetUserAuthState :one
|
|
SELECT status,
|
|
token_version
|
|
FROM sys_users
|
|
WHERE id = $1;
|
|
|
|
-- name: GetUser :one
|
|
SELECT u.id,
|
|
u.account,
|
|
u.username,
|
|
u.password_hash,
|
|
u.status,
|
|
u.avatar_id,
|
|
f.file_url AS avatar_url,
|
|
u.token_version,
|
|
u.created_at,
|
|
u.updated_at
|
|
FROM sys_users u
|
|
LEFT JOIN files f ON u.avatar_id = f.id
|
|
WHERE u.id = $1
|
|
OR u.account = $2;
|
|
|
|
-- name: ListUsers :many
|
|
SELECT u.id,
|
|
u.account,
|
|
u.username,
|
|
u.status,
|
|
u.avatar_id,
|
|
f.file_url AS avatar_url,
|
|
u.created_at,
|
|
u.updated_at
|
|
FROM sys_users u
|
|
LEFT JOIN files f ON u.avatar_id = f.id
|
|
WHERE (sqlc.arg('username')::text = '' OR u.username ILIKE '%' || sqlc.arg('username') || '%')
|
|
ORDER BY u.id
|
|
LIMIT $1 OFFSET $2;
|
|
|
|
-- name: CountUsers :one
|
|
SELECT COUNT(*)
|
|
FROM sys_users
|
|
WHERE (sqlc.arg('username')::text = '' OR username ILIKE '%' || sqlc.arg('username') || '%');
|
|
|
|
-- name: UpdateUser :execrows
|
|
UPDATE sys_users
|
|
SET username = coalesce(sqlc.narg('username'), username),
|
|
status = coalesce(sqlc.narg('status'), status),
|
|
avatar_id = CASE WHEN @update_avatar_id::boolean THEN @avatar_id ELSE avatar_id END
|
|
WHERE id = sqlc.arg('id');
|
|
|
|
-- name: IncrementUserTokenVersion :exec
|
|
UPDATE sys_users
|
|
SET token_version = token_version + 1
|
|
WHERE id = $1;
|
|
|
|
-- name: IncrementTokenVersionForAllUsers :exec
|
|
UPDATE sys_users
|
|
SET token_version = token_version + 1;
|
|
|
|
-- name: UpdateUserPassword :execrows
|
|
UPDATE sys_users
|
|
SET password_hash = $2
|
|
WHERE id = $1;
|
|
|
|
-- name: DeleteUser :execrows
|
|
DELETE
|
|
FROM sys_users
|
|
WHERE id = $1
|
|
AND id <> 1;
|
|
|
|
-- name: ClearUserRoles :exec
|
|
DELETE
|
|
FROM sys_user_role
|
|
WHERE user_id = $1;
|
|
|
|
-- name: CreateUserRole :copyfrom
|
|
INSERT INTO sys_user_role (user_id, role_id)
|
|
VALUES ($1, $2);
|
|
|
|
-- name: ListUserRoles :many
|
|
SELECT r.*
|
|
FROM sys_roles r
|
|
JOIN sys_user_role ur ON r.id = ur.role_id
|
|
WHERE ur.user_id = $1;
|
|
|
|
-- name: ListAdminMenus :many
|
|
SELECT *
|
|
FROM sys_menus
|
|
WHERE status = 1
|
|
AND type <> 2
|
|
ORDER BY sort, id;
|
|
|
|
-- name: ListAdminPermissionCodes :many
|
|
SELECT code
|
|
FROM sys_permissions
|
|
WHERE type = 0;
|
|
|
|
-- name: ListUserMenus :many
|
|
SELECT DISTINCT m.*
|
|
FROM sys_users u
|
|
JOIN sys_user_role ur ON ur.user_id = u.id
|
|
JOIN sys_roles r ON r.id = ur.role_id
|
|
JOIN sys_role_permission rp ON rp.role_id = r.id
|
|
JOIN sys_menu_permission mp ON mp.permission_id = rp.permission_id
|
|
JOIN sys_menus m ON m.id = mp.menu_id
|
|
WHERE u.id = $1
|
|
AND u.status = 1
|
|
AND r.status = 1
|
|
AND m.status = 1
|
|
AND m.type <> 2
|
|
ORDER BY m.sort, m.id;
|
|
|
|
-- name: ListUserPermissionCodes :many
|
|
SELECT DISTINCT p.code
|
|
FROM sys_users u
|
|
JOIN sys_user_role ur ON u.id = ur.user_id
|
|
JOIN sys_roles r on r.id = ur.role_id
|
|
JOIN sys_role_permission rp ON rp.role_id = r.id
|
|
JOIN sys_permissions p ON p.id = rp.permission_id
|
|
JOIN sys_menu_permission mp ON mp.permission_id = p.id
|
|
JOIN sys_menus m ON m.id = mp.menu_id
|
|
WHERE u.id = $1
|
|
AND u.status = 1
|
|
AND r.status = 1
|
|
AND m.status = 1
|
|
AND p."type" = 0;
|
|
|
|
-- name: ListUserApis :many
|
|
SELECT DISTINCT a.id,
|
|
a.PATH,
|
|
a.METHOD
|
|
FROM sys_users u
|
|
JOIN sys_user_role ur ON u.id = ur.user_id
|
|
JOIN sys_roles r ON r.id = ur.role_id
|
|
JOIN sys_role_permission rp ON rp.role_id = r.id
|
|
JOIN sys_api_permission ap ON ap.permission_id = rp.permission_id
|
|
JOIN sys_apis a ON a.id = ap.api_id
|
|
WHERE u.id = $1
|
|
AND u.status = 1
|
|
AND r.status = 1; |