From aeb19df4222269c585de55be5568326222df879d Mon Sep 17 00:00:00 2001 From: grm Date: Sun, 13 Sep 2026 23:52:41 +0300 Subject: Give every blog its own Postgres database A blog is now a database of its own (blog_) on the same server: one pg_dump is a complete backup of a blog, one psql restores it, and nothing a blog's queries do can reach another blog's rows. The control database (DATABASE_URL) keeps only users and the blog registry (id, owner, subdomain, db_name); title, tagline and theme move into a one-row settings table next to the content so the dump really is everything. db.Cluster holds the control pool plus small, lazily opened per-blog pools. store.Store (control) hands out a store.BlogStore per blog; every blog_id parameter and column is gone, the database is the scope. Handlers reach it through blogStore(r), which resolveBlog puts in the context next to the blog. Existing data is moved in place by control migration 00006, a Go migration that runs inside the control transaction: it creates and migrates each blog database, copies the rows preserving ids, and marks the registry; 00007 then drops the old tables. Either every blog is moved or the control database is untouched. /media/{id} now serves the host's blog only, so dashboard previews on the root domain use /b/{sub}/media/{id}. Subdomains are capped at 58 chars so "blog_" + name fits a Postgres identifier. Deleting a user drops their database. Store.Open resets a blog's pool and retries once so a database restored under a running app (dropdb --force, createdb, psql) just works. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A --- internal/db/db.go | 182 +++++++++++++++++++-- internal/db/db_test.go | 25 +++ internal/db/migrations/00001_init.sql | 68 -------- internal/db/migrations/00002_sections.sql | 19 --- internal/db/migrations/00003_layout.sql | 56 ------- internal/db/migrations/00004_section_placement.sql | 13 -- internal/db/migrations/blog/00001_init.sql | 91 +++++++++++ internal/db/migrations/control/00001_init.sql | 68 ++++++++ internal/db/migrations/control/00002_sections.sql | 19 +++ internal/db/migrations/control/00003_layout.sql | 56 +++++++ .../migrations/control/00004_section_placement.sql | 13 ++ .../control/00005_per_blog_databases.sql | 12 ++ .../db/migrations/control/00007_drop_content.sql | 9 + internal/db/split.go | 152 +++++++++++++++++ 14 files changed, 615 insertions(+), 168 deletions(-) create mode 100644 internal/db/db_test.go delete mode 100644 internal/db/migrations/00001_init.sql delete mode 100644 internal/db/migrations/00002_sections.sql delete mode 100644 internal/db/migrations/00003_layout.sql delete mode 100644 internal/db/migrations/00004_section_placement.sql create mode 100644 internal/db/migrations/blog/00001_init.sql create mode 100644 internal/db/migrations/control/00001_init.sql create mode 100644 internal/db/migrations/control/00002_sections.sql create mode 100644 internal/db/migrations/control/00003_layout.sql create mode 100644 internal/db/migrations/control/00004_section_placement.sql create mode 100644 internal/db/migrations/control/00005_per_blog_databases.sql create mode 100644 internal/db/migrations/control/00007_drop_content.sql create mode 100644 internal/db/split.go (limited to 'internal/db') 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/00001_init.sql b/internal/db/migrations/00001_init.sql deleted file mode 100644 index e27dad4..0000000 --- a/internal/db/migrations/00001_init.sql +++ /dev/null @@ -1,68 +0,0 @@ --- +goose Up -CREATE TABLE users ( - id bigserial PRIMARY KEY, - username text NOT NULL UNIQUE, - password_hash text NOT NULL, - role text NOT NULL CHECK (role IN ('superadmin', 'blogger')), - disabled boolean NOT NULL DEFAULT false, - token_version integer NOT NULL DEFAULT 0, - created_at timestamptz NOT NULL DEFAULT now() -); - -CREATE TABLE blogs ( - id bigserial PRIMARY KEY, - owner_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, - subdomain text NOT NULL UNIQUE CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,62}$'), - 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, - blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, - slug text NOT NULL, - title text NOT NULL, - intro_md text NOT NULL DEFAULT '', - intro_html text NOT NULL DEFAULT '', - nav_order integer NOT NULL DEFAULT 0, - show_in_nav boolean NOT NULL DEFAULT true, - is_home boolean NOT NULL DEFAULT false, - created_at timestamptz NOT NULL DEFAULT now(), - UNIQUE (blog_id, slug) -); -CREATE UNIQUE INDEX pages_one_home_per_blog ON pages (blog_id) 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, - blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, - 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_blog ON images (blog_id, created_at DESC); - --- +goose Down -DROP TABLE images; -DROP TABLE posts; -DROP TABLE pages; -DROP TABLE blogs; -DROP TABLE users; diff --git a/internal/db/migrations/00002_sections.sql b/internal/db/migrations/00002_sections.sql deleted file mode 100644 index 388cb17..0000000 --- a/internal/db/migrations/00002_sections.sql +++ /dev/null @@ -1,19 +0,0 @@ --- +goose Up --- Announcements: blog-wide notices shown on every page and post. -CREATE TABLE sections ( - id bigserial PRIMARY KEY, - blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, - title text NOT NULL DEFAULT '', - body_md text NOT NULL DEFAULT '', - body_html text NOT NULL DEFAULT '', - placement text NOT NULL DEFAULT 'above' CHECK (placement IN ('above', 'below', 'sidebar')), - 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_blog ON sections (blog_id, sort_order, id); - --- +goose Down -DROP TABLE sections; diff --git a/internal/db/migrations/00003_layout.sql b/internal/db/migrations/00003_layout.sql deleted file mode 100644 index e8fff23..0000000 --- a/internal/db/migrations/00003_layout.sql +++ /dev/null @@ -1,56 +0,0 @@ --- +goose Up --- Layout modules: what each area of a blog (header, columns, footer) shows. -CREATE TABLE modules ( - id bigserial PRIMARY KEY, - blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, - 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_blog ON modules (blog_id, area, sort_order, id); - --- The menu: blog pages and custom links in one ordered list. -CREATE TABLE menu_items ( - id bigserial PRIMARY KEY, - blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, - 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_blog ON menu_items (blog_id, sort_order, id); - --- Existing blogs keep the menu they had: pages marked "show in menu", in menu --- order, with the home page only if the theme included it. -INSERT INTO menu_items (blog_id, page_id, sort_order) -SELECT p.blog_id, p.id, row_number() OVER (PARTITION BY p.blog_id ORDER BY p.nav_order, p.id) - 1 -FROM pages p JOIN blogs b ON b.id = p.blog_id -WHERE p.show_in_nav AND (NOT p.is_home OR coalesce((b.theme->>'nav_show_home')::boolean, true)); -ALTER TABLE pages DROP COLUMN show_in_nav; - --- ...and the layout their theme described. -INSERT INTO modules (blog_id, area, kind, sort_order) -SELECT id, 'header', 'title', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 1 ELSE 0 END -FROM blogs WHERE coalesce((theme->>'header_show_title')::boolean, true); -INSERT INTO modules (blog_id, area, kind, sort_order) -SELECT id, 'header', 'menu', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 0 ELSE 1 END -FROM blogs WHERE coalesce(theme->>'nav_position', 'below-header') <> 'left-sidebar'; -INSERT INTO modules (blog_id, area, kind, sort_order) -SELECT id, 'left', 'menu', 0 FROM blogs WHERE theme->>'nav_position' = 'left-sidebar'; -INSERT INTO modules (blog_id, area, kind, body, sort_order) -SELECT id, 'footer', 'text', theme->>'footer_text', 0 FROM blogs WHERE coalesce(theme->>'footer_text', '') <> ''; -INSERT INTO modules (blog_id, area, kind, sort_order) -SELECT id, 'footer', 'rss', 1 FROM blogs WHERE coalesce((theme->>'footer_show_rss')::boolean, true); - --- +goose Down -ALTER TABLE pages ADD COLUMN show_in_nav boolean NOT NULL DEFAULT true; -UPDATE pages SET show_in_nav = EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id = pages.id); -DROP TABLE menu_items; -DROP TABLE modules; diff --git a/internal/db/migrations/00004_section_placement.sql b/internal/db/migrations/00004_section_placement.sql deleted file mode 100644 index c36787e..0000000 --- a/internal/db/migrations/00004_section_placement.sql +++ /dev/null @@ -1,13 +0,0 @@ --- +goose Up --- Announcements are placed in a column (left, main, right) at its top or bottom. -ALTER TABLE sections DROP CONSTRAINT sections_placement_check; -UPDATE sections SET placement = CASE placement WHEN 'above' THEN 'main-top' WHEN 'below' THEN 'main-bottom' ELSE 'left-top' END; -ALTER TABLE sections ALTER COLUMN placement SET DEFAULT 'main-top'; -ALTER TABLE sections ADD CONSTRAINT sections_placement_check - CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom')); - --- +goose Down -ALTER TABLE sections DROP CONSTRAINT sections_placement_check; -UPDATE sections SET placement = CASE placement WHEN 'main-top' THEN 'above' WHEN 'main-bottom' THEN 'below' ELSE 'sidebar' END; -ALTER TABLE sections ALTER COLUMN placement SET DEFAULT 'above'; -ALTER TABLE sections ADD CONSTRAINT sections_placement_check CHECK (placement IN ('above', 'below', 'sidebar')); 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/control/00001_init.sql b/internal/db/migrations/control/00001_init.sql new file mode 100644 index 0000000..e27dad4 --- /dev/null +++ b/internal/db/migrations/control/00001_init.sql @@ -0,0 +1,68 @@ +-- +goose Up +CREATE TABLE users ( + id bigserial PRIMARY KEY, + username text NOT NULL UNIQUE, + password_hash text NOT NULL, + role text NOT NULL CHECK (role IN ('superadmin', 'blogger')), + disabled boolean NOT NULL DEFAULT false, + token_version integer NOT NULL DEFAULT 0, + created_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE blogs ( + id bigserial PRIMARY KEY, + owner_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, + subdomain text NOT NULL UNIQUE CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,62}$'), + 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, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + slug text NOT NULL, + title text NOT NULL, + intro_md text NOT NULL DEFAULT '', + intro_html text NOT NULL DEFAULT '', + nav_order integer NOT NULL DEFAULT 0, + show_in_nav boolean NOT NULL DEFAULT true, + is_home boolean NOT NULL DEFAULT false, + created_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (blog_id, slug) +); +CREATE UNIQUE INDEX pages_one_home_per_blog ON pages (blog_id) 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, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + 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_blog ON images (blog_id, created_at DESC); + +-- +goose Down +DROP TABLE images; +DROP TABLE posts; +DROP TABLE pages; +DROP TABLE blogs; +DROP TABLE users; diff --git a/internal/db/migrations/control/00002_sections.sql b/internal/db/migrations/control/00002_sections.sql new file mode 100644 index 0000000..388cb17 --- /dev/null +++ b/internal/db/migrations/control/00002_sections.sql @@ -0,0 +1,19 @@ +-- +goose Up +-- Announcements: blog-wide notices shown on every page and post. +CREATE TABLE sections ( + id bigserial PRIMARY KEY, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + title text NOT NULL DEFAULT '', + body_md text NOT NULL DEFAULT '', + body_html text NOT NULL DEFAULT '', + placement text NOT NULL DEFAULT 'above' CHECK (placement IN ('above', 'below', 'sidebar')), + 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_blog ON sections (blog_id, sort_order, id); + +-- +goose Down +DROP TABLE sections; diff --git a/internal/db/migrations/control/00003_layout.sql b/internal/db/migrations/control/00003_layout.sql new file mode 100644 index 0000000..e8fff23 --- /dev/null +++ b/internal/db/migrations/control/00003_layout.sql @@ -0,0 +1,56 @@ +-- +goose Up +-- Layout modules: what each area of a blog (header, columns, footer) shows. +CREATE TABLE modules ( + id bigserial PRIMARY KEY, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + 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_blog ON modules (blog_id, area, sort_order, id); + +-- The menu: blog pages and custom links in one ordered list. +CREATE TABLE menu_items ( + id bigserial PRIMARY KEY, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + 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_blog ON menu_items (blog_id, sort_order, id); + +-- Existing blogs keep the menu they had: pages marked "show in menu", in menu +-- order, with the home page only if the theme included it. +INSERT INTO menu_items (blog_id, page_id, sort_order) +SELECT p.blog_id, p.id, row_number() OVER (PARTITION BY p.blog_id ORDER BY p.nav_order, p.id) - 1 +FROM pages p JOIN blogs b ON b.id = p.blog_id +WHERE p.show_in_nav AND (NOT p.is_home OR coalesce((b.theme->>'nav_show_home')::boolean, true)); +ALTER TABLE pages DROP COLUMN show_in_nav; + +-- ...and the layout their theme described. +INSERT INTO modules (blog_id, area, kind, sort_order) +SELECT id, 'header', 'title', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 1 ELSE 0 END +FROM blogs WHERE coalesce((theme->>'header_show_title')::boolean, true); +INSERT INTO modules (blog_id, area, kind, sort_order) +SELECT id, 'header', 'menu', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 0 ELSE 1 END +FROM blogs WHERE coalesce(theme->>'nav_position', 'below-header') <> 'left-sidebar'; +INSERT INTO modules (blog_id, area, kind, sort_order) +SELECT id, 'left', 'menu', 0 FROM blogs WHERE theme->>'nav_position' = 'left-sidebar'; +INSERT INTO modules (blog_id, area, kind, body, sort_order) +SELECT id, 'footer', 'text', theme->>'footer_text', 0 FROM blogs WHERE coalesce(theme->>'footer_text', '') <> ''; +INSERT INTO modules (blog_id, area, kind, sort_order) +SELECT id, 'footer', 'rss', 1 FROM blogs WHERE coalesce((theme->>'footer_show_rss')::boolean, true); + +-- +goose Down +ALTER TABLE pages ADD COLUMN show_in_nav boolean NOT NULL DEFAULT true; +UPDATE pages SET show_in_nav = EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id = pages.id); +DROP TABLE menu_items; +DROP TABLE modules; diff --git a/internal/db/migrations/control/00004_section_placement.sql b/internal/db/migrations/control/00004_section_placement.sql new file mode 100644 index 0000000..c36787e --- /dev/null +++ b/internal/db/migrations/control/00004_section_placement.sql @@ -0,0 +1,13 @@ +-- +goose Up +-- Announcements are placed in a column (left, main, right) at its top or bottom. +ALTER TABLE sections DROP CONSTRAINT sections_placement_check; +UPDATE sections SET placement = CASE placement WHEN 'above' THEN 'main-top' WHEN 'below' THEN 'main-bottom' ELSE 'left-top' END; +ALTER TABLE sections ALTER COLUMN placement SET DEFAULT 'main-top'; +ALTER TABLE sections ADD CONSTRAINT sections_placement_check + CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom')); + +-- +goose Down +ALTER TABLE sections DROP CONSTRAINT sections_placement_check; +UPDATE sections SET placement = CASE placement WHEN 'main-top' THEN 'above' WHEN 'main-bottom' THEN 'below' ELSE 'sidebar' END; +ALTER TABLE sections ALTER COLUMN placement SET DEFAULT 'above'; +ALTER TABLE sections ADD CONSTRAINT sections_placement_check CHECK (placement IN ('above', 'below', 'sidebar')); 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 +} -- cgit v1.2.3