1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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
);
|
