From 936d3cf690ace9adfdfe0fb44193c11a0cb26d95 Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Thu, 9 Jan 2025 17:28:46 +0200 Subject: Added goose for migrations, refactored SQL schema, server and client are broken --- internal/data/frequencies.sql.go | 78 -------------- internal/data/members.sql.go | 224 +++++++++++++++++++++++++++++++++++++++ internal/data/models.go | 45 ++++---- internal/data/networks.sql.go | 126 ---------------------- internal/data/users.sql.go | 123 --------------------- 5 files changed, 246 insertions(+), 350 deletions(-) create mode 100644 internal/data/members.sql.go (limited to 'internal/data') diff --git a/internal/data/frequencies.sql.go b/internal/data/frequencies.sql.go index 024c069..9ee8994 100644 --- a/internal/data/frequencies.sql.go +++ b/internal/data/frequencies.sql.go @@ -115,84 +115,6 @@ func (q *Queries) GetNetworkFrequencies(ctx context.Context, networkID snowflake return items, nil } -const setFrequencyColor = `-- name: SetFrequencyColor :one -UPDATE frequencies SET - hex_color = ? -WHERE id = ? -RETURNING id, network_id, name, hex_color, perms, position -` - -type SetFrequencyColorParams struct { - HexColor string - ID snowflake.ID -} - -func (q *Queries) SetFrequencyColor(ctx context.Context, arg SetFrequencyColorParams) (Frequency, error) { - row := q.db.QueryRowContext(ctx, setFrequencyColor, arg.HexColor, arg.ID) - var i Frequency - err := row.Scan( - &i.ID, - &i.NetworkID, - &i.Name, - &i.HexColor, - &i.Perms, - &i.Position, - ) - return i, err -} - -const setFrequencyName = `-- name: SetFrequencyName :one -UPDATE frequencies SET - name = ? -WHERE id = ? -RETURNING id, network_id, name, hex_color, perms, position -` - -type SetFrequencyNameParams struct { - Name string - ID snowflake.ID -} - -func (q *Queries) SetFrequencyName(ctx context.Context, arg SetFrequencyNameParams) (Frequency, error) { - row := q.db.QueryRowContext(ctx, setFrequencyName, arg.Name, arg.ID) - var i Frequency - err := row.Scan( - &i.ID, - &i.NetworkID, - &i.Name, - &i.HexColor, - &i.Perms, - &i.Position, - ) - return i, err -} - -const setFrequencyPerms = `-- name: SetFrequencyPerms :one -UPDATE frequencies SET - perms = ? -WHERE id = ? -RETURNING id, network_id, name, hex_color, perms, position -` - -type SetFrequencyPermsParams struct { - Perms int64 - ID snowflake.ID -} - -func (q *Queries) SetFrequencyPerms(ctx context.Context, arg SetFrequencyPermsParams) (Frequency, error) { - row := q.db.QueryRowContext(ctx, setFrequencyPerms, arg.Perms, arg.ID) - var i Frequency - err := row.Scan( - &i.ID, - &i.NetworkID, - &i.Name, - &i.HexColor, - &i.Perms, - &i.Position, - ) - return i, err -} - const swapFrequencies = `-- name: SwapFrequencies :exec UPDATE frequencies SET position = CASE diff --git a/internal/data/members.sql.go b/internal/data/members.sql.go new file mode 100644 index 0000000..c9932cc --- /dev/null +++ b/internal/data/members.sql.go @@ -0,0 +1,224 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.27.0 +// source: members.sql + +package data + +import ( + "context" + "strings" + + "github.com/kyren223/eko/pkg/snowflake" +) + +const filterUsersInNetwork = `-- name: FilterUsersInNetwork :many +SELECT user_id FROM members +WHERE network_id = ? AND user_id IN (/*SLICE:users*/?) +` + +type FilterUsersInNetworkParams struct { + NetworkID snowflake.ID + Users []snowflake.ID +} + +func (q *Queries) FilterUsersInNetwork(ctx context.Context, arg FilterUsersInNetworkParams) ([]snowflake.ID, error) { + query := filterUsersInNetwork + var queryParams []interface{} + queryParams = append(queryParams, arg.NetworkID) + if len(arg.Users) > 0 { + for _, v := range arg.Users { + queryParams = append(queryParams, v) + } + query = strings.Replace(query, "/*SLICE:users*/?", strings.Repeat(",?", len(arg.Users))[1:], 1) + } else { + query = strings.Replace(query, "/*SLICE:users*/?", "NULL", 1) + } + rows, err := q.db.QueryContext(ctx, query, queryParams...) + if err != nil { + return nil, err + } + defer rows.Close() + var items []snowflake.ID + for rows.Next() { + var user_id snowflake.ID + if err := rows.Scan(&user_id); err != nil { + return nil, err + } + items = append(items, user_id) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getMemberById = `-- name: GetMemberById :one +SELECT user_id, network_id, joined_at, is_member, is_admin, is_muted, is_banned, ban_reason FROM members +WHERE network_id = ? AND user_id = ? +` + +type GetMemberByIdParams struct { + NetworkID snowflake.ID + UserID snowflake.ID +} + +func (q *Queries) GetMemberById(ctx context.Context, arg GetMemberByIdParams) (Member, error) { + row := q.db.QueryRowContext(ctx, getMemberById, arg.NetworkID, arg.UserID) + var i Member + err := row.Scan( + &i.UserID, + &i.NetworkID, + &i.JoinedAt, + &i.IsMember, + &i.IsAdmin, + &i.IsMuted, + &i.IsBanned, + &i.BanReason, + ) + return i, err +} + +const getMembers = `-- name: GetMembers :many +SELECT + users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted, + members.user_id, members.network_id, members.joined_at, members.is_member, members.is_admin, members.is_muted, members.is_banned, members.ban_reason +FROM members +JOIN users ON users.id = members.user_id +WHERE network_id = ? AND is_member = true +` + +type GetMembersRow struct { + User User + Member Member +} + +func (q *Queries) GetMembers(ctx context.Context, networkID snowflake.ID) ([]GetMembersRow, error) { + rows, err := q.db.QueryContext(ctx, getMembers, networkID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetMembersRow + for rows.Next() { + var i GetMembersRow + if err := rows.Scan( + &i.User.ID, + &i.User.Name, + &i.User.PublicKey, + &i.User.Description, + &i.User.IsPublicDM, + &i.User.IsDeleted, + &i.Member.UserID, + &i.Member.NetworkID, + &i.Member.JoinedAt, + &i.Member.IsMember, + &i.Member.IsAdmin, + &i.Member.IsMuted, + &i.Member.IsBanned, + &i.Member.BanReason, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getNetworksOfUser = `-- name: GetNetworksOfUser :many +SELECT networks.id, networks.owner_id, networks.name, networks.icon, networks.bg_hex_color, networks.fg_hex_color, networks.is_public FROM networks +JOIN members ON networks.id = members.network_id +WHERE members.user_id = ? +` + +func (q *Queries) GetNetworksOfUser(ctx context.Context, userID snowflake.ID) ([]Network, error) { + rows, err := q.db.QueryContext(ctx, getNetworksOfUser, userID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []Network + for rows.Next() { + var i Network + if err := rows.Scan( + &i.ID, + &i.OwnerID, + &i.Name, + &i.Icon, + &i.BgHexColor, + &i.FgHexColor, + &i.IsPublic, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const setMember = `-- name: SetMember :one +INSERT INTO members ( + user_id, network_id, + is_member, is_admin, is_muted, + is_banned, ban_reason +) VALUES ( + ?1, ?2, + ?3, ?4, ?5, + ?6, ?7 +) +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 user_id, network_id, joined_at, is_member, is_admin, is_muted, is_banned, ban_reason +` + +type SetMemberParams struct { + UserID snowflake.ID + NetworkID snowflake.ID + IsMember bool + IsAdmin bool + IsMuted bool + IsBanned bool + BanReason *string +} + +func (q *Queries) SetMember(ctx context.Context, arg SetMemberParams) (Member, error) { + row := q.db.QueryRowContext(ctx, setMember, + arg.UserID, + arg.NetworkID, + arg.IsMember, + arg.IsAdmin, + arg.IsMuted, + arg.IsBanned, + arg.BanReason, + ) + var i Member + err := row.Scan( + &i.UserID, + &i.NetworkID, + &i.JoinedAt, + &i.IsMember, + &i.IsAdmin, + &i.IsMuted, + &i.IsBanned, + &i.BanReason, + ) + return i, err +} diff --git a/internal/data/models.go b/internal/data/models.go index 3b5425f..bb0b271 100644 --- a/internal/data/models.go +++ b/internal/data/models.go @@ -9,6 +9,11 @@ import ( "github.com/kyren223/eko/pkg/snowflake" ) +type BlockedUser struct { + BlockingUserID snowflake.ID + BlockedUserID snowflake.ID +} + type Frequency struct { ID snowflake.ID NetworkID snowflake.ID @@ -18,6 +23,17 @@ type Frequency struct { Position int64 } +type Member struct { + UserID snowflake.ID + NetworkID snowflake.ID + JoinedAt string + IsMember bool + IsAdmin bool + IsMuted bool + IsBanned bool + BanReason *string +} + type Message struct { ID snowflake.ID SenderID snowflake.ID @@ -37,6 +53,12 @@ type Network struct { IsPublic bool } +type TrustedUser struct { + TrustingUserID snowflake.ID + TrustedUserID snowflake.ID + TrustedPublicKey []byte +} + type User struct { ID snowflake.ID Name string @@ -45,26 +67,3 @@ type User struct { IsPublicDM bool IsDeleted bool } - -type UserBlockedUser struct { - BlockerUserID snowflake.ID - BlockedUserID snowflake.ID -} - -type UserNetwork struct { - UserID snowflake.ID - NetworkID snowflake.ID - JoinedAt string - IsMember bool - IsAdmin bool - IsMuted bool - IsBanned bool - BanReason *string - Position *int64 -} - -type UserTrustedUser struct { - TrusterUserID snowflake.ID - TrustedUserID snowflake.ID - TrustedPublicKey []byte -} diff --git a/internal/data/networks.sql.go b/internal/data/networks.sql.go index 9adeeb8..5091602 100644 --- a/internal/data/networks.sql.go +++ b/internal/data/networks.sql.go @@ -84,132 +84,6 @@ func (q *Queries) GetNetworkById(ctx context.Context, id snowflake.ID) (Network, return i, err } -const getPublicNetworks = `-- name: GetPublicNetworks :many -SELECT id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public FROM networks -WHERE is_public = true -` - -func (q *Queries) GetPublicNetworks(ctx context.Context) ([]Network, error) { - rows, err := q.db.QueryContext(ctx, getPublicNetworks) - if err != nil { - return nil, err - } - defer rows.Close() - var items []Network - for rows.Next() { - var i Network - if err := rows.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ); err != nil { - return nil, err - } - items = append(items, i) - } - if err := rows.Close(); err != nil { - return nil, err - } - if err := rows.Err(); err != nil { - return nil, err - } - return items, nil -} - -const setNetworkIcon = `-- name: SetNetworkIcon :one -UPDATE networks SET - icon = ?, - bg_hex_color = ?, - fg_hex_color = ? -WHERE id = ? -RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public -` - -type SetNetworkIconParams struct { - Icon string - BgHexColor string - FgHexColor string - ID snowflake.ID -} - -func (q *Queries) SetNetworkIcon(ctx context.Context, arg SetNetworkIconParams) (Network, error) { - row := q.db.QueryRowContext(ctx, setNetworkIcon, - arg.Icon, - arg.BgHexColor, - arg.FgHexColor, - arg.ID, - ) - var i Network - err := row.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ) - return i, err -} - -const setNetworkIsPublic = `-- name: SetNetworkIsPublic :one -UPDATE networks SET - is_public = ? -WHERE id = ? -RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public -` - -type SetNetworkIsPublicParams struct { - IsPublic bool - ID snowflake.ID -} - -func (q *Queries) SetNetworkIsPublic(ctx context.Context, arg SetNetworkIsPublicParams) (Network, error) { - row := q.db.QueryRowContext(ctx, setNetworkIsPublic, arg.IsPublic, arg.ID) - var i Network - err := row.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ) - return i, err -} - -const setNetworkName = `-- name: SetNetworkName :one -UPDATE networks SET - name = ? -WHERE id = ? -RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public -` - -type SetNetworkNameParams struct { - Name string - ID snowflake.ID -} - -func (q *Queries) SetNetworkName(ctx context.Context, arg SetNetworkNameParams) (Network, error) { - row := q.db.QueryRowContext(ctx, setNetworkName, arg.Name, arg.ID) - var i Network - err := row.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ) - return i, err -} - const transferNetwork = `-- name: TransferNetwork :one UPDATE networks SET owner_id = ? diff --git a/internal/data/users.sql.go b/internal/data/users.sql.go index 33eab3f..f7a7f9a 100644 --- a/internal/data/users.sql.go +++ b/internal/data/users.sql.go @@ -52,25 +52,6 @@ func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) error { return err } -const getDeletedUserById = `-- name: GetDeletedUserById :one -SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users -WHERE id = ? AND is_deleted = true -` - -func (q *Queries) GetDeletedUserById(ctx context.Context, id snowflake.ID) (User, error) { - row := q.db.QueryRowContext(ctx, getDeletedUserById, id) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - const getUserById = `-- name: GetUserById :one SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users WHERE id = ? AND is_deleted = false @@ -108,107 +89,3 @@ func (q *Queries) GetUserByPublicKey(ctx context.Context, publicKey ed25519.Publ ) return i, err } - -const setUserDescription = `-- name: SetUserDescription :one -UPDATE users SET - description = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserDescriptionParams struct { - Description string - ID snowflake.ID -} - -func (q *Queries) SetUserDescription(ctx context.Context, arg SetUserDescriptionParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserDescription, arg.Description, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - -const setUserName = `-- name: SetUserName :one -UPDATE users SET - name = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserNameParams struct { - Name string - ID snowflake.ID -} - -func (q *Queries) SetUserName(ctx context.Context, arg SetUserNameParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserName, arg.Name, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - -const setUserPublicDMs = `-- name: SetUserPublicDMs :one -UPDATE users SET - is_public_dm = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserPublicDMsParams struct { - IsPublicDM bool - ID snowflake.ID -} - -func (q *Queries) SetUserPublicDMs(ctx context.Context, arg SetUserPublicDMsParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserPublicDMs, arg.IsPublicDM, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - -const setUserPublicKey = `-- name: SetUserPublicKey :one -UPDATE users SET - public_key = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserPublicKeyParams struct { - PublicKey ed25519.PublicKey - ID snowflake.ID -} - -func (q *Queries) SetUserPublicKey(ctx context.Context, arg SetUserPublicKeyParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserPublicKey, arg.PublicKey, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} -- cgit v1.3.1