Changes between Initial Version and Version 1 of Transactions

Timestamp:

01/29/26 02:00:09 (11 days ago)

Author:

233051

Comment:

--

Transactions

@@ +1 @@
+== Transactions ==
+
+=== Transaction for getting build details ===
+
+{{{
+db.transaction(async (tx) => {
+        const [buildDetails] = await tx
+            .select({
+                id: buildsTable.id,
+                userId: buildsTable.userId,
+                name: buildsTable.name,
+                createdAt: buildsTable.createdAt,
+                description: buildsTable.description,
+                totalPrice: buildsTable.totalPrice,
+                isApproved: buildsTable.isApproved,
+                creator: usersTable.username
+            })
+            .from(buildsTable)
+            .innerJoin(
+                usersTable,
+                eq(buildsTable.userId, usersTable.id)
+            )
+            .where(
+                eq(buildsTable.id, buildId)
+            )
+            .limit(1);
+
+        if (!buildDetails) return null;
+
+        const components = await tx
+            .select({
+                componentId: buildComponentsTable.componentId,
+                component: componentsTable,
+                quantity: buildComponentsTable.numComponents
+            })
+            .from(buildComponentsTable)
+            .innerJoin(
+                componentsTable,
+                eq(buildComponentsTable.componentId, componentsTable.id)
+            )
+            .where(
+                eq(buildComponentsTable.buildId, buildId)
+            );
+
+        const reviews = await tx
+            .select({
+                username: usersTable.username,
+                content: reviewsTable.content,
+                createdAt: reviewsTable.createdAt
+            })
+            .from(reviewsTable)
+            .innerJoin(
+                usersTable,
+                eq(reviewsTable.userId, usersTable.id)
+            )
+            .where(
+                eq(reviewsTable.buildId, buildId)
+            )
+            .orderBy(
+                desc(reviewsTable.createdAt)
+            );
+
+        let [ratingStatistics] = await tx
+            .select({
+                averageRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float), 0)`.as("averageRating"),
+                ratingCount: sql<number>`COUNT(${ratingBuildsTable.value})`.as("ratingCount")
+            })
+            .from(ratingBuildsTable)
+            .where(
+                eq(ratingBuildsTable.buildId, buildId)
+            )
+            .groupBy(ratingBuildsTable.buildId);
+
+        ratingStatistics = {
+            averageRating: Number(ratingStatistics?.averageRating ?? 0),
+            ratingCount: Number(ratingStatistics?.ratingCount ?? 0),
+        }
+
+        let userRating = null;
+        let isFavorite = false;
+        let userReview = null;
+
+        if(userId) {
+            const [rating] = await tx
+                .select()
+                .from(ratingBuildsTable)
+                .where(
+                    and(
+                        eq(ratingBuildsTable.buildId, buildId),
+                        eq(ratingBuildsTable.userId, userId)
+                    )
+                )
+                .limit(1);
+
+            userRating = rating?.value ? Number(rating.value) : null;
+
+            const [favorite] = await tx
+                .select()
+                .from(favoriteBuildsTable)
+                .where(
+                    and(
+                        eq(favoriteBuildsTable.buildId, buildId),
+                        eq(favoriteBuildsTable.userId, userId)
+                    )
+                )
+                .limit(1);
+
+            isFavorite = !!favorite;
+
+            const [review] = await tx
+                .select()
+                .from(reviewsTable)
+                .where(
+                    and(
+                        eq(reviewsTable.buildId, buildId),
+                        eq(reviewsTable.userId, userId)
+                    )
+                )
+                .limit(1);
+
+            userReview = review?.content;
+        }
+
+        return {
+            ...buildDetails,
+            components: components.map(c => ({
+                    ...c.component,
+                    quantity: c.quantity
+            })),
+            reviews: reviews.map(r => ({
+                username: r.username,
+                content: r.content,
+                createdAt: r.createdAt
+            })),
+            ratingStatistics: ratingStatistics,
+            userRating,
+            userReview,
+            isFavorite
+        };
+    });
+}}}
+
+=== Transaction for cloning existing build ===
+
+{{{
+db.transaction(async (tx) => {
+        const [buildToClone] = await tx
+            .select()
+            .from(buildsTable)
+            .where(
+                eq(buildsTable.id, buildId)
+            )
+            .limit(1);
+
+        if (!buildToClone) return null;
+
+        const [newBuild] = await tx
+            .insert(buildsTable)
+            .values({
+                userId: userId,
+                name: `${buildToClone.name} (copy)`,
+                createdAt: new Date().toISOString().split('T')[0],
+                description: buildToClone.description,
+                totalPrice: buildToClone.totalPrice,
+                isApproved: false
+            })
+            .returning({
+                id: buildsTable.id
+            });
+
+        if(!newBuild) return null;
+
+        const existing = await tx
+            .select({
+                componentId: buildComponentsTable.componentId,
+                numComponents: buildComponentsTable.numComponents
+            })
+            .from(buildComponentsTable)
+            .where(eq(buildComponentsTable.buildId, buildId));
+
+        if (existing.length > 0) {
+            await tx.insert(buildComponentsTable).values(
+                existing.map((r) => ({
+                    buildId: newBuild.id,
+                    componentId: r.componentId,
+                    numComponents: r.numComponents
+                })),
+            );
+        }
+
+        return newBuild.id;
+    });
+}}}
+
+=== Transaction for getting current build state ===
+
+{{{
+db.transaction(async (tx) => {
+        const [build] = await tx
+            .select({
+                id: buildsTable.id,
+                userId: buildsTable.userId,
+                isApproved: buildsTable.isApproved,
+                name: buildsTable.name,
+                description: buildsTable.description,
+                totalPrice: buildsTable.totalPrice,
+            })
+            .from(buildsTable)
+            .where(
+                and(
+                    eq(buildsTable.id, buildId),
+                    eq(buildsTable.userId, userId)
+                )
+            )
+            .limit(1);
+
+        if (!build || build.isApproved) return null;
+
+        const components = await tx
+            .select({
+                componentId: buildComponentsTable.componentId,
+                quantity: buildComponentsTable.numComponents
+            })
+            .from(buildComponentsTable)
+            .where(
+                eq(buildComponentsTable.buildId, buildId)
+            );
+
+        return {
+            build,
+            components: components.map(c => ({
+                id: c.componentId,
+                quantity: c.quantity
+            }))
+        };
+    });
+}}}
+
+=== Transaction for adding a new component to the database ===
+
+{{{
+db.transaction(async (tx) => {
+        const [newComponent] = await tx
+            .insert(componentsTable)
+            .values({
+                name: name,
+                brand: brand,
+                price: price.toFixed(2),
+                imgUrl: imgUrl,
+                type: type
+            })
+            .returning({
+                id: componentsTable.id
+            });
+
+        const componentId = newComponent.id;
+
+        const config = typeConfigMap[type as ComponentType];
+
+        await tx
+            .insert(config.table)
+            .values({
+                componentId: componentId,
+                ...specificData
+            });
+
+        if (type === 'case') {
+            if (specificData.storageFormFactors) {
+                await tx.insert(caseStorageFormFactorsTable).values(
+                    specificData.storageFormFactors.map((sf: any) => ({
+                        caseId: componentId,
+                        formFactor: sf.formFactor,
+                        numSlots: sf.numSlots,
+                    }))
+                );
+            }
+            if (specificData.psFormFactors) {
+                await tx.insert(casePsFormFactorsTable).values(
+                    specificData.psFormFactors.map((pf: any) => ({
+                        caseId: componentId,
+                        formFactor: pf.formFactor,
+                    }))
+                );
+            }
+            if (specificData.moboFormFactors) {
+                await tx.insert(caseMoboFormFactorsTable).values(
+                    specificData.moboFormFactors.map((mf: any) => ({
+                        caseId: componentId,
+                        formFactor: mf.formFactor,
+                    }))
+                );
+            }
+        }
+
+        if (type === 'cooler' && specificData.cpuSockets) {
+            await tx.insert(coolerCPUSocketsTable).values(
+                specificData.cpuSockets.map((socket: any) => ({ coolerId: componentId, socket }))
+            );
+        }
+
+        return componentId;
+    });
+}}}
+
+=== Transaction for adding a component to a build ===
+
+{{{
+db.transaction(async (tx) => {
+        const [build] = await tx
+            .select()
+            .from(buildsTable)
+            .where(
+                and(
+                    eq(buildsTable.id, buildId),
+                    eq(buildsTable.userId, userId)
+                )
+            )
+            .limit(1);
+
+        if(!build || build.isApproved) return null;
+
+        await tx
+            .insert(buildComponentsTable)
+            .values({
+                buildId,
+                componentId,
+                numComponents: 1
+            })
+            .onConflictDoUpdate({
+                target: [buildComponentsTable.buildId, buildComponentsTable.componentId],
+                set: {
+                    numComponents: sql`${buildComponentsTable.numComponents} + 1`
+                }
+            });
+
+        const buildComponents = await tx
+            .select({
+                price:  componentsTable.price,
+                quantity: buildComponentsTable.numComponents
+            })
+            .from(buildComponentsTable)
+            .innerJoin(
+                componentsTable,
+                eq(buildComponentsTable.componentId, componentsTable.id)
+            )
+            .where(
+                eq(buildComponentsTable.buildId, buildId)
+            );
+
+        // const totalPrice = buildComponents.reduce((sum, c) =>
+        //     sum + (Number(c.price) * c.quantity), 0
+        // );
+
+        // await tx
+        //     .update(buildsTable)
+        //     .set({
+        //         totalPrice: totalPrice.toFixed(2)
+        //     })
+        //     .where(
+        //         eq(buildsTable.id, buildId)
+        //     );
+
+        return buildId;
+    })
+}}}
+
+=== Transaction for removing a component from a build ===
+
+{{{
+db.transaction(async (tx) => {
+        const [build] = await tx
+            .select()
+            .from(buildsTable)
+            .where(
+                and(
+                    eq(buildsTable.id, buildId),
+                    eq(buildsTable.userId, userId)
+                )
+            )
+            .limit(1);
+
+        if(!build || build.isApproved) return null;
+
+        const [existing] = await tx
+            .select({
+                quantity: buildComponentsTable.numComponents
+            })
+            .from(buildComponentsTable)
+            .where(
+                and(
+                    eq(buildComponentsTable.buildId, buildId),
+                    eq(buildComponentsTable.componentId, componentId)
+                )
+            )
+            .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)
+            .innerJoin(
+                componentsTable,
+                eq(buildComponentsTable.componentId, componentsTable.id)
+            )
+            .where(
+                eq(buildComponentsTable.buildId, buildId)
+            );
+
+        // const totalPrice = buildComponents.reduce((sum, c) =>
+        //     sum + (Number(c.price) * c.quantity), 0
+        // );
+
+        // await tx
+        //     .update(buildsTable)
+        //     .set({
+        //         totalPrice: totalPrice.toFixed(2)
+        //     })
+        //     .where(
+        //         eq(buildsTable.id, buildId)
+        //     );
+
+        return componentId;
+    })
+}}}