From aeb19df4222269c585de55be5568326222df879d Mon Sep 17 00:00:00 2001 From: grm Date: Sun, 13 Sep 2026 23:52:41 +0300 Subject: Give every blog its own Postgres database A blog is now a database of its own (blog_) on the same server: one pg_dump is a complete backup of a blog, one psql restores it, and nothing a blog's queries do can reach another blog's rows. The control database (DATABASE_URL) keeps only users and the blog registry (id, owner, subdomain, db_name); title, tagline and theme move into a one-row settings table next to the content so the dump really is everything. db.Cluster holds the control pool plus small, lazily opened per-blog pools. store.Store (control) hands out a store.BlogStore per blog; every blog_id parameter and column is gone, the database is the scope. Handlers reach it through blogStore(r), which resolveBlog puts in the context next to the blog. Existing data is moved in place by control migration 00006, a Go migration that runs inside the control transaction: it creates and migrates each blog database, copies the rows preserving ids, and marks the registry; 00007 then drops the old tables. Either every blog is moved or the control database is untouched. /media/{id} now serves the host's blog only, so dashboard previews on the root domain use /b/{sub}/media/{id}. Subdomains are capped at 58 chars so "blog_" + name fits a Postgres identifier. Deleting a user drops their database. Store.Open resets a blog's pool and retries once so a database restored under a running app (dropdb --force, createdb, psql) just works. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A --- internal/store/modules.go | 57 +++++++++++++++++++++++------------------------ 1 file changed, 28 insertions(+), 29 deletions(-) (limited to 'internal/store/modules.go') diff --git a/internal/store/modules.go b/internal/store/modules.go index c7d9760..e0c3cd5 100644 --- a/internal/store/modules.go +++ b/internal/store/modules.go @@ -9,7 +9,6 @@ import ( // block of custom HTML… placed in one of the areas of the page. type Module struct { ID int64 - BlogID int64 Area string // header | left | right | above | below | footer Kind string // title | logo | menu | archive | recent | html | rss | text | sitemap Title string // optional heading (side columns and footer) @@ -20,20 +19,20 @@ type Module struct { UpdatedAt time.Time } -const moduleCols = `id, blog_id, area, kind, title, body, count, sort_order, created_at, updated_at` +const moduleCols = `id, area, kind, title, body, count, sort_order, created_at, updated_at` func scanModule(row interface{ Scan(...any) error }) (*Module, error) { var m Module - err := row.Scan(&m.ID, &m.BlogID, &m.Area, &m.Kind, &m.Title, &m.Body, &m.Count, &m.SortOrder, &m.CreatedAt, &m.UpdatedAt) + err := row.Scan(&m.ID, &m.Area, &m.Kind, &m.Title, &m.Body, &m.Count, &m.SortOrder, &m.CreatedAt, &m.UpdatedAt) if err != nil { return nil, wrap(err) } return &m, nil } -// ListModules returns every module of a blog, grouped by area in display order. -func (s *Store) ListModules(ctx context.Context, blogID int64) ([]Module, error) { - rows, err := s.db.Query(ctx, `SELECT `+moduleCols+` FROM modules WHERE blog_id=$1 ORDER BY area, sort_order, id`, blogID) +// ListModules returns every module of the blog, grouped by area in display order. +func (bs *BlogStore) ListModules(ctx context.Context) ([]Module, error) { + rows, err := bs.db.Query(ctx, `SELECT `+moduleCols+` FROM modules ORDER BY area, sort_order, id`) if err != nil { return nil, err } @@ -49,36 +48,36 @@ func (s *Store) ListModules(ctx context.Context, blogID int64) ([]Module, error) return out, rows.Err() } -func (s *Store) ModuleByID(ctx context.Context, blogID, id int64) (*Module, error) { - return scanModule(s.db.QueryRow(ctx, `SELECT `+moduleCols+` FROM modules WHERE blog_id=$1 AND id=$2`, blogID, id)) +func (bs *BlogStore) ModuleByID(ctx context.Context, id int64) (*Module, error) { + return scanModule(bs.db.QueryRow(ctx, `SELECT `+moduleCols+` FROM modules WHERE id=$1`, id)) } // CreateModule appends a module to the end of its area. -func (s *Store) CreateModule(ctx context.Context, m *Module) (*Module, error) { - return scanModule(s.db.QueryRow(ctx, `INSERT INTO modules (blog_id, area, kind, title, body, count, sort_order) - VALUES ($1,$2,$3,$4,$5,$6,(SELECT coalesce(max(sort_order),-1)+1 FROM modules WHERE blog_id=$1 AND area=$2)) RETURNING `+moduleCols, - m.BlogID, m.Area, m.Kind, m.Title, m.Body, m.Count)) +func (bs *BlogStore) CreateModule(ctx context.Context, m *Module) (*Module, error) { + return scanModule(bs.db.QueryRow(ctx, `INSERT INTO modules (area, kind, title, body, count, sort_order) + VALUES ($1,$2,$3,$4,$5,(SELECT coalesce(max(sort_order),-1)+1 FROM modules WHERE area=$1)) RETURNING `+moduleCols, + m.Area, m.Kind, m.Title, m.Body, m.Count)) } // UpdateModule saves the editable settings; area and kind never change. -func (s *Store) UpdateModule(ctx context.Context, m *Module) error { - _, err := s.db.Exec(ctx, `UPDATE modules SET title=$3, body=$4, count=$5, updated_at=now() WHERE blog_id=$1 AND id=$2`, - m.BlogID, m.ID, m.Title, m.Body, m.Count) +func (bs *BlogStore) UpdateModule(ctx context.Context, m *Module) error { + _, err := bs.db.Exec(ctx, `UPDATE modules SET title=$2, body=$3, count=$4, updated_at=now() WHERE id=$1`, + m.ID, m.Title, m.Body, m.Count) return wrap(err) } -func (s *Store) DeleteModule(ctx context.Context, blogID, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM modules WHERE blog_id=$1 AND id=$2`, blogID, id) +func (bs *BlogStore) DeleteModule(ctx context.Context, id int64) error { + _, err := bs.db.Exec(ctx, `DELETE FROM modules WHERE id=$1`, id) return err } // MoveModule swaps sort_order with the neighbouring module of the same area (dir = -1 up, +1 down). -func (s *Store) MoveModule(ctx context.Context, blogID, id int64, dir int) error { - cur, err := s.ModuleByID(ctx, blogID, id) +func (bs *BlogStore) MoveModule(ctx context.Context, id int64, dir int) error { + cur, err := bs.ModuleByID(ctx, id) if err != nil { return err } - all, err := s.ListModules(ctx, blogID) + all, err := bs.ListModules(ctx) if err != nil { return err } @@ -98,13 +97,13 @@ func (s *Store) MoveModule(ctx context.Context, blogID, id int64, dir int) error return nil } mods[idx], mods[j] = mods[j], mods[idx] - tx, err := s.db.Begin(ctx) + tx, err := bs.db.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) for i, m := range mods { // renumber the area; keeps orders dense - if _, err := tx.Exec(ctx, `UPDATE modules SET sort_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, m.ID, i); err != nil { + if _, err := tx.Exec(ctx, `UPDATE modules SET sort_order=$2 WHERE id=$1`, m.ID, i); err != nil { return err } } @@ -112,16 +111,16 @@ func (s *Store) MoveModule(ctx context.Context, blogID, id int64, dir int) error } // ResetModules puts the layout back to what a new blog starts with. -func (s *Store) ResetModules(ctx context.Context, blogID int64) error { - tx, err := s.db.Begin(ctx) +func (bs *BlogStore) ResetModules(ctx context.Context) error { + tx, err := bs.db.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) - if _, err := tx.Exec(ctx, `DELETE FROM modules WHERE blog_id=$1`, blogID); err != nil { + if _, err := tx.Exec(ctx, `DELETE FROM modules`); err != nil { return err } - if err := insertDefaultModules(ctx, tx, blogID); err != nil { + if err := insertDefaultModules(ctx, tx); err != nil { return err } return tx.Commit(ctx) @@ -129,8 +128,8 @@ func (s *Store) ResetModules(ctx context.Context, blogID int64) error { // insertDefaultModules is the starting layout: title and menu in the header, // an RSS link in the footer. Must match what the layout migration gave old blogs. -func insertDefaultModules(ctx context.Context, tx querier, blogID int64) error { - _, err := tx.Exec(ctx, `INSERT INTO modules (blog_id, area, kind, sort_order) VALUES - ($1,'header','title',0), ($1,'header','menu',1), ($1,'footer','rss',0)`, blogID) +func insertDefaultModules(ctx context.Context, tx querier) error { + _, err := tx.Exec(ctx, `INSERT INTO modules (area, kind, sort_order) VALUES + ('header','title',0), ('header','menu',1), ('footer','rss',0)`) return err } -- cgit v1.2.3