diff options
Diffstat (limited to 'internal')
35 files changed, 883 insertions, 301 deletions
diff --git a/internal/db/db.go b/internal/db/db.go index 44d489b..601680d 100644 --- a/internal/db/db.go +++ b/internal/db/db.go @@ -1,22 +1,45 @@ -// Package db opens the Postgres pool and applies embedded migrations. +// Package db opens the Postgres pools and applies the embedded migrations. +// +// There are two kinds of database: the control database (users and the blog +// registry, named by DATABASE_URL) and one database per blog on the same +// server, created by the app. A Cluster hands out pools for both. package db import ( "context" "database/sql" "embed" + "errors" "fmt" + "io/fs" + "net/url" + "regexp" + "strings" + "sync" + "time" + "github.com/jackc/pgx/v5" + "github.com/jackc/pgx/v5/pgconn" "github.com/jackc/pgx/v5/pgxpool" _ "github.com/jackc/pgx/v5/stdlib" "github.com/pressly/goose/v3" ) -//go:embed migrations/*.sql +//go:embed migrations/control/*.sql migrations/blog/*.sql var migrations embed.FS -func Open(ctx context.Context, url string) (*pgxpool.Pool, error) { - pool, err := pgxpool.New(ctx, url) +// Cluster is the connection to one Postgres server: the control pool plus a +// lazily opened, cached pool per blog database. +type Cluster struct { + controlURL string + control *pgxpool.Pool + mu sync.Mutex + blogs map[string]*pgxpool.Pool +} + +// Open connects to the control database. +func Open(ctx context.Context, controlURL string) (*Cluster, error) { + pool, err := pgxpool.New(ctx, controlURL) if err != nil { return nil, fmt.Errorf("connect: %w", err) } @@ -24,20 +47,155 @@ func Open(ctx context.Context, url string) (*pgxpool.Pool, error) { pool.Close() return nil, fmt.Errorf("ping: %w", err) } - return pool, nil + return &Cluster{controlURL: controlURL, control: pool, blogs: map[string]*pgxpool.Pool{}}, nil } -// Migrate applies all pending migrations using goose over database/sql. -func Migrate(ctx context.Context, url string) error { - sqldb, err := sql.Open("pgx", url) +func (c *Cluster) Control() *pgxpool.Pool { return c.control } + +// Blog returns the pool for a blog database, opening it on first use. Blog +// pools are small and drop idle connections quickly, so a hundred quiet blogs +// cost nothing; only the busy ones hold connections. +func (c *Cluster) Blog(ctx context.Context, dbName string) (*pgxpool.Pool, error) { + c.mu.Lock() + defer c.mu.Unlock() + if p, ok := c.blogs[dbName]; ok { + return p, nil + } + cfg, err := pgxpool.ParseConfig(c.BlogURL(dbName)) + if err != nil { + return nil, err + } + cfg.MaxConns = 4 + cfg.MinConns = 0 + cfg.MaxConnIdleTime = 2 * time.Minute + p, err := pgxpool.NewWithConfig(ctx, cfg) + if err != nil { + return nil, err + } + c.blogs[dbName] = p + return p, nil +} + +// forget closes and drops the cached pool for a blog database, if any. +func (c *Cluster) forget(dbName string) { + c.mu.Lock() + p, ok := c.blogs[dbName] + delete(c.blogs, dbName) + c.mu.Unlock() + if ok { + p.Close() + } +} + +// Close closes every pool. +func (c *Cluster) Close() { + c.mu.Lock() + defer c.mu.Unlock() + for name, p := range c.blogs { + p.Close() + delete(c.blogs, name) + } + c.control.Close() +} + +// BlogURL is the control URL pointed at another database on the same server. +func (c *Cluster) BlogURL(dbName string) string { return withDatabase(c.controlURL, dbName) } + +func withDatabase(dsn, dbName string) string { + u, err := url.Parse(dsn) + if err != nil { + return dsn + } + u.Path = "/" + dbName + return u.String() +} + +var dbNameRe = regexp.MustCompile(`^[a-z0-9_]{1,63}$`) + +// DBName is the database a blog lives in: "blog_" + subdomain, dashes as +// underscores so the name needs no quoting in psql or pg_dump. Subdomains only +// allow [a-z0-9-], so the mapping is one-to-one. +func DBName(subdomain string) string { return "blog_" + strings.ReplaceAll(subdomain, "-", "_") } + +// ErrDatabaseExists is returned by CreateBlogDB when the name is taken — a +// leftover from a deleted blog or a failed attempt that must be dropped by hand. +var ErrDatabaseExists = errors.New("database already exists") + +// CreateBlogDB creates an empty blog database and applies the blog migrations. +// CREATE DATABASE cannot run inside a transaction, so this always uses its own +// connection; callers doing registry work in a transaction must order it so a +// failure here rolls the transaction back. +func (c *Cluster) CreateBlogDB(ctx context.Context, dbName string) error { + if !dbNameRe.MatchString(dbName) { + return fmt.Errorf("invalid database name %q", dbName) + } + if err := createDatabase(ctx, c.control, dbName); err != nil { + return err + } + if err := MigrateBlog(ctx, c.BlogURL(dbName)); err != nil { + c.control.Exec(ctx, `DROP DATABASE IF EXISTS `+pgx.Identifier{dbName}.Sanitize()+` WITH (FORCE)`) + return fmt.Errorf("migrate %s: %w", dbName, err) + } + return nil +} + +// execer is the pool or connection createDatabase runs on. +type execer interface { + Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error) +} + +func createDatabase(ctx context.Context, db execer, dbName string) error { + _, err := db.Exec(ctx, `CREATE DATABASE `+pgx.Identifier{dbName}.Sanitize()) + var pgErr *pgconn.PgError + if errors.As(err, &pgErr) && pgErr.Code == "42P04" { // duplicate_database + return fmt.Errorf("%w: %s", ErrDatabaseExists, dbName) + } + if err != nil { + return fmt.Errorf("create database %s: %w", dbName, err) + } + return nil +} + +// DropBlogDB closes the blog's pool and drops its database, kicking any other +// session still connected to it. +func (c *Cluster) DropBlogDB(ctx context.Context, dbName string) error { + if !dbNameRe.MatchString(dbName) { + return fmt.Errorf("invalid database name %q", dbName) + } + c.forget(dbName) + _, err := c.control.Exec(ctx, `DROP DATABASE IF EXISTS `+pgx.Identifier{dbName}.Sanitize()+` WITH (FORCE)`) + return err +} + +// ---- migrations ------------------------------------------------------------ + +// MigrateControl applies the control database migrations, including the Go +// migration that moves content out into the blog databases. +func MigrateControl(ctx context.Context, controlURL string) error { + return migrate(ctx, controlURL, "migrations/control", goose.WithGoMigrations(splitMigration(controlURL))) +} + +// MigrateBlog applies the blog schema migrations to one blog database. +func MigrateBlog(ctx context.Context, blogURL string) error { + return migrate(ctx, blogURL, "migrations/blog") +} + +// migrate runs goose over database/sql. A Provider (rather than the package +// globals) keeps the two migration sets, and the Go migration, apart. +func migrate(ctx context.Context, dsn, dir string, opts ...goose.ProviderOption) error { + sqldb, err := sql.Open("pgx", dsn) if err != nil { return err } defer sqldb.Close() - goose.SetBaseFS(migrations) - goose.SetLogger(goose.NopLogger()) - if err := goose.SetDialect("postgres"); err != nil { + fsys, err := fs.Sub(migrations, dir) + if err != nil { + return err + } + p, err := goose.NewProvider(goose.DialectPostgres, sqldb, fsys, opts...) + if err != nil { return err } - return goose.UpContext(ctx, sqldb, "migrations") + _, err = p.Up(ctx) + return err } diff --git a/internal/db/db_test.go b/internal/db/db_test.go new file mode 100644 index 0000000..b4fa3da --- /dev/null +++ b/internal/db/db_test.go @@ -0,0 +1,25 @@ +package db + +import ( + "strings" + "testing" +) + +func TestDBName(t *testing.T) { + for sub, want := range map[string]string{"alice": "blog_alice", "my-blog": "blog_my_blog", "a-b-c": "blog_a_b_c", "www": "blog_www"} { + if got := DBName(sub); got != want || !dbNameRe.MatchString(got) { + t.Errorf("DBName(%q) = %q, want %q", sub, got, want) + } + } + // the longest subdomain the admin form accepts still fits a Postgres identifier + if n := len(DBName(strings.Repeat("a-", 29))); n > 63 { // 58 chars, the admin form's limit + t.Errorf("database name too long: %d", n) + } +} + +func TestWithDatabase(t *testing.T) { + got := withDatabase("postgres://u:p@db:5432/blogspace?sslmode=disable", "blog_alice") + if want := "postgres://u:p@db:5432/blog_alice?sslmode=disable"; got != want { + t.Errorf("got %q, want %q", got, want) + } +} diff --git a/internal/db/migrations/blog/00001_init.sql b/internal/db/migrations/blog/00001_init.sql new file mode 100644 index 0000000..4649935 --- /dev/null +++ b/internal/db/migrations/blog/00001_init.sql @@ -0,0 +1,91 @@ +-- +goose Up +-- One database per blog: nothing here carries a blog id, the database is the scope. +CREATE TABLE settings ( + id boolean PRIMARY KEY DEFAULT true CHECK (id), -- exactly one row + title text NOT NULL, + tagline text NOT NULL DEFAULT '', + theme jsonb NOT NULL DEFAULT '{}'::jsonb, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE pages ( + id bigserial PRIMARY KEY, + slug text NOT NULL UNIQUE, + title text NOT NULL, + intro_md text NOT NULL DEFAULT '', + intro_html text NOT NULL DEFAULT '', + nav_order integer NOT NULL DEFAULT 0, + is_home boolean NOT NULL DEFAULT false, + created_at timestamptz NOT NULL DEFAULT now() +); +CREATE UNIQUE INDEX pages_one_home ON pages ((true)) WHERE is_home; + +CREATE TABLE posts ( + id bigserial PRIMARY KEY, + page_id bigint NOT NULL REFERENCES pages(id) ON DELETE CASCADE, + slug text NOT NULL, + title text NOT NULL, + body_md text NOT NULL DEFAULT '', + body_html text NOT NULL DEFAULT '', + published boolean NOT NULL DEFAULT true, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (page_id, slug) +); +CREATE INDEX posts_page_created ON posts (page_id, created_at DESC); + +CREATE TABLE images ( + id uuid PRIMARY KEY, + filename text NOT NULL, + content_type text NOT NULL, + size integer NOT NULL, + data bytea NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); +CREATE INDEX images_created ON images (created_at DESC); + +-- Announcements: blog-wide notices shown on every page and post. +CREATE TABLE sections ( + id bigserial PRIMARY KEY, + title text NOT NULL DEFAULT '', + body_md text NOT NULL DEFAULT '', + body_html text NOT NULL DEFAULT '', + placement text NOT NULL DEFAULT 'main-top' + CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom')), + style text NOT NULL DEFAULT 'note' CHECK (style IN ('plain', 'note', 'warning')), + enabled boolean NOT NULL DEFAULT true, + sort_order integer NOT NULL DEFAULT 0, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now() +); +CREATE INDEX sections_order ON sections (sort_order, id); + +-- Layout modules: what each area of a blog (header, columns, footer) shows. +CREATE TABLE modules ( + id bigserial PRIMARY KEY, + area text NOT NULL CHECK (area IN ('header', 'left', 'right', 'above', 'below', 'footer')), + kind text NOT NULL CHECK (kind IN ('title', 'logo', 'menu', 'archive', 'recent', 'html', 'rss', 'text', 'sitemap')), + title text NOT NULL DEFAULT '', + body text NOT NULL DEFAULT '', + count integer NOT NULL DEFAULT 5, + sort_order integer NOT NULL DEFAULT 0, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now() +); +CREATE INDEX modules_area ON modules (area, sort_order, id); + +-- The menu: blog pages and custom links in one ordered list. +CREATE TABLE menu_items ( + id bigserial PRIMARY KEY, + page_id bigint REFERENCES pages(id) ON DELETE CASCADE, + label text NOT NULL DEFAULT '', + url text NOT NULL DEFAULT '', + sort_order integer NOT NULL DEFAULT 0, + CHECK (page_id IS NOT NULL OR url <> '') +); +CREATE UNIQUE INDEX menu_items_page ON menu_items (page_id) WHERE page_id IS NOT NULL; +CREATE INDEX menu_items_order ON menu_items (sort_order, id); + +-- +goose Down +DROP TABLE menu_items, modules, sections, images, posts, pages, settings; diff --git a/internal/db/migrations/00001_init.sql b/internal/db/migrations/control/00001_init.sql index e27dad4..e27dad4 100644 --- a/internal/db/migrations/00001_init.sql +++ b/internal/db/migrations/control/00001_init.sql diff --git a/internal/db/migrations/00002_sections.sql b/internal/db/migrations/control/00002_sections.sql index 388cb17..388cb17 100644 --- a/internal/db/migrations/00002_sections.sql +++ b/internal/db/migrations/control/00002_sections.sql diff --git a/internal/db/migrations/00003_layout.sql b/internal/db/migrations/control/00003_layout.sql index e8fff23..e8fff23 100644 --- a/internal/db/migrations/00003_layout.sql +++ b/internal/db/migrations/control/00003_layout.sql diff --git a/internal/db/migrations/00004_section_placement.sql b/internal/db/migrations/control/00004_section_placement.sql index c36787e..c36787e 100644 --- a/internal/db/migrations/00004_section_placement.sql +++ b/internal/db/migrations/control/00004_section_placement.sql diff --git a/internal/db/migrations/control/00005_per_blog_databases.sql b/internal/db/migrations/control/00005_per_blog_databases.sql new file mode 100644 index 0000000..92d3fa2 --- /dev/null +++ b/internal/db/migrations/control/00005_per_blog_databases.sql @@ -0,0 +1,12 @@ +-- +goose Up +-- Each blog gets its own database; the registry remembers which one. +-- db_name stays nullable until the Go migration 00006 has moved the content. +ALTER TABLE blogs ADD COLUMN db_name text UNIQUE; +-- "blog_" + subdomain must fit in a 63-char Postgres identifier. +ALTER TABLE blogs DROP CONSTRAINT blogs_subdomain_check; +ALTER TABLE blogs ADD CONSTRAINT blogs_subdomain_check CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,57}$'); + +-- +goose Down +ALTER TABLE blogs DROP CONSTRAINT blogs_subdomain_check; +ALTER TABLE blogs ADD CONSTRAINT blogs_subdomain_check CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,62}$'); +ALTER TABLE blogs DROP COLUMN db_name; diff --git a/internal/db/migrations/control/00007_drop_content.sql b/internal/db/migrations/control/00007_drop_content.sql new file mode 100644 index 0000000..9daa1ac --- /dev/null +++ b/internal/db/migrations/control/00007_drop_content.sql @@ -0,0 +1,9 @@ +-- +goose Up +-- The content now lives in the per-blog databases (see 00006 in split.go); +-- the control database keeps only users and the blog registry. +DROP TABLE menu_items, modules, sections, images, posts, pages; +ALTER TABLE blogs DROP COLUMN title, DROP COLUMN tagline, DROP COLUMN theme, DROP COLUMN updated_at; +ALTER TABLE blogs ALTER COLUMN db_name SET NOT NULL; + +-- +goose Down +-- Not reversible: the content is gone from this database. Restore from a backup instead. diff --git a/internal/db/split.go b/internal/db/split.go new file mode 100644 index 0000000..668d255 --- /dev/null +++ b/internal/db/split.go @@ -0,0 +1,152 @@ +package db + +import ( + "context" + "database/sql" + "errors" + "fmt" + "log" + + "github.com/jackc/pgx/v5" + "github.com/pressly/goose/v3" +) + +// splitMigration is control migration 00006: it moves every blog's content out +// of the control database into a database of its own. It runs in the control +// transaction, so either every blog is moved and marked with its db_name or +// nothing changes; the blog databases it created are then dropped by hand +// (the error says which) and the next start retries. 00007 drops the old +// tables afterwards. +func splitMigration(controlURL string) *goose.Migration { + up := func(ctx context.Context, tx *sql.Tx) error { + return splitBlogs(ctx, tx, controlURL) + } + down := func(ctx context.Context, tx *sql.Tx) error { + return errors.New("the per-blog split cannot be undone; restore the control database from a backup") + } + return goose.NewGoMigration(6, &goose.GoFunc{RunTx: up}, &goose.GoFunc{RunTx: down}) +} + +// splitTable is one content table to copy: the query selects the blog's rows +// in the column order of the new table. +type splitTable struct { + name string + cols []string + query string // $1 = blog id + seq bool // bigserial id to bump after the copy +} + +var splitTables = []splitTable{ + {"pages", []string{"id", "slug", "title", "intro_md", "intro_html", "nav_order", "is_home", "created_at"}, + `SELECT id, slug, title, intro_md, intro_html, nav_order, is_home, created_at FROM pages WHERE blog_id=$1`, true}, + {"posts", []string{"id", "page_id", "slug", "title", "body_md", "body_html", "published", "created_at", "updated_at"}, + `SELECT p.id, p.page_id, p.slug, p.title, p.body_md, p.body_html, p.published, p.created_at, p.updated_at + FROM posts p JOIN pages g ON g.id=p.page_id WHERE g.blog_id=$1`, true}, + {"images", []string{"id", "filename", "content_type", "size", "data", "created_at"}, + `SELECT id, filename, content_type, size, data, created_at FROM images WHERE blog_id=$1`, false}, + {"sections", []string{"id", "title", "body_md", "body_html", "placement", "style", "enabled", "sort_order", "created_at", "updated_at"}, + `SELECT id, title, body_md, body_html, placement, style, enabled, sort_order, created_at, updated_at FROM sections WHERE blog_id=$1`, true}, + {"modules", []string{"id", "area", "kind", "title", "body", "count", "sort_order", "created_at", "updated_at"}, + `SELECT id, area, kind, title, body, count, sort_order, created_at, updated_at FROM modules WHERE blog_id=$1`, true}, + {"menu_items", []string{"id", "page_id", "label", "url", "sort_order"}, + `SELECT id, page_id, label, url, sort_order FROM menu_items WHERE blog_id=$1`, true}, +} + +func splitBlogs(ctx context.Context, tx *sql.Tx, controlURL string) error { + type blog struct { + id int64 + sub, title, tagline string + theme []byte + createdAt, updatedAt any + } + rows, err := tx.QueryContext(ctx, `SELECT id, subdomain, title, tagline, theme, created_at, updated_at FROM blogs WHERE db_name IS NULL ORDER BY id`) + if err != nil { + return err + } + var blogs []blog + for rows.Next() { + var b blog + if err := rows.Scan(&b.id, &b.sub, &b.title, &b.tagline, &b.theme, &b.createdAt, &b.updatedAt); err != nil { + rows.Close() + return err + } + blogs = append(blogs, b) + } + rows.Close() + if err := rows.Err(); err != nil { + return err + } + if len(blogs) == 0 { + return nil + } + + // DDL needs its own connection: CREATE DATABASE refuses to run in a transaction. + admin, err := pgx.Connect(ctx, controlURL) + if err != nil { + return err + } + defer admin.Close(ctx) + var created []string + for _, b := range blogs { + name := DBName(b.sub) + log.Printf("moving blog %q into database %s", b.sub, name) + if err := createDatabase(ctx, admin, name); err != nil { + return fmt.Errorf("%w — a leftover of an earlier failed split must be dropped by hand (created so far: %v)", err, created) + } + created = append(created, name) + if err := MigrateBlog(ctx, withDatabase(controlURL, name)); err != nil { + return fmt.Errorf("migrate %s: %w (drop the databases %v before retrying)", name, err, created) + } + if err := copyBlog(ctx, tx, withDatabase(controlURL, name), b.id, b.title, b.tagline, b.theme, b.createdAt, b.updatedAt); err != nil { + return fmt.Errorf("copy blog %q: %w (drop the databases %v before retrying)", b.sub, err, created) + } + if _, err := tx.ExecContext(ctx, `UPDATE blogs SET db_name=$1 WHERE id=$2`, name, b.id); err != nil { + return err + } + } + return nil +} + +// copyBlog streams one blog's rows from the control transaction into its new database. +func copyBlog(ctx context.Context, tx *sql.Tx, blogURL string, blogID int64, title, tagline string, theme []byte, createdAt, updatedAt any) error { + conn, err := pgx.Connect(ctx, blogURL) + if err != nil { + return err + } + defer conn.Close(ctx) + if _, err := conn.Exec(ctx, `INSERT INTO settings (title, tagline, theme, created_at, updated_at) VALUES ($1,$2,$3,$4,$5)`, + title, tagline, theme, createdAt, updatedAt); err != nil { + return fmt.Errorf("settings: %w", err) + } + for _, t := range splitTables { + rows, err := tx.QueryContext(ctx, t.query, blogID) + if err != nil { + return fmt.Errorf("%s: %w", t.name, err) + } + n := len(t.cols) + src := pgx.CopyFromFunc(func() ([]any, error) { + if !rows.Next() { + return nil, rows.Err() + } + vals := make([]any, n) + ptrs := make([]any, n) + for i := range vals { + ptrs[i] = &vals[i] + } + // database/sql hands back int64/string/bool/[]byte/time.Time, + // which pgx encodes for the matching column types. + return vals, rows.Scan(ptrs...) + }) + _, err = conn.CopyFrom(ctx, pgx.Identifier{t.name}, t.cols, src) + rows.Close() + if err != nil { + return fmt.Errorf("%s: %w", t.name, err) + } + if t.seq { + if _, err := conn.Exec(ctx, `SELECT setval(pg_get_serial_sequence($1,'id'), coalesce(max(id),0)+1, false) FROM `+pgx.Identifier{t.name}.Sanitize(), t.name); err != nil { + return fmt.Errorf("%s sequence: %w", t.name, err) + } + } + } + return nil +} diff --git a/internal/store/blogs.go b/internal/store/blogs.go index b7edee0..a2e88a8 100644 --- a/internal/store/blogs.go +++ b/internal/store/blogs.go @@ -3,34 +3,52 @@ package store import ( "context" "encoding/json" + "errors" + "fmt" "time" + "github.com/gramanas/blogspace/internal/db" "github.com/jackc/pgx/v5" ) +// Blog is a registry row (control database) plus the settings row of the +// blog's own database, which Store.Open fills in. type Blog struct { ID int64 OwnerID int64 Subdomain string + DBName string + CreatedAt time.Time + // from the blog database Title string Tagline string ThemeJSON json.RawMessage - CreatedAt time.Time UpdatedAt time.Time } -const blogCols = `id, owner_id, subdomain, title, tagline, theme, created_at, updated_at` +const blogCols = `id, owner_id, subdomain, db_name, created_at` func scanBlog(row interface{ Scan(...any) error }) (*Blog, error) { var b Blog - err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.Title, &b.Tagline, &b.ThemeJSON, &b.CreatedAt, &b.UpdatedAt) + err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.DBName, &b.CreatedAt) if err != nil { return nil, wrap(err) } return &b, nil } -// CreateBlogger creates a user, their blog and a default home page in one transaction. +func (bs *BlogStore) loadSettings(ctx context.Context, b *Blog) error { + err := bs.db.QueryRow(ctx, `SELECT title, tagline, theme, updated_at FROM settings`).Scan(&b.Title, &b.Tagline, &b.ThemeJSON, &b.UpdatedAt) + if errors.Is(err, pgx.ErrNoRows) { // registered, but the database is empty: not a 404 + return fmt.Errorf("database %s of blog %q has no settings row (empty or wrong dump?)", b.DBName, b.Subdomain) + } + if err != nil { + return fmt.Errorf("settings of %s: %w", b.DBName, err) + } + return nil +} + +// CreateBlogger creates a user, their blog and a default home page. func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdomain, title string) (*User, *Blog, error) { tx, err := s.db.Begin(ctx) if err != nil { @@ -43,13 +61,10 @@ func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdo if err != nil { return nil, nil, err } - b, err := createBlog(ctx, tx, u.ID, subdomain, title) + b, err := s.createBlog(ctx, tx, u.ID, subdomain, title) if err != nil { return nil, nil, err } - if err := tx.Commit(ctx); err != nil { - return nil, nil, err - } return u, b, nil } @@ -60,30 +75,64 @@ func (s *Store) CreateBlog(ctx context.Context, ownerID int64, subdomain, title return nil, err } defer tx.Rollback(ctx) - b, err := createBlog(ctx, tx, ownerID, subdomain, title) + return s.createBlog(ctx, tx, ownerID, subdomain, title) +} + +// createBlog registers the blog in tx, then creates and fills its database and +// commits. The registry insert goes first so a taken subdomain fails before +// any DDL; if anything after that fails the transaction rolls back and the new +// database is dropped again. +func (s *Store) createBlog(ctx context.Context, tx pgx.Tx, ownerID int64, subdomain, title string) (*Blog, error) { + name := db.DBName(subdomain) + b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, db_name) VALUES ($1,$2,$3) RETURNING `+blogCols, + ownerID, subdomain, name)) + if err != nil { + return nil, err + } + if err := s.cluster.CreateBlogDB(ctx, name); err != nil { + return nil, err + } + bs, err := s.fillNewBlog(ctx, b, title) + if err == nil { + err = tx.Commit(ctx) + } if err != nil { + s.cluster.DropBlogDB(ctx, name) return nil, err } - return b, tx.Commit(ctx) + if err := bs.loadSettings(ctx, b); err != nil { + return nil, err + } + return b, nil } -func createBlog(ctx context.Context, tx pgx.Tx, ownerID int64, subdomain, title string) (*Blog, error) { - b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, title) VALUES ($1,$2,$3) RETURNING `+blogCols, - ownerID, subdomain, title)) +// fillNewBlog writes what every new blog starts with: its settings, a home +// page in the menu and the default layout. +func (s *Store) fillNewBlog(ctx context.Context, b *Blog, title string) (*BlogStore, error) { + pool, err := s.cluster.Blog(ctx, b.DBName) if err != nil { return nil, err } + bs := &BlogStore{db: pool} + btx, err := pool.Begin(ctx) + if err != nil { + return nil, err + } + defer btx.Rollback(ctx) + if _, err := btx.Exec(ctx, `INSERT INTO settings (title, created_at, updated_at) VALUES ($1,$2,$2)`, title, b.CreatedAt); err != nil { + return nil, err + } var homeID int64 - if err := tx.QueryRow(ctx, `INSERT INTO pages (blog_id, slug, title, nav_order, is_home) VALUES ($1,'home','Home',0,true) RETURNING id`, b.ID).Scan(&homeID); err != nil { + if err := btx.QueryRow(ctx, `INSERT INTO pages (slug, title, nav_order, is_home) VALUES ('home','Home',0,true) RETURNING id`).Scan(&homeID); err != nil { return nil, wrap(err) } - if err := addMenuPage(ctx, tx, b.ID, homeID); err != nil { + if err := addMenuPage(ctx, btx, homeID); err != nil { return nil, err } - if err := insertDefaultModules(ctx, tx, b.ID); err != nil { + if err := insertDefaultModules(ctx, btx); err != nil { return nil, err } - return b, nil + return bs, btx.Commit(ctx) } func (s *Store) BlogByID(ctx context.Context, id int64) (*Blog, error) { @@ -98,17 +147,38 @@ func (s *Store) BlogByOwner(ctx context.Context, ownerID int64) (*Blog, error) { return scanBlog(s.db.QueryRow(ctx, `SELECT `+blogCols+` FROM blogs WHERE owner_id=$1`, ownerID)) } -func (s *Store) UpdateBlogSettings(ctx context.Context, id int64, title, tagline string) error { - _, err := s.db.Exec(ctx, `UPDATE blogs SET title=$2, tagline=$3, updated_at=now() WHERE id=$1`, id, title, tagline) - return err +// ListBlogs returns every registered blog, oldest first. +func (s *Store) ListBlogs(ctx context.Context) ([]Blog, error) { + rows, err := s.db.Query(ctx, `SELECT `+blogCols+` FROM blogs ORDER BY id`) + if err != nil { + return nil, err + } + defer rows.Close() + var out []Blog + for rows.Next() { + b, err := scanBlog(rows) + if err != nil { + return nil, err + } + out = append(out, *b) + } + return out, rows.Err() +} + +// DeleteBlog unregisters the blog and drops its database. +func (s *Store) DeleteBlog(ctx context.Context, b *Blog) error { + if _, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, b.ID); err != nil { + return err + } + return s.cluster.DropBlogDB(ctx, b.DBName) } -func (s *Store) UpdateBlogTheme(ctx context.Context, id int64, theme json.RawMessage) error { - _, err := s.db.Exec(ctx, `UPDATE blogs SET theme=$2, updated_at=now() WHERE id=$1`, id, theme) +func (bs *BlogStore) UpdateSettings(ctx context.Context, title, tagline string) error { + _, err := bs.db.Exec(ctx, `UPDATE settings SET title=$1, tagline=$2, updated_at=now()`, title, tagline) return err } -func (s *Store) DeleteBlog(ctx context.Context, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, id) +func (bs *BlogStore) UpdateTheme(ctx context.Context, theme json.RawMessage) error { + _, err := bs.db.Exec(ctx, `UPDATE settings SET theme=$1, updated_at=now()`, theme) return err } diff --git a/internal/store/images.go b/internal/store/images.go index 3699e75..b222468 100644 --- a/internal/store/images.go +++ b/internal/store/images.go @@ -9,7 +9,6 @@ import ( type Image struct { ID uuid.UUID - BlogID int64 Filename string ContentType string Size int @@ -17,15 +16,15 @@ type Image struct { CreatedAt time.Time } -func (s *Store) CreateImage(ctx context.Context, blogID int64, filename, contentType string, data []byte) (*Image, error) { - img := &Image{ID: uuid.New(), BlogID: blogID, Filename: filename, ContentType: contentType, Size: len(data)} - err := s.db.QueryRow(ctx, `INSERT INTO images (id, blog_id, filename, content_type, size, data) VALUES ($1,$2,$3,$4,$5,$6) RETURNING created_at`, - img.ID, blogID, filename, contentType, len(data), data).Scan(&img.CreatedAt) +func (bs *BlogStore) CreateImage(ctx context.Context, filename, contentType string, data []byte) (*Image, error) { + img := &Image{ID: uuid.New(), Filename: filename, ContentType: contentType, Size: len(data)} + err := bs.db.QueryRow(ctx, `INSERT INTO images (id, filename, content_type, size, data) VALUES ($1,$2,$3,$4,$5) RETURNING created_at`, + img.ID, filename, contentType, len(data), data).Scan(&img.CreatedAt) return img, err } -func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) { - rows, err := s.db.Query(ctx, `SELECT id, blog_id, filename, content_type, size, created_at FROM images WHERE blog_id=$1 ORDER BY created_at DESC`, blogID) +func (bs *BlogStore) ListImages(ctx context.Context) ([]Image, error) { + rows, err := bs.db.Query(ctx, `SELECT id, filename, content_type, size, created_at FROM images ORDER BY created_at DESC`) if err != nil { return nil, err } @@ -33,7 +32,7 @@ func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) { var out []Image for rows.Next() { var i Image - if err := rows.Scan(&i.ID, &i.BlogID, &i.Filename, &i.ContentType, &i.Size, &i.CreatedAt); err != nil { + if err := rows.Scan(&i.ID, &i.Filename, &i.ContentType, &i.Size, &i.CreatedAt); err != nil { return nil, err } out = append(out, i) @@ -42,17 +41,17 @@ func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) { } // ImageData loads an image including its bytes. -func (s *Store) ImageData(ctx context.Context, id uuid.UUID) (*Image, error) { +func (bs *BlogStore) ImageData(ctx context.Context, id uuid.UUID) (*Image, error) { var i Image - err := s.db.QueryRow(ctx, `SELECT id, blog_id, filename, content_type, size, data, created_at FROM images WHERE id=$1`, id). - Scan(&i.ID, &i.BlogID, &i.Filename, &i.ContentType, &i.Size, &i.Data, &i.CreatedAt) + err := bs.db.QueryRow(ctx, `SELECT id, filename, content_type, size, data, created_at FROM images WHERE id=$1`, id). + Scan(&i.ID, &i.Filename, &i.ContentType, &i.Size, &i.Data, &i.CreatedAt) if err != nil { return nil, wrap(err) } return &i, nil } -func (s *Store) DeleteImage(ctx context.Context, blogID int64, id uuid.UUID) error { - _, err := s.db.Exec(ctx, `DELETE FROM images WHERE blog_id=$1 AND id=$2`, blogID, id) +func (bs *BlogStore) DeleteImage(ctx context.Context, id uuid.UUID) error { + _, err := bs.db.Exec(ctx, `DELETE FROM images WHERE id=$1`, id) return err } diff --git a/internal/store/menu.go b/internal/store/menu.go index 2d3eabd..cedb7ad 100644 --- a/internal/store/menu.go +++ b/internal/store/menu.go @@ -2,14 +2,11 @@ package store import ( "context" - - "github.com/jackc/pgx/v5/pgconn" ) // MenuItem is one entry of a blog's menu: either a page of the blog or a custom link. type MenuItem struct { ID int64 - BlogID int64 PageID *int64 // nil for custom links Label string // custom links only; pages use their title URL string // custom links only @@ -39,11 +36,11 @@ func (m MenuItem) Href() string { return "/" + m.PageSlug } -const menuCols = `m.id, m.blog_id, m.page_id, m.label, m.url, m.sort_order, coalesce(p.title,''), coalesce(p.slug,''), coalesce(p.is_home,false)` +const menuCols = `m.id, m.page_id, m.label, m.url, m.sort_order, coalesce(p.title,''), coalesce(p.slug,''), coalesce(p.is_home,false)` -func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error) { - rows, err := s.db.Query(ctx, `SELECT `+menuCols+` FROM menu_items m LEFT JOIN pages p ON p.id=m.page_id - WHERE m.blog_id=$1 ORDER BY m.sort_order, m.id`, blogID) +func (bs *BlogStore) ListMenu(ctx context.Context) ([]MenuItem, error) { + rows, err := bs.db.Query(ctx, `SELECT `+menuCols+` FROM menu_items m LEFT JOIN pages p ON p.id=m.page_id + ORDER BY m.sort_order, m.id`) if err != nil { return nil, err } @@ -51,7 +48,7 @@ func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error) var out []MenuItem for rows.Next() { var m MenuItem - if err := rows.Scan(&m.ID, &m.BlogID, &m.PageID, &m.Label, &m.URL, &m.SortOrder, &m.PageTitle, &m.PageSlug, &m.IsHome); err != nil { + if err := rows.Scan(&m.ID, &m.PageID, &m.Label, &m.URL, &m.SortOrder, &m.PageTitle, &m.PageSlug, &m.IsHome); err != nil { return nil, err } out = append(out, m) @@ -60,41 +57,41 @@ func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error) } // AddMenuPage appends a page to the menu; a page already in the menu is left where it is. -func (s *Store) AddMenuPage(ctx context.Context, blogID, pageID int64) error { - return addMenuPage(ctx, s.db, blogID, pageID) +func (bs *BlogStore) AddMenuPage(ctx context.Context, pageID int64) error { + return addMenuPage(ctx, bs.db, pageID) } -func addMenuPage(ctx context.Context, db querier, blogID, pageID int64) error { - _, err := db.Exec(ctx, `INSERT INTO menu_items (blog_id, page_id, sort_order) - SELECT $1, id, (SELECT coalesce(max(sort_order),-1)+1 FROM menu_items WHERE blog_id=$1) - FROM pages WHERE blog_id=$1 AND id=$2 ON CONFLICT (page_id) WHERE page_id IS NOT NULL DO NOTHING`, blogID, pageID) +func addMenuPage(ctx context.Context, db querier, pageID int64) error { + _, err := db.Exec(ctx, `INSERT INTO menu_items (page_id, sort_order) + SELECT id, (SELECT coalesce(max(sort_order),-1)+1 FROM menu_items) + FROM pages WHERE id=$1 ON CONFLICT (page_id) WHERE page_id IS NOT NULL DO NOTHING`, pageID) return err } // SetPageInMenu adds or removes a page's menu entry. -func (s *Store) SetPageInMenu(ctx context.Context, blogID, pageID int64, on bool) error { +func (bs *BlogStore) SetPageInMenu(ctx context.Context, pageID int64, on bool) error { if on { - return s.AddMenuPage(ctx, blogID, pageID) + return bs.AddMenuPage(ctx, pageID) } - _, err := s.db.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND page_id=$2`, blogID, pageID) + _, err := bs.db.Exec(ctx, `DELETE FROM menu_items WHERE page_id=$1`, pageID) return err } // AddMenuLink appends a custom link to the menu. -func (s *Store) AddMenuLink(ctx context.Context, blogID int64, label, url string) error { - _, err := s.db.Exec(ctx, `INSERT INTO menu_items (blog_id, label, url, sort_order) - VALUES ($1,$2,$3,(SELECT coalesce(max(sort_order),-1)+1 FROM menu_items WHERE blog_id=$1))`, blogID, label, url) +func (bs *BlogStore) AddMenuLink(ctx context.Context, label, url string) error { + _, err := bs.db.Exec(ctx, `INSERT INTO menu_items (label, url, sort_order) + VALUES ($1,$2,(SELECT coalesce(max(sort_order),-1)+1 FROM menu_items))`, label, url) return err } -func (s *Store) DeleteMenuItem(ctx context.Context, blogID, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND id=$2`, blogID, id) +func (bs *BlogStore) DeleteMenuItem(ctx context.Context, id int64) error { + _, err := bs.db.Exec(ctx, `DELETE FROM menu_items WHERE id=$1`, id) return err } // MoveMenuItem swaps sort_order with the neighbouring entry (dir = -1 up, +1 down). -func (s *Store) MoveMenuItem(ctx context.Context, blogID, id int64, dir int) error { - items, err := s.ListMenu(ctx, blogID) +func (bs *BlogStore) MoveMenuItem(ctx context.Context, id int64, dir int) error { + items, err := bs.ListMenu(ctx) if err != nil { return err } @@ -109,20 +106,15 @@ func (s *Store) MoveMenuItem(ctx context.Context, blogID, id int64, dir int) err return nil } items[idx], items[j] = items[j], items[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 items { // renumber everything; keeps orders dense - if _, err := tx.Exec(ctx, `UPDATE menu_items SET sort_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, m.ID, i); err != nil { + if _, err := tx.Exec(ctx, `UPDATE menu_items SET sort_order=$2 WHERE id=$1`, m.ID, i); err != nil { return err } } return tx.Commit(ctx) } - -// querier is what the helpers shared with page transactions need: the pool or a pgx.Tx. -type querier interface { - Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error) -} 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 } diff --git a/internal/store/pages.go b/internal/store/pages.go index cd0e652..55cad33 100644 --- a/internal/store/pages.go +++ b/internal/store/pages.go @@ -7,7 +7,6 @@ import ( type Page struct { ID int64 - BlogID int64 Slug string Title string IntroMD string @@ -19,20 +18,20 @@ type Page struct { PostCount int // filled by ListPages only } -const pageCols = `id, blog_id, slug, title, intro_md, intro_html, nav_order, EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id=pages.id), is_home, created_at` +const pageCols = `id, slug, title, intro_md, intro_html, nav_order, EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id=pages.id), is_home, created_at` func scanPage(row interface{ Scan(...any) error }) (*Page, error) { var p Page - err := row.Scan(&p.ID, &p.BlogID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt) + err := row.Scan(&p.ID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt) if err != nil { return nil, wrap(err) } return &p, nil } -func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) { - rows, err := s.db.Query(ctx, `SELECT `+pageCols+`, (SELECT count(*) FROM posts WHERE page_id=pages.id) - FROM pages WHERE blog_id=$1 ORDER BY nav_order, id`, blogID) +func (bs *BlogStore) ListPages(ctx context.Context) ([]Page, error) { + rows, err := bs.db.Query(ctx, `SELECT `+pageCols+`, (SELECT count(*) FROM posts WHERE page_id=pages.id) + FROM pages ORDER BY nav_order, id`) if err != nil { return nil, err } @@ -40,7 +39,7 @@ func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) { var out []Page for rows.Next() { var p Page - if err := rows.Scan(&p.ID, &p.BlogID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt, &p.PostCount); err != nil { + if err := rows.Scan(&p.ID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt, &p.PostCount); err != nil { return nil, err } out = append(out, p) @@ -48,34 +47,34 @@ func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) { return out, rows.Err() } -func (s *Store) PageByID(ctx context.Context, blogID, id int64) (*Page, error) { - return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 AND id=$2`, blogID, id)) +func (bs *BlogStore) PageByID(ctx context.Context, id int64) (*Page, error) { + return scanPage(bs.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE id=$1`, id)) } -func (s *Store) PageBySlug(ctx context.Context, blogID int64, slug string) (*Page, error) { - return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 AND slug=$2`, blogID, slug)) +func (bs *BlogStore) PageBySlug(ctx context.Context, slug string) (*Page, error) { + return scanPage(bs.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE slug=$1`, slug)) } -func (s *Store) HomePage(ctx context.Context, blogID int64) (*Page, error) { - return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 ORDER BY is_home DESC, nav_order, id LIMIT 1`, blogID)) +func (bs *BlogStore) HomePage(ctx context.Context) (*Page, error) { + return scanPage(bs.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages ORDER BY is_home DESC, nav_order, id LIMIT 1`)) } // CreatePage adds a page at the end of the page list and, when ShowInNav is // set, to the end of the menu. -func (s *Store) CreatePage(ctx context.Context, p *Page) (*Page, error) { - tx, err := s.db.Begin(ctx) +func (bs *BlogStore) CreatePage(ctx context.Context, p *Page) (*Page, error) { + tx, err := bs.db.Begin(ctx) if err != nil { return nil, err } defer tx.Rollback(ctx) - created, err := scanPage(tx.QueryRow(ctx, `INSERT INTO pages (blog_id, slug, title, intro_md, intro_html, nav_order) - VALUES ($1,$2,$3,$4,$5,(SELECT coalesce(max(nav_order),-1)+1 FROM pages WHERE blog_id=$1)) RETURNING `+pageCols, - p.BlogID, p.Slug, p.Title, p.IntroMD, p.IntroHTML)) + created, err := scanPage(tx.QueryRow(ctx, `INSERT INTO pages (slug, title, intro_md, intro_html, nav_order) + VALUES ($1,$2,$3,$4,(SELECT coalesce(max(nav_order),-1)+1 FROM pages)) RETURNING `+pageCols, + p.Slug, p.Title, p.IntroMD, p.IntroHTML)) if err != nil { return nil, err } if p.ShowInNav { - if err := addMenuPage(ctx, tx, p.BlogID, created.ID); err != nil { + if err := addMenuPage(ctx, tx, created.ID); err != nil { return nil, err } created.ShowInNav = true @@ -84,20 +83,20 @@ func (s *Store) CreatePage(ctx context.Context, p *Page) (*Page, error) { } // UpdatePage saves the page and keeps its menu entry in step with ShowInNav. -func (s *Store) UpdatePage(ctx context.Context, p *Page) error { - tx, err := s.db.Begin(ctx) +func (bs *BlogStore) UpdatePage(ctx context.Context, p *Page) error { + tx, err := bs.db.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) - if _, err := tx.Exec(ctx, `UPDATE pages SET slug=$3, title=$4, intro_md=$5, intro_html=$6 WHERE blog_id=$1 AND id=$2`, - p.BlogID, p.ID, p.Slug, p.Title, p.IntroMD, p.IntroHTML); err != nil { + if _, err := tx.Exec(ctx, `UPDATE pages SET slug=$2, title=$3, intro_md=$4, intro_html=$5 WHERE id=$1`, + p.ID, p.Slug, p.Title, p.IntroMD, p.IntroHTML); err != nil { return wrap(err) } if p.ShowInNav { - err = addMenuPage(ctx, tx, p.BlogID, p.ID) + err = addMenuPage(ctx, tx, p.ID) } else { - _, err = tx.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND page_id=$2`, p.BlogID, p.ID) + _, err = tx.Exec(ctx, `DELETE FROM menu_items WHERE page_id=$1`, p.ID) } if err != nil { return err @@ -105,30 +104,30 @@ func (s *Store) UpdatePage(ctx context.Context, p *Page) error { return tx.Commit(ctx) } -func (s *Store) DeletePage(ctx context.Context, blogID, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM pages WHERE blog_id=$1 AND id=$2 AND NOT is_home`, blogID, id) +func (bs *BlogStore) DeletePage(ctx context.Context, id int64) error { + _, err := bs.db.Exec(ctx, `DELETE FROM pages WHERE id=$1 AND NOT is_home`, id) return err } // SetHomePage moves the home flag to the given page. -func (s *Store) SetHomePage(ctx context.Context, blogID, id int64) error { - tx, err := s.db.Begin(ctx) +func (bs *BlogStore) SetHomePage(ctx context.Context, id int64) error { + tx, err := bs.db.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) - if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=false WHERE blog_id=$1 AND is_home`, blogID); err != nil { + if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=false WHERE is_home`); err != nil { return err } - if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=true WHERE blog_id=$1 AND id=$2`, blogID, id); err != nil { + if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=true WHERE id=$1`, id); err != nil { return err } return tx.Commit(ctx) } // MovePage swaps nav_order with the neighbouring page (dir = -1 up, +1 down). -func (s *Store) MovePage(ctx context.Context, blogID, id int64, dir int) error { - pages, err := s.ListPages(ctx, blogID) +func (bs *BlogStore) MovePage(ctx context.Context, id int64, dir int) error { + pages, err := bs.ListPages(ctx) if err != nil { return err } @@ -143,13 +142,13 @@ func (s *Store) MovePage(ctx context.Context, blogID, id int64, dir int) error { return nil } pages[idx], pages[j] = pages[j], pages[idx] - tx, err := s.db.Begin(ctx) + tx, err := bs.db.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) for i, p := range pages { // renumber everything; keeps orders dense - if _, err := tx.Exec(ctx, `UPDATE pages SET nav_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, p.ID, i); err != nil { + if _, err := tx.Exec(ctx, `UPDATE pages SET nav_order=$2 WHERE id=$1`, p.ID, i); err != nil { return err } } diff --git a/internal/store/posts.go b/internal/store/posts.go index 9f3c4f9..94bc856 100644 --- a/internal/store/posts.go +++ b/internal/store/posts.go @@ -31,8 +31,8 @@ func scanPost(row interface{ Scan(...any) error }) (*Post, error) { return &p, nil } -func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) { - rows, err := s.db.Query(ctx, q, args...) +func (bs *BlogStore) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) { + rows, err := bs.db.Query(ctx, q, args...) if err != nil { return nil, err } @@ -48,21 +48,21 @@ func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post return out, rows.Err() } -// ListPosts returns all posts of a blog for the dashboard, optionally filtered by page. -func (s *Store) ListPosts(ctx context.Context, blogID int64, pageID int64) ([]Post, error) { - return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND ($2=0 OR p.page_id=$2) ORDER BY p.created_at DESC, p.id DESC`, blogID, pageID) +// ListPosts returns all posts of the blog for the dashboard, optionally filtered by page. +func (bs *BlogStore) ListPosts(ctx context.Context, pageID int64) ([]Post, error) { + return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id + WHERE ($1=0 OR p.page_id=$1) ORDER BY p.created_at DESC, p.id DESC`, pageID) } // PublishedPosts returns a page of published posts for the public site. -func (s *Store) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) { - posts, err := s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id +func (bs *BlogStore) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) { + posts, err := bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.page_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, pageID, limit, offset) if err != nil { return nil, 0, err } var total int - err = s.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total) + err = bs.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total) return posts, total, err } @@ -74,10 +74,10 @@ type PostRef struct { CreatedAt time.Time } -// PublishedPostIndex lists every published post of a blog, newest first, without bodies. -func (s *Store) PublishedPostIndex(ctx context.Context, blogID int64) ([]PostRef, error) { - rows, err := s.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC`, blogID) +// PublishedPostIndex lists every published post of the blog, newest first, without bodies. +func (bs *BlogStore) PublishedPostIndex(ctx context.Context) ([]PostRef, error) { + rows, err := bs.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id + WHERE p.published ORDER BY p.created_at DESC, p.id DESC`) if err != nil { return nil, err } @@ -93,40 +93,38 @@ func (s *Store) PublishedPostIndex(ctx context.Context, blogID int64) ([]PostRef return out, rows.Err() } -// RecentPublishedPosts returns the newest published posts across a whole blog (for feeds). -func (s *Store) RecentPublishedPosts(ctx context.Context, blogID int64, limit int) ([]Post, error) { - return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2`, blogID, limit) +// RecentPublishedPosts returns the newest published posts across the whole blog (for feeds). +func (bs *BlogStore) RecentPublishedPosts(ctx context.Context, limit int) ([]Post, error) { + return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id + WHERE p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $1`, limit) } -func (s *Store) PostByID(ctx context.Context, blogID, id int64) (*Post, error) { - return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND p.id=$2`, blogID, id)) +func (bs *BlogStore) PostByID(ctx context.Context, id int64) (*Post, error) { + return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id)) } -func (s *Store) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) { - return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id +func (bs *BlogStore) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) { + return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.page_id=$1 AND p.slug=$2 AND p.published`, pageID, slug)) } -func (s *Store) CreatePost(ctx context.Context, p *Post) (*Post, error) { +func (bs *BlogStore) CreatePost(ctx context.Context, p *Post) (*Post, error) { var id int64 - err := s.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`, + err := bs.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published).Scan(&id) if err != nil { return nil, wrap(err) } - return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id)) + return bs.PostByID(ctx, id) } -// UpdatePost updates a post; the page must belong to the same blog (checked by handler). -func (s *Store) UpdatePost(ctx context.Context, p *Post) error { - _, err := s.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`, +func (bs *BlogStore) UpdatePost(ctx context.Context, p *Post) error { + _, err := bs.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`, p.ID, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published) return wrap(err) } -func (s *Store) DeletePost(ctx context.Context, blogID, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM posts p USING pages g WHERE g.id=p.page_id AND g.blog_id=$1 AND p.id=$2`, blogID, id) +func (bs *BlogStore) DeletePost(ctx context.Context, id int64) error { + _, err := bs.db.Exec(ctx, `DELETE FROM posts WHERE id=$1`, id) return err } diff --git a/internal/store/sections.go b/internal/store/sections.go index caac251..2f742f7 100644 --- a/internal/store/sections.go +++ b/internal/store/sections.go @@ -9,7 +9,6 @@ import ( // Section is an announcement shown on every page and post of a blog. type Section struct { ID int64 - BlogID int64 Title string BodyMD string BodyHTML string @@ -25,19 +24,19 @@ type Section struct { func (s Section) Column() string { c, _, _ := strings.Cut(s.Placement, "-"); return c } func (s Section) Position() string { _, p, _ := strings.Cut(s.Placement, "-"); return p } -const sectionCols = `id, blog_id, title, body_md, body_html, placement, style, enabled, sort_order, created_at, updated_at` +const sectionCols = `id, title, body_md, body_html, placement, style, enabled, sort_order, created_at, updated_at` func scanSection(row interface{ Scan(...any) error }) (*Section, error) { var s Section - err := row.Scan(&s.ID, &s.BlogID, &s.Title, &s.BodyMD, &s.BodyHTML, &s.Placement, &s.Style, &s.Enabled, &s.SortOrder, &s.CreatedAt, &s.UpdatedAt) + err := row.Scan(&s.ID, &s.Title, &s.BodyMD, &s.BodyHTML, &s.Placement, &s.Style, &s.Enabled, &s.SortOrder, &s.CreatedAt, &s.UpdatedAt) if err != nil { return nil, wrap(err) } return &s, nil } -func (s *Store) collectSections(ctx context.Context, q string, args ...any) ([]Section, error) { - rows, err := s.db.Query(ctx, q, args...) +func (bs *BlogStore) collectSections(ctx context.Context, q string, args ...any) ([]Section, error) { + rows, err := bs.db.Query(ctx, q, args...) if err != nil { return nil, err } @@ -53,44 +52,44 @@ func (s *Store) collectSections(ctx context.Context, q string, args ...any) ([]S return out, rows.Err() } -func (s *Store) ListSections(ctx context.Context, blogID int64) ([]Section, error) { - return s.collectSections(ctx, `SELECT `+sectionCols+` FROM sections WHERE blog_id=$1 ORDER BY sort_order, id`, blogID) +func (bs *BlogStore) ListSections(ctx context.Context) ([]Section, error) { + return bs.collectSections(ctx, `SELECT `+sectionCols+` FROM sections ORDER BY sort_order, id`) } // EnabledSections is what the public blog renders. -func (s *Store) EnabledSections(ctx context.Context, blogID int64) ([]Section, error) { - return s.collectSections(ctx, `SELECT `+sectionCols+` FROM sections WHERE blog_id=$1 AND enabled ORDER BY sort_order, id`, blogID) +func (bs *BlogStore) EnabledSections(ctx context.Context) ([]Section, error) { + return bs.collectSections(ctx, `SELECT `+sectionCols+` FROM sections WHERE enabled ORDER BY sort_order, id`) } -func (s *Store) SectionByID(ctx context.Context, blogID, id int64) (*Section, error) { - return scanSection(s.db.QueryRow(ctx, `SELECT `+sectionCols+` FROM sections WHERE blog_id=$1 AND id=$2`, blogID, id)) +func (bs *BlogStore) SectionByID(ctx context.Context, id int64) (*Section, error) { + return scanSection(bs.db.QueryRow(ctx, `SELECT `+sectionCols+` FROM sections WHERE id=$1`, id)) } -func (s *Store) CreateSection(ctx context.Context, sec *Section) (*Section, error) { - return scanSection(s.db.QueryRow(ctx, `INSERT INTO sections (blog_id, title, body_md, body_html, placement, style, enabled, sort_order) - VALUES ($1,$2,$3,$4,$5,$6,$7,(SELECT coalesce(max(sort_order),-1)+1 FROM sections WHERE blog_id=$1)) RETURNING `+sectionCols, - sec.BlogID, sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled)) +func (bs *BlogStore) CreateSection(ctx context.Context, sec *Section) (*Section, error) { + return scanSection(bs.db.QueryRow(ctx, `INSERT INTO sections (title, body_md, body_html, placement, style, enabled, sort_order) + VALUES ($1,$2,$3,$4,$5,$6,(SELECT coalesce(max(sort_order),-1)+1 FROM sections)) RETURNING `+sectionCols, + sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled)) } -func (s *Store) UpdateSection(ctx context.Context, sec *Section) error { - _, err := s.db.Exec(ctx, `UPDATE sections SET title=$3, body_md=$4, body_html=$5, placement=$6, style=$7, enabled=$8, updated_at=now() WHERE blog_id=$1 AND id=$2`, - sec.BlogID, sec.ID, sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled) +func (bs *BlogStore) UpdateSection(ctx context.Context, sec *Section) error { + _, err := bs.db.Exec(ctx, `UPDATE sections SET title=$2, body_md=$3, body_html=$4, placement=$5, style=$6, enabled=$7, updated_at=now() WHERE id=$1`, + sec.ID, sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled) return wrap(err) } -func (s *Store) SetSectionEnabled(ctx context.Context, blogID, id int64, on bool) error { - _, err := s.db.Exec(ctx, `UPDATE sections SET enabled=$3, updated_at=now() WHERE blog_id=$1 AND id=$2`, blogID, id, on) +func (bs *BlogStore) SetSectionEnabled(ctx context.Context, id int64, on bool) error { + _, err := bs.db.Exec(ctx, `UPDATE sections SET enabled=$2, updated_at=now() WHERE id=$1`, id, on) return err } -func (s *Store) DeleteSection(ctx context.Context, blogID, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM sections WHERE blog_id=$1 AND id=$2`, blogID, id) +func (bs *BlogStore) DeleteSection(ctx context.Context, id int64) error { + _, err := bs.db.Exec(ctx, `DELETE FROM sections WHERE id=$1`, id) return err } // MoveSection swaps sort_order with the neighbouring section (dir = -1 up, +1 down). -func (s *Store) MoveSection(ctx context.Context, blogID, id int64, dir int) error { - secs, err := s.ListSections(ctx, blogID) +func (bs *BlogStore) MoveSection(ctx context.Context, id int64, dir int) error { + secs, err := bs.ListSections(ctx) if err != nil { return err } @@ -105,13 +104,13 @@ func (s *Store) MoveSection(ctx context.Context, blogID, id int64, dir int) erro return nil } secs[idx], secs[j] = secs[j], secs[idx] - tx, err := s.db.Begin(ctx) + tx, err := bs.db.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) for i, sec := range secs { // renumber everything; keeps orders dense - if _, err := tx.Exec(ctx, `UPDATE sections SET sort_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, sec.ID, i); err != nil { + if _, err := tx.Exec(ctx, `UPDATE sections SET sort_order=$2 WHERE id=$1`, sec.ID, i); err != nil { return err } } diff --git a/internal/store/store.go b/internal/store/store.go index 472bb2a..c8e97ea 100644 --- a/internal/store/store.go +++ b/internal/store/store.go @@ -1,9 +1,15 @@ // Package store holds the data models and all SQL queries. +// +// Store talks to the control database (users, blog registry) and hands out a +// BlogStore per blog, which is bound to that blog's own database — nothing a +// BlogStore does can reach another blog's rows. package store import ( + "context" "errors" + "github.com/gramanas/blogspace/internal/db" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgconn" "github.com/jackc/pgx/v5/pgxpool" @@ -13,10 +19,36 @@ var ErrNotFound = errors.New("not found") var ErrConflict = errors.New("already exists") type Store struct { + db *pgxpool.Pool // control database + cluster *db.Cluster +} + +func New(cluster *db.Cluster) *Store { return &Store{db: cluster.Control(), cluster: cluster} } + +// BlogStore runs the content queries of one blog against its database. +type BlogStore struct { db *pgxpool.Pool } -func New(db *pgxpool.Pool) *Store { return &Store{db: db} } +// Open returns the blog's store and fills in the settings (title, tagline, +// theme) kept in the blog database. +func (s *Store) Open(ctx context.Context, b *Blog) (*BlogStore, error) { + pool, err := s.cluster.Blog(ctx, b.DBName) + if err != nil { + return nil, err + } + bs := &BlogStore{db: pool} + if err := bs.loadSettings(ctx, b); err != nil { + // A database restored underneath a running app (dropdb --force, + // createdb, psql < dump) leaves the pool holding dead connections; + // drop them and try once more before giving up. + pool.Reset() + if err := bs.loadSettings(ctx, b); err != nil { + return nil, err + } + } + return bs, nil +} // wrap maps driver errors onto the store's sentinel errors. func wrap(err error) error { @@ -32,3 +64,8 @@ func wrap(err error) error { } return err } + +// querier is what the helpers shared with transactions need: the pool or a pgx.Tx. +type querier interface { + Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error) +} diff --git a/internal/store/users.go b/internal/store/users.go index 3ea9bdb..0c47c24 100644 --- a/internal/store/users.go +++ b/internal/store/users.go @@ -2,6 +2,7 @@ package store import ( "context" + "errors" "time" ) @@ -69,9 +70,21 @@ func (s *Store) SetUserDisabled(ctx context.Context, id int64, disabled bool) er return err } +// DeleteUser removes the user, their registry row and their blog database. +// The registry goes first: a stale blog database is harmless and can be +// dropped by hand, a registry row without a database is not. func (s *Store) DeleteUser(ctx context.Context, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM users WHERE id=$1`, id) - return err + b, err := s.BlogByOwner(ctx, id) + if err != nil && !errors.Is(err, ErrNotFound) { + return err + } + if _, err := s.db.Exec(ctx, `DELETE FROM users WHERE id=$1`, id); err != nil { + return err + } + if b != nil { + return s.cluster.DropBlogDB(ctx, b.DBName) + } + return nil } // UserWithBlog is a row for the admin overview. @@ -79,12 +92,11 @@ type UserWithBlog struct { User BlogID *int64 Subdomain *string - BlogTitle *string } func (s *Store) ListUsers(ctx context.Context) ([]UserWithBlog, error) { rows, err := s.db.Query(ctx, `SELECT u.id, u.username, u.password_hash, u.role, u.disabled, u.token_version, u.created_at, - b.id, b.subdomain, b.title + b.id, b.subdomain FROM users u LEFT JOIN blogs b ON b.owner_id = u.id ORDER BY u.role, u.username`) if err != nil { @@ -95,7 +107,7 @@ func (s *Store) ListUsers(ctx context.Context) ([]UserWithBlog, error) { for rows.Next() { var r UserWithBlog if err := rows.Scan(&r.ID, &r.Username, &r.PasswordHash, &r.Role, &r.Disabled, &r.TokenVersion, &r.CreatedAt, - &r.BlogID, &r.Subdomain, &r.BlogTitle); err != nil { + &r.BlogID, &r.Subdomain); err != nil { return nil, err } out = append(out, r) diff --git a/internal/web/handlers_admin.go b/internal/web/handlers_admin.go index 4287411..e0b3991 100644 --- a/internal/web/handlers_admin.go +++ b/internal/web/handlers_admin.go @@ -9,11 +9,14 @@ import ( "github.com/gramanas/blogspace/internal/auth" "github.com/gramanas/blogspace/internal/config" + "github.com/gramanas/blogspace/internal/db" "github.com/gramanas/blogspace/internal/store" ) var usernameRe = regexp.MustCompile(`^[a-zA-Z0-9_.-]{2,40}$`) -var subdomainRe = regexp.MustCompile(`^[a-z0-9](-?[a-z0-9]){0,62}$`) + +// 58 chars at most: "blog_" + subdomain must fit a Postgres database name. +var subdomainRe = regexp.MustCompile(`^[a-z0-9](-?[a-z0-9]){0,57}$`) func (s *Server) handleAdmin(w http.ResponseWriter, r *http.Request) { users, err := s.st.ListUsers(r.Context()) @@ -70,6 +73,11 @@ func (s *Server) handleAdminNewUser(w http.ResponseWriter, r *http.Request) { s.renderStatus(w, r, http.StatusConflict, "admin/new_user.html", f) return } + if errors.Is(err, db.ErrDatabaseExists) { + f["error"] = "A database " + db.DBName(sub) + " already exists in Postgres (a leftover of a deleted blog?). Drop it or pick another subdomain." + s.renderStatus(w, r, http.StatusConflict, "admin/new_user.html", f) + return + } s.serverError(w, err) return } @@ -151,7 +159,7 @@ func (s *Server) handleAdminDeleteUser(w http.ResponseWriter, r *http.Request) { s.plainError(w, http.StatusBadRequest, "This user owns the root blog and cannot be deleted.") return } - if err := s.st.DeleteUser(r.Context(), u.ID); err != nil { // cascades to blog, pages, posts, images + if err := s.st.DeleteUser(r.Context(), u.ID); err != nil { // drops their blog database too s.serverError(w, err) return } diff --git a/internal/web/handlers_blog.go b/internal/web/handlers_blog.go index 42ec45b..f2e7713 100644 --- a/internal/web/handlers_blog.go +++ b/internal/web/handlers_blog.go @@ -17,19 +17,19 @@ const postsPerPage = 10 func (s *Server) blogView(r *http.Request) (map[string]any, error) { blog := currentBlog(r) ctx := r.Context() - pages, err := s.st.ListPages(ctx, blog.ID) + pages, err := blogStore(r).ListPages(ctx) if err != nil { return nil, err } - menu, err := s.st.ListMenu(ctx, blog.ID) + menu, err := blogStore(r).ListMenu(ctx) if err != nil { return nil, err } - mods, err := s.st.ListModules(ctx, blog.ID) + mods, err := blogStore(r).ListModules(ctx) if err != nil { return nil, err } - sections, err := s.st.EnabledSections(ctx, blog.ID) + sections, err := blogStore(r).EnabledSections(ctx) if err != nil { return nil, err } @@ -38,14 +38,14 @@ func (s *Server) blogView(r *http.Request) (map[string]any, error) { v := map[string]any{"theme": theme, "css": theme.CSS(), "layout": layout, "menu": menu, "pages": pages} // Module data is only fetched when a visible module shows it. if layout.NeedsArchive() { - index, err := s.st.PublishedPostIndex(ctx, blog.ID) + index, err := blogStore(r).PublishedPostIndex(ctx) if err != nil { return nil, err } v["archive"] = groupArchive(index) } if n := layout.MaxRecent(); n > 0 { - recent, err := s.st.RecentPublishedPosts(ctx, blog.ID, n) + recent, err := blogStore(r).RecentPublishedPosts(ctx, n) if err != nil { return nil, err } @@ -56,7 +56,7 @@ func (s *Server) blogView(r *http.Request) (map[string]any, error) { } func (s *Server) handleBlogHome(w http.ResponseWriter, r *http.Request) { - page, err := s.st.HomePage(r.Context(), currentBlog(r).ID) + page, err := blogStore(r).HomePage(r.Context()) if err != nil { s.blogNotFound(w, r) return @@ -65,7 +65,7 @@ func (s *Server) handleBlogHome(w http.ResponseWriter, r *http.Request) { } func (s *Server) handleBlogPage(w http.ResponseWriter, r *http.Request) { - page, err := s.st.PageBySlug(r.Context(), currentBlog(r).ID, r.PathValue("page")) + page, err := blogStore(r).PageBySlug(r.Context(), r.PathValue("page")) if err != nil { s.blogNotFound(w, r) return @@ -87,7 +87,7 @@ func (s *Server) renderPage(w http.ResponseWriter, r *http.Request, page *store. if n < 1 { n = 1 } - posts, total, err := s.st.PublishedPosts(r.Context(), page.ID, postsPerPage, (n-1)*postsPerPage) + posts, total, err := blogStore(r).PublishedPosts(r.Context(), page.ID, postsPerPage, (n-1)*postsPerPage) if err != nil { s.serverError(w, err) return @@ -102,13 +102,12 @@ func (s *Server) renderPage(w http.ResponseWriter, r *http.Request, page *store. } func (s *Server) handleBlogPost(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) - page, err := s.st.PageBySlug(r.Context(), blog.ID, r.PathValue("page")) + page, err := blogStore(r).PageBySlug(r.Context(), r.PathValue("page")) if err != nil { s.blogNotFound(w, r) return } - post, err := s.st.PublishedPostBySlug(r.Context(), page.ID, r.PathValue("post")) + post, err := blogStore(r).PublishedPostBySlug(r.Context(), page.ID, r.PathValue("post")) if err != nil { s.blogNotFound(w, r) return @@ -154,7 +153,7 @@ type rssItem struct { func (s *Server) handleBlogFeed(w http.ResponseWriter, r *http.Request) { blog := currentBlog(r) - posts, err := s.st.RecentPublishedPosts(r.Context(), blog.ID, 30) + posts, err := blogStore(r).RecentPublishedPosts(r.Context(), 30) if err != nil { s.serverError(w, err) return diff --git a/internal/web/handlers_dashboard.go b/internal/web/handlers_dashboard.go index f210891..a1e0d89 100644 --- a/internal/web/handlers_dashboard.go +++ b/internal/web/handlers_dashboard.go @@ -6,13 +6,12 @@ import ( ) func (s *Server) handleBlogOverview(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) - pages, err := s.st.ListPages(r.Context(), blog.ID) + pages, err := blogStore(r).ListPages(r.Context()) if err != nil { s.serverError(w, err) return } - posts, err := s.st.ListPosts(r.Context(), blog.ID, 0) + posts, err := blogStore(r).ListPosts(r.Context(), 0) if err != nil { s.serverError(w, err) return @@ -35,7 +34,7 @@ func (s *Server) handleSettings(w http.ResponseWriter, r *http.Request) { s.renderStatus(w, r, http.StatusBadRequest, "dashboard/settings.html", map[string]any{"error": "Title is required (max 120 chars); tagline max 300."}) return } - if err := s.st.UpdateBlogSettings(r.Context(), blog.ID, title, tagline); err != nil { + if err := blogStore(r).UpdateSettings(r.Context(), title, tagline); err != nil { s.serverError(w, err) return } diff --git a/internal/web/handlers_design.go b/internal/web/handlers_design.go index 1e18001..9f98e9e 100644 --- a/internal/web/handlers_design.go +++ b/internal/web/handlers_design.go @@ -20,7 +20,7 @@ var allowedImageTypes = map[string]bool{"image/png": true, "image/jpeg": true, " func (s *Server) handleDesignForm(w http.ResponseWriter, r *http.Request) { blog := currentBlog(r) - images, err := s.st.ListImages(r.Context(), blog.ID) + images, err := blogStore(r).ListImages(r.Context()) if err != nil { s.serverError(w, err) return @@ -39,7 +39,7 @@ func (s *Server) handleDesign(w http.ResponseWriter, r *http.Request) { for field, dst := range map[string]*string{"bg_image_file": &theme.BgImage, "header_image_file": &theme.HeaderImage, "logo_file": &theme.Logo, "favicon_file": &theme.Favicon} { img, err := s.readUpload(r, field) if err != nil { - images, _ := s.st.ListImages(r.Context(), blog.ID) + images, _ := blogStore(r).ListImages(r.Context()) s.renderStatus(w, r, http.StatusBadRequest, "dashboard/design.html", map[string]any{"theme": theme, "images": images, "presets": Presets(), "error": err.Error()}) return } @@ -47,7 +47,7 @@ func (s *Server) handleDesign(w http.ResponseWriter, r *http.Request) { *dst = img.ID.String() } } - if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil { + if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil { s.serverError(w, err) return } @@ -64,7 +64,7 @@ func (s *Server) handleDesignPreset(w http.ResponseWriter, r *http.Request) { return } theme := ParseTheme(blog.ThemeJSON).WithPreset(p) - if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil { + if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil { s.serverError(w, err) return } @@ -75,11 +75,11 @@ func (s *Server) handleDesignPreset(w http.ResponseWriter, r *http.Request) { // stay in the library and the menu is left alone. func (s *Server) handleDesignReset(w http.ResponseWriter, r *http.Request) { blog := currentBlog(r) - if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, DefaultTheme().JSON()); err != nil { + if err := blogStore(r).UpdateTheme(r.Context(), DefaultTheme().JSON()); err != nil { s.serverError(w, err) return } - if err := s.st.ResetModules(r.Context(), blog.ID); err != nil { + if err := blogStore(r).ResetModules(r.Context()); err != nil { s.serverError(w, err) return } @@ -122,7 +122,7 @@ func (s *Server) storeUpload(r *http.Request, fh *multipart.FileHeader) (*store. if name == "" || name == "." || len(name) > 120 { name = "image" } - return s.st.CreateImage(r.Context(), currentBlog(r).ID, name, ct, buf.Bytes()) + return blogStore(r).CreateImage(r.Context(), name, ct, buf.Bytes()) } // imageMarkdown is the line the editor inserts for an uploaded image. @@ -153,7 +153,7 @@ func kbString(n int64) string { // ---- image library --------------------------------------------------------- func (s *Server) handleImages(w http.ResponseWriter, r *http.Request) { - images, err := s.st.ListImages(r.Context(), currentBlog(r).ID) + images, err := blogStore(r).ListImages(r.Context()) if err != nil { s.serverError(w, err) return @@ -207,7 +207,7 @@ func (s *Server) handleImageDelete(w http.ResponseWriter, r *http.Request) { http.NotFound(w, r) return } - if err := s.st.DeleteImage(r.Context(), blog.ID, id); err != nil { + if err := blogStore(r).DeleteImage(r.Context(), id); err != nil { s.serverError(w, err) return } @@ -227,7 +227,7 @@ func (s *Server) handleImageDelete(w http.ResponseWriter, r *http.Request) { theme.Logo, changed = "", true } if changed { - if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil { + if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil { s.serverError(w, err) return } diff --git a/internal/web/handlers_layout.go b/internal/web/handlers_layout.go index b22c1b5..1046a19 100644 --- a/internal/web/handlers_layout.go +++ b/internal/web/handlers_layout.go @@ -25,15 +25,15 @@ func (s *Server) layoutURL(r *http.Request) string { // layoutData is everything layout.html needs; it is rebuilt after a failed form too. func (s *Server) layoutData(r *http.Request) (map[string]any, error) { blog := currentBlog(r) - mods, err := s.st.ListModules(r.Context(), blog.ID) + mods, err := blogStore(r).ListModules(r.Context()) if err != nil { return nil, err } - menu, err := s.st.ListMenu(r.Context(), blog.ID) + menu, err := blogStore(r).ListMenu(r.Context()) if err != nil { return nil, err } - pages, err := s.st.ListPages(r.Context(), blog.ID) + pages, err := blogStore(r).ListPages(r.Context()) if err != nil { return nil, err } @@ -63,7 +63,7 @@ func (s *Server) handleLayout(w http.ResponseWriter, r *http.Request) { func (s *Server) handleLayoutWidths(w http.ResponseWriter, r *http.Request) { blog := currentBlog(r) theme := LayoutFromForm(ParseTheme(blog.ThemeJSON), r.Form) - if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil { + if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil { s.serverError(w, err) return } @@ -81,7 +81,7 @@ func (s *Server) handleAreaToggle(w http.ResponseWriter, r *http.Request) { theme := ParseTheme(blog.ThemeJSON) on := !theme.AreaOn(area) theme.SetAreaOn(area, on) - if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil { + if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil { s.serverError(w, err) return } @@ -93,14 +93,13 @@ func (s *Server) handleAreaToggle(w http.ResponseWriter, r *http.Request) { } func (s *Server) handleModuleAdd(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) area, kind := r.PathValue("area"), r.FormValue("kind") if !allowedKind(area, kind) { s.plainError(w, http.StatusBadRequest, "That module cannot go in that area.") return } if singleModuleAreas[area] { - mods, err := s.st.ListModules(r.Context(), blog.ID) + mods, err := blogStore(r).ListModules(r.Context()) if err != nil { s.serverError(w, err) return @@ -112,7 +111,7 @@ func (s *Server) handleModuleAdd(w http.ResponseWriter, r *http.Request) { } } } - m := &store.Module{BlogID: blog.ID, Area: area, Kind: kind, Count: 5} + m := &store.Module{Area: area, Kind: kind, Count: 5} switch kind { // sensible headings so the module reads well before it is edited case "archive": m.Title = "Archive" @@ -121,7 +120,7 @@ func (s *Server) handleModuleAdd(w http.ResponseWriter, r *http.Request) { case "sitemap": m.Title = "Pages" } - m, err := s.st.CreateModule(r.Context(), m) + m, err := blogStore(r).CreateModule(r.Context(), m) if err != nil { s.serverError(w, err) return @@ -143,7 +142,7 @@ func anchorFor(area string) string { func (s *Server) loadModule(w http.ResponseWriter, r *http.Request) *store.Module { id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64) - m, err := s.st.ModuleByID(r.Context(), currentBlog(r).ID, id) + m, err := blogStore(r).ModuleByID(r.Context(), id) if err != nil { if errors.Is(err, store.ErrNotFound) { http.NotFound(w, r) @@ -196,7 +195,7 @@ func (s *Server) handleModuleSave(w http.ResponseWriter, r *http.Request) { fail("Content is too long (20 KB max).") return } - if err := s.st.UpdateModule(r.Context(), m); err != nil { + if err := blogStore(r).UpdateModule(r.Context(), m); err != nil { s.serverError(w, err) return } @@ -212,7 +211,7 @@ func (s *Server) handleModuleMove(w http.ResponseWriter, r *http.Request) { if r.FormValue("dir") == "up" { dir = -1 } - if err := s.st.MoveModule(r.Context(), m.BlogID, m.ID, dir); err != nil { + if err := blogStore(r).MoveModule(r.Context(), m.ID, dir); err != nil { s.serverError(w, err) return } @@ -240,7 +239,7 @@ func (s *Server) handleModuleDelete(w http.ResponseWriter, r *http.Request) { } func (s *Server) deleteModule(w http.ResponseWriter, r *http.Request, m *store.Module) { - if err := s.st.DeleteModule(r.Context(), m.BlogID, m.ID); err != nil { + if err := blogStore(r).DeleteModule(r.Context(), m.ID); err != nil { s.serverError(w, err) return } @@ -250,13 +249,12 @@ func (s *Server) deleteModule(w http.ResponseWriter, r *http.Request, m *store.M // ---- menu -------------------------------------------------------------------- func (s *Server) handleMenuAddPage(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) pageID, _ := strconv.ParseInt(r.FormValue("page"), 10, 64) if pageID == 0 { redirectOK(w, r, s.layoutURL(r)+"#menu", "Pick a page first.") return } - if err := s.st.AddMenuPage(r.Context(), blog.ID, pageID); err != nil { + if err := blogStore(r).AddMenuPage(r.Context(), pageID); err != nil { s.serverError(w, err) return } @@ -264,7 +262,6 @@ func (s *Server) handleMenuAddPage(w http.ResponseWriter, r *http.Request) { } func (s *Server) handleMenuAddLink(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) label, link := strings.TrimSpace(r.FormValue("label")), strings.TrimSpace(r.FormValue("url")) if link != "" && !strings.HasPrefix(link, "/") && !strings.Contains(link, ":") { link = "https://" + link // "example.org" is what people type @@ -277,7 +274,7 @@ func (s *Server) handleMenuAddLink(w http.ResponseWriter, r *http.Request) { redirectOK(w, r, s.layoutURL(r)+"#menu", "That address does not look right. Use a full web address like https://example.org or a path like /about.") return } - if err := s.st.AddMenuLink(r.Context(), blog.ID, label, link); err != nil { + if err := blogStore(r).AddMenuLink(r.Context(), label, link); err != nil { s.serverError(w, err) return } @@ -290,7 +287,7 @@ func (s *Server) handleMenuMove(w http.ResponseWriter, r *http.Request) { if r.FormValue("dir") == "up" { dir = -1 } - if err := s.st.MoveMenuItem(r.Context(), currentBlog(r).ID, id, dir); err != nil { + if err := blogStore(r).MoveMenuItem(r.Context(), id, dir); err != nil { s.serverError(w, err) return } @@ -299,7 +296,7 @@ func (s *Server) handleMenuMove(w http.ResponseWriter, r *http.Request) { func (s *Server) handleMenuDelete(w http.ResponseWriter, r *http.Request) { id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64) - if err := s.st.DeleteMenuItem(r.Context(), currentBlog(r).ID, id); err != nil { + if err := blogStore(r).DeleteMenuItem(r.Context(), id); err != nil { s.serverError(w, err) return } diff --git a/internal/web/handlers_media.go b/internal/web/handlers_media.go index 9d5a515..4e237fa 100644 --- a/internal/web/handlers_media.go +++ b/internal/web/handlers_media.go @@ -22,7 +22,7 @@ func (s *Server) handleMedia(w http.ResponseWriter, r *http.Request) { w.WriteHeader(http.StatusNotModified) return } - img, err := s.st.ImageData(r.Context(), id) + img, err := blogStore(r).ImageData(r.Context(), id) if err != nil { if errors.Is(err, store.ErrNotFound) { http.NotFound(w, r) diff --git a/internal/web/handlers_pages.go b/internal/web/handlers_pages.go index a226953..09257a3 100644 --- a/internal/web/handlers_pages.go +++ b/internal/web/handlers_pages.go @@ -16,7 +16,7 @@ var reservedPageSlugs = map[string]bool{"media": true, "static": true, "feed.xml "webadmin": true, "logout": true, "dashboard": true, "account": true, "admin": true, "b": true, "healthz": true} func (s *Server) handlePages(w http.ResponseWriter, r *http.Request) { - pages, err := s.st.ListPages(r.Context(), currentBlog(r).ID) + pages, err := blogStore(r).ListPages(r.Context()) if err != nil { s.serverError(w, err) return @@ -27,7 +27,7 @@ func (s *Server) handlePages(w http.ResponseWriter, r *http.Request) { // loadPage fetches the page named in the URL, or nil (having written the response) on failure. func (s *Server) loadPage(w http.ResponseWriter, r *http.Request) *store.Page { id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64) - p, err := s.st.PageByID(r.Context(), currentBlog(r).ID, id) + p, err := blogStore(r).PageByID(r.Context(), id) if err != nil { if errors.Is(err, store.ErrNotFound) { http.NotFound(w, r) @@ -51,7 +51,7 @@ func (s *Server) handlePageForm(w http.ResponseWriter, r *http.Request) { func (s *Server) handlePageSave(w http.ResponseWriter, r *http.Request) { blog := currentBlog(r) - p := &store.Page{BlogID: blog.ID} + p := &store.Page{} if r.PathValue("id") != "" { if p = s.loadPage(w, r); p == nil { return @@ -86,11 +86,11 @@ func (s *Server) handlePageSave(w http.ResponseWriter, r *http.Request) { p.Slug = slug.WithSuffix(base, n) if p.ID == 0 { var created *store.Page - if created, err = s.st.CreatePage(r.Context(), p); err == nil { + if created, err = blogStore(r).CreatePage(r.Context(), p); err == nil { p = created } } else { - err = s.st.UpdatePage(r.Context(), p) + err = blogStore(r).UpdatePage(r.Context(), p) } if !errors.Is(err, store.ErrConflict) || !autoSlug || n >= 50 { break @@ -129,7 +129,7 @@ func (s *Server) handlePageDelete(w http.ResponseWriter, r *http.Request) { s.plainError(w, http.StatusBadRequest, "The home page cannot be deleted. Make another page the home page first.") return } - if err := s.st.DeletePage(r.Context(), p.BlogID, p.ID); err != nil { + if err := blogStore(r).DeletePage(r.Context(), p.ID); err != nil { s.serverError(w, err) return } @@ -145,7 +145,7 @@ func (s *Server) handlePageMove(w http.ResponseWriter, r *http.Request) { if r.FormValue("dir") == "up" { dir = -1 } - if err := s.st.MovePage(r.Context(), p.BlogID, p.ID, dir); err != nil { + if err := blogStore(r).MovePage(r.Context(), p.ID, dir); err != nil { s.serverError(w, err) return } @@ -157,7 +157,7 @@ func (s *Server) handlePageHome(w http.ResponseWriter, r *http.Request) { if p == nil { return } - if err := s.st.SetHomePage(r.Context(), p.BlogID, p.ID); err != nil { + if err := blogStore(r).SetHomePage(r.Context(), p.ID); err != nil { s.serverError(w, err) return } diff --git a/internal/web/handlers_posts.go b/internal/web/handlers_posts.go index 136b7fe..02a3fd7 100644 --- a/internal/web/handlers_posts.go +++ b/internal/web/handlers_posts.go @@ -12,14 +12,13 @@ import ( ) func (s *Server) handlePosts(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) pageID, _ := strconv.ParseInt(r.URL.Query().Get("page"), 10, 64) - pages, err := s.st.ListPages(r.Context(), blog.ID) + pages, err := blogStore(r).ListPages(r.Context()) if err != nil { s.serverError(w, err) return } - posts, err := s.st.ListPosts(r.Context(), blog.ID, pageID) + posts, err := blogStore(r).ListPosts(r.Context(), pageID) if err != nil { s.serverError(w, err) return @@ -29,7 +28,7 @@ func (s *Server) handlePosts(w http.ResponseWriter, r *http.Request) { func (s *Server) loadPost(w http.ResponseWriter, r *http.Request) *store.Post { id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64) - p, err := s.st.PostByID(r.Context(), currentBlog(r).ID, id) + p, err := blogStore(r).PostByID(r.Context(), id) if err != nil { if errors.Is(err, store.ErrNotFound) { http.NotFound(w, r) @@ -42,8 +41,7 @@ func (s *Server) loadPost(w http.ResponseWriter, r *http.Request) *store.Post { } func (s *Server) handlePostForm(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) - pages, err := s.st.ListPages(r.Context(), blog.ID) + pages, err := blogStore(r).ListPages(r.Context()) if err != nil { s.serverError(w, err) return @@ -55,7 +53,7 @@ func (s *Server) handlePostForm(w http.ResponseWriter, r *http.Request) { } } else if pid, _ := strconv.ParseInt(r.URL.Query().Get("page"), 10, 64); pid != 0 { p.PageID = pid - } else if hp, err := s.st.HomePage(r.Context(), blog.ID); err == nil { + } else if hp, err := blogStore(r).HomePage(r.Context()); err == nil { p.PageID = hp.ID } s.render(w, r, "dashboard/post_form.html", map[string]any{"post": p, "pages": pages}) @@ -63,7 +61,7 @@ func (s *Server) handlePostForm(w http.ResponseWriter, r *http.Request) { func (s *Server) handlePostSave(w http.ResponseWriter, r *http.Request) { blog := currentBlog(r) - pages, err := s.st.ListPages(r.Context(), blog.ID) + pages, err := blogStore(r).ListPages(r.Context()) if err != nil { s.serverError(w, err) return @@ -118,11 +116,11 @@ func (s *Server) handlePostSave(w http.ResponseWriter, r *http.Request) { p.Slug = slug.WithSuffix(base, n) if p.ID == 0 { var created *store.Post - if created, err = s.st.CreatePost(r.Context(), p); err == nil { + if created, err = blogStore(r).CreatePost(r.Context(), p); err == nil { p = created } } else { - err = s.st.UpdatePost(r.Context(), p) + err = blogStore(r).UpdatePost(r.Context(), p) } if !errors.Is(err, store.ErrConflict) || !autoSlug || n >= 50 { break @@ -156,7 +154,7 @@ func (s *Server) handlePostDelete(w http.ResponseWriter, r *http.Request) { if p == nil { return } - if err := s.st.DeletePost(r.Context(), currentBlog(r).ID, p.ID); err != nil { + if err := blogStore(r).DeletePost(r.Context(), p.ID); err != nil { s.serverError(w, err) return } diff --git a/internal/web/handlers_sections.go b/internal/web/handlers_sections.go index f5e2e59..1239545 100644 --- a/internal/web/handlers_sections.go +++ b/internal/web/handlers_sections.go @@ -19,7 +19,7 @@ func (s *Server) sectionsURL(r *http.Request) string { } func (s *Server) handleSections(w http.ResponseWriter, r *http.Request) { - secs, err := s.st.ListSections(r.Context(), currentBlog(r).ID) + secs, err := blogStore(r).ListSections(r.Context()) if err != nil { s.serverError(w, err) return @@ -29,7 +29,7 @@ func (s *Server) handleSections(w http.ResponseWriter, r *http.Request) { func (s *Server) loadSection(w http.ResponseWriter, r *http.Request) *store.Section { id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64) - sec, err := s.st.SectionByID(r.Context(), currentBlog(r).ID, id) + sec, err := blogStore(r).SectionByID(r.Context(), id) if err != nil { if errors.Is(err, store.ErrNotFound) { http.NotFound(w, r) @@ -58,8 +58,7 @@ func normalizeSection(sec *store.Section) { } func (s *Server) handleSectionSave(w http.ResponseWriter, r *http.Request) { - blog := currentBlog(r) - sec := &store.Section{BlogID: blog.ID} + sec := &store.Section{} if r.PathValue("id") != "" { if sec = s.loadSection(w, r); sec == nil { return @@ -92,9 +91,9 @@ func (s *Server) handleSectionSave(w http.ResponseWriter, r *http.Request) { sec.BodyMD = appendImageMD(sec.BodyMD, img) sec.BodyHTML = markdown.Render(sec.BodyMD) if sec.ID == 0 { - sec, err = s.st.CreateSection(r.Context(), sec) + sec, err = blogStore(r).CreateSection(r.Context(), sec) } else { - err = s.st.UpdateSection(r.Context(), sec) + err = blogStore(r).UpdateSection(r.Context(), sec) } if err != nil { s.serverError(w, err) @@ -120,7 +119,7 @@ func (s *Server) handleSectionDelete(w http.ResponseWriter, r *http.Request) { if sec == nil { return } - if err := s.st.DeleteSection(r.Context(), sec.BlogID, sec.ID); err != nil { + if err := blogStore(r).DeleteSection(r.Context(), sec.ID); err != nil { s.serverError(w, err) return } @@ -136,7 +135,7 @@ func (s *Server) handleSectionMove(w http.ResponseWriter, r *http.Request) { if r.FormValue("dir") == "up" { dir = -1 } - if err := s.st.MoveSection(r.Context(), sec.BlogID, sec.ID, dir); err != nil { + if err := blogStore(r).MoveSection(r.Context(), sec.ID, dir); err != nil { s.serverError(w, err) return } @@ -149,7 +148,7 @@ func (s *Server) handleSectionToggle(w http.ResponseWriter, r *http.Request) { if sec == nil { return } - if err := s.st.SetSectionEnabled(r.Context(), sec.BlogID, sec.ID, !sec.Enabled); err != nil { + if err := blogStore(r).SetSectionEnabled(r.Context(), sec.ID, !sec.Enabled); err != nil { s.serverError(w, err) return } diff --git a/internal/web/routes.go b/internal/web/routes.go index 4b3d14f..36ae892 100644 --- a/internal/web/routes.go +++ b/internal/web/routes.go @@ -66,6 +66,8 @@ func (s *Server) rootRoutes() http.Handler { m.HandleFunc("GET /b/{sub}/images", s.withBlog(s.handleImages)) m.HandleFunc("POST /b/{sub}/images/upload", s.withBlog(s.handleImageUpload)) m.HandleFunc("POST /b/{sub}/images/{id}/delete", s.withBlog(s.handleImageDelete)) + // Images live in the blog's database, so dashboard previews on this host go through /b/. + m.HandleFunc("GET /b/{sub}/media/{id}", s.withBlog(s.handleMedia)) // superadmin m.HandleFunc("GET /admin/{$}", s.requireAdmin(s.handleAdmin)) @@ -77,7 +79,7 @@ func (s *Server) rootRoutes() http.Handler { m.HandleFunc("GET /admin/users/{id}/delete", s.requireAdmin(s.handleAdminDeleteUserConfirm)) m.HandleFunc("POST /admin/users/{id}/delete", s.requireAdmin(s.handleAdminDeleteUser)) - m.HandleFunc("GET /media/{id}", s.handleMedia) + m.HandleFunc("GET /media/{id}", s.hostBlog(s.handleMedia)) m.Handle("GET /static/{file}", s.staticHandler()) m.HandleFunc("GET /favicon.ico", s.hostBlog(s.handleFavicon)) // The root domain also serves the superadmin's blog. Literal routes above win @@ -98,7 +100,7 @@ func (s *Server) subdomainRoutes() http.Handler { m := http.NewServeMux() // Bloggers type /webadmin on their own blog; send them to the real login page. m.HandleFunc("GET /webadmin", s.handleWebadminRedirect) - m.HandleFunc("GET /media/{id}", s.handleMedia) + m.HandleFunc("GET /media/{id}", s.hostBlog(s.handleMedia)) m.Handle("GET /static/{file}", s.staticHandler()) m.HandleFunc("GET /favicon.ico", s.hostBlog(s.handleFavicon)) s.blogRoutes(m, s.hostBlog) diff --git a/internal/web/server.go b/internal/web/server.go index 483663e..a7af06b 100644 --- a/internal/web/server.go +++ b/internal/web/server.go @@ -66,9 +66,10 @@ func hostname(h string) string { type ctxKey int const ( - ctxUser ctxKey = iota - ctxBlog // the blog a handler operates on (public page, or /b/{sub}/ management) - ctxHostSub // subdomain derived from the Host header + ctxUser ctxKey = iota + ctxBlog // the blog a handler operates on (public page, or /b/{sub}/ management) + ctxBlogStore // that blog's store, bound to its own database + ctxHostSub // subdomain derived from the Host header ) func currentUser(r *http.Request) *store.User { @@ -81,13 +82,33 @@ func currentBlog(r *http.Request) *store.Blog { return b } +// blogStore is the store of the blog in the context; only valid behind hostBlog or withBlog. +func blogStore(r *http.Request) *store.BlogStore { + bs, _ := r.Context().Value(ctxBlogStore).(*store.BlogStore) + return bs +} + // ---- middleware ---------------------------------------------------------- +// resolveBlog looks a subdomain up in the registry and opens its database. +func (s *Server) resolveBlog(r *http.Request, sub string) (*http.Request, error) { + blog, err := s.st.BlogBySubdomain(r.Context(), sub) + if err != nil { + return r, err + } + bs, err := s.st.Open(r.Context(), blog) + if err != nil { + return r, err + } + ctx := context.WithValue(r.Context(), ctxBlog, blog) + return r.WithContext(context.WithValue(ctx, ctxBlogStore, bs)), nil +} + // hostBlog resolves the blog named by the Host header into the context for public pages. func (s *Server) hostBlog(next http.HandlerFunc) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { sub, _ := r.Context().Value(ctxHostSub).(string) - blog, err := s.st.BlogBySubdomain(r.Context(), sub) + r, err := s.resolveBlog(r, sub) if err != nil { if errors.Is(err, store.ErrNotFound) { s.plainError(w, http.StatusNotFound, "No blog here (yet).") @@ -96,7 +117,7 @@ func (s *Server) hostBlog(next http.HandlerFunc) http.HandlerFunc { s.serverError(w, err) return } - next(w, r.WithContext(context.WithValue(r.Context(), ctxBlog, blog))) + next(w, r) } } @@ -176,7 +197,7 @@ func (s *Server) requireAdmin(next http.HandlerFunc) http.HandlerFunc { func (s *Server) withBlog(next http.HandlerFunc) http.HandlerFunc { return s.requireAuth(func(w http.ResponseWriter, r *http.Request) { u := currentUser(r) - blog, err := s.st.BlogBySubdomain(r.Context(), r.PathValue("sub")) + r, err := s.resolveBlog(r, r.PathValue("sub")) if err != nil { if errors.Is(err, store.ErrNotFound) { http.NotFound(w, r) @@ -185,11 +206,11 @@ func (s *Server) withBlog(next http.HandlerFunc) http.HandlerFunc { s.serverError(w, err) return } - if blog.OwnerID != u.ID && !u.IsSuperadmin() { + if blog := currentBlog(r); blog.OwnerID != u.ID && !u.IsSuperadmin() { s.plainError(w, http.StatusForbidden, "This is not your blog.") return } - next(w, r.WithContext(context.WithValue(r.Context(), ctxBlog, blog))) + next(w, r) }) } diff --git a/internal/web/templates/admin/index.html b/internal/web/templates/admin/index.html index ecb0112..8293368 100644 --- a/internal/web/templates/admin/index.html +++ b/internal/web/templates/admin/index.html @@ -10,7 +10,7 @@ {{range .Data.users}}<tr{{if .Disabled}} class="disabled"{{end}}> <td>{{.Username}}{{if .Disabled}} <span class="tag">disabled</span>{{end}}</td> <td>{{.Role}}</td> - <td>{{if .Subdomain}}<a href="/b/{{deref .Subdomain}}/">{{deref .BlogTitle}}</a> <a href="{{$.Data.cfg.BlogURL (deref .Subdomain)}}" target="_blank" class="muted">{{if eq (deref .Subdomain) "www"}}{{$.Data.cfg.BaseDomain}}{{else}}{{deref .Subdomain}}{{end}} ↗</a>{{else}}<span class="muted">—</span>{{end}}</td> + <td>{{if .Subdomain}}<a href="/b/{{deref .Subdomain}}/">{{if eq (deref .Subdomain) "www"}}{{$.Data.cfg.BaseDomain}}{{else}}{{deref .Subdomain}}{{end}}</a> <a href="{{$.Data.cfg.BlogURL (deref .Subdomain)}}" target="_blank" class="muted">view ↗</a>{{else}}<span class="muted">—</span>{{end}}</td> <td class="nowrap">{{date .CreatedAt}}</td> <td class="nowrap"> {{if ne .ID $.User.ID}} diff --git a/internal/web/templates/dashboard/design.html b/internal/web/templates/dashboard/design.html index 4c86c79..9c2a12f 100644 --- a/internal/web/templates/dashboard/design.html +++ b/internal/web/templates/dashboard/design.html @@ -34,7 +34,7 @@ <option value="tile"{{if eq $t.BgMode "tile"}} selected{{end}}>Repeat (tile)</option> </select></label> </div> - {{template "imagepick" (dict "name" "bg_image" "current" $t.BgImage "images" .Data.images "label" "Background image")}} + {{template "imagepick" (dict "name" "bg_image" "current" $t.BgImage "sub" .Blog.Subdomain "images" .Data.images "label" "Background image")}} </div> <div class="card" id="text"> @@ -102,7 +102,7 @@ </select></label> </div> <p class="muted small">The title, logo and menu are header modules — add, remove and order them on the <a href="/b/{{.Blog.Subdomain}}/layout#header">Layout</a> tab. The banner below is shown across the top of the header whenever one is chosen.</p> - {{template "imagepick" (dict "name" "header_image" "current" $t.HeaderImage "images" .Data.images "label" "Header image (banner)")}} + {{template "imagepick" (dict "name" "header_image" "current" $t.HeaderImage "sub" .Blog.Subdomain "images" .Data.images "label" "Header image (banner)")}} </div> <div class="card" id="menu"> @@ -161,8 +161,8 @@ <option value="large"{{if eq $t.LogoSize "large"}} selected{{end}}>Large (140 px)</option> </select></label> </div> - {{template "imagepick" (dict "name" "logo" "current" $t.Logo "images" .Data.images "label" "Logo" "hint" "Shown wherever you place a Logo module (Layout tab); it links to your front page.")}} - {{template "imagepick" (dict "name" "favicon" "current" $t.Favicon "images" .Data.images "label" "Favicon" "hint" "The small icon browsers show in the tab. A square PNG or ICO of 32–64 px works best; pick None for the Blogspace icon." "accept" "image/png,image/x-icon,image/gif,image/webp,image/jpeg,.ico")}} + {{template "imagepick" (dict "name" "logo" "current" $t.Logo "sub" .Blog.Subdomain "images" .Data.images "label" "Logo" "hint" "Shown wherever you place a Logo module (Layout tab); it links to your front page.")}} + {{template "imagepick" (dict "name" "favicon" "current" $t.Favicon "sub" .Blog.Subdomain "images" .Data.images "label" "Favicon" "hint" "The small icon browsers show in the tab. A square PNG or ICO of 32–64 px works best; pick None for the Blogspace icon." "accept" "image/png,image/x-icon,image/gif,image/webp,image/jpeg,.ico")}} </div> <div class="savebar"> diff --git a/internal/web/templates/dashboard/images.html b/internal/web/templates/dashboard/images.html index 1c64391..2e39531 100644 --- a/internal/web/templates/dashboard/images.html +++ b/internal/web/templates/dashboard/images.html @@ -14,7 +14,7 @@ <div class="gallery"> {{range .Data.images}} <div class="card thumb"> - <a href="/media/{{.ID}}" target="_blank"><img src="/media/{{.ID}}" alt="{{.Filename}}"></a> + <a href="/b/{{$.Blog.Subdomain}}/media/{{.ID}}" target="_blank"><img src="/b/{{$.Blog.Subdomain}}/media/{{.ID}}" alt="{{.Filename}}"></a> <div class="meta">{{.Filename}} <span class="muted">({{kb .Size}})</span></div> <input class="copy" readonly value="" onclick="this.select()"> <form method="post" action="/b/{{$.Blog.Subdomain}}/images/{{.ID}}/delete" class="inline"><input type="hidden" name="_csrf" value="{{$.CSRF}}"><button class="mini danger">delete</button></form> diff --git a/internal/web/templates/partials/imagepick.html b/internal/web/templates/partials/imagepick.html index 0c6eced..4431d02 100644 --- a/internal/web/templates/partials/imagepick.html +++ b/internal/web/templates/partials/imagepick.html @@ -3,7 +3,7 @@ {{if .hint}}<p class="muted small">{{.hint}}</p>{{end}} <div class="picks"> <label class="pick none"><input type="radio" name="{{.name}}" value="none"{{if not .current}} checked{{end}}><span class="swatch">×</span><span class="name">None</span></label> - {{range .images}}<label class="pick"><input type="radio" name="{{$.name}}" value="{{.ID}}"{{if eq .ID.String $.current}} checked{{end}}><img src="/media/{{.ID}}" alt=""><span class="name">{{.Filename}}</span></label>{{end}} + {{range .images}}<label class="pick"><input type="radio" name="{{$.name}}" value="{{.ID}}"{{if eq .ID.String $.current}} checked{{end}}><img src="/b/{{$.sub}}/media/{{.ID}}" alt=""><span class="name">{{.Filename}}</span></label>{{end}} </div> <label>…or upload a new one<br><input type="file" name="{{.name}}_file" accept="{{if .accept}}{{.accept}}{{else}}image/*{{end}}"></label> </div>{{end}} diff --git a/internal/web/web_test.go b/internal/web/web_test.go index a988a23..5078c44 100644 --- a/internal/web/web_test.go +++ b/internal/web/web_test.go @@ -48,10 +48,17 @@ func TestHostRoutingWithoutDB(t *testing.T) { } // On the root domain the literal management routes must win over the blog's /{page} wildcards. +// "blog_" + subdomain must fit a 63-char Postgres database name. +func TestSubdomainLength(t *testing.T) { + if !subdomainRe.MatchString(strings.Repeat("a", 58)) || subdomainRe.MatchString(strings.Repeat("a", 59)) { + t.Error("subdomains must be at most 58 chars") + } +} + func TestRootRoutePrecedence(t *testing.T) { cfg := &config.Config{BaseDomain: "example.com", JWTSecret: []byte("x"), MaxUploadBytes: 1 << 20} s := NewServer(cfg, nil) - for path, want := range map[string]int{"/webadmin": 200, "/healthz": 200, "/dashboard": 303, "/admin/": 303, "/b/alice/posts": 303, "/b/alice/announcements": 303, "/b/alice/layout": 303, "/account/password": 303} { + for path, want := range map[string]int{"/webadmin": 200, "/healthz": 200, "/dashboard": 303, "/admin/": 303, "/b/alice/posts": 303, "/b/alice/announcements": 303, "/b/alice/layout": 303, "/b/alice/media/6ba7b810-9dad-11d1-80b4-00c04fd430c8": 303, "/account/password": 303} { rec := httptest.NewRecorder() req := httptest.NewRequest("GET", path, nil) req.Host = "example.com" |