commit 801183b1ad3b2984a19f05dbff20d606a0bb826f
parent 0cc7e62609eb47f85ec919efe886a5d58a5f6d63
Author: Jacob R. Edwards <jacob@jacobedwards.org>
Date: Tue, 30 Jul 2024 16:19:07 -0700
Add user settings
Also support FastCGI.
Diffstat:
8 files changed, 305 insertions(+), 16 deletions(-)
diff --git a/cmd/api/Makefile b/cmd/api/Makefile
@@ -1,18 +1,41 @@
name = api.spaceplanner.app
+prog = api
# Database
dbname = ${name}
dbdir = /var/postgresql/data
-dbuser = _postgresql
+dbuser = _spaceplanner
dburl = postgresql://${dbuser}@localhost/${dbname}
dbsql = migration
dbbackups = backups
+# Server
+srvuser = _spaceplanner
+srvroot = /var/www
+srvdir = /htdocs/api.spaceplanner.app
+srvsock = ${srvroot}/run/api.spaceplanner.app.sock
+srvsockuser = www
+
all: ${name}
${name}:
go build
+# I would use ${name} as dependancy, but since this must be run as
+# root and I offload everything to go(1), this isn't what I want.
+run:
+ install -o ${srvuser} -g ${srvuser} -m 0700 ${prog} ${srvroot}/${srvdir}/${prog}
+ kfcgi -dv -n 1 -N 1 \
+ -s ${srvsock} -u ${srvsockuser} \
+ -U ${srvuser} \
+ -p ${srvroot} ${srvdir}/${prog}
+
+test-run: ${name}
+ go run . localhost:8888
+
+clean:
+ rm -f ${name}
+
db: createdb updatedb
createdb:
@@ -23,12 +46,12 @@ createdb:
# (${dbname})
updatedb: backupdb
for f in $$(./scripts/dbupdates ${dbsql}); do \
- psql ${dburl} < ${dbsql}/$$f || { \
+ psql -f "${dbsql}/$$f" ${dburl} ${dbuser} || { \
echo "FAILED TO APPLY $$f" ; \
exit 1 ; \
- }; \
+ } ; \
echo "APPLIED $$f" ; \
- echo ${dbsql}/"$ff" > ${dbsql}/last ; \
+ echo ${dbsql}/"$$f" > ${dbsql}/last ; \
done
backupdb:
@@ -44,4 +67,4 @@ restoredb: backupdb
editdb:
psql ${dburl}
-.PHONY: all db createdb updatedb backupdb restoredb editdb
+.PHONY: all run clean db createdb updatedb backupdb restoredb editdb
diff --git a/cmd/api/main.go b/cmd/api/main.go
@@ -4,6 +4,8 @@ import (
"database/sql"
"log"
"net/http"
+ "net/http/fcgi"
+ "os"
"github.com/gin-gonic/gin"
_ "github.com/lib/pq"
@@ -11,7 +13,7 @@ import (
)
func main() {
- env, err := initEnv("postgres://_postgresql@localhost/test?sslmode=disable")
+ env, err := initEnv("postgres://_spaceplanner@localhost/api.spaceplanner.app?sslmode=disable")
if err != nil {
log.Fatalf("Unable to initialize environment: %s", err.Error())
}
@@ -31,7 +33,17 @@ func main() {
engine.NoRoute(noRoute)
engine.Use(AuthMiddleware(auth))
setRoutes(env, engine.Group("/v0"), auth)
- engine.Run("localhost:8888")
+
+ args := os.Args[1:]
+ if len(args) > 1 {
+ log.Fatal("Too many arguments")
+ }
+
+ if len(args) == 0 {
+ fcgi.Serve(nil, engine)
+ } else {
+ engine.Run(args[0])
+ }
}
func initEnv(database string) (*Env, error) {
@@ -63,6 +75,13 @@ func setRoutes(env *Env, r *gin.RouterGroup, auth *jwt.GinJWTMiddleware) {
func setAuthenticatedRoutes(env *Env, r *gin.RouterGroup) {
r.DELETE("/users/:user", env.DeleteUser)
+
+ user := r.Group("/users/:user/")
+ user.PUT("/settings", env.SetUserSettings)
+ user.PUT("/settings/:setting", env.SetUserSetting)
+ user.GET("/settings", env.GetUserSettings)
+ user.GET("/settings/:setting", env.GetUserSetting)
+
}
func noRoute(c *gin.Context) {
diff --git a/cmd/api/migration/2024-07-27T05:11:12.sql b/cmd/api/migration/2024-07-27T05:11:12.sql
@@ -0,0 +1,13 @@
+CREATE TABLE user_settings (
+ username varchar(128),
+ name varchar,
+ strval varchar,
+ numval int,
+ boolval bool,
+ PRIMARY KEY (username, name),
+ CONSTRAINT one_value_type CHECK (
+ (strval IS NOT NULL)::int +
+ (numval IS NOT NULL)::int +
+ (boolval IS NOT NULL)::int = 1
+ )
+);
diff --git a/cmd/api/migration/2024-07-27T19:08:01.sql b/cmd/api/migration/2024-07-27T19:08:01.sql
@@ -0,0 +1,3 @@
+CREATE SCHEMA spaceplanner ;
+ALTER TABLE users SET SCHEMA spaceplanner ;
+ALTER TABLE user_settings SET SCHEMA spaceplanner ;
diff --git a/cmd/api/scripts/newmigration b/cmd/api/scripts/newmigration
@@ -0,0 +1,3 @@
+#!/bin/sh
+
+echo "$1"/"$(date -u +%FT%T)".sql
diff --git a/cmd/api/users.go b/cmd/api/users.go
@@ -49,3 +49,95 @@ func (e *Env) GetUser(c *gin.Context) {
Respond(c, http.StatusOK, user)
}
+
+func (e *Env) GetUserSettings(c *gin.Context) {
+ user := c.Param("user")
+ if user == "" {
+ RespondError(c, http.StatusNotFound, "No username given")
+ return
+ }
+
+ settings, err := e.backend.GetUserSettings(user)
+ if err != nil {
+ RespondError(c, 400, "Unable to get settings: %s", err.Error())
+ return
+ }
+
+ Respond(c, http.StatusOK, settings)
+}
+
+func (e *Env) GetUserSetting(c *gin.Context) {
+ user := c.Param("user")
+ name := c.Param("setting")
+ if user == "" || name == "" {
+ RespondError(c, http.StatusNotFound, "Username or setting not given")
+ return
+ }
+
+ setting, err := e.backend.GetUserSettings(user, name)
+ if err != nil {
+ RespondError(c, 400, "Unable to get settings: %s", err.Error())
+ return
+ }
+
+ Respond(c, http.StatusOK, setting)
+}
+
+func (e *Env) SetUserSettings(c *gin.Context) {
+ user := c.Param("user")
+ if user == "" {
+ RespondError(c, http.StatusNotFound, "No username given")
+ return
+ }
+
+ settings := make(map[string]backend.Setting)
+ if err := c.ShouldBind(&settings); err != nil {
+ RespondError(c, 400, "Unable to read settings: %s", err.Error())
+ return
+ }
+
+ tx, err := e.backend.DB.Begin()
+ if err != nil {
+ RespondError(c, 400, "Unable to begin transaction: %s", err.Error())
+ return
+ }
+ if _, err := e.backend.TxDeleteUserSettings(tx, user); err != nil {
+ tx.Rollback()
+ RespondError(c, 400, "Unable to truncate settings: %s", err.Error())
+ return
+ }
+ if err := e.backend.TxUpdateUserSettings(tx, user, settings); err != nil {
+ RespondError(c, 400, "Unable to set settings: %s", err.Error())
+ return
+ }
+ if err = tx.Commit(); err != nil {
+ RespondError(c, 400, "Unable to commit transaction: %s", err.Error())
+ return
+ }
+
+ Respond(c, http.StatusOK, settings)
+}
+
+func (e *Env) SetUserSetting(c *gin.Context) {
+ user := c.Param("user")
+ name := c.Param("setting")
+ if user == "" || name == "" {
+ RespondError(c, http.StatusNotFound, "Username or setting name not given")
+ return
+ }
+
+ var value interface{}
+ if err := c.ShouldBind(&value); err != nil {
+ RespondError(c, 400, "Unable to get value: %s", err.Error())
+ return
+ }
+
+ setting := make(map[string]backend.Setting)
+ setting[name] = value
+ if err := e.backend.UpdateUserSettings(user, setting);
+ err != nil {
+ RespondError(c, 400, "Unable to update %q: %s", setting, err.Error())
+ }
+
+ Respond(c, 200, setting)
+}
diff --git a/internal/backend/env.go b/internal/backend/env.go
@@ -6,7 +6,7 @@ import (
)
type Env struct {
- db *sql.DB
+ DB *sql.DB
stmts map[string]*sql.Stmt
}
@@ -17,7 +17,7 @@ func NewEnv(db *sql.DB) (*Env, error) {
return nil, errors.New("No database")
}
return &Env{
- db: db,
+ DB: db,
stmts: make(map[string]*sql.Stmt),
}, nil
}
@@ -28,7 +28,7 @@ func (e *Env) CacheStmt(name, sql string) (*sql.Stmt, error) {
return stmt, nil
}
- stmt, err := e.db.Prepare(sql)
+ stmt, err := e.DB.Prepare(sql)
if err != nil {
return nil, err
}
@@ -40,5 +40,5 @@ func (e *Env) Free() {
for _, s := range e.stmts {
s.Close()
}
- e.db.Close()
+ e.DB.Close()
}
diff --git a/internal/backend/user.go b/internal/backend/user.go
@@ -2,7 +2,9 @@ package backend
import (
"errors"
+ "database/sql"
"golang.org/x/crypto/bcrypt"
+ "github.com/lib/pq"
)
// Database representation of user
@@ -11,6 +13,16 @@ type User struct {
hash string
}
+type Setting interface{}
+
+type rawSetting struct {
+ username *string
+ name *string
+ strval *string
+ numval *int
+ boolval *bool
+}
+
func (e *Env) CreateUser(username string, password string) error {
if username == "" {
return errors.New("Empty username")
@@ -22,11 +34,7 @@ func (e *Env) CreateUser(username string, password string) error {
}
user := User{ Name: username, hash: string(hash) }
- err = e.insertUser(user)
- if err != nil {
- return err
- }
- return nil
+ return e.insertUser(user)
}
func (e *Env) DeleteUser(username string) error {
@@ -72,6 +80,134 @@ func (e *Env) LoginUser(username string, password string) (User, error) {
return user, nil;
}
+func (e *Env) UpdateUserSettings(username string, settings map[string]Setting) error {
+ tx, err := e.DB.Begin()
+ if err != nil {
+ return err
+ }
+ err = e.TxUpdateUserSettings(tx, username, settings)
+ if err != nil {
+ tx.Rollback()
+ return err
+ }
+ return tx.Commit()
+}
+
+func (e *Env) TxUpdateUserSettings(tx *sql.Tx, username string, settings map[string]Setting) error {
+ stmt, err := e.CacheStmt("set_user_settings", `INSERT INTO user_settings VALUES ($1, $2, $3, $4, $5)
+ ON CONFLICT (username, name) DO UPDATE
+ SET (strval, numval, boolval) = (EXCLUDED.strval, EXCLUDED.numval, EXCLUDED.boolval)`)
+ if err != nil {
+ return err
+ }
+ stmt = tx.Stmt(stmt)
+
+ for name, setting := range settings {
+ r := toRawSetting(username, name, setting)
+ _, err = stmt.Exec(r.username, r.name, r.strval, r.numval, r.boolval)
+ if err != nil {
+ return err
+ }
+ }
+
+ return nil
+}
+
+func (e *Env) GetUserSettings(username string, names ...string) (map[string]Setting, error) {
+ stmt, err := e.CacheStmt("get_user_settings", `SELECT * FROM user_settings WHERE
+ user_settings.username = $1 AND
+ (ARRAY_LENGTH($2::varchar[], 1) IS NULL OR user_settings.name = ANY ($2))`)
+ if err != nil {
+ return nil, err
+ }
+
+ rows, err := stmt.Query(username, pq.Array(names))
+ if err != nil {
+ return nil, err
+ }
+
+ return collectSettings(rows)
+}
+
+func (e *Env) DeleteUserSettings(username string, names ...string) (map[string]Setting, error) {
+ var deleted map[string]Setting
+ tx, err := e.DB.Begin()
+ if err != nil {
+ return deleted, err
+ }
+ deleted, err = e.TxDeleteUserSettings(tx, username, names...)
+ if err != nil {
+ tx.Rollback()
+ return deleted, err
+ }
+ return deleted, tx.Commit()
+}
+
+func (e *Env) TxDeleteUserSettings(tx *sql.Tx, username string, names ...string) (map[string]Setting, error) {
+ var deleted map[string]Setting
+ stmt, err := e.CacheStmt("del_user_settings", `DELETE FROM user_settings WHERE user_settings.username = $1 AND
+ (ARRAY_LENGTH($2::string[], 1) IS NULL OR user_settings.name = ANY ($2)) RETURNING`)
+ if err != nil {
+ return deleted, err
+ }
+
+ rows, err := tx.Stmt(stmt).Query(username, names)
+ if err != nil {
+ return deleted, err
+ }
+ return collectSettings(rows)
+}
+
+func toRawSetting(username string, name string, setting Setting) *rawSetting {
+ var b *bool
+ var i *int
+ var s *string
+
+ switch v := setting.(type) {
+ case string:
+ s = &v
+ case int:
+ i = &v
+ case bool:
+ b = &v
+ default:
+ panic("Setting.Value: Unexpected type")
+ }
+
+ return &rawSetting{
+ username: &username,
+ name: &name,
+ strval: s,
+ numval: i,
+ boolval: b,
+ }
+}
+
+func collectSettings(rows *sql.Rows) (map[string]Setting, error) {
+ settings := make(map[string]Setting)
+ for rows.Next() {
+ var setting Setting
+ var raw rawSetting
+ err := rows.Scan(&raw.username, &raw.name, &raw.strval, &raw.numval, &raw.boolval)
+ if err != nil {
+ return settings, err
+ }
+
+ if raw.strval != nil {
+ setting = *raw.strval
+ } else if raw.numval != nil {
+ setting = *raw.numval
+ } else if raw.boolval != nil {
+ setting = *raw.boolval
+ } else {
+ return settings, errors.New("Database constraint error: no setting set")
+ }
+ settings[*raw.name] = setting
+ }
+
+ return settings, nil
+}
+
func (e *Env) insertUser(user User) error {
stmt, err := e.CacheStmt("insert_user", "INSERT INTO users VALUES ($1, $2)")
if err != nil {
