bbj2/server/cmd/db/db.go

137 lines
3.0 KiB
Go
Raw Permalink Normal View History

2022-06-01 03:27:55 +00:00
package db
2022-06-14 21:05:39 +00:00
import (
"database/sql"
_ "embed"
"errors"
"fmt"
"os"
"strings"
"time"
2022-06-01 03:30:45 +00:00
2022-06-14 21:05:39 +00:00
"git.tilde.town/tildetown/bbj2/server/cmd/config"
"github.com/google/uuid"
2022-06-14 21:05:39 +00:00
_ "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.
2022-06-01 03:27:55 +00:00
type User struct {
ID string
Username string
Hash string
Created time.Time
2022-06-01 03:27:55 +00:00
}
2022-06-01 03:30:45 +00:00
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
}
2022-06-14 21:05:39 +00:00
2022-06-17 21:01:25 +00:00
func Setup(opts *config.Options) (func(), error) {
2022-06-14 21:05:39 +00:00
db, err := sql.Open("sqlite3", opts.Config.DBPath)
opts.DB = db
2022-06-17 21:12:05 +00:00
// TODO consider inlining EnsureSchema here
2022-06-14 21:05:39 +00:00
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
2022-06-21 17:57:52 +00:00
stmt, err = db.Prepare("select user_id, user_name, auth_hash from users where user_name = ?")
if err != nil {
return
}
defer stmt.Close()
2022-06-21 17:57:52 +00:00
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)
2022-06-17 21:01:25 +00:00
// TODO return user so we have ID
return
}