147 lines
6.8 KiB
SQL
147 lines
6.8 KiB
SQL
CREATE SCHEMA "books";
|
|
--> statement-breakpoint
|
|
CREATE TYPE "books"."metadata_status" AS ENUM('pending', 'matched', 'low_confidence', 'not_found', 'manual');--> statement-breakpoint
|
|
CREATE TABLE "books"."files" (
|
|
"id" serial PRIMARY KEY,
|
|
"work_id" integer NOT NULL,
|
|
"path" text NOT NULL,
|
|
"format" text NOT NULL,
|
|
"size_bytes" bigint NOT NULL,
|
|
"sha256" text NOT NULL,
|
|
"md5" text,
|
|
"source" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "books"."notes" (
|
|
"id" serial PRIMARY KEY,
|
|
"work_id" integer NOT NULL,
|
|
"file_id" integer,
|
|
"locator" text,
|
|
"page" integer,
|
|
"highlight" text,
|
|
"note" text,
|
|
"color" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "books"."progress" (
|
|
"id" serial PRIMARY KEY,
|
|
"work_id" integer NOT NULL,
|
|
"file_id" integer,
|
|
"locator" text,
|
|
"page" integer,
|
|
"percent" real DEFAULT 0 NOT NULL,
|
|
"started_at" timestamp with time zone,
|
|
"finished_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "progress_percent_check" CHECK ("percent" between 0 and 100)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "books"."shelf_works" (
|
|
"shelf_id" integer,
|
|
"work_id" integer,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
"added_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "shelf_works_pkey" PRIMARY KEY("shelf_id","work_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "books"."shelves" (
|
|
"id" serial PRIMARY KEY,
|
|
"slug" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "books"."works" (
|
|
"id" serial PRIMARY KEY,
|
|
"title" text NOT NULL,
|
|
"subtitle" text,
|
|
"authors" text[] DEFAULT '{}'::text[] NOT NULL,
|
|
"isbn10" text,
|
|
"isbn13" text,
|
|
"publisher" text,
|
|
"year" integer,
|
|
"language" text,
|
|
"page_count" integer,
|
|
"description" text,
|
|
"tags" text[] DEFAULT '{}'::text[] NOT NULL,
|
|
"cover_url" text,
|
|
"cover_path" text,
|
|
"openlibrary_work_id" text,
|
|
"openlibrary_edition_id" text,
|
|
"google_books_id" text,
|
|
"annas_md5" text[] DEFAULT '{}'::text[] NOT NULL,
|
|
"source_refs" jsonb DEFAULT '{}' NOT NULL,
|
|
"metadata_status" "books"."metadata_status" DEFAULT 'pending'::"books"."metadata_status" NOT NULL,
|
|
"metadata_source" text,
|
|
"metadata_fetched_at" timestamp with time zone,
|
|
"notes" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "works_year_check" CHECK ("year" is null or "year" between 0 and 2100)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "files_path_key" ON "books"."files" ("path");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "files_sha256_key" ON "books"."files" ("sha256");--> statement-breakpoint
|
|
CREATE INDEX "files_work_id_idx" ON "books"."files" ("work_id");--> statement-breakpoint
|
|
CREATE INDEX "notes_work_id_idx" ON "books"."notes" ("work_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "progress_work_id_key" ON "books"."progress" ("work_id");--> statement-breakpoint
|
|
CREATE INDEX "shelf_works_work_id_idx" ON "books"."shelf_works" ("work_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "shelves_slug_key" ON "books"."shelves" ("slug");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "works_isbn13_key" ON "books"."works" ("isbn13") WHERE "isbn13" is not null;--> statement-breakpoint
|
|
CREATE INDEX "works_title_idx" ON "books"."works" (lower("title"));--> statement-breakpoint
|
|
ALTER TABLE "books"."files" ADD CONSTRAINT "files_work_id_works_id_fkey" FOREIGN KEY ("work_id") REFERENCES "books"."works"("id") ON DELETE CASCADE;--> statement-breakpoint
|
|
ALTER TABLE "books"."notes" ADD CONSTRAINT "notes_work_id_works_id_fkey" FOREIGN KEY ("work_id") REFERENCES "books"."works"("id") ON DELETE CASCADE;--> statement-breakpoint
|
|
ALTER TABLE "books"."notes" ADD CONSTRAINT "notes_file_id_files_id_fkey" FOREIGN KEY ("file_id") REFERENCES "books"."files"("id") ON DELETE SET NULL;--> statement-breakpoint
|
|
ALTER TABLE "books"."progress" ADD CONSTRAINT "progress_work_id_works_id_fkey" FOREIGN KEY ("work_id") REFERENCES "books"."works"("id") ON DELETE CASCADE;--> statement-breakpoint
|
|
ALTER TABLE "books"."progress" ADD CONSTRAINT "progress_file_id_files_id_fkey" FOREIGN KEY ("file_id") REFERENCES "books"."files"("id") ON DELETE SET NULL;--> statement-breakpoint
|
|
ALTER TABLE "books"."shelf_works" ADD CONSTRAINT "shelf_works_shelf_id_shelves_id_fkey" FOREIGN KEY ("shelf_id") REFERENCES "books"."shelves"("id") ON DELETE CASCADE;--> statement-breakpoint
|
|
ALTER TABLE "books"."shelf_works" ADD CONSTRAINT "shelf_works_work_id_works_id_fkey" FOREIGN KEY ("work_id") REFERENCES "books"."works"("id") ON DELETE CASCADE;
|
|
--> statement-breakpoint
|
|
-- Hand-written: updated_at triggers, grants, default shelves, GUI registration.
|
|
DO $$
|
|
DECLARE t text;
|
|
BEGIN
|
|
FOREACH t IN ARRAY ARRAY['works','files','shelves','progress','notes'] LOOP
|
|
EXECUTE format('CREATE TRIGGER touch_updated_at BEFORE UPDATE ON books.%I FOR EACH ROW EXECUTE FUNCTION meta.touch_updated_at()', t);
|
|
END LOOP;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'personal_app') THEN
|
|
GRANT USAGE ON SCHEMA books TO personal_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA books TO personal_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA books TO personal_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA books GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO personal_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA books GRANT USAGE, SELECT ON SEQUENCES TO personal_app;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'personal_ro') THEN
|
|
GRANT USAGE ON SCHEMA books TO personal_ro;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA books TO personal_ro;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA books GRANT SELECT ON TABLES TO personal_ro;
|
|
END IF;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
INSERT INTO books.shelves (slug, name, sort_order) VALUES
|
|
('to-read', 'To read', 10), ('reading', 'Reading', 20), ('done', 'Done', 30), ('reference', 'Reference', 40)
|
|
ON CONFLICT DO NOTHING;
|
|
--> statement-breakpoint
|
|
DO $$
|
|
BEGIN
|
|
IF to_regclass('meta.tables') IS NOT NULL THEN
|
|
INSERT INTO meta.tables (schema_name, table_name, slug, display_name, kind, editable, has_done, sort_order)
|
|
VALUES ('books','works','books-works','Books','core',true,false,1000),
|
|
('books','shelves','books-shelves','Book shelves','core',true,false,1000),
|
|
('books','files','books-files','Book files','core',false,false,1000)
|
|
ON CONFLICT DO NOTHING;
|
|
PERFORM meta.sync();
|
|
END IF;
|
|
END $$;
|