From c72d0897f7b37843109c86f61d97eba05ba3ca0d Mon Sep 17 00:00:00 2001 From: dujinkim Date: Fri, 13 Jun 2025 07:08:01 +0000 Subject: (대표님) 20250613 16시 08분 b-rfq, document 등 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/migrations/0128_sad_vermin.sql | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) create mode 100644 db/migrations/0128_sad_vermin.sql (limited to 'db/migrations/0128_sad_vermin.sql') diff --git a/db/migrations/0128_sad_vermin.sql b/db/migrations/0128_sad_vermin.sql new file mode 100644 index 00000000..86c27be9 --- /dev/null +++ b/db/migrations/0128_sad_vermin.sql @@ -0,0 +1,36 @@ +DROP INDEX "latest_revision_idx";--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" ADD COLUMN "current_revision" varchar(10) DEFAULT 'Rev.0' NOT NULL;--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" ADD COLUMN "latest_revision_id" integer;--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;--> statement-breakpoint +CREATE UNIQUE INDEX "attachment_revision_idx" ON "b_rfq_attachment_revisions" USING btree ("attachment_id","revision_no");--> statement-breakpoint +CREATE UNIQUE INDEX "latest_revision_idx" ON "b_rfq_attachment_revisions" USING btree ("attachment_id","is_latest") WHERE "b_rfq_attachment_revisions"."is_latest" = $1;--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_name";--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" DROP COLUMN "original_file_name";--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_path";--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_size";--> statement-breakpoint +ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_type";--> statement-breakpoint +CREATE VIEW "public"."attachments_with_latest_revision" AS ( + SELECT + a.id as attachment_id, + a.attachment_type, + a.serial_no, + a.rfq_id, + a.description, + a.current_revision, + + r.id as revision_id, + r.file_name, + r.original_file_name, + r.file_path, + r.file_size, + r.file_type, + r.revision_comment, + + a.created_by, + u.name as created_by_name, + a.created_at, + a.updated_at + FROM b_rfq_attachments a + LEFT JOIN b_rfq_attachment_revisions r ON a.latest_revision_id = r.id + LEFT JOIN users u ON a.created_by = u.id + ); \ No newline at end of file -- cgit v1.2.3