Index: database/drizzle/queries/builds.ts =================================================================== --- database/drizzle/queries/builds.ts (revision ad211d1ca8417919b1358701f1f24d8b8b875a44) +++ database/drizzle/queries/builds.ts (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) @@ -200,5 +200,6 @@ .select({ componentId: buildComponentsTable.componentId, - component: componentsTable + component: componentsTable, + quantity: buildComponentsTable.numComponents }) .from(buildComponentsTable) @@ -292,5 +293,8 @@ return { ...buildDetails, - components: components.map(c => c.component), + components: components.map(c => ({ + ...c.component, + quantity: c.quantity + })), reviews: reviews.map(r => ({ username: r.username, @@ -419,5 +423,8 @@ const existing = await tx - .select({ componentId: buildComponentsTable.componentId }) + .select({ + componentId: buildComponentsTable.componentId, + numComponents: buildComponentsTable.numComponents + }) .from(buildComponentsTable) .where(eq(buildComponentsTable.buildId, buildId)); @@ -428,4 +435,5 @@ buildId: newBuild.id, componentId: r.componentId, + numComponents: r.numComponents })), ); @@ -545,5 +553,6 @@ const components = await tx .select({ - componentId: buildComponentsTable.componentId + componentId: buildComponentsTable.componentId, + quantity: buildComponentsTable.numComponents }) .from(buildComponentsTable) @@ -554,5 +563,8 @@ return { build, - componentIds: components.map(c => c.componentId) + components: components.map(c => ({ + id: c.componentId, + quantity: c.quantity + })) }; }); Index: database/drizzle/queries/components.ts =================================================================== --- database/drizzle/queries/components.ts (revision ad211d1ca8417919b1358701f1f24d8b8b875a44) +++ database/drizzle/queries/components.ts (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) @@ -196,4 +196,5 @@ id: componentsTable.id, type: componentsTable.type, + quantity: buildComponentsTable.numComponents }) .from(buildComponentsTable) @@ -231,4 +232,5 @@ componentsDetails.push({ ...comp, + quantity: comp.quantity, details: details }); @@ -256,6 +258,4 @@ if(!existingComponents) return null; - - const existing = { @@ -278,9 +278,12 @@ ...existing.networkAdapters, ...existing.soundCards - ].filter(Boolean).length; + ].reduce((sum: number, c: any) => { + if (!c) return sum; + return sum + (c.quantity || 1); + }, 0); const existingTDP = existingComponents.reduce((sum, c) => { const tdp = c.details?.tdp ? Number(c.details.tdp) : 0; - return sum + tdp; + return sum + (tdp * (c.quantity || 1)); }, 0); @@ -456,8 +459,8 @@ if (existing.memory.length > 0) { const totalModules = existing.memory.reduce((sum: number, m: any) => - sum + Number(m.details.modules), 0 + sum + (Number(m.details.modules) * m.quantity), 0 ); const totalCapacity = existing.memory.reduce((sum: number, m: any) => - sum + Number(m.details.capacity), 0 + sum + (Number(m.details.capacity) * m.quantity), 0 ); @@ -704,7 +707,7 @@ if (!caseStorageFormFactor) return false; - const usedSlots = existing.storage.filter( - (s: any) => s.details.formFactor === stor.formFactor - ).length; + const usedSlots = existing.storage + .filter((s: any) => s.details.formFactor === stor.formFactor) + .reduce((sum: number, s: any) => sum + s.quantity, 0); return usedSlots < Number(caseStorageFormFactor.numSlots); @@ -821,7 +824,7 @@ if (!storageFF) return false; - const usedSlots = existing.storage.filter( - (s: any) => s.details.formFactor === stor.details.formFactor - ).length; + const usedSlots = existing.storage + .filter((s: any) => s.details.formFactor === stor.details.formFactor) + .reduce((sum: number, s: any) => sum + s.quantity, 0);; return usedSlots <= Number(storageFF.numSlots); @@ -963,5 +966,12 @@ .values({ buildId, - componentId + componentId, + numComponents: 1 + }) + .onConflictDoUpdate({ + target: [buildComponentsTable.buildId, buildComponentsTable.componentId], + set: { + numComponents: sql`${buildComponentsTable.numComponents} + 1` + } }); @@ -969,4 +979,5 @@ .select({ price: componentsTable.price, + quantity: buildComponentsTable.numComponents }) .from(buildComponentsTable) @@ -979,5 +990,7 @@ ); - const totalPrice = buildComponents.reduce((sum, c) => sum + Number(c.price), 0); + const totalPrice = buildComponents.reduce((sum, c) => + sum + (Number(c.price) * c.quantity), 0 + ); await tx @@ -1009,6 +1022,9 @@ if(!build || build.isApproved) return null; - const result = await tx - .delete(buildComponentsTable) + const [existing] = await tx + .select({ + quantity: buildComponentsTable.numComponents + }) + .from(buildComponentsTable) .where( and( @@ -1016,11 +1032,36 @@ eq(buildComponentsTable.componentId, componentId) ) - ); - - if(result.rowCount === 0) return null; + ) + .limit(1); + + if (!existing) return null; + + if (existing.quantity > 1) { + await tx + .update(buildComponentsTable) + .set({ + numComponents: sql`${buildComponentsTable.numComponents} - 1` + }) + .where( + and( + eq(buildComponentsTable.buildId, buildId), + eq(buildComponentsTable.componentId, componentId) + ) + ); + } else { + await tx + .delete(buildComponentsTable) + .where( + and( + eq(buildComponentsTable.buildId, buildId), + eq(buildComponentsTable.componentId, componentId) + ) + ); + } const buildComponents = await tx .select({ price: componentsTable.price, + quantity: buildComponentsTable.numComponents }) .from(buildComponentsTable) @@ -1033,5 +1074,7 @@ ); - const totalPrice = buildComponents.reduce((sum, c) => sum + Number(c.price), 0); + const totalPrice = buildComponents.reduce((sum, c) => + sum + (Number(c.price) * c.quantity), 0 + ); await tx @@ -1044,5 +1087,5 @@ ); - return result; + return componentId; }) } Index: database/drizzle/schema/builds.ts =================================================================== --- database/drizzle/schema/builds.ts (revision ad211d1ca8417919b1358701f1f24d8b8b875a44) +++ database/drizzle/schema/builds.ts (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) @@ -17,5 +17,4 @@ export const buildComponentsTable = pgTable("build_component", { - id: serial("id").primaryKey(), buildId: integer("build_id") .notNull() @@ -24,5 +23,11 @@ .notNull() .references(() => componentsTable.id, { onDelete: "cascade", onUpdate: "cascade" }), + numComponents: integer("num_components") + .notNull() + .default(1), }, + (t) => ({ + pk: primaryKey({ columns: [t.buildId, t.componentId] }), + }), ); Index: database/migrations/0000_regular_kulan_gath.sql =================================================================== --- database/migrations/0000_regular_kulan_gath.sql (revision ad211d1ca8417919b1358701f1f24d8b8b875a44) +++ (revision ) @@ -1,242 +1,0 @@ -CREATE TABLE "build_component" ( - "id" serial PRIMARY KEY NOT NULL, - "build_id" integer NOT NULL, - "component_id" integer NOT NULL -); ---> statement-breakpoint -CREATE TABLE "build" ( - "id" serial PRIMARY KEY NOT NULL, - "user_id" integer NOT NULL, - "name" text NOT NULL, - "created_at" date NOT NULL, - "description" text, - "total_price" numeric NOT NULL, - "is_approved" boolean NOT NULL -); ---> statement-breakpoint -CREATE TABLE "favorite_build" ( - "build_id" integer NOT NULL, - "user_id" integer NOT NULL, - CONSTRAINT "favorite_build_build_id_user_id_pk" PRIMARY KEY("build_id","user_id") -); ---> statement-breakpoint -CREATE TABLE "rating_build" ( - "build_id" integer NOT NULL, - "user_id" integer NOT NULL, - "value" numeric NOT NULL, - CONSTRAINT "rating_build_build_id_user_id_pk" PRIMARY KEY("build_id","user_id"), - CONSTRAINT "check_value" CHECK ("rating_build"."value" BETWEEN 1 AND 5) -); ---> statement-breakpoint -CREATE TABLE "review" ( - "id" serial PRIMARY KEY NOT NULL, - "build_id" integer NOT NULL, - "user_id" integer NOT NULL, - "content" text NOT NULL, - "created_at" date NOT NULL, - CONSTRAINT "review_build_id_user_id_unique" UNIQUE("build_id","user_id") -); ---> statement-breakpoint -CREATE TABLE "cpu" ( - "component_id" integer PRIMARY KEY NOT NULL, - "socket" text NOT NULL, - "cores" integer NOT NULL, - "threads" integer NOT NULL, - "base_clock" numeric NOT NULL, - "boost_clock" numeric, - "tdp" numeric NOT NULL -); ---> statement-breakpoint -CREATE TABLE "gpu" ( - "component_id" integer PRIMARY KEY NOT NULL, - "vram" numeric NOT NULL, - "tdp" numeric NOT NULL, - "base_clock" numeric, - "boost_clock" numeric, - "chipset" text NOT NULL, - "length" numeric NOT NULL -); ---> statement-breakpoint -CREATE TABLE "cables" ( - "component_id" integer PRIMARY KEY NOT NULL, - "length_cm" numeric NOT NULL, - "type" text NOT NULL -); ---> statement-breakpoint -CREATE TABLE "case_mobo_form_factors" ( - "case_id" integer NOT NULL, - "form_factor" text NOT NULL, - CONSTRAINT "case_mobo_form_factors_case_id_form_factor_pk" PRIMARY KEY("case_id","form_factor") -); ---> statement-breakpoint -CREATE TABLE "case_ps_form_factors" ( - "case_id" integer NOT NULL, - "form_factor" text NOT NULL, - CONSTRAINT "case_ps_form_factors_case_id_form_factor_pk" PRIMARY KEY("case_id","form_factor") -); ---> statement-breakpoint -CREATE TABLE "case_storage_form_factors" ( - "case_id" integer NOT NULL, - "form_factor" text NOT NULL, - "num_slots" integer NOT NULL, - CONSTRAINT "case_storage_form_factors_case_id_form_factor_pk" PRIMARY KEY("case_id","form_factor") -); ---> statement-breakpoint -CREATE TABLE "components" ( - "id" serial PRIMARY KEY NOT NULL, - "name" text NOT NULL, - "brand" text NOT NULL, - "price" numeric NOT NULL, - "img_url" text, - "type" text NOT NULL, - CONSTRAINT "check_type" CHECK ("components"."type" in - ('cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard', 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card', 'cables', 'network_adapter', 'network_card')) -); ---> statement-breakpoint -CREATE TABLE "cooler_cpu_sockets" ( - "cooler_id" integer NOT NULL, - "socket" text NOT NULL, - CONSTRAINT "cooler_cpu_sockets_cooler_id_socket_pk" PRIMARY KEY("cooler_id","socket") -); ---> statement-breakpoint -CREATE TABLE "cooler" ( - "component_id" integer PRIMARY KEY NOT NULL, - "type" text NOT NULL, - "height" numeric NOT NULL, - "max_tdp_supported" numeric NOT NULL -); ---> statement-breakpoint -CREATE TABLE "memory_card" ( - "component_id" integer PRIMARY KEY NOT NULL, - "num_slots" integer NOT NULL, - "interface" text NOT NULL -); ---> statement-breakpoint -CREATE TABLE "memory" ( - "component_id" integer PRIMARY KEY NOT NULL, - "type" text NOT NULL, - "speed" numeric NOT NULL, - "capacity" numeric NOT NULL, - "modules" integer NOT NULL -); ---> statement-breakpoint -CREATE TABLE "motherboard" ( - "component_id" integer PRIMARY KEY NOT NULL, - "socket" text NOT NULL, - "chipset" text NOT NULL, - "form_factor" text NOT NULL, - "ram_type" text NOT NULL, - "num_ram_slots" integer NOT NULL, - "max_ram_capacity" numeric NOT NULL, - "pci_express_slots" numeric NOT NULL -); ---> statement-breakpoint -CREATE TABLE "network_adapter" ( - "component_id" integer PRIMARY KEY NOT NULL, - "wifi_version" text NOT NULL, - "interface" text NOT NULL, - "num_antennas" integer NOT NULL -); ---> statement-breakpoint -CREATE TABLE "network_card" ( - "component_id" integer PRIMARY KEY NOT NULL, - "num_ports" integer NOT NULL, - "speed" numeric NOT NULL, - "interface" text NOT NULL -); ---> statement-breakpoint -CREATE TABLE "optical_drive" ( - "component_id" integer PRIMARY KEY NOT NULL, - "form_factor" text NOT NULL, - "type" text NOT NULL, - "interface" text NOT NULL, - "write_speed" numeric NOT NULL, - "read_speed" numeric NOT NULL -); ---> statement-breakpoint -CREATE TABLE "pc_case" ( - "component_id" integer PRIMARY KEY NOT NULL, - "cooler_max_height" numeric NOT NULL, - "gpu_max_length" numeric NOT NULL -); ---> statement-breakpoint -CREATE TABLE "power_supply" ( - "component_id" integer PRIMARY KEY NOT NULL, - "type" text NOT NULL, - "wattage" numeric NOT NULL, - "form_factor" text NOT NULL -); ---> statement-breakpoint -CREATE TABLE "sound_card" ( - "component_id" integer PRIMARY KEY NOT NULL, - "sample_rate" numeric NOT NULL, - "bit_depth" numeric NOT NULL, - "chipset" text NOT NULL, - "interface" text NOT NULL, - "channel" text NOT NULL -); ---> statement-breakpoint -CREATE TABLE "storage" ( - "component_id" integer PRIMARY KEY NOT NULL, - "type" text NOT NULL, - "capacity" numeric NOT NULL, - "form_factor" text NOT NULL -); ---> statement-breakpoint -CREATE TABLE "admins" ( - "user_id" integer PRIMARY KEY NOT NULL -); ---> statement-breakpoint -CREATE TABLE "suggestions" ( - "id" serial PRIMARY KEY NOT NULL, - "user_id" integer NOT NULL, - "admin_id" integer, - "link" text NOT NULL, - "admin_comment" text, - "description" text, - "status" text DEFAULT 'pending' NOT NULL, - "component_type" text NOT NULL, - CONSTRAINT "check_status" CHECK ("suggestions"."status" in ('pending', 'approved', 'rejected')), - CONSTRAINT "check_type" CHECK ("suggestions"."component_type" in - ('cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard', 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card', 'cables', 'network_adapter', 'network_card')) -); ---> statement-breakpoint -CREATE TABLE "users" ( - "id" serial PRIMARY KEY NOT NULL, - "username" text NOT NULL, - "password" text NOT NULL, - "email" text NOT NULL, - CONSTRAINT "users_username_unique" UNIQUE("username"), - CONSTRAINT "users_email_unique" UNIQUE("email") -); ---> statement-breakpoint -ALTER TABLE "build_component" ADD CONSTRAINT "build_component_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "build_component" ADD CONSTRAINT "build_component_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "build" ADD CONSTRAINT "build_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "favorite_build" ADD CONSTRAINT "favorite_build_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "favorite_build" ADD CONSTRAINT "favorite_build_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "rating_build" ADD CONSTRAINT "rating_build_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "rating_build" ADD CONSTRAINT "rating_build_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "review" ADD CONSTRAINT "review_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "review" ADD CONSTRAINT "review_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "cpu" ADD CONSTRAINT "cpu_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "gpu" ADD CONSTRAINT "gpu_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "cables" ADD CONSTRAINT "cables_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "case_mobo_form_factors" ADD CONSTRAINT "case_mobo_form_factors_case_id_pc_case_component_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."pc_case"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "case_ps_form_factors" ADD CONSTRAINT "case_ps_form_factors_case_id_pc_case_component_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."pc_case"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "case_storage_form_factors" ADD CONSTRAINT "case_storage_form_factors_case_id_pc_case_component_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."pc_case"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "cooler_cpu_sockets" ADD CONSTRAINT "cooler_cpu_sockets_cooler_id_cooler_component_id_fk" FOREIGN KEY ("cooler_id") REFERENCES "public"."cooler"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "cooler" ADD CONSTRAINT "cooler_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "memory_card" ADD CONSTRAINT "memory_card_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "memory" ADD CONSTRAINT "memory_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "motherboard" ADD CONSTRAINT "motherboard_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "network_adapter" ADD CONSTRAINT "network_adapter_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "network_card" ADD CONSTRAINT "network_card_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "optical_drive" ADD CONSTRAINT "optical_drive_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "pc_case" ADD CONSTRAINT "pc_case_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "power_supply" ADD CONSTRAINT "power_supply_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "sound_card" ADD CONSTRAINT "sound_card_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "storage" ADD CONSTRAINT "storage_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "admins" ADD CONSTRAINT "admins_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "suggestions" ADD CONSTRAINT "suggestions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint -ALTER TABLE "suggestions" ADD CONSTRAINT "suggestions_admin_id_admins_user_id_fk" FOREIGN KEY ("admin_id") REFERENCES "public"."admins"("user_id") ON DELETE set null ON UPDATE cascade; Index: database/migrations/0000_shallow_darkhawk.sql =================================================================== --- database/migrations/0000_shallow_darkhawk.sql (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) +++ database/migrations/0000_shallow_darkhawk.sql (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) @@ -0,0 +1,243 @@ +CREATE TABLE "build_component" ( + "build_id" integer NOT NULL, + "component_id" integer NOT NULL, + "num_components" integer DEFAULT 1 NOT NULL, + CONSTRAINT "build_component_build_id_component_id_pk" PRIMARY KEY("build_id","component_id") +); +--> statement-breakpoint +CREATE TABLE "build" ( + "id" serial PRIMARY KEY NOT NULL, + "user_id" integer NOT NULL, + "name" text NOT NULL, + "created_at" date NOT NULL, + "description" text, + "total_price" numeric NOT NULL, + "is_approved" boolean NOT NULL +); +--> statement-breakpoint +CREATE TABLE "favorite_build" ( + "build_id" integer NOT NULL, + "user_id" integer NOT NULL, + CONSTRAINT "favorite_build_build_id_user_id_pk" PRIMARY KEY("build_id","user_id") +); +--> statement-breakpoint +CREATE TABLE "rating_build" ( + "build_id" integer NOT NULL, + "user_id" integer NOT NULL, + "value" numeric NOT NULL, + CONSTRAINT "rating_build_build_id_user_id_pk" PRIMARY KEY("build_id","user_id"), + CONSTRAINT "check_value" CHECK ("rating_build"."value" BETWEEN 1 AND 5) +); +--> statement-breakpoint +CREATE TABLE "review" ( + "id" serial PRIMARY KEY NOT NULL, + "build_id" integer NOT NULL, + "user_id" integer NOT NULL, + "content" text NOT NULL, + "created_at" date NOT NULL, + CONSTRAINT "review_build_id_user_id_unique" UNIQUE("build_id","user_id") +); +--> statement-breakpoint +CREATE TABLE "cpu" ( + "component_id" integer PRIMARY KEY NOT NULL, + "socket" text NOT NULL, + "cores" integer NOT NULL, + "threads" integer NOT NULL, + "base_clock" numeric NOT NULL, + "boost_clock" numeric, + "tdp" numeric NOT NULL +); +--> statement-breakpoint +CREATE TABLE "gpu" ( + "component_id" integer PRIMARY KEY NOT NULL, + "vram" numeric NOT NULL, + "tdp" numeric NOT NULL, + "base_clock" numeric, + "boost_clock" numeric, + "chipset" text NOT NULL, + "length" numeric NOT NULL +); +--> statement-breakpoint +CREATE TABLE "cables" ( + "component_id" integer PRIMARY KEY NOT NULL, + "length_cm" numeric NOT NULL, + "type" text NOT NULL +); +--> statement-breakpoint +CREATE TABLE "case_mobo_form_factors" ( + "case_id" integer NOT NULL, + "form_factor" text NOT NULL, + CONSTRAINT "case_mobo_form_factors_case_id_form_factor_pk" PRIMARY KEY("case_id","form_factor") +); +--> statement-breakpoint +CREATE TABLE "case_ps_form_factors" ( + "case_id" integer NOT NULL, + "form_factor" text NOT NULL, + CONSTRAINT "case_ps_form_factors_case_id_form_factor_pk" PRIMARY KEY("case_id","form_factor") +); +--> statement-breakpoint +CREATE TABLE "case_storage_form_factors" ( + "case_id" integer NOT NULL, + "form_factor" text NOT NULL, + "num_slots" integer NOT NULL, + CONSTRAINT "case_storage_form_factors_case_id_form_factor_pk" PRIMARY KEY("case_id","form_factor") +); +--> statement-breakpoint +CREATE TABLE "components" ( + "id" serial PRIMARY KEY NOT NULL, + "name" text NOT NULL, + "brand" text NOT NULL, + "price" numeric NOT NULL, + "img_url" text, + "type" text NOT NULL, + CONSTRAINT "check_type" CHECK ("components"."type" in + ('cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard', 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card', 'cables', 'network_adapter', 'network_card')) +); +--> statement-breakpoint +CREATE TABLE "cooler_cpu_sockets" ( + "cooler_id" integer NOT NULL, + "socket" text NOT NULL, + CONSTRAINT "cooler_cpu_sockets_cooler_id_socket_pk" PRIMARY KEY("cooler_id","socket") +); +--> statement-breakpoint +CREATE TABLE "cooler" ( + "component_id" integer PRIMARY KEY NOT NULL, + "type" text NOT NULL, + "height" numeric NOT NULL, + "max_tdp_supported" numeric NOT NULL +); +--> statement-breakpoint +CREATE TABLE "memory_card" ( + "component_id" integer PRIMARY KEY NOT NULL, + "num_slots" integer NOT NULL, + "interface" text NOT NULL +); +--> statement-breakpoint +CREATE TABLE "memory" ( + "component_id" integer PRIMARY KEY NOT NULL, + "type" text NOT NULL, + "speed" numeric NOT NULL, + "capacity" numeric NOT NULL, + "modules" integer NOT NULL +); +--> statement-breakpoint +CREATE TABLE "motherboard" ( + "component_id" integer PRIMARY KEY NOT NULL, + "socket" text NOT NULL, + "chipset" text NOT NULL, + "form_factor" text NOT NULL, + "ram_type" text NOT NULL, + "num_ram_slots" integer NOT NULL, + "max_ram_capacity" numeric NOT NULL, + "pci_express_slots" numeric NOT NULL +); +--> statement-breakpoint +CREATE TABLE "network_adapter" ( + "component_id" integer PRIMARY KEY NOT NULL, + "wifi_version" text NOT NULL, + "interface" text NOT NULL, + "num_antennas" integer NOT NULL +); +--> statement-breakpoint +CREATE TABLE "network_card" ( + "component_id" integer PRIMARY KEY NOT NULL, + "num_ports" integer NOT NULL, + "speed" numeric NOT NULL, + "interface" text NOT NULL +); +--> statement-breakpoint +CREATE TABLE "optical_drive" ( + "component_id" integer PRIMARY KEY NOT NULL, + "form_factor" text NOT NULL, + "type" text NOT NULL, + "interface" text NOT NULL, + "write_speed" numeric NOT NULL, + "read_speed" numeric NOT NULL +); +--> statement-breakpoint +CREATE TABLE "pc_case" ( + "component_id" integer PRIMARY KEY NOT NULL, + "cooler_max_height" numeric NOT NULL, + "gpu_max_length" numeric NOT NULL +); +--> statement-breakpoint +CREATE TABLE "power_supply" ( + "component_id" integer PRIMARY KEY NOT NULL, + "type" text NOT NULL, + "wattage" numeric NOT NULL, + "form_factor" text NOT NULL +); +--> statement-breakpoint +CREATE TABLE "sound_card" ( + "component_id" integer PRIMARY KEY NOT NULL, + "sample_rate" numeric NOT NULL, + "bit_depth" numeric NOT NULL, + "chipset" text NOT NULL, + "interface" text NOT NULL, + "channel" text NOT NULL +); +--> statement-breakpoint +CREATE TABLE "storage" ( + "component_id" integer PRIMARY KEY NOT NULL, + "type" text NOT NULL, + "capacity" numeric NOT NULL, + "form_factor" text NOT NULL +); +--> statement-breakpoint +CREATE TABLE "admins" ( + "user_id" integer PRIMARY KEY NOT NULL +); +--> statement-breakpoint +CREATE TABLE "suggestions" ( + "id" serial PRIMARY KEY NOT NULL, + "user_id" integer NOT NULL, + "admin_id" integer, + "link" text NOT NULL, + "admin_comment" text, + "description" text, + "status" text DEFAULT 'pending' NOT NULL, + "component_type" text NOT NULL, + CONSTRAINT "check_status" CHECK ("suggestions"."status" in ('pending', 'approved', 'rejected')), + CONSTRAINT "check_type" CHECK ("suggestions"."component_type" in + ('cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard', 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card', 'cables', 'network_adapter', 'network_card')) +); +--> statement-breakpoint +CREATE TABLE "users" ( + "id" serial PRIMARY KEY NOT NULL, + "username" text NOT NULL, + "password" text NOT NULL, + "email" text NOT NULL, + CONSTRAINT "users_username_unique" UNIQUE("username"), + CONSTRAINT "users_email_unique" UNIQUE("email") +); +--> statement-breakpoint +ALTER TABLE "build_component" ADD CONSTRAINT "build_component_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "build_component" ADD CONSTRAINT "build_component_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "build" ADD CONSTRAINT "build_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "favorite_build" ADD CONSTRAINT "favorite_build_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "favorite_build" ADD CONSTRAINT "favorite_build_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "rating_build" ADD CONSTRAINT "rating_build_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "rating_build" ADD CONSTRAINT "rating_build_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "review" ADD CONSTRAINT "review_build_id_build_id_fk" FOREIGN KEY ("build_id") REFERENCES "public"."build"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "review" ADD CONSTRAINT "review_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "cpu" ADD CONSTRAINT "cpu_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "gpu" ADD CONSTRAINT "gpu_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "cables" ADD CONSTRAINT "cables_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "case_mobo_form_factors" ADD CONSTRAINT "case_mobo_form_factors_case_id_pc_case_component_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."pc_case"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "case_ps_form_factors" ADD CONSTRAINT "case_ps_form_factors_case_id_pc_case_component_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."pc_case"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "case_storage_form_factors" ADD CONSTRAINT "case_storage_form_factors_case_id_pc_case_component_id_fk" FOREIGN KEY ("case_id") REFERENCES "public"."pc_case"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "cooler_cpu_sockets" ADD CONSTRAINT "cooler_cpu_sockets_cooler_id_cooler_component_id_fk" FOREIGN KEY ("cooler_id") REFERENCES "public"."cooler"("component_id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "cooler" ADD CONSTRAINT "cooler_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "memory_card" ADD CONSTRAINT "memory_card_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "memory" ADD CONSTRAINT "memory_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "motherboard" ADD CONSTRAINT "motherboard_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "network_adapter" ADD CONSTRAINT "network_adapter_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "network_card" ADD CONSTRAINT "network_card_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "optical_drive" ADD CONSTRAINT "optical_drive_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "pc_case" ADD CONSTRAINT "pc_case_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "power_supply" ADD CONSTRAINT "power_supply_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "sound_card" ADD CONSTRAINT "sound_card_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "storage" ADD CONSTRAINT "storage_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "admins" ADD CONSTRAINT "admins_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "suggestions" ADD CONSTRAINT "suggestions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint +ALTER TABLE "suggestions" ADD CONSTRAINT "suggestions_admin_id_admins_user_id_fk" FOREIGN KEY ("admin_id") REFERENCES "public"."admins"("user_id") ON DELETE set null ON UPDATE cascade; Index: database/migrations/meta/0000_snapshot.json =================================================================== --- database/migrations/meta/0000_snapshot.json (revision ad211d1ca8417919b1358701f1f24d8b8b875a44) +++ database/migrations/meta/0000_snapshot.json (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) @@ -1,4 +1,4 @@ { - "id": "779a3882-651b-446d-8967-3728aeea2888", + "id": "d1ab4ce8-e387-4d74-8d5b-511fdbe0a032", "prevId": "00000000-0000-0000-0000-000000000000", "version": "7", @@ -9,10 +9,4 @@ "schema": "", "columns": { - "id": { - "name": "id", - "type": "serial", - "primaryKey": true, - "notNull": true - }, "build_id": { "name": "build_id", @@ -26,4 +20,11 @@ "primaryKey": false, "notNull": true + }, + "num_components": { + "name": "num_components", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 1 } }, @@ -57,5 +58,13 @@ } }, - "compositePrimaryKeys": {}, + "compositePrimaryKeys": { + "build_component_build_id_component_id_pk": { + "name": "build_component_build_id_component_id_pk", + "columns": [ + "build_id", + "component_id" + ] + } + }, "uniqueConstraints": {}, "policies": {}, Index: database/migrations/meta/_journal.json =================================================================== --- database/migrations/meta/_journal.json (revision ad211d1ca8417919b1358701f1f24d8b8b875a44) +++ database/migrations/meta/_journal.json (revision a744c9059a60bfc8e0fdf8994f5a9496d9122748) @@ -6,6 +6,6 @@ "idx": 0, "version": "7", - "when": 1769188258136, - "tag": "0000_regular_kulan_gath", + "when": 1769598141213, + "tag": "0000_shallow_darkhawk", "breakpoints": true }