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
33
34
35
36
37
38
39
40
41
42
43
44
45
|
import { pgTable, pgView, timestamp, integer, varchar, serial } from 'drizzle-orm/pg-core';
import { projects } from './projects';
import { eq, sql } from "drizzle-orm";
export const projectGtcFiles = pgTable('project_gtc_files', {
id: serial("id").primaryKey(),
projectId: integer('project_id').references(() => projects.id).notNull(),
fileName: varchar("file_name", { length: 255 }).notNull(),
filePath: varchar("file_path", { length: 1024 }).notNull(),
originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
fileSize: integer('file_size'),
mimeType: varchar("mime_type", { length: 100 }),
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
// Project GTC 뷰 (프로젝트 정보와 파일 정보를 조인)
export const projectGtcView = pgView('project_gtc_view').as((qb) => {
return qb
.select({
// 프로젝트 정보
id: sql<number>`${projects.id}`.as('id'),
code: sql<string>`${projects.code}`.as('code'),
name: sql<string>`${projects.name}`.as('name'),
type: sql<string>`${projects.type}`.as('type'),
projectCreatedAt: sql<Date>`${projects.createdAt}`.as('project_created_at'),
projectUpdatedAt: sql<Date>`${projects.updatedAt}`.as('project_updated_at'),
// GTC 파일 정보
gtcFileId: sql<number | null>`${projectGtcFiles.id}`.as('gtc_file_id'),
fileName: sql<string | null>`${projectGtcFiles.fileName}`.as('fileName'),
filePath: sql<string | null>`${projectGtcFiles.filePath}`.as('filePath'),
originalFileName: sql<string | null>`${projectGtcFiles.originalFileName}`.as('originalFileName'),
fileSize: sql<number | null>`${projectGtcFiles.fileSize}`.as('fileSize'),
mimeType: sql<string | null>`${projectGtcFiles.mimeType}`.as('mimeType'),
gtcCreatedAt: sql<Date | null>`${projectGtcFiles.createdAt}`.as('gtcCreatedAt'),
gtcUpdatedAt: sql<Date | null>`${projectGtcFiles.updatedAt}`.as('gtcUpdatedAt'),
})
.from(projects)
.leftJoin(projectGtcFiles, eq(projects.id, projectGtcFiles.projectId));
});
// 타입 정의
export type ProjectGtcFile = typeof projectGtcFiles.$inferSelect;
export type ProjectGtcView = typeof projectGtcView.$inferSelect;
|
