64 lines
3.1 KiB
MySQL
64 lines
3.1 KiB
MySQL
|
CREATE TYPE "public"."annotation_type" AS ENUM('HIGHLIGHT', 'DRAW', 'COMMENT', 'SHAPE', 'REDACTION', 'TEXTBOX', 'STAMP');--> statement-breakpoint
|
||
|
|
CREATE TABLE "annotation_replies" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"annotation_id" uuid NOT NULL,
|
||
|
|
"user_id" text NOT NULL,
|
||
|
|
"user_name" text NOT NULL,
|
||
|
|
"message" text NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "annotation_versions" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"annotation_id" uuid NOT NULL,
|
||
|
|
"file_id" uuid NOT NULL,
|
||
|
|
"page_index" integer NOT NULL,
|
||
|
|
"user_id" text NOT NULL,
|
||
|
|
"user_name" text NOT NULL,
|
||
|
|
"type" "annotation_type" NOT NULL,
|
||
|
|
"data" jsonb NOT NULL,
|
||
|
|
"kind" text NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "annotations" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"file_id" uuid NOT NULL,
|
||
|
|
"page_index" integer NOT NULL,
|
||
|
|
"user_id" text NOT NULL,
|
||
|
|
"user_name" text NOT NULL,
|
||
|
|
"type" "annotation_type" NOT NULL,
|
||
|
|
"data" jsonb NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
||
|
|
"resolved" boolean DEFAULT false NOT NULL,
|
||
|
|
"resolved_at" timestamp with time zone
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "bookmarks" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"file_id" uuid NOT NULL,
|
||
|
|
"page_index" integer NOT NULL,
|
||
|
|
"user_id" text NOT NULL,
|
||
|
|
"title" text NOT NULL,
|
||
|
|
"parent_id" uuid,
|
||
|
|
"note" text,
|
||
|
|
"y_offset" double precision NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
CREATE TABLE "files" (
|
||
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
||
|
|
"title" text NOT NULL,
|
||
|
|
"description" text,
|
||
|
|
"pdf_url" text NOT NULL,
|
||
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
||
|
|
);
|
||
|
|
--> statement-breakpoint
|
||
|
|
ALTER TABLE "annotation_replies" ADD CONSTRAINT "annotation_replies_annotation_id_annotations_id_fk" FOREIGN KEY ("annotation_id") REFERENCES "public"."annotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "annotation_versions" ADD CONSTRAINT "annotation_versions_file_id_files_id_fk" FOREIGN KEY ("file_id") REFERENCES "public"."files"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "annotations" ADD CONSTRAINT "annotations_file_id_files_id_fk" FOREIGN KEY ("file_id") REFERENCES "public"."files"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
ALTER TABLE "bookmarks" ADD CONSTRAINT "bookmarks_file_id_files_id_fk" FOREIGN KEY ("file_id") REFERENCES "public"."files"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
||
|
|
CREATE INDEX "annotation_replies_annotation_idx" ON "annotation_replies" USING btree ("annotation_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "annotation_versions_file_idx" ON "annotation_versions" USING btree ("file_id");--> statement-breakpoint
|
||
|
|
CREATE INDEX "annotations_file_page_idx" ON "annotations" USING btree ("file_id","page_index");
|