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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
/* eslint-disable @typescript-eslint/no-explicit-any */
"use server";
import { sql } from 'drizzle-orm';
import { oracleKnex } from '@/lib/oracle-db/db';
import db from '@/db/db';
import { DatabaseSchema, TableName } from '@/lib/oracle-db/nonsap/oracle-schema';
import * as nonsapSchema from '@/db/schema/NONSAP/nonsap';
// 동기화할 테이블 목록 (단순화)
const TARGET_TABLES: TableName[] = ['CMCTB_CDNM', 'CMCTB_CD'];
// 간단한 로거
const logger = {
info: (message: string, ...args: unknown[]) => console.log(`[NONSAP-SYNC] ${message}`, ...args),
error: (message: string, ...args: unknown[]) => console.error(`[NONSAP-SYNC ERROR] ${message}`, ...args),
warn: (message: string, ...args: unknown[]) => console.warn(`[NONSAP-SYNC WARN] ${message}`, ...args),
success: (message: string, ...args: unknown[]) => console.log(`[NONSAP-SYNC SUCCESS] ${message}`, ...args),
};
/**
* Oracle에서 테이블 데이터 조회 (단순화)
*/
async function fetchOracleData<T extends TableName>(tableName: T): Promise<DatabaseSchema[T][]> {
try {
const query = `SELECT * FROM ${tableName}`;
const result = await oracleKnex.raw(query);
// Oracle knex raw 결과에서 실제 데이터 추출
const rows = Array.isArray(result) ? result : result.rows || [];
const cleanResults = rows.map((row: any) => row);
logger.info(`Fetched ${cleanResults.length} records from ${tableName}`);
return cleanResults as DatabaseSchema[T][];
} catch (error) {
logger.error(`Error fetching data from ${tableName}:`, error);
throw error;
}
}
/**
* 레코드 정규화 (컬럼명 대문자화 및 필터링)
*/
function normalizeRecord(record: any, tableSchema: any): any {
const normalizedRecord: any = {};
const schemaColumns = Object.keys(tableSchema);
for (const [key, value] of Object.entries(record)) {
if (!key || typeof key !== 'string' || key === 'undefined' || key.trim() === '') {
continue;
}
// 대문자로 변환하여 스키마와 매칭
const upperKey = key.toUpperCase();
// 스키마에 해당 컬럼이 있는지 확인
if (schemaColumns.includes(upperKey)) {
normalizedRecord[upperKey] = value;
}
}
return normalizedRecord;
}
/**
* PostgreSQL에 데이터 삽입 (삭제 후 재삽입)
*/
async function syncToPostgres<T extends TableName>(
tableName: T,
data: DatabaseSchema[T][]
): Promise<void> {
if (data.length === 0) return;
try {
// 테이블 스키마 가져오기
const tableCamelCase = tableName.toLowerCase()
.split('_')
.map((word, index) => index === 0 ? word : word.charAt(0).toUpperCase() + word.slice(1))
.join('');
const tableSchema = (nonsapSchema as any)[tableCamelCase];
if (!tableSchema) {
throw new Error(`Table schema not found for ${tableName} (${tableCamelCase})`);
}
// 데이터 정규화
const cleanData = data
.map(record => normalizeRecord(record, tableSchema))
.filter(record => Object.keys(record).length > 0);
if (cleanData.length === 0) {
logger.info(`${tableName} - No valid records after cleaning`);
return;
}
// 1. 기존 데이터 모두 삭제
const tableNameLower = tableName.toLowerCase();
logger.info(`${tableName} - Deleting all existing data`);
await db.execute(sql`
DELETE FROM ${sql.identifier('nonsap')}.${sql.identifier(tableNameLower)}
`);
// 2. 새 데이터 모두 삽입
logger.info(`${tableName} - Inserting ${cleanData.length} new records`);
await db.insert(tableSchema as any).values(cleanData);
logger.success(`Successfully synced ${cleanData.length} records for ${tableName}`);
} catch (error) {
logger.error(`Error syncing data to ${tableName}:`, error);
throw error;
}
}
/**
* 테이블 동기화 (단순화)
*/
async function syncTable<T extends TableName>(tableName: T): Promise<void> {
logger.info(`Starting sync for table: ${tableName}`);
try {
// Oracle에서 데이터 조회
const oracleData = await fetchOracleData(tableName);
if (oracleData.length === 0) {
logger.info(`No data found for ${tableName}, skipping...`);
return;
}
// PostgreSQL에 동기화 (삭제 후 재삽입)
await syncToPostgres(tableName, oracleData);
logger.success(`Table ${tableName} sync completed successfully`);
} catch (error) {
logger.error(`Table ${tableName} sync failed:`, error);
throw error;
}
}
/**
* 모든 테이블 동기화 (단순화)
*/
async function syncAllTables(): Promise<void> {
logger.info('Starting simplified NONSAP data synchronization');
const startTime = Date.now();
let successCount = 0;
let errorCount = 0;
logger.info(`Total tables to sync: ${TARGET_TABLES.length}`);
for (const tableName of TARGET_TABLES) {
try {
await syncTable(tableName);
successCount++;
} catch (error) {
errorCount++;
logger.error(`Failed to sync ${tableName}:`, error);
}
}
const duration = Date.now() - startTime;
logger.info(`Sync completed in ${(duration / 1000).toFixed(2)}s`);
logger.info(`Success: ${successCount}, Errors: ${errorCount}`);
if (errorCount === 0) {
logger.success('✅ All tables synchronized successfully!');
} else {
logger.warn(`${errorCount} tables had errors during sync`);
}
}
/**
* 수동 동기화 트리거 (단순화)
*/
export async function triggerSync(): Promise<void> {
logger.info('Manual sync triggered');
await syncAllTables();
}
/**
* 특정 테이블 동기화
*/
export async function syncSpecificTable(tableName: TableName): Promise<void> {
logger.info(`Manual sync triggered for table: ${tableName}`);
await syncTable(tableName);
}
|
