// vote.go stores forum votes — forge-local social state (0001 §8), like
// GitHub stars: per-account, mutable, retractable, and never part of the
// artifact DAG or the sync protocol. Scores are always DERIVED from the
// vote rows at read; no denormalized counter exists to drift or race.
package oltp
import (
"fmt"
"strings"
"time"
)
const voteSchema = `
CREATE TABLE IF NOT EXISTS forum_vote (
repo TEXT NOT NULL,
account TEXT NOT NULL,
post TEXT NOT NULL, -- full forum-post artifact hash
thread TEXT NOT NULL, -- thread root, denormalized so list pages aggregate in one query
dir INTEGER NOT NULL CHECK (dir IN (-1, 1)),
at TEXT NOT NULL,
-- The key IS the idempotency: one row per voter per post. A changed
-- mind is an upsert, a retraction a delete.
PRIMARY KEY (repo, account, post)
);
CREATE INDEX IF NOT EXISTS forum_vote_by_post ON forum_vote (repo, post, dir);
CREATE INDEX IF NOT EXISTS forum_vote_by_thread ON forum_vote (repo, thread, dir);`
// SetForumVote records one account's vote on a post: dir 1 up, -1 down,
// 0 retracts. thread is the post's thread root, carried so thread-level
// aggregation never needs a join back to the index.
func (d *DB) SetForumVote(repo, account, post, thread string, dir int) error {
switch dir {
case 0:
_, err := d.w.Exec(`DELETE FROM forum_vote WHERE repo = ? AND account = ? AND post = ?`,
repo, account, post)
return err
case 1, -1:
_, err := d.w.Exec(`INSERT INTO forum_vote (repo, account, post, thread, dir, at)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (repo, account, post) DO UPDATE SET dir = excluded.dir, at = excluded.at`,
repo, account, post, thread, dir, time.Now().UTC().Format(time.RFC3339))
return err
}
return fmt.Errorf("oltp: vote direction %d: want -1, 0 or 1", dir)
}
// ForumPostScores returns net score per post for the posts that have
// votes; unvoted posts are absent, so callers render nothing rather
// than a meaningless zero.
func (d *DB) ForumPostScores(repo string, posts []string) (map[string]int, error) {
return d.voteSums(`SELECT post, sum(dir) FROM forum_vote
WHERE repo = ? AND post IN (`+placeholders(len(posts))+`) GROUP BY post`, repo, posts)
}
// ForumThreadScores returns net score per thread root, summed over the
// thread's posts.
func (d *DB) ForumThreadScores(repo string, threads []string) (map[string]int, error) {
return d.voteSums(`SELECT thread, sum(dir) FROM forum_vote
WHERE repo = ? AND thread IN (`+placeholders(len(threads))+`) GROUP BY thread`, repo, threads)
}
// ForumAccountVotes returns one account's own votes on the given posts,
// for rendering which arrow is lit. An empty account has none.
func (d *DB) ForumAccountVotes(repo, account string, posts []string) (map[string]int, error) {
if account == "" {
return map[string]int{}, nil
}
args := append([]any{repo, account}, toAny(posts)...)
rows, err := d.db.Query(`SELECT post, dir FROM forum_vote
WHERE repo = ? AND account = ? AND post IN (`+placeholders(len(posts))+`)`, args...)
if err != nil {
return nil, err
}
defer rows.Close()
out := map[string]int{}
for rows.Next() {
var p string
var n int
if err := rows.Scan(&p, &n); err != nil {
return nil, err
}
out[p] = n
}
return out, rows.Err()
}
func (d *DB) voteSums(query, repo string, keys []string) (map[string]int, error) {
if len(keys) == 0 {
return map[string]int{}, nil
}
args := append([]any{repo}, toAny(keys)...)
rows, err := d.db.Query(query, args...)
if err != nil {
return nil, err
}
defer rows.Close()
out := map[string]int{}
for rows.Next() {
var k string
var n int
if err := rows.Scan(&k, &n); err != nil {
return nil, err
}
out[k] = n
}
return out, rows.Err()
}
func placeholders(n int) string {
if n == 0 {
return "''" // IN ('') matches nothing; keeps the SQL well-formed
}
return strings.TrimSuffix(strings.Repeat("?,", n), ",")
}
func toAny(ss []string) []any {
out := make([]any, len(ss))
for i, s := range ss {
out[i] = s
}
return out
}