diff options
Diffstat (limited to 'internal/db/migrations/control')
6 files changed, 177 insertions, 0 deletions
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. |