From cbd5c09c5e1403709d4aabf91051443f147689e5 Mon Sep 17 00:00:00 2001 From: Anhgelus Morhtuuzh Date: Mon, 22 Dec 2025 18:34:02 +0100 Subject: refactor(backend): move db related in new package --- backend/admin.go | 53 ++++++++ backend/db.go | 68 ---------- backend/migrations/000_init.sql | 6 - backend/router.go | 6 +- backend/stats.go | 226 -------------------------------- backend/storage/db.go | 68 ++++++++++ backend/storage/migrations/000_init.sql | 6 + backend/storage/stats.go | 182 +++++++++++++++++++++++++ main.go | 5 +- 9 files changed, 315 insertions(+), 305 deletions(-) create mode 100644 backend/admin.go delete mode 100644 backend/db.go delete mode 100644 backend/migrations/000_init.sql delete mode 100644 backend/stats.go create mode 100644 backend/storage/db.go create mode 100644 backend/storage/migrations/000_init.sql create mode 100644 backend/storage/stats.go diff --git a/backend/admin.go b/backend/admin.go new file mode 100644 index 0000000..087a3ea --- /dev/null +++ b/backend/admin.go @@ -0,0 +1,53 @@ +package backend + +import ( + "log/slog" + "net/http" + "strconv" + + "git.anhgelus.world/anhgelus/small-web/backend/storage" + "github.com/go-chi/chi/v5" +) + +type adminData struct { + *data + Visits []storage.StatsRow + Rows []storage.StatsRow + PagesNumber int + CurrentPage int +} + +func HandleAdmin(r *chi.Mux) { + r.Get("/admin", func(w http.ResponseWriter, r *http.Request) { + ctx := r.Context() + if !ctx.Value(loginKey).(bool) { + w.Header().Set("WWW-Authenticate", `Basic realm="restricted", charset="UTF-8"`) + http.Error(w, "Unauthorized", http.StatusUnauthorized) + return + } + d := new(adminData) + d.data = new(data) + rawPage := r.URL.Query().Get("page") + page := 1 + var err error + if rawPage != "" { + page, err = strconv.Atoi(rawPage) + if err != nil || page < 1 { + slog.Warn("invalid page number", "rawPage", rawPage) + http.Error(w, "Bad request", http.StatusBadRequest) + return + } + } + d.Rows, err = storage.GetStatsRows(ctx, uint(page)) + if err != nil { + panic(err) + } + d.Visits, err = storage.GetUnionStatsRows(ctx) + if err != nil { + panic(err) + } + d.PagesNumber = page + max(len(d.Rows)-storage.StatsPerPage+1, 0) + d.CurrentPage = page + d.handleGeneric(w, r, "admin", d) + }) +} diff --git a/backend/db.go b/backend/db.go deleted file mode 100644 index 3828880..0000000 --- a/backend/db.go +++ /dev/null @@ -1,68 +0,0 @@ -package backend - -import ( - "context" - "database/sql" - "embed" - "fmt" - "log/slog" - "regexp" - "slices" - "strconv" - - _ "github.com/mattn/go-sqlite3" -) - -//go:embed migrations -var migrations embed.FS - -var nameReg = regexp.MustCompile(`(\d{3})_[a-zA-Z_-]+.sql`) - -func ConnectDatabase(cfg *Config) *sql.DB { - db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?cache=shared", cfg.Database)) - if err != nil { - panic(err) - } - return db -} - -func RunMigration(ctx context.Context, db *sql.DB) error { - entries, err := migrations.ReadDir("migrations") - if err != nil { - return err - } - type runMig struct { - val string - n int - } - var toRun []runMig - for _, e := range entries { - rawId := nameReg.FindStringSubmatch(e.Name()) - id, err := strconv.Atoi(rawId[1]) - if err != nil { - return err - } - b, err := migrations.ReadFile("migrations/" + e.Name()) - if err != nil { - return err - } - slog.Debug("loading migration", "n", id, "file", e.Name(), "content", string(b)) - toRun = append(toRun, runMig{ - val: string(b), n: id, - }) - } - if len(toRun) == 0 { - return nil - } - slices.SortFunc(toRun, func(a, b runMig) int { - return a.n - b.n - }) - for _, m := range toRun { - slog.Info("migrating", "n", m.n) - _, err := db.ExecContext(ctx, m.val) - if err != nil { - return err - } - } - return nil -} diff --git a/backend/migrations/000_init.sql b/backend/migrations/000_init.sql deleted file mode 100644 index 6dd385c..0000000 --- a/backend/migrations/000_init.sql +++ /dev/null @@ -1,6 +0,0 @@ -CREATE TABLE IF NOT EXISTS stats( - id INTEGER PRIMARY KEY, - visit INTEGER NOT NULL, - origin TEXT NOT NULL, - target TEXT NOT NULL -); diff --git a/backend/router.go b/backend/router.go index f2ed775..161433d 100644 --- a/backend/router.go +++ b/backend/router.go @@ -14,6 +14,7 @@ import ( "strings" "time" + "git.anhgelus.world/anhgelus/small-web/backend/storage" "github.com/go-chi/chi/v5" "github.com/go-chi/chi/v5/middleware" "github.com/go-chi/httplog/v3" @@ -24,7 +25,6 @@ const ( configKey = "config" assetsFSKey = "assets_fs" debugKey = "debug" - dbKey = "db" loginKey = "login" ) @@ -95,7 +95,7 @@ func NewRouter(debug bool, cfg *Config, db *sql.DB, assets fs.FS) *chi.Mux { ctx = context.WithValue(ctx, configKey, cfg) ctx = context.WithValue(ctx, assetsFSKey, assets) ctx = context.WithValue(ctx, debugKey, debug) - return context.WithValue(ctx, dbKey, db) + return context.WithValue(ctx, storage.DBKey, db) } r.Use(func(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { @@ -108,7 +108,7 @@ func NewRouter(debug bool, cfg *Config, db *sql.DB, assets fs.FS) *chi.Mux { go func(r *http.Request) { ctx, cancel := context.WithTimeout(context.Background(), 1*time.Second) defer cancel() - if err := UpdateStats(setContext(ctx), r); err != nil { + if err := storage.UpdateStats(setContext(ctx), r, cfg.Domain); err != nil { slog.Error("updating stats", "error", err) } }(r) diff --git a/backend/stats.go b/backend/stats.go deleted file mode 100644 index f081194..0000000 --- a/backend/stats.go +++ /dev/null @@ -1,226 +0,0 @@ -package backend - -import ( - "context" - "database/sql" - "fmt" - "log/slog" - "net/http" - "regexp" - "slices" - "strconv" - "strings" - "sync" - "time" - - "github.com/go-chi/chi/v5" -) - -type loaded struct { - data map[string]struct{} - mu *sync.RWMutex -} - -func (l *loaded) Has(k string) bool { - l.mu.RLock() - defer l.mu.RUnlock() - _, ok := l.data[k] - return ok -} - -func (l *loaded) Add(k string) { - l.mu.Lock() - defer l.mu.Unlock() - l.data[k] = struct{}{} -} - -func (l *loaded) Remove(k string) { - l.mu.Lock() - defer l.mu.Unlock() - delete(l.data, k) -} - -func newLoaded() *loaded { - return &loaded{ - data: make(map[string]struct{}), - mu: new(sync.RWMutex), - } -} - -var trimRefererReg = regexp.MustCompile(`https?://([a-z-0-9.]+(:\d+)?)/.*`) - -var load = newLoaded() - -func getDB(ctx context.Context) *sql.DB { - return ctx.Value(dbKey).(*sql.DB) -} - -func UpdateStats(ctx context.Context, r *http.Request) error { - target := r.URL.Path - if strings.HasPrefix(target, "/static") || strings.HasPrefix(target, "/admin") { - return nil - } - ref := r.Header.Get("Referer") - if ref == "" { - return nil - } - subs := trimRefererReg.FindStringSubmatch(ref) - if len(subs) < 2 { - return nil - } - ref = subs[1] - if ref == ctx.Value(configKey).(*Config).Domain || ref == fmt.Sprintf("localhost:%d", 8000) { - ref = subs[0][strings.Index(subs[0], ref)+len(ref):] - if ref == target || strings.HasPrefix(ref, "/admin") || ref == "/favicon.ico" { - return nil - } - } - // using /assets/styles.css to detect if a page is loaded → majority of bots will not load this - if target == "/assets/styles.css" { - target = ref - ref = "?" - } - if load.Has(target) { - return nil - } - db := getDB(ctx) - rows, err := db.QueryContext(ctx, "SELECT id, visit FROM stats WHERE origin = ? AND target = ?", ref, target) - if err != nil { - return err - } - defer func() { - if err == nil { - slog.Debug("stats updated") - load.Add(target) - go func(target string) { - time.Sleep(5 * time.Second) - load.Remove(target) - }(target) - } - }() - if !rows.Next() { - _, err = db.ExecContext(ctx, "INSERT INTO stats (origin, target, visit) VALUES (?, ?, 1)", ref, target) - return err - } - var id uint - var nb uint - err = rows.Scan(&id, &nb) - if err != nil { - return err - } - err = rows.Close() - if err != nil { - return err - } - _, err = db.ExecContext(ctx, "UPDATE stats SET visit = ? WHERE id = ?", nb+1, id) - return err -} - -type statRow struct { - Origin string - Target string - Visit uint -} - -const statPerPage = 25 - -func GetStatRows(ctx context.Context, page uint) ([]statRow, error) { - rows, err := getDB(ctx).QueryContext( - ctx, - "SELECT origin, target, visit FROM stats ORDER BY visit DESC LIMIT ? OFFSET ?", - statPerPage, (page-1)*statPerPage, - ) - if err != nil { - return nil, err - } - defer rows.Close() - statRows := make([]statRow, statPerPage) - var i uint8 - for i = 0; rows.Next(); i++ { - var stat statRow - err = rows.Scan(&stat.Origin, &stat.Target, &stat.Visit) - if err != nil { - return nil, err - } - statRows[i] = stat - } - if i == 0 { - return nil, nil - } - return statRows[:i], nil -} - -func GetUnionStatRows(ctx context.Context) ([]statRow, error) { - rows, err := getDB(ctx).QueryContext(ctx, "SELECT target, visit FROM stats ORDER BY visit DESC") - if err != nil { - return nil, err - } - defer rows.Close() - data := make(map[string]uint) - for rows.Next() { - var stat statRow - err = rows.Scan(&stat.Target, &stat.Visit) - if err != nil { - return nil, err - } - if _, ok := data[stat.Target]; !ok { - data[stat.Target] = stat.Visit - } else { - data[stat.Target] += stat.Visit - } - } - var statRows []statRow - for k, v := range data { - statRows = append(statRows, statRow{ - Target: k, - Visit: v, - }) - } - slices.SortFunc(statRows, func(a, b statRow) int { - return int(b.Visit) - int(a.Visit) - }) - return statRows, nil -} - -type adminData struct { - *data - Visits []statRow - Rows []statRow - PagesNumber int - CurrentPage int -} - -func HandleAdmin(r *chi.Mux) { - r.Get("/admin", func(w http.ResponseWriter, r *http.Request) { - ctx := r.Context() - if !ctx.Value(loginKey).(bool) { - w.Header().Set("WWW-Authenticate", `Basic realm="restricted", charset="UTF-8"`) - http.Error(w, "Unauthorized", http.StatusUnauthorized) - return - } - d := new(adminData) - d.data = new(data) - rawPage := r.URL.Query().Get("page") - page := 1 - var err error - if rawPage != "" { - page, err = strconv.Atoi(rawPage) - if err != nil || page < 1 { - slog.Warn("invalid page number", "rawPage", rawPage) - http.Error(w, "Bad request", http.StatusBadRequest) - return - } - } - d.Rows, err = GetStatRows(ctx, uint(page)) - if err != nil { - panic(err) - } - d.Visits, err = GetUnionStatRows(ctx) - if err != nil { - panic(err) - } - d.PagesNumber = page + max(len(d.Rows)-statPerPage+1, 0) - d.CurrentPage = page - d.handleGeneric(w, r, "admin", d) - }) -} diff --git a/backend/storage/db.go b/backend/storage/db.go new file mode 100644 index 0000000..7a7c6e0 --- /dev/null +++ b/backend/storage/db.go @@ -0,0 +1,68 @@ +package storage + +import ( + "context" + "database/sql" + "embed" + "fmt" + "log/slog" + "regexp" + "slices" + "strconv" + + _ "github.com/mattn/go-sqlite3" +) + +//go:embed migrations +var migrations embed.FS + +var nameReg = regexp.MustCompile(`(\d{3})_[a-zA-Z_-]+.sql`) + +func ConnectDatabase(file string) *sql.DB { + db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?cache=shared", file)) + if err != nil { + panic(err) + } + return db +} + +func RunMigration(ctx context.Context, db *sql.DB) error { + entries, err := migrations.ReadDir("migrations") + if err != nil { + return err + } + type runMig struct { + val string + n int + } + var toRun []runMig + for _, e := range entries { + rawId := nameReg.FindStringSubmatch(e.Name()) + id, err := strconv.Atoi(rawId[1]) + if err != nil { + return err + } + b, err := migrations.ReadFile("migrations/" + e.Name()) + if err != nil { + return err + } + slog.Debug("loading migration", "n", id, "file", e.Name(), "content", string(b)) + toRun = append(toRun, runMig{ + val: string(b), n: id, + }) + } + if len(toRun) == 0 { + return nil + } + slices.SortFunc(toRun, func(a, b runMig) int { + return a.n - b.n + }) + for _, m := range toRun { + slog.Info("migrating", "n", m.n) + _, err := db.ExecContext(ctx, m.val) + if err != nil { + return err + } + } + return nil +} diff --git a/backend/storage/migrations/000_init.sql b/backend/storage/migrations/000_init.sql new file mode 100644 index 0000000..6dd385c --- /dev/null +++ b/backend/storage/migrations/000_init.sql @@ -0,0 +1,6 @@ +CREATE TABLE IF NOT EXISTS stats( + id INTEGER PRIMARY KEY, + visit INTEGER NOT NULL, + origin TEXT NOT NULL, + target TEXT NOT NULL +); diff --git a/backend/storage/stats.go b/backend/storage/stats.go new file mode 100644 index 0000000..757168d --- /dev/null +++ b/backend/storage/stats.go @@ -0,0 +1,182 @@ +package storage + +import ( + "context" + "database/sql" + "fmt" + "log/slog" + "net/http" + "regexp" + "slices" + "strings" + "sync" + "time" +) + +const DBKey = "db" + +type loaded struct { + data map[string]struct{} + mu *sync.RWMutex +} + +func (l *loaded) Has(k string) bool { + l.mu.RLock() + defer l.mu.RUnlock() + _, ok := l.data[k] + return ok +} + +func (l *loaded) Add(k string) { + l.mu.Lock() + defer l.mu.Unlock() + l.data[k] = struct{}{} +} + +func (l *loaded) Remove(k string) { + l.mu.Lock() + defer l.mu.Unlock() + delete(l.data, k) +} + +func newLoaded() *loaded { + return &loaded{ + data: make(map[string]struct{}), + mu: new(sync.RWMutex), + } +} + +var trimRefererReg = regexp.MustCompile(`https?://([a-z-0-9.]+(:\d+)?)/.*`) + +var load = newLoaded() + +func getDB(ctx context.Context) *sql.DB { + return ctx.Value(DBKey).(*sql.DB) +} + +func UpdateStats(ctx context.Context, r *http.Request, domain string) error { + target := r.URL.Path + if strings.HasPrefix(target, "/static") || strings.HasPrefix(target, "/admin") { + return nil + } + ref := r.Header.Get("Referer") + if ref == "" { + return nil + } + subs := trimRefererReg.FindStringSubmatch(ref) + if len(subs) < 2 { + return nil + } + ref = subs[1] + if ref == domain || ref == fmt.Sprintf("localhost:%d", 8000) { + ref = subs[0][strings.Index(subs[0], ref)+len(ref):] + if ref == target || strings.HasPrefix(ref, "/admin") || ref == "/favicon.ico" { + return nil + } + } + // using /assets/styles.css to detect if a page is loaded → majority of bots will not load this + if target == "/assets/styles.css" { + target = ref + ref = "?" + } + if load.Has(target) { + return nil + } + db := getDB(ctx) + rows, err := db.QueryContext(ctx, "SELECT id, visit FROM stats WHERE origin = ? AND target = ?", ref, target) + if err != nil { + return err + } + defer func() { + if err == nil { + slog.Debug("stats updated") + load.Add(target) + go func(target string) { + time.Sleep(5 * time.Second) + load.Remove(target) + }(target) + } + }() + if !rows.Next() { + _, err = db.ExecContext(ctx, "INSERT INTO stats (origin, target, visit) VALUES (?, ?, 1)", ref, target) + return err + } + var id uint + var nb uint + err = rows.Scan(&id, &nb) + if err != nil { + return err + } + err = rows.Close() + if err != nil { + return err + } + _, err = db.ExecContext(ctx, "UPDATE stats SET visit = ? WHERE id = ?", nb+1, id) + return err +} + +type StatsRow struct { + Origin string + Target string + Visit uint +} + +const StatsPerPage = 25 + +func GetStatsRows(ctx context.Context, page uint) ([]StatsRow, error) { + rows, err := getDB(ctx).QueryContext( + ctx, + "SELECT origin, target, visit FROM stats ORDER BY visit DESC LIMIT ? OFFSET ?", + StatsPerPage, (page-1)*StatsPerPage, + ) + if err != nil { + return nil, err + } + defer rows.Close() + statRows := make([]StatsRow, StatsPerPage) + var i uint8 + for i = 0; rows.Next(); i++ { + var stat StatsRow + err = rows.Scan(&stat.Origin, &stat.Target, &stat.Visit) + if err != nil { + return nil, err + } + statRows[i] = stat + } + if i == 0 { + return nil, nil + } + return statRows[:i], nil +} + +func GetUnionStatsRows(ctx context.Context) ([]StatsRow, error) { + rows, err := getDB(ctx).QueryContext(ctx, "SELECT target, visit FROM stats ORDER BY visit DESC") + if err != nil { + return nil, err + } + defer rows.Close() + data := make(map[string]uint) + for rows.Next() { + var stat StatsRow + err = rows.Scan(&stat.Target, &stat.Visit) + if err != nil { + return nil, err + } + if _, ok := data[stat.Target]; !ok { + data[stat.Target] = stat.Visit + } else { + data[stat.Target] += stat.Visit + } + } + var statRows []StatsRow + for k, v := range data { + statRows = append(statRows, StatsRow{ + Target: k, + Visit: v, + }) + } + slices.SortFunc(statRows, func(a, b StatsRow) int { + return int(b.Visit) - int(a.Visit) + }) + return statRows, nil +} diff --git a/main.go b/main.go index 4f2fd6b..b1a192e 100644 --- a/main.go +++ b/main.go @@ -15,6 +15,7 @@ import ( "time" "git.anhgelus.world/anhgelus/small-web/backend" + "git.anhgelus.world/anhgelus/small-web/backend/storage" "github.com/joho/godotenv" ) @@ -61,9 +62,9 @@ func main() { ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second) defer cancel() - db := backend.ConnectDatabase(cfg) + db := storage.ConnectDatabase(cfg.Database) defer db.Close() - err := backend.RunMigration(ctx, db) + err := storage.RunMigration(ctx, db) if err != nil { panic(err) } -- cgit v1.2.3