Documentation
¶
Overview ¶
Package database owns the SQLite connection and exposes typed CRUD for each table. Migrations run automatically on Open.
Index ¶
- Variables
- type ApiURL
- type BannedUser
- type CommandUsage
- type DB
- func (db *DB) BanUser(ctx context.Context, discordUserID, reason, bannedBy string) (created bool, err error)
- func (db *DB) CachedGuildPrefix(discordGuildID string) (string, bool)
- func (db *DB) CommandUsageCount(ctx context.Context, commandName string) (int64, error)
- func (db *DB) CreateGuild(ctx context.Context, discordGuildID string) (*Guild, error)
- func (db *DB) CreateUser(ctx context.Context, discordUserID string, guildID int64) (*User, error)
- func (db *DB) EnsureGuildExists(ctx context.Context, discordGuildID string, audioEnabledOnInsert bool) error
- func (db *DB) EnsureUser(ctx context.Context, discordGuildID, discordUserID string) (*User, error)
- func (db *DB) ForgetUser(ctx context.Context, discordUserID string) (int64, error)
- func (db *DB) GetApiURL(ctx context.Context, name string) (string, error)
- func (db *DB) GetApiURLRow(ctx context.Context, name string) (*ApiURL, error)
- func (db *DB) GetBannedUser(ctx context.Context, discordUserID string) (*BannedUser, error)
- func (db *DB) GetGuildPrefixOverride(ctx context.Context, discordGuildID string) (string, error)
- func (db *DB) GetRecentUserRatings(ctx context.Context, userID int64, limit int) ([]*UserRating, error)
- func (db *DB) GetUserByDiscordID(ctx context.Context, discordUserID string, guildID int64) (*User, error)
- func (db *DB) GetUserCommandTotal(ctx context.Context, userID int64) (int64, error)
- func (db *DB) GetUserRatings(ctx context.Context, userID int64) ([]*UserRating, error)
- func (db *DB) GetUserTopCommand(ctx context.Context, userID int64) (string, int64, error)
- func (db *DB) GuildByDiscordID(ctx context.Context, discordGuildID string) (*Guild, error)
- func (db *DB) GuildByID(ctx context.Context, id int64) (*Guild, error)
- func (db *DB) IncrementCommandUsage(ctx context.Context, commandName string) error
- func (db *DB) IncrementUserCommandUsage(ctx context.Context, discordGuildID, discordUserID, commandName string) error
- func (db *DB) IsGuildAudioEnabled(ctx context.Context, discordGuildID string) (bool, error)
- func (db *DB) IsUserBanned(ctx context.Context, discordUserID string) (banned bool, reason string, err error)
- func (db *DB) ListBannedUsersInGuild(ctx context.Context, discordGuildID string) ([]*BannedUser, error)
- func (db *DB) MarkGuildJoined(ctx context.Context, discordGuildID string) error
- func (db *DB) MarkGuildLeft(ctx context.Context, discordGuildID string) error
- func (db *DB) RecordUserCommandUsage(ctx context.Context, discordGuildID, discordUserID, commandName string) error
- func (db *DB) SetGuildPrefixOverride(ctx context.Context, discordGuildID, prefix string) error
- func (db *DB) SetUserRating(ctx context.Context, userID int64, name string, value int) error
- func (db *DB) TrackCommandInvocation(ctx context.Context, usageKey, discordGuildID, discordUserID string) error
- func (db *DB) UnbanUser(ctx context.Context, discordUserID string) (removed bool, err error)
- func (db *DB) WelcomeNeeded(ctx context.Context, discordGuildID string) (bool, error)
- type Guild
- type User
- type UserCommandUsage
- type UserRating
Constants ¶
This section is empty.
Variables ¶
var ErrApiURLNotAvailable = errors.New("api url not available")
ErrApiURLNotAvailable is returned when no row matches the requested ApiName, or the row exists but IsActive is false.
Functions ¶
This section is empty.
Types ¶
type ApiURL ¶
type ApiURL struct {
ID int64 `db:"ID"`
ApiName string `db:"ApiName"`
ApiURL string `db:"ApiURL"`
Description sql.NullString `db:"Description"`
IsActive bool `db:"IsActive"`
CreatedAt string `db:"CreatedAt"`
UpdatedAt sql.NullString `db:"UpdatedAt"`
}
ApiURL mirrors a row in the ApiURL table.
type BannedUser ¶
type BannedUser struct {
ID int64 `db:"ID"`
DiscordUserID string `db:"Discord_UserID"`
Reason sql.NullString `db:"Reason"`
BannedAt string `db:"BannedAt"`
BannedBy sql.NullString `db:"BannedBy"`
}
BannedUser is one row in the BannedUser table. Discord_UserID is UNIQUE so a user has at most one ban regardless of guild count — bans are global. Reason and BannedBy are nullable for flexibility (pre-emptive bans don't need a reason; bans issued via direct DB edit may have no recorded issuer).
type CommandUsage ¶
type CommandUsage struct {
ID int64 `db:"ID"`
CommandName string `db:"CommandName"`
UsageCount int64 `db:"UsageCount"`
LastUsedAt string `db:"LastUsedAt"`
}
CommandUsage mirrors a row in the CommandUsage table — an aggregate invocation count per top-level command.
type DB ¶
DB wraps *sqlx.DB so callers get struct-scanning (Get/Select) for free while keeping the embedded *sql.DB methods (Exec, QueryRow, Begin) available.
func Open ¶
Open dials the SQLite file at path (":memory:" for tests), applies the required pragmas, and runs any pending migrations.
func (*DB) BanUser ¶
func (db *DB) BanUser(ctx context.Context, discordUserID, reason, bannedBy string) (created bool, err error)
BanUser inserts or updates a ban row. Returns (true, nil) when a new ban was created, (false, nil) when an existing ban's metadata was refreshed. Idempotent — issuing a ban twice updates the reason/issuer/timestamp on the second call rather than erroring.
func (*DB) CachedGuildPrefix ¶
CachedGuildPrefix returns the in-memory cached prefix for a guild, or ("", false) on a miss. Pure map read — no context, no DB. The per-message fast path: callers fall back to GetGuildPrefixOverride only on a miss.
func (*DB) CommandUsageCount ¶
CommandUsageCount returns the recorded count for commandName (0 if unseen).
func (*DB) CreateGuild ¶
CreateGuild inserts a new row with defaults applied for everything but the Discord ID. RETURNING gives us the populated row back in one round trip.
func (*DB) CreateUser ¶
CreateUser inserts a (Discord user, guild) row. The guild must already exist — the FK constraint is enforced.
func (*DB) EnsureGuildExists ¶
func (db *DB) EnsureGuildExists(ctx context.Context, discordGuildID string, audioEnabledOnInsert bool) error
EnsureGuildExists inserts a row only if one isn't already present. Used at bot startup to seed known guilds without clobbering admin changes from previous runs.
func (*DB) EnsureUser ¶
EnsureUser guarantees a (guild, user) row exists and returns it. It creates the Guild row first when needed (the FK requires it), defaulting new guilds to audio-disabled. Both inserts are idempotent, so repeat calls are cheap no-ops that never overwrite existing Dosh / IsDayOne values.
func (*DB) ForgetUser ¶
ForgetUser hard-deletes every row for this Discord user across all guilds — the privacy "right to be forgotten" path. Returns the number of rows removed (0 if the user had no stored data).
func (*DB) GetApiURL ¶
GetApiURL returns the URL string for the given ApiName, but only when IsActive is true. Missing names and inactive rows both return ErrApiURLNotAvailable so callers can fail uniformly.
func (*DB) GetApiURLRow ¶
GetApiURLRow returns the full ApiURL row including inactive entries. Useful for admin-style introspection; callers wanting just the URL with activeness enforced should use GetApiURL.
func (*DB) GetBannedUser ¶
GetBannedUser returns the ban row for the given user, or (nil, nil) if no ban is recorded. The (nil, nil) case is the common one and is NOT an error.
func (*DB) GetGuildPrefixOverride ¶
GetGuildPrefixOverride returns the guild's command prefix: the per-guild override when set, otherwise defaultPrefix. Memoized — the first lookup per guild hits the DB, later ones read the cache (this runs on every message). Any DB problem degrades to the default, returned alongside the error so the caller keeps a usable value while still able to log.
func (*DB) GetRecentUserRatings ¶
func (db *DB) GetRecentUserRatings(ctx context.Context, userID int64, limit int) ([]*UserRating, error)
GetRecentUserRatings returns the N most recently updated ratings for the user, freshest first. Powers the recent-3 corner on /user profile.
func (*DB) GetUserByDiscordID ¶
func (db *DB) GetUserByDiscordID(ctx context.Context, discordUserID string, guildID int64) (*User, error)
GetUserByDiscordID returns the row for this Discord user within the given guild context, or nil when no match exists.
func (*DB) GetUserCommandTotal ¶
GetUserCommandTotal returns the sum of UsageCount across every command this user has recorded. COALESCE keeps "no rows at all" returning 0 cleanly.
func (*DB) GetUserRatings ¶
GetUserRatings returns every stored rating for the user, sorted by name — stable ordering for the future "all ratings" profile page.
func (*DB) GetUserTopCommand ¶
GetUserTopCommand returns the user's most-used command name + its count. When a user has no usage rows yet, returns ("", 0, nil) — caller treats that as the "no commands recorded" rendering case. Ties on UsageCount break by freshest LastUsedAt so a recent burst surfaces over an old equal-count one.
func (*DB) GuildByDiscordID ¶
GuildByDiscordID returns nil (without error) when no row matches.
func (*DB) GuildByID ¶
GuildByID looks up by our internal surrogate ID. Useful when following a foreign key from another row.
func (*DB) IncrementCommandUsage ¶
IncrementCommandUsage bumps the count for commandName, creating the row on first use. Atomic upsert — safe to call on every invocation.
func (*DB) IncrementUserCommandUsage ¶
func (db *DB) IncrementUserCommandUsage(ctx context.Context, discordGuildID, discordUserID, commandName string) error
IncrementUserCommandUsage bumps the per-(user, command) counter. The SELECT-driven INSERT is defense-in-depth: callers should use RecordUserCommandUsage (which ensures the User row first), but if EnsureUser is ever skipped or fails silently upstream, a missing user still produces a clean no-op rather than a constraint violation.
func (*DB) IsGuildAudioEnabled ¶
IsGuildAudioEnabled returns the AudioEnabled flag for the row matching discordGuildID. A missing row returns (false, nil) — unknown guilds default to disabled.
func (*DB) IsUserBanned ¶
func (db *DB) IsUserBanned(ctx context.Context, discordUserID string) (banned bool, reason string, err error)
IsUserBanned reports whether the given Discord user ID has an active ban, along with the recorded reason ("" when the ban has none — reasons are optional, e.g. pre-emptive bans). Runs in the dispatch hot path (every slash + prefix invocation), so keep the query single-row and indexed (Discord_UserID is UNIQUE → automatic index). Empty input short-circuits to (false, "", nil) without a DB round trip.
func (*DB) ListBannedUsersInGuild ¶
func (db *DB) ListBannedUsersInGuild(ctx context.Context, discordGuildID string) ([]*BannedUser, error)
ListBannedUsersInGuild returns banned users who have a User row tied to the given guild — i.e., users who have invoked the bot in this guild at least once. Used by /admin banned-list so server admins see only bans relevant to their server, not the global list. Ordered newest-first.
Trade-off: a user banned pre-emptively who has never used the bot in this guild won't appear here. That's the right behavior — they're not present in the guild's bot-user pool. The maintainer can still see the global list via DBeaver / direct DB query if they need it.
func (*DB) MarkGuildJoined ¶
MarkGuildJoined records that the bot is present in the guild: it creates the row if missing and clears LeftAt either way. Called from GuildCreate, which fires for every guild on connect (backfill) and on each new join.
func (*DB) MarkGuildLeft ¶
MarkGuildLeft stamps LeftAt without deleting anything, so per-guild data survives a removal and is restored if the bot rejoins. No-op if the guild has no row.
func (*DB) RecordUserCommandUsage ¶
func (db *DB) RecordUserCommandUsage(ctx context.Context, discordGuildID, discordUserID, commandName string) error
RecordUserCommandUsage materializes the (guild, user) row if it doesn't exist yet, then bumps the per-(user, command) counter. Called for every successful slash-command invocation so /user profile reflects accurate counts from the user's first interaction onward.
func (*DB) SetGuildPrefixOverride ¶
SetGuildPrefixOverride sets the guild's prefix, or resets to the default when prefix is empty (stored as NULL). Upserts so a missing row is created rather than silently skipped, and updates the cache so the change is live immediately.
func (*DB) SetUserRating ¶
SetUserRating upserts the latest score for a (user, rating) pair, bumping UpdatedAt every time. UNIQUE(UserID, RatingName) means there's only ever one "current" value per rating type per user — no history is retained.
func (*DB) TrackCommandInvocation ¶
func (db *DB) TrackCommandInvocation(ctx context.Context, usageKey, discordGuildID, discordUserID string) error
TrackCommandInvocation records one command invocation end-to-end: bumps the bot-wide aggregate (CommandUsage), then materializes the (guild, user) row and bumps their per-(user, command) counter (UserCommandUsage). Both the slash dispatcher (events.CommandHandler) and the prefix dispatcher (prefix.ParsePrefixCmds) call this so every invocation lands in both counters with one call.
Empty discordGuildID or discordUserID skips the per-user step (DM context or bot invoker — the caller is expected to zero discordUserID for bots). The aggregate always fires when usageKey is non-empty.
func (*DB) UnbanUser ¶
UnbanUser deletes the ban row, if any. Returns true when a row was deleted (the user was previously banned), false when no row existed. Does NOT touch the User table — the user's profile, ratings, and command history survive the ban/unban cycle so they pick up where they left off.
func (*DB) WelcomeNeeded ¶
WelcomeNeeded reports whether GuildCreate should fire a welcome message for this guild. Returns true on first-ever sighting (no row exists) and on rejoin (row exists with LeftAt set); false on reconnect/backfill (row exists, LeftAt is NULL). Must be called BEFORE MarkGuildJoined, which clears LeftAt and would destroy the rejoin signal.
type Guild ¶
type Guild struct {
ID int64 `db:"ID"`
DiscordGuildID string `db:"Discord_GuildID"`
AudioEnabled bool `db:"AudioEnabled"`
PrefixOverride sql.NullString `db:"PrefixOverride"`
DiscordEventNotifChannelID sql.NullString `db:"Discord_EventNotifChannelID"`
JoinedAt string `db:"JoinedAt"`
LeftAt sql.NullString `db:"LeftAt"` // NULL while the bot is in the guild
}
Guild mirrors a row in the Guild table. db tags map struct fields to column names so sqlx can populate the struct via Get/Select.
type User ¶
type User struct {
ID int64 `db:"ID"`
DiscordUserID string `db:"Discord_UserID"`
GuildID int64 `db:"GuildID"`
Dosh int64 `db:"Dosh"`
IsDayOne bool `db:"IsDayOne"`
CreatedAt string `db:"CreatedAt"`
}
User is a (Discord user, guild) pair. The same Discord user appears as multiple rows if they're tracked in multiple guilds.
type UserCommandUsage ¶
type UserCommandUsage struct {
ID int64 `db:"ID"`
UserID int64 `db:"UserID"`
CommandName string `db:"CommandName"`
UsageCount int64 `db:"UsageCount"`
LastUsedAt string `db:"LastUsedAt"`
}
UserCommandUsage is one (user, command) invocation counter. The dispatcher calls RecordUserCommandUsage, which ensures the User row exists before bumping the counter — so every slash command from the user's first invocation onward is counted accurately.
type UserRating ¶
type UserRating struct {
ID int64 `db:"ID"`
UserID int64 `db:"UserID"`
RatingName string `db:"RatingName"`
Value int `db:"Value"`
UpdatedAt string `db:"UpdatedAt"`
}
UserRating is one /rate-this score stored against a (user, guild) pair. One row per (UserID, RatingName); new ratings overwrite the previous value.
Source Files
¶
Directories
¶
| Path | Synopsis |
|---|---|
|
Package migrations holds the SQL files goose runs at startup.
|
Package migrations holds the SQL files goose runs at startup. |