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 }