diff options
| author | dujinkim <dujin.kim@dtsolution.co.kr> | 2025-06-13 07:08:01 +0000 |
|---|---|---|
| committer | dujinkim <dujin.kim@dtsolution.co.kr> | 2025-06-13 07:08:01 +0000 |
| commit | c72d0897f7b37843109c86f61d97eba05ba3ca0d (patch) | |
| tree | 887dd877f3f8beafa92b4d9a7b16c84b4a5795d8 /db/migrations/0128_sad_vermin.sql | |
| parent | ff902243a658067fae858a615c0629aa2e0a4837 (diff) | |
(대표님) 20250613 16시 08분 b-rfq, document 등
Diffstat (limited to 'db/migrations/0128_sad_vermin.sql')
| -rw-r--r-- | db/migrations/0128_sad_vermin.sql | 36 |
1 files changed, 36 insertions, 0 deletions
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 |