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
|
CREATE TABLE "permissions" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permissions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"permission_key" text NOT NULL,
"description" text,
"created_at" timestamp DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "role_permissions" (
"role_id" integer NOT NULL,
"permission_id" integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE "roles" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "roles_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"name" text NOT NULL,
"created_at" timestamp DEFAULT now()
);
--> statement-breakpoint
DROP VIEW "public"."user_view";--> statement-breakpoint
ALTER TABLE "user_roles" DROP CONSTRAINT "user_roles_user_id_users_id_fk";
--> statement-breakpoint
ALTER TABLE "user_roles" ALTER COLUMN "user_id" SET NOT NULL;--> statement-breakpoint
ALTER TABLE "user_roles" ADD COLUMN "role_id" integer NOT NULL;--> statement-breakpoint
ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_permissions_id_fk" FOREIGN KEY ("permission_id") REFERENCES "public"."permissions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_roles" DROP COLUMN "id";--> statement-breakpoint
ALTER TABLE "user_roles" DROP COLUMN "role";--> statement-breakpoint
CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."email" as "user_email", "users"."image_url" as "user_image", "companies"."id" as "company_id", "companies"."name" as "company_name",
array_agg("roles"."name")
as "roles", "users"."created_at" as "created_at" from "users" left join "companies" on "users"."company_id" = "companies"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "companies"."id");
|
