diff options
Diffstat (limited to 'query')
| -rw-r--r-- | query/frequencies.sql | 18 | ||||
| -rw-r--r-- | query/members.sql | 37 | ||||
| -rw-r--r-- | query/networks.sql | 24 | ||||
| -rw-r--r-- | query/users.sql | 28 | ||||
| -rw-r--r-- | query/users_networks.sql | 65 |
5 files changed, 37 insertions, 135 deletions
diff --git a/query/frequencies.sql b/query/frequencies.sql index a43af54..fba8f51 100644 --- a/query/frequencies.sql +++ b/query/frequencies.sql @@ -26,23 +26,5 @@ UPDATE frequencies SET END WHERE network_id = @network_id AND position IN (@pos1, @pos2); --- name: SetFrequencyName :one -UPDATE frequencies SET - name = ? -WHERE id = ? -RETURNING *; - --- name: SetFrequencyColor :one -UPDATE frequencies SET - hex_color = ? -WHERE id = ? -RETURNING *; - --- name: SetFrequencyPerms :one -UPDATE frequencies SET - perms = ? -WHERE id = ? -RETURNING *; - -- name: DeleteFrequency :exec DELETE FROM frequencies WHERE id = ?; diff --git a/query/members.sql b/query/members.sql new file mode 100644 index 0000000..04e04cd --- /dev/null +++ b/query/members.sql @@ -0,0 +1,37 @@ +-- name: GetMembers :many +SELECT + sqlc.embed(users), + sqlc.embed(members) +FROM members +JOIN users ON users.id = members.user_id +WHERE network_id = ? AND is_member = true; + +-- name: GetMemberById :one +SELECT * FROM members +WHERE network_id = ? AND user_id = ?; + +-- name: GetNetworksOfUser :many +SELECT networks.* FROM networks +JOIN members ON networks.id = members.network_id +WHERE members.user_id = ?; + +-- name: SetMember :one +INSERT INTO members ( + user_id, network_id, + is_member, is_admin, is_muted, + is_banned, ban_reason +) VALUES ( + @user_id, @network_id, + @is_member, @is_admin, @is_muted, + @is_banned, @ban_reason +) +ON CONFLICT DO +UPDATE SET + is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted, + is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position +WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id +RETURNING *; + +-- name: FilterUsersInNetwork :many +SELECT user_id FROM members +WHERE network_id = ? AND user_id IN (sqlc.slice('users')); diff --git a/query/networks.sql b/query/networks.sql index 64f56dc..b1d01c7 100644 --- a/query/networks.sql +++ b/query/networks.sql @@ -1,7 +1,3 @@ --- name: GetPublicNetworks :many -SELECT * FROM networks -WHERE is_public = true; - -- name: GetNetworkById :one SELECT * FROM networks WHERE id = ?; @@ -16,26 +12,6 @@ INSERT INTO networks ( ) RETURNING *; --- name: SetNetworkName :one -UPDATE networks SET - name = ? -WHERE id = ? -RETURNING *; - --- name: SetNetworkIcon :one -UPDATE networks SET - icon = ?, - bg_hex_color = ?, - fg_hex_color = ? -WHERE id = ? -RETURNING *; - --- name: SetNetworkIsPublic :one -UPDATE networks SET - is_public = ? -WHERE id = ? -RETURNING *; - -- name: TransferNetwork :one UPDATE networks SET owner_id = ? diff --git a/query/users.sql b/query/users.sql index d136f15..8247dda 100644 --- a/query/users.sql +++ b/query/users.sql @@ -6,10 +6,6 @@ WHERE id = ? AND is_deleted = false; SELECT * FROM users WHERE public_key = ? AND is_deleted = false; --- name: GetDeletedUserById :one -SELECT * FROM users -WHERE id = ? AND is_deleted = true; - -- name: CreateUser :one INSERT INTO users ( id, name, public_key @@ -18,30 +14,6 @@ INSERT INTO users ( ) RETURNING *; --- name: SetUserName :one -UPDATE users SET - name = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - --- name: SetUserPublicKey :one -UPDATE users SET - public_key = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - --- name: SetUserDescription :one -UPDATE users SET - description = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - --- name: SetUserPublicDMs :one -UPDATE users SET - is_public_dm = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - -- name: DeleteUser :exec UPDATE users SET is_deleted = true diff --git a/query/users_networks.sql b/query/users_networks.sql deleted file mode 100644 index bde3c3b..0000000 --- a/query/users_networks.sql +++ /dev/null @@ -1,65 +0,0 @@ --- name: GetNetworkBannedUsers :many -SELECT - sqlc.embed(users), - users_networks.ban_reason -FROM users_networks -JOIN users ON users.id = users_networks.user_id -WHERE users_networks.network_id = ?; - --- name: GetNetworkMembers :many -SELECT - sqlc.embed(users), - users_networks.joined_at, - users_networks.is_admin, - users_networks.is_muted -FROM users_networks -JOIN users ON users.id = users_networks.user_id -WHERE users_networks.network_id = ? AND is_member = true; - --- name: GetNetworkMemberById :one -SELECT * -FROM users_networks -WHERE users_networks.network_id = ? AND users_networks.user_id = ?; - --- name: GetUserNetworks :many -SELECT sqlc.embed(networks), users_networks.position FROM networks -JOIN users_networks ON networks.id = users_networks.network_id -WHERE users_networks.user_id = ? -ORDER BY users_networks.position; - --- name: GetUserNetwork :one -SELECT * FROM users_networks -WHERE user_id = ? AND network_id = ?; - --- name: SetMember :one -INSERT INTO users_networks ( - user_id, network_id, - is_member, is_admin, is_muted, - is_banned, ban_reason, position -) VALUES ( - @user_id, @network_id, - @is_member, @is_admin, @is_muted, - @is_banned, @ban_reason, - CASE - WHEN @is_member = false THEN NULL - ELSE (SELECT COUNT(*) FROM users_networks WHERE user_id = @user_id) - END -) -ON CONFLICT DO -UPDATE SET - is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted, - is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position -WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id -RETURNING *; - --- name: SwapUserNetworks :exec -UPDATE users_networks SET - position = CASE - WHEN position = @pos1 THEN @pos2 - WHEN position = @pos2 THEN @pos1 - END -WHERE user_id = @user_id AND position IN (@pos1, @pos2); - --- name: FilterUsersInNetwork :many -SELECT user_id FROM users_networks -WHERE network_id = ? AND user_id IN (sqlc.slice('users')); |