diff options
Diffstat (limited to 'lib/oracle-db/nonsap/3.generate-postgres-drizzle-schema.ts')
| -rw-r--r-- | lib/oracle-db/nonsap/3.generate-postgres-drizzle-schema.ts | 273 |
1 files changed, 273 insertions, 0 deletions
diff --git a/lib/oracle-db/nonsap/3.generate-postgres-drizzle-schema.ts b/lib/oracle-db/nonsap/3.generate-postgres-drizzle-schema.ts new file mode 100644 index 00000000..cfafb1e0 --- /dev/null +++ b/lib/oracle-db/nonsap/3.generate-postgres-drizzle-schema.ts @@ -0,0 +1,273 @@ +import fs from 'fs'; +import path from 'path'; + +interface TableColumn { + owner: string; + tableName: string; + columnName: string; + dataType: string; + dataLength: number; + nullable: string; + tableComments: string; + columnComments: string; + constraints: string; +} + +interface TableSchema { + tableName: string; + tableComments: string; + columns: TableColumn[]; +} + +// 컬러 로그 함수들 +const log = { + info: (msg: string) => console.log(`\x1b[34mℹ\x1b[0m ${msg}`), + success: (msg: string) => console.log(`\x1b[32m✓\x1b[0m ${msg}`), + warning: (msg: string) => console.log(`\x1b[33m⚠\x1b[0m ${msg}`), + error: (msg: string) => console.log(`\x1b[31m✗\x1b[0m ${msg}`), +}; + +// Oracle -> PostgreSQL 타입 매핑 (nonsap용) +function mapOracleTypeToPostgres(oracleType: string, dataLength: number): string { + const type = oracleType.toUpperCase(); + + switch (type) { + case 'VARCHAR2': + if (dataLength <= 255) { + return `varchar({ length: ${dataLength} })`; + } else { + return 'text()'; + } + case 'NUMBER': + return 'numeric()'; + case 'DATE': + return 'timestamp()'; + case 'CHAR': + return `char({ length: ${dataLength} })`; + case 'CLOB': + return 'text()'; + case 'BLOB': + return 'bytea()'; + case 'INTEGER': + return 'integer()'; + case 'DECIMAL': + return 'decimal()'; + case 'FLOAT': + return 'real()'; + case 'DOUBLE': + return 'doublePrecision()'; + default: + log.warning(`Unknown Oracle type: ${type}, defaulting to text()`); + return 'text()'; + } +} + +// 테이블명을 camelCase로 변환 +function toCamelCase(str: string): string { + return str.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase()); +} + +// 컬럼명을 camelCase로 변환 +function toColumnName(str: string): string { + return str.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase()); +} + +// CSV 파싱 함수 +function parseCSV(csvContent: string): TableColumn[] { + const lines = csvContent.split('\n'); + const data: TableColumn[] = []; + + for (let i = 1; i < lines.length; i++) { + const line = lines[i].trim(); + if (!line) continue; + + const values = line.split(','); + if (values.length < 9) continue; + + data.push({ + owner: values[0], + tableName: values[1], + columnName: values[2], + dataType: values[3], + dataLength: parseInt(values[4]) || 0, + nullable: values[5], + tableComments: values[6], + columnComments: values[7], + constraints: values[8] || '', + }); + } + + return data; +} + +// 테이블별로 그룹화 +function groupByTable(columns: TableColumn[]): TableSchema[] { + const tables = new Map<string, TableSchema>(); + + for (const column of columns) { + if (!tables.has(column.tableName)) { + tables.set(column.tableName, { + tableName: column.tableName, + tableComments: column.tableComments, + columns: [], + }); + } + tables.get(column.tableName)!.columns.push(column); + } + + return Array.from(tables.values()); +} + +// 제약조건 파싱 및 처리 +function parseConstraints(constraints: string) { + const result = { + isPrimaryKey: false, + isUnique: false, + hasCheck: false, + hasForeignKey: false, + }; + + if (!constraints) return result; + + const constraintUpper = constraints.toUpperCase(); + result.isPrimaryKey = constraintUpper.includes('PK'); + result.isUnique = constraintUpper.includes('UNIQUE'); + result.hasCheck = constraintUpper.includes('CHECK'); + result.hasForeignKey = constraintUpper.includes('FK') || constraintUpper.includes('FOREIGN'); + + return result; +} + +// Drizzle 스키마 생성 +function generateDrizzleSchema(tables: TableSchema[]): string { + let schema = `import { pgSchema, varchar, text, numeric, timestamp, char, bytea, integer, decimal, real, doublePrecision, primaryKey } from 'drizzle-orm/pg-core'; + +// NonSAP Oracle 데이터베이스 스키마 +export const nonsapSchema = pgSchema('nonsap'); + +`; + + // Primary key 정보를 추적하기 위한 Map + const primaryKeys = new Map<string, string[]>(); + + // 각 테이블에 대한 스키마 생성 + for (const table of tables) { + const tableName = toCamelCase(table.tableName); + const pkColumns: string[] = []; + + // Primary key 컬럼 찾기 + for (const column of table.columns) { + const constraintInfo = parseConstraints(column.constraints); + if (constraintInfo.isPrimaryKey) { + pkColumns.push(column.columnName); // 원본 컬럼명 그대로 사용 + } + } + + if (pkColumns.length > 0) { + primaryKeys.set(tableName, pkColumns); + } + + // 테이블 코멘트 추가 + if (table.tableComments) { + schema += `// ${table.tableComments}\n`; + } + + schema += `export const ${tableName} = nonsapSchema.table('${table.tableName.toLowerCase()}', {\n`; + + table.columns.forEach((column) => { + const columnName = column.columnName; // 원본 컬럼명 그대로 사용 + const postgresType = mapOracleTypeToPostgres(column.dataType, column.dataLength); + const nullable = column.nullable === 'Y' ? '' : '.notNull()'; + const constraintInfo = parseConstraints(column.constraints); + + // 컬럼 코멘트 추가 + if (column.columnComments) { + schema += ` // ${column.columnComments}\n`; + } + + let columnDef = ` ${columnName}: ${postgresType}${nullable}`; + + // Primary key는 별도로 처리되므로 여기서는 제외 + if (constraintInfo.isUnique && !constraintInfo.isPrimaryKey) { + columnDef += '.unique()'; + } + + columnDef += ',\n'; + schema += columnDef; + }); + + schema += '}'; + + // Primary key 추가 + if (pkColumns.length > 0) { + if (pkColumns.length === 1) { + schema += `, (table) => ({\n pk: primaryKey({ columns: [table.${pkColumns[0]}] })\n})`; + } else { + const pkColumnsStr = pkColumns.map(col => `table.${col}`).join(', '); + schema += `, (table) => ({\n pk: primaryKey({ columns: [${pkColumnsStr}] })\n})`; + } + } + + schema += ');\n\n'; + } + + // 타입 export 추가 + schema += `// 타입 정의\n`; + for (const table of tables) { + const tableName = toCamelCase(table.tableName); + const typeName = tableName.charAt(0).toUpperCase() + tableName.slice(1); + schema += `export type ${typeName} = typeof ${tableName}.$inferSelect;\n`; + schema += `export type New${typeName} = typeof ${tableName}.$inferInsert;\n`; + } + + return schema; +} + +async function main() { + try { + log.info('Starting NonSAP Oracle to PostgreSQL Drizzle schema generation...'); + + // CSV 파일 읽기 + const csvPath = path.join(__dirname, '1.table-and-columns-info.csv'); + const csvContent = fs.readFileSync(csvPath, 'utf-8'); + + log.info('Parsing CSV data...'); + const columns = parseCSV(csvContent); + + if (columns.length === 0) { + throw new Error('No data found in CSV file'); + } + + log.info(`Parsed ${columns.length} column definitions`); + + // 테이블별로 그룹화 + const tables = groupByTable(columns); + + log.info(`Found ${tables.length} tables`); + + // Drizzle 스키마 생성 + log.info('Generating Drizzle schema...'); + const schemaCode = generateDrizzleSchema(tables); + + // 파일 저장 + const outputPath = path.join(__dirname, 'schema.ts'); + fs.writeFileSync(outputPath, schemaCode, 'utf-8'); + + log.success(`Generated Drizzle schema: ${outputPath}`); + log.success(`Generated ${tables.length} table schemas with ${columns.length} total columns`); + + } catch (error: unknown) { + if (error instanceof Error) { + log.error(`Failed to generate schema: ${error.message}`); + } else { + log.error('Failed to generate schema: Unknown error'); + } + process.exit(1); + } +} + +if (require.main === module) { + main(); +} + +export { main };
\ No newline at end of file |