diff options
Diffstat (limited to 'internal')
| -rw-r--r-- | internal/data/frequencies.sql.go | 78 | ||||
| -rw-r--r-- | internal/data/members.sql.go | 224 | ||||
| -rw-r--r-- | internal/data/models.go | 45 | ||||
| -rw-r--r-- | internal/data/networks.sql.go | 126 | ||||
| -rw-r--r-- | internal/data/users.sql.go | 123 | ||||
| -rw-r--r-- | internal/server/api/database.go | 21 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109134844_users.sql | 12 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109135310_networks.sql | 13 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109135557_frequencies.sql | 24 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109140401_messages.sql | 18 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109141122_members.sql | 15 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql | 17 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109143707_on_user_delete.sql | 15 | ||||
| -rw-r--r-- | internal/server/api/migrations/20250109144057_indices.sql | 9 |
14 files changed, 389 insertions, 351 deletions
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 -} diff --git a/internal/server/api/database.go b/internal/server/api/database.go index 0617515..00e8d4d 100644 --- a/internal/server/api/database.go +++ b/internal/server/api/database.go @@ -2,19 +2,26 @@ package api import ( "database/sql" + "embed" "log" _ "github.com/mattn/go-sqlite3" + "github.com/pressly/goose/v3" "github.com/kyren223/eko/pkg/assert" ) +//go:embed migrations/*.sql +var embedMigrations embed.FS + var db *sql.DB func ConnectToDatabase() { var err error db, err = sql.Open("sqlite3", "file:server.db?cache=shared") - assert.NoError(err, "DB should always be accessible") + if err != nil { + log.Fatalln("unable to open db:", err) + } assert.AddFlush(db) log.Println("established connection with the database") @@ -29,6 +36,18 @@ func ConnectToDatabase() { assert.NoError(err, "DB pragmas should always execute with no errors") } + log.Println("opened database, running up migrations...") + + goose.SetBaseFS(embedMigrations) + if err := goose.SetDialect("sqlite3"); err != nil { + assert.NoError(err, "sqlite3 is a valid dialect that shouldn't error") + } + if err := goose.Up(db, "migrations"); err != nil { + db.Close() + log.Fatalln("error running up migrations:", err) + } + log.Println("database up migrations applied successfully") + log.Println("database connection ready to be used") } diff --git a/internal/server/api/migrations/20250109134844_users.sql b/internal/server/api/migrations/20250109134844_users.sql new file mode 100644 index 0000000..8ebb9eb --- /dev/null +++ b/internal/server/api/migrations/20250109134844_users.sql @@ -0,0 +1,12 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS users ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + public_key BLOB NOT NULL UNIQUE, + description TEXT NOT NULL DEFAULT '', + is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (false, true)) DEFAULT true, + is_deleted BOOLEAN NOT NULL CHECK (is_deleted IN (false, true)) DEFAULT false +); + +-- +goose Down +DROP TABLE IF EXISTS users; diff --git a/internal/server/api/migrations/20250109135310_networks.sql b/internal/server/api/migrations/20250109135310_networks.sql new file mode 100644 index 0000000..c3332c8 --- /dev/null +++ b/internal/server/api/migrations/20250109135310_networks.sql @@ -0,0 +1,13 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS networks ( + id INTEGER PRIMARY KEY, + owner_id INT NOT NULL REFERENCES users (id), + name TEXT NOT NULL, + icon TEXT NOT NULL, + bg_hex_color TEXT NOT NULL, + fg_hex_color TEXT NOT NULL, + is_public BOOLEAN NOT NULL CHECK (is_public IN (false, true)) +); + +-- +goose Down +DROP TABLE IF EXISTS networks; diff --git a/internal/server/api/migrations/20250109135557_frequencies.sql b/internal/server/api/migrations/20250109135557_frequencies.sql new file mode 100644 index 0000000..a444af1 --- /dev/null +++ b/internal/server/api/migrations/20250109135557_frequencies.sql @@ -0,0 +1,24 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS frequencies ( + id INTEGER PRIMARY KEY, + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + name TEXT NOT NULL, + hex_color TEXT NOT NULL, + perms INT NOT NULL CHECK (perms IN (0, 1, 2)), + -- 0 no access | 1 read | 2 read & write + position INT NOT NULL +); + +-- +goose StatementBegin +CREATE TRIGGER IF NOT EXISTS on_frequency_delete +AFTER DELETE ON frequencies +BEGIN + UPDATE frequencies SET + position = position - 1 + WHERE network_id = OLD.network_id AND position > OLD.position; +END +-- +goose StatementEnd + +-- +goose Down +DROP TABLE IF EXISTS frequencies; +DROP TRIGGER IF EXISTS on_frequency_delete; diff --git a/internal/server/api/migrations/20250109140401_messages.sql b/internal/server/api/migrations/20250109140401_messages.sql new file mode 100644 index 0000000..92f75f4 --- /dev/null +++ b/internal/server/api/migrations/20250109140401_messages.sql @@ -0,0 +1,18 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS messages ( + id INTEGER PRIMARY KEY, + sender_id INT NOT NULL REFERENCES users (id), + content TEXT NOT NULL, + edited BOOLEAN NOT NULL CHECK (edited IN (false, true)) DEFAULT false, + + frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE, + receiver_id INT REFERENCES users (id), + + CHECK ( + (frequency_id IS NOT NULL AND receiver_id IS NULL) OR + (frequency_id IS NULL AND receiver_id IS NOT NULL) + ) +); + +-- +goose Down +DROP TABLE IF EXISTS messages; diff --git a/internal/server/api/migrations/20250109141122_members.sql b/internal/server/api/migrations/20250109141122_members.sql new file mode 100644 index 0000000..61fba61 --- /dev/null +++ b/internal/server/api/migrations/20250109141122_members.sql @@ -0,0 +1,15 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS members ( + user_id INT NOT NULL REFERENCES users (id), + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + joined_at TEXT NOT NULL DEFAULT current_timestamp, + is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true, + is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false, + is_muted BOOLEAN NOT NULL CHECK (is_muted IN (false, true)) DEFAULT false, + is_banned BOOLEAN NOT NULL CHECK (is_banned IN (false, true)) DEFAULT false, + ban_reason TEXT, -- null ONLY if is_banned is false + PRIMARY KEY (user_id, network_id) +); + +-- +goose Down +DROP TABLE IF EXISTS members; diff --git a/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql new file mode 100644 index 0000000..8916378 --- /dev/null +++ b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql @@ -0,0 +1,17 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS trusted_users ( + trusting_user_id INT NOT NULL REFERENCES users (id), + trusted_user_id INT NOT NULL REFERENCES users (id), + trusted_public_key BLOB NOT NULL, + PRIMARY KEY (trusting_user_id, trusted_user_id) +); + +CREATE TABLE IF NOT EXISTS blocked_users ( + blocking_user_id INT NOT NULL REFERENCES users (id), + blocked_user_id INT NOT NULL REFERENCES users (id), + PRIMARY KEY (blocking_user_id, blocked_user_id) +); + +-- +goose Down +DROP TABLE IF EXISTS trusted_users; +DROP TABLE IF EXISTS blocked_users; diff --git a/internal/server/api/migrations/20250109143707_on_user_delete.sql b/internal/server/api/migrations/20250109143707_on_user_delete.sql new file mode 100644 index 0000000..940bec4 --- /dev/null +++ b/internal/server/api/migrations/20250109143707_on_user_delete.sql @@ -0,0 +1,15 @@ +-- +goose Up +-- +goose StatementBegin +CREATE TRIGGER on_user_delete +AFTER UPDATE OF is_deleted ON users +WHEN NEW.is_deleted = true +BEGIN + DELETE FROM networks WHERE owner_id = NEW.id; + DELETE FROM members WHERE user_id = NEW.id; + DELETE FROM trusted_users WHERE trusting_user_id = NEW.id OR trusted_user_id = NEW.id; + DELETE FROM blocked_users WHERE blocking_user_id = NEW.id OR blocked_user_id = NEW.id; +END +-- +goose StatementEnd + +-- +goose Down +DROP TRIGGER IF EXISTS on_user_delete; diff --git a/internal/server/api/migrations/20250109144057_indices.sql b/internal/server/api/migrations/20250109144057_indices.sql new file mode 100644 index 0000000..1732fb2 --- /dev/null +++ b/internal/server/api/migrations/20250109144057_indices.sql @@ -0,0 +1,9 @@ +-- +goose Up +CREATE INDEX IF NOT EXISTS idx_frequency_network ON frequencies (network_id); +CREATE INDEX IF NOT EXISTS idx_frequency_messages ON messages (frequency_id); +CREATE INDEX IF NOT EXISTS idx_direct_messages ON messages (sender_id, receiver_id); + +-- +goose Down +DROP INDEX IF EXISTS idx_frequency_network; +DROP INDEX IF EXISTS idx_frequency_messages; +DROP INDEX IF EXISTS idx_direct_messages; |