bbj2/server/cmd/db/db.go

package db

import (
	"database/sql"
	_ "embed"
	"errors"
	"fmt"
	"os"
	"strings"
	"time"

	"git.tilde.town/tildetown/bbj2/server/cmd/config"
	"github.com/google/uuid"
	_ "github.com/mattn/go-sqlite3"
)

//go:embed schema.sql
var schemaSQL string

// TODO I'm not sold on this hash system; without transport encryption, it
// doesn't really help anything. I'd rather have plaintext + transport
// encryption and then, on the server side, proper salted hashing. I can't
// figure out if there was a reason for this approach that I'm just
// overlooking.
type User struct {
	ID       string
	Username string
	Hash     string
	Created  time.Time
}

type Thread struct {
	ID         string `json:"thread_id"`
	Author     string
	Title      string
	LastMod    time.Time `json:"last_mod"`
	Created    time.Time
	ReplyCount int    `json:"reply_count"`
	Pinned     int    // TODO bool
	LastAuthor string `json:"last_author"`
	Messages   []Message
}

type Message struct {
	ThreadID string `json:"thread_id"`
	PostID   string `json:"post_id"`
	Author   string
	Created  time.Time
	Edited   int // TODO bool
	Body     string
	SendRaw  int `json:"send_raw"` // TODO bool
}

func Setup(opts *config.Options) (func(), error) {
	db, err := sql.Open("sqlite3", opts.Config.DBPath)
	opts.DB = db
	// TODO consider inlining EnsureSchema here
	return func() { db.Close() }, err
}

func EnsureSchema(opts config.Options) error {
	db := opts.DB

	if opts.Reset {
		err := os.Remove(opts.Config.DBPath)
		if err != nil {
			return fmt.Errorf("failed to delete database: %w", err)
		}
	}
	rows, err := db.Query("select version from meta")
	if err == nil {
		defer rows.Close()
		rows.Next()
		var version string
		err = rows.Scan(&version)
		if err != nil {
			return fmt.Errorf("failed to check database schema version: %w", err)
		} else if version == "" {
			return errors.New("database is in unknown state")
		}
		return nil
	}

	if !strings.Contains(err.Error(), "no such table") {
		return fmt.Errorf("got error checking database state: %w", err)
	}

	_, err = db.Exec(schemaSQL)
	if err != nil {
		return fmt.Errorf("failed to initialize database schema: %w", err)
	}

	return nil
}

func GetUserByName(db *sql.DB, username string) (u *User, err error) {
	var stmt *sql.Stmt
	stmt, err = db.Prepare("select user_id, user_name, auth_hash from users where user_name = ?")
	if err != nil {
		return
	}
	defer stmt.Close()

	u = &User{}

	if err = stmt.QueryRow(username).Scan(
		&u.ID,
		&u.Username,
		&u.Hash,
		// TODO support the rest
	); err != nil {
		if strings.Contains(err.Error(), "no rows in result") {
			err = errors.New("no such user")
		}
	}

	return
}

func CreateUser(db *sql.DB, u User) (err error) {
	var id uuid.UUID
	if id, err = uuid.NewRandom(); err != nil {
		return
	}
	var stmt *sql.Stmt
	if stmt, err = db.Prepare(`INSERT INTO users VALUES(?, ?, ?, "", "", 0, 0, ?)`); err != nil {
		return
	}
	defer stmt.Close()

	_, err = stmt.Exec(id, u.Username, u.Hash, u.Created)

	// TODO return user so we have ID

	return
}