source: database/drizzle/queries/components.ts@ 83fb5e2

main
Last change on this file since 83fb5e2 was 83fb5e2, checked in by Tome <gjorgievtome@…>, 7 months ago

fix returns and add checks
  • Property mode set to 100644
File size: 15.5 KB
RevLine 
[1ebac59]1import type { Database } from "../db";
[ea09e98]2import {
[83fb5e2]3 buildComponentsTable,
[ea09e98]4 buildsTable, cablesTable, caseMoboFormFactorsTable, casePsFormFactorsTable, caseStorageFormFactorsTable,
5 componentsTable, coolerCPUSocketsTable, coolersTable,
6 CPUTable,
7 GPUTable, memoryCardsTable,
8 memoryTable, motherboardsTable,
9 networkAdaptersTable,
[83fb5e2]10 networkCardsTable, opticalDrivesTable, pcCasesTable, powerSupplyTable,
11 ratingBuildsTable, soundCardsTable, storageTable
[ea09e98]12} from "../schema";
[c4dd17d]13import {and, desc, eq, ilike} from "drizzle-orm";
[83fb5e2]14export async function getAllComponents(db: Database, limit?: number, componentType?: string, q?: string) {
[c4dd17d]15 let queryConditions = [];
[1ebac59]16
[c4dd17d]17 if (q) {
18 queryConditions.push(
19 ilike(componentsTable.name, `%${q}%`)
20 );
21 }
22
23 if(componentType && componentType.trim() !== 'all') {
24 queryConditions.push(
25 eq(componentsTable.type, componentType.trim().toLowerCase())
26 );
27 }
[1ebac59]28
[c4dd17d]29 const componentsList = await db
30 .select({
31 id: componentsTable.id,
32 name: componentsTable.name,
33 brand: componentsTable.brand,
34 price: componentsTable.price,
35 })
36 .from(componentsTable)
37 .where(
38 queryConditions.length > 0 ?
39 and (
40 ...queryConditions
41 )
42 : undefined
43 )
44 .orderBy(
45 desc(componentsTable.price)
46 )
47 .limit(limit || 100); // 100 placeholder
[1ebac59]48
[c4dd17d]49 return componentsList;
[1ebac59]50}
51
[c4dd17d]52export async function getComponentDetails(db: Database, componentId: number) {
[ea09e98]53 const [component] = await db
[c4dd17d]54 .select()
55 .from(componentsTable)
56 .where(
57 eq(componentsTable.id, componentId)
58 )
59 .limit(1);
[1ebac59]60
[ea09e98]61 if(!component) return null;
62
63 let details: any = {};
64
65 switch (component.type) {
66 case 'cpu':
67 [details] = await db.select().from(CPUTable).where(eq(CPUTable.componentId, componentId));
68 break;
69 case 'gpu':
70 [details] = await db.select().from(GPUTable).where(eq(GPUTable.componentId, componentId));
71 break;
72 case 'memory':
73 [details] = await db.select().from(memoryTable).where(eq(memoryTable.componentId, componentId));
74 break;
75 case 'power_supply':
76 [details] = await db.select().from(powerSupplyTable).where(eq(powerSupplyTable.componentId, componentId));
77 break;
78 case 'case':
79 [details] = await db.select().from(pcCasesTable).where(eq(pcCasesTable.componentId, componentId));
80 if (details) {
81 details.storageFormFactors = await db.select().from(caseStorageFormFactorsTable).where(eq(caseStorageFormFactorsTable.caseId, componentId));
82 details.psFormFactors = await db.select().from(casePsFormFactorsTable).where(eq(casePsFormFactorsTable.caseId, componentId));
83 details.moboFormFactors = await db.select().from(caseMoboFormFactorsTable).where(eq(caseMoboFormFactorsTable.caseId, componentId));
84 }
85 break;
86 case 'cooler':
87 [details] = await db.select().from(coolersTable).where(eq(coolersTable.componentId, componentId));
88 if (details) {
89 details.cpuSockets = await db.select().from(coolerCPUSocketsTable).where(eq(coolerCPUSocketsTable.coolerId, componentId));
90 }
91 break;
92 case 'motherboard':
93 [details] = await db.select().from(motherboardsTable).where(eq(motherboardsTable.componentId, componentId));
94 break;
95 case 'storage':
96 [details] = await db.select().from(storageTable).where(eq(storageTable.componentId, componentId));
97 break;
98 case 'memory_card':
99 [details] = await db.select().from(memoryCardsTable).where(eq(memoryCardsTable.componentId, componentId));
100 break;
101 case 'optical_drive':
102 [details] = await db.select().from(opticalDrivesTable).where(eq(opticalDrivesTable.componentId, componentId));
103 break;
104 case 'sound_card':
105 [details] = await db.select().from(soundCardsTable).where(eq(soundCardsTable.componentId, componentId));
106 break;
107 case 'cables':
108 [details] = await db.select().from(cablesTable).where(eq(cablesTable.componentId, componentId));
109 break;
110 case 'network_adapter':
111 [details] = await db.select().from(networkAdaptersTable).where(eq(networkAdaptersTable.componentId, componentId));
112 break;
113 case 'network_card':
114 [details] = await db.select().from(networkCardsTable).where(eq(networkCardsTable.componentId, componentId));
115 break;
116 }
117
118 return {
119 ...component,
120 details: details || null
121 };
122}
123
124export async function addNewComponent(db: Database, name: string, brand: string, price: number, imgUrl: string, type: string, specificData: any) {
125 return db.transaction(async (tx) => {
126 const [newComponent] = await tx
127 .insert(componentsTable)
128 .values({
129 name: name,
130 brand: brand,
131 price: price.toFixed(2),
132 imgUrl: imgUrl,
133 type: type
134 })
135 .returning({
136 id: componentsTable.id
137 });
138
139 const componentId = newComponent.id;
140
141 switch (type) {
142 case 'cpu':
143 await tx.insert(CPUTable).values({
144 componentId,
145 socket: specificData.socket,
146 cores: specificData.cores,
147 threads: specificData.threads,
148 baseClock: specificData.baseClock,
149 boostClock: specificData.boostClock,
150 tdp: specificData.tdp,
151 });
152 break;
153
154 case 'gpu':
155 await tx.insert(GPUTable).values({
156 componentId,
157 vram: specificData.vram,
158 tdp: specificData.tdp,
159 baseClock: specificData.baseClock,
160 boostClock: specificData.boostClock,
161 chipset: specificData.chipset,
162 length: specificData.length,
163 });
164 break;
165
166 case 'memory':
167 await tx.insert(memoryTable).values({
168 componentId,
169 type: specificData.type,
170 speed: specificData.speed,
171 capacity: specificData.capacity,
172 modules: specificData.modules,
173 });
174 break;
175
176 case 'power_supply':
177 await tx.insert(powerSupplyTable).values({
178 componentId,
179 type: specificData.type,
180 wattage: specificData.wattage,
181 formFactor: specificData.formFactor,
182 });
183 break;
184
185 case 'case':
186 await tx.insert(pcCasesTable).values({
187 componentId,
188 coolerMaxHeight: specificData.coolerMaxHeight,
189 gpuMaxLength: specificData.gpuMaxLength,
190 });
191
192 if (specificData.storageFormFactors) {
193 await tx.insert(caseStorageFormFactorsTable).values(
194 specificData.storageFormFactors.map((sf: any) => ({
195 caseId: componentId,
196 formFactor: sf.formFactor,
197 numSlots: sf.numSlots,
198 }))
199 );
200 }
201
202 if (specificData.psFormFactors) {
203 await tx.insert(casePsFormFactorsTable).values(
204 specificData.psFormFactors.map((pf: any) => ({
205 caseId: componentId,
206 formFactor: pf.formFactor,
207 }))
208 );
209 }
210
211 if (specificData.moboFormFactors) {
212 await tx.insert(caseMoboFormFactorsTable).values(
213 specificData.moboFormFactors.map((mf: any) => ({
214 caseId: componentId,
215 formFactor: mf.formFactor,
216 }))
217 );
218 }
219 break;
220
221 case 'cooler':
222 await tx.insert(coolersTable).values({
223 componentId,
224 type: specificData.type,
225 height: specificData.height,
226 maxTdpSupported: specificData.maxTdpSupported,
227 });
228
229 if (specificData.cpuSockets) {
230 await tx.insert(coolerCPUSocketsTable).values(
231 specificData.cpuSockets.map((socket: string) => ({
232 coolerId: componentId,
233 socket: socket,
234 }))
235 );
236 }
237 break;
238
239 case 'motherboard':
240 await tx.insert(motherboardsTable).values({
241 componentId,
242 socket: specificData.socket,
243 chipset: specificData.chipset,
244 formFactor: specificData.formFactor,
245 ramType: specificData.ramType,
246 numRamSlots: specificData.numRamSlots,
247 maxRamCapacity: specificData.maxRamCapacity,
[83fb5e2]248 pciExpressSlots: specificData.pciExpressSlots,
[ea09e98]249 });
250 break;
251
252 case 'storage':
253 await tx.insert(storageTable).values({
254 componentId,
255 type: specificData.type,
256 capacity: specificData.capacity,
257 formFactor: specificData.formFactor,
258 });
259 break;
260
261 case 'memory_card':
262 await tx.insert(memoryCardsTable).values({
263 componentId,
264 numSlots: specificData.numSlots,
265 interface: specificData.interface,
266 });
267 break;
268
269 case 'optical_drive':
270 await tx.insert(opticalDrivesTable).values({
271 componentId,
272 formFactor: specificData.formFactor,
273 type: specificData.type,
274 interface: specificData.interface,
275 writeSpeed: specificData.writeSpeed,
276 readSpeed: specificData.readSpeed,
277 });
278 break;
279
280 case 'sound_card':
281 await tx.insert(soundCardsTable).values({
282 componentId,
283 sampleRate: specificData.sampleRate,
284 bitDepth: specificData.bitDepth,
285 chipset: specificData.chipset,
286 interface: specificData.interface,
287 channel: specificData.channel,
288 });
289 break;
290
291 case 'cables':
292 await tx.insert(cablesTable).values({
293 componentId,
294 lengthCm: specificData.lengthCm,
295 type: specificData.type,
296 });
297 break;
298
299 case 'network_adapter':
300 await tx.insert(networkAdaptersTable).values({
301 componentId,
302 wifiVersion: specificData.wifiVersion,
303 interface: specificData.interface,
304 numAntennas: specificData.numAntennas,
305 });
306 break;
307
308 case 'network_card':
309 await tx.insert(networkCardsTable).values({
310 componentId,
311 numPorts: specificData.numPorts,
312 speed: specificData.speed,
313 interface: specificData.interface,
314 });
315 break;
316
317 default:
318 return null;
319 }
320
321 return componentId;
322 });
[83fb5e2]323}
324
325export async function getCompatibleComponents(db: Database, buildId: number, componentType: string) {
326 return db.transaction(async (tx) => {
327 const [build] = await tx
328 .select({
329 buildId: buildsTable.id,
330 userId: buildsTable.userId,
331 })
332 .from(buildsTable)
333 .where(
334 eq(buildsTable.id, buildId)
335 )
336 .limit(1);
337
338 if(!build) return null;
339
340
341 return null;
342 });
343}
344
345export async function addComponentToBuild(db: Database, buildId: number, componentId: number) {
346 const [build] = await db
347 .select()
348 .from(buildsTable)
349 .where(
350 eq(buildsTable.id, buildId)
351 )
352 .limit(1);
353
354 if(!build) return null;
355
356 const [component] = await db
357 .select()
358 .from(componentsTable)
359 .where(
360 eq(componentsTable.id, componentId)
361 )
362 .limit(1);
363
364 if(!component) return null;
365
366 const existing = await db
367 .select()
368 .from(buildComponentsTable)
369 .where(
370 and(
371 eq(buildComponentsTable.buildId, buildId),
372 eq(buildComponentsTable.componentId, componentId)
373 )
374 )
375 .limit(1);
376
377 if(existing.length > 0) return null;
378
379 const [result] = await db
380 .insert(buildComponentsTable)
381 .values({
382 buildId,
383 componentId
384 })
385 .returning({
386 id: buildComponentsTable.buildId
387 });
388
389 const buildComponents = await db
390 .select({
391 price: componentsTable.price,
392 })
393 .from(buildComponentsTable)
394 .innerJoin(
395 componentsTable,
396 eq(buildComponentsTable.componentId, componentsTable.id)
397 )
398 .where(
399 eq(buildComponentsTable.buildId, buildId)
400 );
401
402 const totalPrice = buildComponents.reduce((sum, c) => sum + Number(c.price), 0);
403
404 await db
405 .update(buildsTable)
406 .set({
407 totalPrice: totalPrice.toFixed(2)
408 })
409 .where(
410 eq(buildsTable.id, buildId)
411 );
412
413 return result?.id ?? null;
414}
415
416export async function removeComponentFromBuild(db: Database, buildId: number, componentId: number) {
417 const [build] = await db
418 .select()
419 .from(buildsTable)
420 .where(
421 eq(buildsTable.id, buildId)
422 )
423 .limit(1);
424
425 if(!build) return null;
426
427 const [component] = await db
428 .select()
429 .from(componentsTable)
430 .where(
431 eq(componentsTable.id, componentId)
432 )
433 .limit(1);
434
435 if(!component) return null;
436
437 const result = await db
438 .delete(buildComponentsTable)
439 .where(
440 and(
441 eq(buildComponentsTable.buildId, buildId),
442 eq(buildComponentsTable.componentId, componentId)
443 )
444 );
445
446 if(result.rowCount === 0) return null;
447
448 const buildComponents = await db
449 .select({
450 price: componentsTable.price,
451 })
452 .from(buildComponentsTable)
453 .innerJoin(
454 componentsTable,
455 eq(buildComponentsTable.componentId, componentsTable.id)
456 )
457 .where(
458 eq(buildComponentsTable.buildId, buildId)
459 );
460
461 const totalPrice = buildComponents.reduce((sum, c) => sum + Number(c.price), 0);
462
463 await db
464 .update(buildsTable)
465 .set({
466 totalPrice: totalPrice.toFixed(2)
467 })
468 .where(
469 eq(buildsTable.id, buildId)
470 );
471
472 return result;
473}
474
Note: See TracBrowser for help on using the repository browser.