diff options
Diffstat (limited to 'internal/db/migrations')
6 files changed, 6 insertions, 158 deletions
diff --git a/internal/db/migrations/control/00001_init.sql b/internal/db/migrations/control/00001_init.sql index e27dad4..0b26b37 100644 --- a/internal/db/migrations/control/00001_init.sql +++ b/internal/db/migrations/control/00001_init.sql @@ -1,4 +1,6 @@ -- +goose Up +-- The control database: accounts and the list of blogs. Everything a blog +-- contains lives in its own database (see migrations/blog). CREATE TABLE users ( id bigserial PRIMARY KEY, username text NOT NULL UNIQUE, @@ -9,60 +11,15 @@ CREATE TABLE users ( created_at timestamptz NOT NULL DEFAULT now() ); +-- "blog_" + subdomain must fit a 63-char Postgres identifier, hence 58. 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() + subdomain text NOT NULL UNIQUE CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,57}$'), + db_name text NOT NULL UNIQUE, + created_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 deleted file mode 100644 index 388cb17..0000000 --- a/internal/db/migrations/control/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/control/00003_layout.sql b/internal/db/migrations/control/00003_layout.sql deleted file mode 100644 index e8fff23..0000000 --- a/internal/db/migrations/control/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/control/00004_section_placement.sql b/internal/db/migrations/control/00004_section_placement.sql deleted file mode 100644 index c36787e..0000000 --- a/internal/db/migrations/control/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/control/00005_per_blog_databases.sql b/internal/db/migrations/control/00005_per_blog_databases.sql deleted file mode 100644 index 92d3fa2..0000000 --- a/internal/db/migrations/control/00005_per_blog_databases.sql +++ /dev/null @@ -1,12 +0,0 @@ --- +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 deleted file mode 100644 index 9daa1ac..0000000 --- a/internal/db/migrations/control/00007_drop_content.sql +++ /dev/null @@ -1,9 +0,0 @@ --- +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. |