89 lines
4.8 KiB
SQL
89 lines
4.8 KiB
SQL
CREATE TYPE "public"."content_status" AS ENUM('draft', 'published');--> statement-breakpoint
|
|
CREATE TYPE "public"."home_mode" AS ENUM('posts', 'tag', 'page');--> statement-breakpoint
|
|
CREATE TABLE "nav_items" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "nav_items_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"label" text NOT NULL,
|
|
"url" text,
|
|
"page_id" integer,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
CONSTRAINT "nav_items_target_check" CHECK (("nav_items"."url" IS NULL) <> ("nav_items"."page_id" IS NULL))
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "pages" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "pages_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"title" text NOT NULL,
|
|
"slug" text NOT NULL,
|
|
"body" text DEFAULT '' NOT NULL,
|
|
"status" "content_status" DEFAULT 'draft' NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "pages_slug_unique" UNIQUE("slug")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "post_tags" (
|
|
"post_id" integer NOT NULL,
|
|
"tag_id" integer NOT NULL,
|
|
CONSTRAINT "post_tags_post_id_tag_id_pk" PRIMARY KEY("post_id","tag_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "posts" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "posts_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"title" text NOT NULL,
|
|
"slug" text NOT NULL,
|
|
"body" text DEFAULT '' NOT NULL,
|
|
"author_name" text NOT NULL,
|
|
"featured_image_url" text,
|
|
"featured_image_alt" text,
|
|
"status" "content_status" DEFAULT 'draft' NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"published_at" timestamp with time zone,
|
|
CONSTRAINT "posts_slug_unique" UNIQUE("slug")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "sessions" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" integer NOT NULL,
|
|
"expires_at" timestamp with time zone NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "settings" (
|
|
"id" integer PRIMARY KEY NOT NULL,
|
|
"site_title" text DEFAULT 'My Blog' NOT NULL,
|
|
"header_text" text DEFAULT '' NOT NULL,
|
|
"footer_text" text DEFAULT '' NOT NULL,
|
|
"posts_per_page" integer DEFAULT 10 NOT NULL,
|
|
"excerpt_words" integer DEFAULT 40 NOT NULL,
|
|
"home_mode" "home_mode" DEFAULT 'posts' NOT NULL,
|
|
"home_tag_id" integer,
|
|
"home_page_id" integer,
|
|
CONSTRAINT "settings_single_row_check" CHECK ("settings"."id" = 1),
|
|
CONSTRAINT "settings_posts_per_page_check" CHECK ("settings"."posts_per_page" BETWEEN 1 AND 50),
|
|
CONSTRAINT "settings_excerpt_words_check" CHECK ("settings"."excerpt_words" BETWEEN 5 AND 200)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "tags" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "tags_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"name" text NOT NULL,
|
|
"slug" text NOT NULL,
|
|
CONSTRAINT "tags_name_unique" UNIQUE("name"),
|
|
CONSTRAINT "tags_slug_unique" UNIQUE("slug")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "users" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"username" text NOT NULL,
|
|
"password_hash" text NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "users_username_unique" UNIQUE("username")
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "nav_items" ADD CONSTRAINT "nav_items_page_id_pages_id_fk" FOREIGN KEY ("page_id") REFERENCES "public"."pages"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "post_tags" ADD CONSTRAINT "post_tags_post_id_posts_id_fk" FOREIGN KEY ("post_id") REFERENCES "public"."posts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "post_tags" ADD CONSTRAINT "post_tags_tag_id_tags_id_fk" FOREIGN KEY ("tag_id") REFERENCES "public"."tags"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "settings" ADD CONSTRAINT "settings_home_tag_id_tags_id_fk" FOREIGN KEY ("home_tag_id") REFERENCES "public"."tags"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "settings" ADD CONSTRAINT "settings_home_page_id_pages_id_fk" FOREIGN KEY ("home_page_id") REFERENCES "public"."pages"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "post_tags_tag_id_idx" ON "post_tags" USING btree ("tag_id");--> statement-breakpoint
|
|
CREATE INDEX "posts_status_published_at_idx" ON "posts" USING btree ("status","published_at"); |