diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/db.ts | 4 | ||||
| -rw-r--r-- | db/schema/index.ts | 5 | ||||
| -rw-r--r-- | db/schema/test-table-v2.ts | 139 | ||||
| -rw-r--r-- | db/seeds/test-table-v2.ts | 187 |
4 files changed, 332 insertions, 3 deletions
@@ -3,8 +3,8 @@ import { Pool } from 'pg'; import * as schema from './schema'; const pool = new Pool({ - connectionString: process.env.DATABASE_URL as string, - // connectionString: "postgresql://dts:dujinDTS2@localhost:5432/evcp", + // connectionString: process.env.DATABASE_URL as string, + connectionString: "postgresql://dts:dujinDTS2@localhost:5432/evcp", max: Number(process.env.DB_POOL_MAX) || 4, }); diff --git a/db/schema/index.ts b/db/schema/index.ts index 022431cc..da17b069 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -95,4 +95,7 @@ export * from './avl/avl'; export * from './avl/vendor-pool'; // === Email Logs 스키마 === export * from './emailLogs'; -export * from './emailWhitelist';
\ No newline at end of file +export * from './emailWhitelist'; + +// === Test Table V2 (테스트용 스키마) === +export * from './test-table-v2';
\ No newline at end of file diff --git a/db/schema/test-table-v2.ts b/db/schema/test-table-v2.ts new file mode 100644 index 00000000..37ccccbd --- /dev/null +++ b/db/schema/test-table-v2.ts @@ -0,0 +1,139 @@ +/** + * Test Table Schema for client-table-v2 테스트용 스키마 + * + * 3가지 패턴 테스트를 위한 테이블: + * 1. testProducts - 기본 상품 테이블 (Client-Side, Factory Service 패턴용) + * 2. testOrders - 주문 테이블 (Custom Service 패턴용 - 조인 테스트) + * 3. testCustomers - 고객 테이블 (Custom Service 패턴용 - 조인 테스트) + */ + +import { + integer, + serial, + pgTable, + varchar, + timestamp, + pgEnum, + text, + numeric, + boolean, + index, +} from "drizzle-orm/pg-core"; + +// === Enums === + +export const testProductStatusEnum = pgEnum("test_product_status", [ + "active", + "inactive", + "discontinued", +]); + +export const testOrderStatusEnum = pgEnum("test_order_status", [ + "pending", + "processing", + "shipped", + "delivered", + "cancelled", +]); + +// === Tables === + +/** + * 상품 테이블 - Client-Side 및 Factory Service 패턴 테스트용 + */ +export const testProducts = pgTable( + "test_products", + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + name: varchar("name", { length: 255 }).notNull(), + sku: varchar("sku", { length: 50 }).notNull().unique(), + description: text("description"), + category: varchar("category", { length: 100 }).notNull(), + price: numeric("price", { precision: 10, scale: 2 }).notNull(), + stock: integer("stock").notNull().default(0), + status: testProductStatusEnum("status").notNull().default("active"), + isNew: boolean("is_new").default(false), + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .defaultNow() + .notNull(), + }, + (table) => { + return { + categoryIdx: index("test_products_category_idx").on(table.category), + statusIdx: index("test_products_status_idx").on(table.status), + }; + } +); + +/** + * 고객 테이블 - Custom Service 패턴의 조인 테스트용 + */ +export const testCustomers = pgTable( + "test_customers", + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + name: varchar("name", { length: 255 }).notNull(), + email: varchar("email", { length: 255 }).notNull().unique(), + phone: varchar("phone", { length: 40 }), + country: varchar("country", { length: 100 }), + tier: varchar("tier", { length: 40 }).notNull().default("standard"), // standard, premium, vip + totalOrders: integer("total_orders").default(0), + createdAt: timestamp("created_at", { withTimezone: true }) + .defaultNow() + .notNull(), + }, + (table) => { + return { + tierIdx: index("test_customers_tier_idx").on(table.tier), + }; + } +); + +/** + * 주문 테이블 - Custom Service 패턴용 (고객/상품 조인) + */ +export const testOrders = pgTable( + "test_orders", + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + orderNumber: varchar("order_number", { length: 50 }).notNull().unique(), + customerId: integer("customer_id") + .references(() => testCustomers.id, { onDelete: "cascade" }) + .notNull(), + productId: integer("product_id") + .references(() => testProducts.id, { onDelete: "set null" }), + quantity: integer("quantity").notNull().default(1), + unitPrice: numeric("unit_price", { precision: 10, scale: 2 }).notNull(), + totalAmount: numeric("total_amount", { precision: 10, scale: 2 }).notNull(), + status: testOrderStatusEnum("status").notNull().default("pending"), + notes: text("notes"), + orderedAt: timestamp("ordered_at", { withTimezone: true }) + .defaultNow() + .notNull(), + shippedAt: timestamp("shipped_at", { withTimezone: true }), + deliveredAt: timestamp("delivered_at", { withTimezone: true }), + }, + (table) => { + return { + customerIdx: index("test_orders_customer_idx").on(table.customerId), + productIdx: index("test_orders_product_idx").on(table.productId), + statusIdx: index("test_orders_status_idx").on(table.status), + orderedAtIdx: index("test_orders_ordered_at_idx").on(table.orderedAt), + }; + } +); + +// === Types === + +export type TestProduct = typeof testProducts.$inferSelect; +export type NewTestProduct = typeof testProducts.$inferInsert; + +export type TestCustomer = typeof testCustomers.$inferSelect; +export type NewTestCustomer = typeof testCustomers.$inferInsert; + +export type TestOrder = typeof testOrders.$inferSelect; +export type NewTestOrder = typeof testOrders.$inferInsert; + diff --git a/db/seeds/test-table-v2.ts b/db/seeds/test-table-v2.ts new file mode 100644 index 00000000..07bf8914 --- /dev/null +++ b/db/seeds/test-table-v2.ts @@ -0,0 +1,187 @@ +/** + * Test Table V2 Seeding Script + * + * 사용법: + * npx tsx db/seeds/test-table-v2.ts + */ + +import db from "@/db/db"; +import { faker } from "@faker-js/faker"; +import { + testProducts, + testCustomers, + testOrders, + NewTestProduct, + NewTestCustomer, + NewTestOrder, +} from "../schema/test-table-v2"; + +// === Generators === + +const CATEGORIES = [ + "Electronics", + "Clothing", + "Home & Garden", + "Sports", + "Books", + "Toys", + "Food", + "Health", +]; + +const PRODUCT_STATUSES = ["active", "inactive", "discontinued"] as const; +const ORDER_STATUSES = ["pending", "processing", "shipped", "delivered", "cancelled"] as const; +const CUSTOMER_TIERS = ["standard", "premium", "vip"] as const; +const COUNTRIES = ["Korea", "USA", "Japan", "Germany", "UK", "France", "China", "Singapore"]; + +function generateProduct(): NewTestProduct { + const category = faker.helpers.arrayElement(CATEGORIES); + const price = faker.number.float({ min: 10, max: 1000, fractionDigits: 2 }); + + return { + name: faker.commerce.productName(), + sku: faker.string.alphanumeric({ length: 8, casing: "upper" }), + description: faker.commerce.productDescription(), + category, + price: price.toString(), + stock: faker.number.int({ min: 0, max: 500 }), + status: faker.helpers.arrayElement(PRODUCT_STATUSES), + isNew: faker.datatype.boolean({ probability: 0.2 }), + createdAt: faker.date.past({ years: 2 }), + updatedAt: faker.date.recent({ days: 30 }), + }; +} + +function generateCustomer(): NewTestCustomer { + return { + name: faker.person.fullName(), + email: faker.internet.email(), + phone: faker.phone.number(), + country: faker.helpers.arrayElement(COUNTRIES), + tier: faker.helpers.arrayElement(CUSTOMER_TIERS), + totalOrders: faker.number.int({ min: 0, max: 100 }), + createdAt: faker.date.past({ years: 3 }), + }; +} + +function generateOrder( + customerId: number, + productId: number | null, + productPrice: number +): NewTestOrder { + const quantity = faker.number.int({ min: 1, max: 10 }); + const unitPrice = productPrice || faker.number.float({ min: 10, max: 500, fractionDigits: 2 }); + const totalAmount = quantity * unitPrice; + const status = faker.helpers.arrayElement(ORDER_STATUSES); + const orderedAt = faker.date.past({ years: 1 }); + + let shippedAt: Date | undefined; + let deliveredAt: Date | undefined; + + if (status === "shipped" || status === "delivered") { + shippedAt = faker.date.between({ from: orderedAt, to: new Date() }); + } + if (status === "delivered") { + deliveredAt = faker.date.between({ from: shippedAt || orderedAt, to: new Date() }); + } + + return { + orderNumber: `ORD-${faker.string.alphanumeric({ length: 8, casing: "upper" })}`, + customerId, + productId, + quantity, + unitPrice: unitPrice.toString(), + totalAmount: totalAmount.toString(), + status, + notes: faker.datatype.boolean({ probability: 0.3 }) ? faker.lorem.sentence() : null, + orderedAt, + shippedAt, + deliveredAt, + }; +} + +// === Main Seeding Function === + +export async function seedTestTableV2(options: { + productCount?: number; + customerCount?: number; + orderCount?: number; +} = {}) { + const { + productCount = 100, + customerCount = 50, + orderCount = 200, + } = options; + + console.log("🗑️ Clearing existing test data..."); + + // Delete in order (orders first due to FK) + await db.delete(testOrders); + await db.delete(testCustomers); + await db.delete(testProducts); + + console.log(`📦 Generating ${productCount} products...`); + const products: NewTestProduct[] = []; + for (let i = 0; i < productCount; i++) { + products.push(generateProduct()); + } + const insertedProducts = await db.insert(testProducts).values(products).returning(); + console.log(`✅ Inserted ${insertedProducts.length} products`); + + console.log(`👥 Generating ${customerCount} customers...`); + const customers: NewTestCustomer[] = []; + for (let i = 0; i < customerCount; i++) { + customers.push(generateCustomer()); + } + const insertedCustomers = await db.insert(testCustomers).values(customers).returning(); + console.log(`✅ Inserted ${insertedCustomers.length} customers`); + + console.log(`🛒 Generating ${orderCount} orders...`); + const orders: NewTestOrder[] = []; + for (let i = 0; i < orderCount; i++) { + const customer = faker.helpers.arrayElement(insertedCustomers); + const product = faker.helpers.arrayElement(insertedProducts); + orders.push(generateOrder(customer.id, product.id, parseFloat(product.price))); + } + const insertedOrders = await db.insert(testOrders).values(orders).returning(); + console.log(`✅ Inserted ${insertedOrders.length} orders`); + + console.log("🎉 Test table v2 seeding completed!"); + + return { + products: insertedProducts.length, + customers: insertedCustomers.length, + orders: insertedOrders.length, + }; +} + +// === CLI Runner === + +async function main() { + console.log("⏳ Starting test-table-v2 seed..."); + const start = Date.now(); + + try { + const result = await seedTestTableV2({ + productCount: 100, + customerCount: 50, + orderCount: 200, + }); + + const end = Date.now(); + console.log(`\n📊 Summary:`); + console.log(` Products: ${result.products}`); + console.log(` Customers: ${result.customers}`); + console.log(` Orders: ${result.orders}`); + console.log(`\n✅ Seed completed in ${end - start}ms`); + } catch (err) { + console.error("❌ Seed failed:", err); + process.exit(1); + } + + process.exit(0); +} + +// Run if called directly +main(); + |