From 63a1d24295122728820a61aa93b3da6cd6d401d9 Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Sat, 23 Nov 2024 17:10:29 +0200 Subject: Updated user queries and added trigger on user delete --- internal/data/frequencies.sql.go | 22 ++++- internal/data/messages.sql.go | 6 +- internal/data/models.go | 44 +++++++-- internal/data/networks.sql.go | 36 ++++++-- internal/data/users.sql.go | 193 ++++++++++++++++++++++++++++++++++++--- 5 files changed, 269 insertions(+), 32 deletions(-) (limited to 'internal/data') diff --git a/internal/data/frequencies.sql.go b/internal/data/frequencies.sql.go index dacbf94..d978f1f 100644 --- a/internal/data/frequencies.sql.go +++ b/internal/data/frequencies.sql.go @@ -17,7 +17,7 @@ INSERT INTO frequencies ( ) VALUES ( ?, ?, ? ) -RETURNING id, network_id, name +RETURNING id, network_id, name, hex_color, perms, position ` type CreateFrequencyParams struct { @@ -29,12 +29,19 @@ type CreateFrequencyParams struct { func (q *Queries) CreateFrequency(ctx context.Context, arg CreateFrequencyParams) (Frequency, error) { row := q.db.QueryRowContext(ctx, createFrequency, arg.ID, arg.NetworkID, arg.Name) var i Frequency - err := row.Scan(&i.ID, &i.NetworkID, &i.Name) + err := row.Scan( + &i.ID, + &i.NetworkID, + &i.Name, + &i.HexColor, + &i.Perms, + &i.Position, + ) return i, err } const getNetworkFrequencies = `-- name: GetNetworkFrequencies :many -SELECT id, network_id, name FROM frequencies +SELECT id, network_id, name, hex_color, perms, position FROM frequencies WHERE network_id = ? ORDER BY id ` @@ -48,7 +55,14 @@ func (q *Queries) GetNetworkFrequencies(ctx context.Context, networkID snowflake var items []Frequency for rows.Next() { var i Frequency - if err := rows.Scan(&i.ID, &i.NetworkID, &i.Name); err != nil { + if err := rows.Scan( + &i.ID, + &i.NetworkID, + &i.Name, + &i.HexColor, + &i.Perms, + &i.Position, + ); err != nil { return nil, err } items = append(items, i) diff --git a/internal/data/messages.sql.go b/internal/data/messages.sql.go index e84b306..33e010d 100644 --- a/internal/data/messages.sql.go +++ b/internal/data/messages.sql.go @@ -17,7 +17,7 @@ INSERT INTO messages ( ) VALUES ( ?, ?, ?, ?, ? ) -RETURNING id, sender_id, content, frequency_id, receiver_id +RETURNING id, sender_id, content, edited, frequency_id, receiver_id ` type CreateMessageParams struct { @@ -41,6 +41,7 @@ func (q *Queries) CreateMessage(ctx context.Context, arg CreateMessageParams) (M &i.ID, &i.SenderID, &i.Content, + &i.Edited, &i.FrequencyID, &i.ReceiverID, ) @@ -48,7 +49,7 @@ func (q *Queries) CreateMessage(ctx context.Context, arg CreateMessageParams) (M } const listMessages = `-- name: ListMessages :many -SELECT id, sender_id, content, frequency_id, receiver_id FROM messages +SELECT id, sender_id, content, edited, frequency_id, receiver_id FROM messages ORDER BY id ` @@ -65,6 +66,7 @@ func (q *Queries) ListMessages(ctx context.Context) ([]Message, error) { &i.ID, &i.SenderID, &i.Content, + &i.Edited, &i.FrequencyID, &i.ReceiverID, ); err != nil { diff --git a/internal/data/models.go b/internal/data/models.go index 9d8f116..193b0f5 100644 --- a/internal/data/models.go +++ b/internal/data/models.go @@ -13,29 +13,61 @@ type Frequency struct { ID snowflake.ID NetworkID snowflake.ID Name string + HexColor *string + Perms int64 + Position int64 } type Message struct { ID snowflake.ID SenderID snowflake.ID Content string + Edited bool FrequencyID *snowflake.ID ReceiverID *snowflake.ID } type Network struct { - ID snowflake.ID - Name string - OwnerID snowflake.ID + ID snowflake.ID + OwnerID snowflake.ID + Name string + Icon string + BgHexColor *string + FgHexColor string + IsPublic bool +} + +type NetworkBannedUser struct { + NetworkID snowflake.ID + BannedUserID snowflake.ID + BannedAt string + Reason *string } type User struct { - ID snowflake.ID - Name string - PublicKey ed25519.PublicKey + ID snowflake.ID + Name string + PublicKey ed25519.PublicKey + Description *string + IsPublicDm bool + IsDeleted bool +} + +type UserBlockedUser struct { + BlockerUserID snowflake.ID + BlockedUserID snowflake.ID +} + +type UserTrustedUser struct { + TrusterUserID snowflake.ID + TrustedUserID snowflake.ID + TrustedPublicKey []byte } type UsersNetwork struct { UserID snowflake.ID NetworkID snowflake.ID + JoinedAt string + IsAdmin bool + IsMuted bool } diff --git a/internal/data/networks.sql.go b/internal/data/networks.sql.go index 1333863..efa468c 100644 --- a/internal/data/networks.sql.go +++ b/internal/data/networks.sql.go @@ -17,7 +17,7 @@ INSERT INTO networks ( ) VALUES ( ?, ?, ? ) -RETURNING id, name, owner_id +RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public ` type CreateNetworkParams struct { @@ -29,24 +29,40 @@ type CreateNetworkParams struct { func (q *Queries) CreateNetwork(ctx context.Context, arg CreateNetworkParams) (Network, error) { row := q.db.QueryRowContext(ctx, createNetwork, arg.ID, arg.Name, arg.OwnerID) var i Network - err := row.Scan(&i.ID, &i.Name, &i.OwnerID) + err := row.Scan( + &i.ID, + &i.OwnerID, + &i.Name, + &i.Icon, + &i.BgHexColor, + &i.FgHexColor, + &i.IsPublic, + ) return i, err } const getNetwork = `-- name: GetNetwork :one -SELECT id, name, owner_id FROM networks +SELECT id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public FROM networks WHERE id = ? ` func (q *Queries) GetNetwork(ctx context.Context, id snowflake.ID) (Network, error) { row := q.db.QueryRowContext(ctx, getNetwork, id) var i Network - err := row.Scan(&i.ID, &i.Name, &i.OwnerID) + err := row.Scan( + &i.ID, + &i.OwnerID, + &i.Name, + &i.Icon, + &i.BgHexColor, + &i.FgHexColor, + &i.IsPublic, + ) return i, err } const listNetworks = `-- name: ListNetworks :many -SELECT id, name, owner_id FROM networks +SELECT id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public FROM networks ORDER BY id ` @@ -59,7 +75,15 @@ func (q *Queries) ListNetworks(ctx context.Context) ([]Network, error) { var items []Network for rows.Next() { var i Network - if err := rows.Scan(&i.ID, &i.Name, &i.OwnerID); err != nil { + 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) diff --git a/internal/data/users.sql.go b/internal/data/users.sql.go index c92021d..7d2e065 100644 --- a/internal/data/users.sql.go +++ b/internal/data/users.sql.go @@ -18,7 +18,7 @@ INSERT INTO users ( ) VALUES ( ?, ?, ? ) -RETURNING id, name, public_key +RETURNING id, name, public_key, description, is_public_dm, is_deleted ` type CreateUserParams struct { @@ -30,39 +30,152 @@ type CreateUserParams struct { func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) { row := q.db.QueryRowContext(ctx, createUser, arg.ID, arg.Name, arg.PublicKey) var i User - err := row.Scan(&i.ID, &i.Name, &i.PublicKey) + err := row.Scan( + &i.ID, + &i.Name, + &i.PublicKey, + &i.Description, + &i.IsPublicDm, + &i.IsDeleted, + ) + return i, err +} + +const deleteUser = `-- name: DeleteUser :one +UPDATE users SET + is_deleted = true +WHERE id = ? AND is_deleted = false +RETURNING id, name, public_key, description, is_public_dm, is_deleted +` + +func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) (User, error) { + row := q.db.QueryRowContext(ctx, deleteUser, id) + var i User + err := row.Scan( + &i.ID, + &i.Name, + &i.PublicKey, + &i.Description, + &i.IsPublicDm, + &i.IsDeleted, + ) + return i, 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 FROM users -WHERE id = ? +SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users +WHERE id = ? AND is_deleted = false ` func (q *Queries) GetUserById(ctx context.Context, id snowflake.ID) (User, error) { row := q.db.QueryRowContext(ctx, getUserById, id) var i User - err := row.Scan(&i.ID, &i.Name, &i.PublicKey) + err := row.Scan( + &i.ID, + &i.Name, + &i.PublicKey, + &i.Description, + &i.IsPublicDm, + &i.IsDeleted, + ) return i, err } const getUserByPublicKey = `-- name: GetUserByPublicKey :one -SELECT id, name, public_key FROM users -WHERE public_key = ? +SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users +WHERE public_key = ? AND is_deleted = false ` func (q *Queries) GetUserByPublicKey(ctx context.Context, publicKey ed25519.PublicKey) (User, error) { row := q.db.QueryRowContext(ctx, getUserByPublicKey, publicKey) var i User - err := row.Scan(&i.ID, &i.Name, &i.PublicKey) + err := row.Scan( + &i.ID, + &i.Name, + &i.PublicKey, + &i.Description, + &i.IsPublicDm, + &i.IsDeleted, + ) + return i, err +} + +const serUserPublicDMs = `-- name: SerUserPublicDMs :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 SerUserPublicDMsParams struct { + IsPublicDm bool + ID snowflake.ID +} + +func (q *Queries) SerUserPublicDMs(ctx context.Context, arg SerUserPublicDMsParams) (User, error) { + row := q.db.QueryRowContext(ctx, serUserPublicDMs, 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 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 = ? -RETURNING id, name, public_key +WHERE id = ? AND is_deleted = false +RETURNING id, name, public_key, description, is_public_dm, is_deleted ` type SetUserNameParams struct { @@ -73,15 +186,22 @@ type SetUserNameParams struct { 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) + 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 = ? -RETURNING id, name, public_key +WHERE id = ? AND is_deleted = false +RETURNING id, name, public_key, description, is_public_dm, is_deleted ` type SetUserPublicKeyParams struct { @@ -92,6 +212,51 @@ type SetUserPublicKeyParams struct { 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) + err := row.Scan( + &i.ID, + &i.Name, + &i.PublicKey, + &i.Description, + &i.IsPublicDm, + &i.IsDeleted, + ) + return i, err +} + +const updateUser = `-- name: UpdateUser :one +UPDATE users SET + name = COALESCE(?, name), + public_key = COALESCE(?, public_key), + description = COALESCE(?, description), + is_public_dm = COALESCE(?, is_public_dm) +WHERE id = ? AND is_deleted = false +RETURNING id, name, public_key, description, is_public_dm, is_deleted +` + +type UpdateUserParams struct { + Name string + PublicKey ed25519.PublicKey + Description *string + IsPublicDm bool + ID snowflake.ID +} + +func (q *Queries) UpdateUser(ctx context.Context, arg UpdateUserParams) (User, error) { + row := q.db.QueryRowContext(ctx, updateUser, + arg.Name, + arg.PublicKey, + arg.Description, + 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 } -- cgit v1.3.1