sgc-portal/mobile-src/scripts/db2mapper.js

#!/usr/bin/env node
/**
 * @license
 * Copyright Google LLC All Rights Reserved.
 * 
 * npm install --save-dev colors
 * npm install --save-dev iconv-lite
 */

 'use strict';

 require('colors');
 const fs = require('fs-extra');

const readline = require('readline');

const rl = readline.createInterface({
	input: process.stdin,
	output: process.stdout,
});

function toCamelCase(str, isObj) {
    const name =  str.toLowerCase().replace(/[^a-zA-Z0-9]+(.)/g, (m, chr) => chr.toUpperCase());
    if (isObj) {
        return name[0].toUpperCase() + name.substring(1);
    } else {
        return name;
    }
}

function getToday() {
    const happyNewYear = new Date();
    const year = happyNewYear.getFullYear();
    const month = happyNewYear.getMonth() + 1;
    const date = happyNewYear.getDate();
    const dateStr = [];
    dateStr.push("" + year);
    if (month >= 10) {
        dateStr.push("" + month);
    } else {
        dateStr.push("0" + month);
    }
    if (date >= 10) {
        dateStr.push("" + date);
    } else {
        dateStr.push("0" + date);
    }
    return dateStr.join('-');
}

function runFormat(dirName, ext, callBack ) {
    let fileName = null;
    let contents = [];
    switch(ext) {
        case 'xml' :
            fileName = dirName + '.xml';
            contents.push('<?xml version="1.0" encoding="UTF-8" ?>');
            contents.push('<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">');
            contents.push('<mapper namespace="sample.'+toCamelCase(tableInfo.name + '_mapper', true)+'">');
            contents.push('\t<!-- '+tableInfo.comments+' -->');
            contents.push('\t<resultMap type="sample.dto.'+toCamelCase(tableInfo.name + '_vo', true)+'" id="'+toCamelCase(tableInfo.name, false)+'Map">');
            tableInfo.colunms.forEach(colunm => {
                contents.push('\t\t<id property="'+toCamelCase(colunm.name, false)+'" column="'+colunm.name.toUpperCase()+'" /><!-- '+colunm.comments+' -->');
            });
            contents.push('\t</resultMap>');
            contents.push('');
            contents.push('\t<!-- '+tableInfo.comments+' - List -->');
            contents.push('\t<select id="'+toCamelCase('select_' + tableInfo.name + '_List', false) +'" parameterType="sample.dto.'+toCamelCase(tableInfo.name + '_vo', true)+'" resultMap="'+toCamelCase(tableInfo.name, false)+'Map"><![CDATA[');
            contents.push('\tSELECT ');
            let selectColunmList = [];
            tableInfo.colunms.forEach(colunm => {
                switch(colunm.type) {
                    case 'DATE' :
                        selectColunmList.push('\t\tTO_CHAR('+colunm.name.toUpperCase()+', \'YYYY-MM-DD\') AS ' + colunm.name.toUpperCase());
                        break;
                    default :
                        selectColunmList.push('\t\tA.' + colunm.name.toUpperCase() + ' AS ' + colunm.name.toUpperCase());
                        break;
                }

            });
            contents.push(selectColunmList.join(" ,\n"));
            contents.push('\tFROM ');
            contents.push('\t\t' + tableInfo.name.toUpperCase() +' A ');
            contents.push('\tWHERE ');
            contents.push('\t\t1=1');
            contents.push('\tORDER BY ');
            contents.push('\t\t1');
            contents.push('\t]]></select>');
            contents.push('');
            contents.push('\t<!-- '+tableInfo.comments+' - View -->');
            contents.push('\t<select id="'+toCamelCase('select_' + tableInfo.name + '_view', false) +'" parameterType="sample.dto.'+toCamelCase(tableInfo.name + '_vo', true)+'" resultMap="'+toCamelCase(tableInfo.name, false)+'Map"><![CDATA[');
            contents.push('\tSELECT ');
            contents.push(selectColunmList.join(" ,\n"));
            contents.push('\tFROM ');
            contents.push('\t\t' + tableInfo.name.toUpperCase() +' A ');
            contents.push('\tWHERE ');
            const pkeysLst = [];
            if (tableInfo.pkeys.length) {
                tableInfo.pkeys.forEach(key => {
                    pkeysLst.push('\t\t'+key.toUpperCase()+' = #{'+toCamelCase(key, false)+'}');
                });
            } else {
                pkeysLst.push('\t\t1 = 2');
            }
            contents.push(pkeysLst.join(' AND\n'));
            contents.push('\t]]></select>');
            contents.push('');
            contents.push('\t<!-- '+tableInfo.comments+' - Insert -->');
            contents.push('\t<insert id="'+toCamelCase('insert_' + tableInfo.name, false) +'" parameterType="sample.dto.'+toCamelCase(tableInfo.name + '_vo', true)+'"><![CDATA[');
            contents.push('\tINSERT INTO ');
            contents.push('\t\t' + tableInfo.name.toUpperCase());
            contents.push('\t(');
            contents.push(selectColunmList.join(" ,\n"));
            contents.push('\t) VALUES (');
            let insertColunmList = [];
            tableInfo.colunms.forEach(colunm => {
                switch(colunm.type) {
                    case 'DATE' :
                        insertColunmList.push('\t\tSYSDATE');
                        break; 
                    default :
                        insertColunmList.push('\t\t#{' + toCamelCase(colunm.name, false) + '}');
                        break;
                }
            });
            contents.push(insertColunmList.join(" ,\n"));
            contents.push('\t)');
            contents.push('\t]]></insert>');
            contents.push('');
            contents.push('\t<!-- '+tableInfo.comments+' - Update -->');
            contents.push('\t<update id="'+toCamelCase('update_' + tableInfo.name, false) +'" parameterType="sample.dto.'+toCamelCase(tableInfo.name + '_vo', true)+'"><![CDATA[');
            contents.push('\tUPDATE ');
            contents.push('\t\t' + tableInfo.name.toUpperCase());
            contents.push('\tSET');
            let updateColunmList = [];
            tableInfo.colunms.forEach(colunm => {
                switch(colunm.type) {
                    case 'DATE' :
                        updateColunmList.push('\t\t'+colunm.name.toUpperCase()+' = SYSDATE');
                        break; 
                    default :
                        updateColunmList.push('\t\t'+colunm.name.toUpperCase()+' = #{' + toCamelCase(colunm.name, false) + '}');
                        break;
                }
            });
            contents.push(updateColunmList.join(" ,\n"));
            contents.push('\tWHERE');
            contents.push(pkeysLst.join(' AND\n'));
            contents.push('\t]]></update>');
            contents.push('');
            contents.push('\t<!-- '+tableInfo.comments+' - Delete -->');
            contents.push('\t<delete id="'+toCamelCase('delete_' + tableInfo.name, false) +'" parameterType="sample.dto.'+toCamelCase(tableInfo.name + '_vo', true)+'"><![CDATA[');
            contents.push('\tDELETE ');
            contents.push('\t\t' + tableInfo.name.toUpperCase());
            contents.push('\tWHERE');
            contents.push(pkeysLst.join(' AND\n'));
            contents.push('\t]]></delete>');
            contents.push('</mapper>');
            break;
        case 'mapper' :
            fileName = dirName + 'Mapper.java';
            contents.push('package sample.persistence;');
            contents.push('');
            contents.push('import org.apache.ibatis.annotations.Mapper;');
            contents.push('import java.util.List;');
            contents.push('');
            contents.push('import sample.dto'+toCamelCase(tableInfo.name + '_vo', true)+';');
            contents.push('');
            contents.push('/**');
            contents.push(' * ' + tableInfo.comments + ' Mapper');
            contents.push(' * fileName : ' + toCamelCase(tableInfo.name + '_Mapper', true) + '.java');
            contents.push(' *');
            contents.push(' * @author outmind0@gmail.com');
            contents.push(' * @since ' + getToday());
            contents.push(' */');
            contents.push('@Mapper');
            contents.push('public interface '+toCamelCase(tableInfo.name + '_mapper', true)+' {');
            contents.push('');
            contents.push('\t/**');
            contents.push('\t * ' + tableInfo.comments + ' - List');
            contents.push('\t * @param vo');
            contents.push('\t * @return ');
            contents.push('\t */');
            contents.push('\tList<' + toCamelCase(tableInfo.name + '_vo', true) + '> ' + toCamelCase('select_' + tableInfo.name + '_List', false) + '('+toCamelCase(tableInfo.name + '_vo', true)+' vo);');
            contents.push('');
            contents.push('\t/**');
            contents.push('\t * ' + tableInfo.comments + ' - View');
            contents.push('\t * @param vo');
            contents.push('\t * @return ');
            contents.push('\t */');
            contents.push('\t' + toCamelCase(tableInfo.name + '_vo', true) + ' ' + toCamelCase('select_' + tableInfo.name + '_view', false) + '('+toCamelCase(tableInfo.name + '_vo', true)+' vo);');
            contents.push('');
            contents.push('\t/**');
            contents.push('\t * ' + tableInfo.comments + ' - Insert');
            contents.push('\t * @param vo');
            contents.push('\t * @return ');
            contents.push('\t */');
            contents.push('\tvoid ' + toCamelCase('insert_' + tableInfo.name, false) + '('+toCamelCase(tableInfo.name + '_vo', true)+' vo);');
            contents.push('');
            contents.push('\t/**');
            contents.push('\t * ' + tableInfo.comments + ' - Update');
            contents.push('\t * @param vo');
            contents.push('\t * @return ');
            contents.push('\t */');
            contents.push('\tvoid ' + toCamelCase('update_' + tableInfo.name, false) + '('+toCamelCase(tableInfo.name + '_vo', true)+' vo);');
            contents.push('');
            contents.push('\t/**');
            contents.push('\t * ' + tableInfo.comments + ' - Delete');
            contents.push('\t * @param vo');
            contents.push('\t * @return ');
            contents.push('\t */');
            contents.push('\tvoid ' + toCamelCase('delete_' + tableInfo.name, false) + '('+toCamelCase(tableInfo.name + '_vo', true)+' vo);');
            contents.push('');
            contents.push('}');
            break;
        case 'vo' :
            fileName = dirName + 'Vo.java';
            contents.push('package sample.dto;');
            contents.push('');
            contents.push('import lombok.Getter;');
            contents.push('import lombok.Setter;');
            contents.push('');
            contents.push('/**');
            contents.push(' * ' + tableInfo.comments + ' Vo');
            contents.push(' * fileName : ' + toCamelCase(tableInfo.name + '_vo', true) + '.java');
            contents.push(' *');
            contents.push(' * @author outmind0@gmail.com');
            contents.push(' * @since ' + getToday());
            contents.push(' */');
            contents.push('@Getter');
            contents.push('@Setter');
            contents.push('public class '+toCamelCase(tableInfo.name + '_vo', true)+' {');
            tableInfo.colunms.forEach(colunm => {
                contents.push('');
                contents.push('\t/**');
                contents.push('\t *'+colunm.comments);
                contents.push('\t */');
                let javaType = 'String';
                switch(colunm.type) {
                    case 'NUMBER' :
                        javaType = 'Integer';
                        break;
                }
                contents.push('\t private ' +javaType+ ' ' + toCamelCase(colunm.name) + ';');
            });
            contents.push('}');
            
            break;
        case 'interface' :
            fileName = dirName + '.ts';
            contents.push('/**');
            contents.push(' * ' + tableInfo.comments);
            contents.push(' */');
            contents.push('export interface '+toCamelCase(tableInfo.name, true)+' {');
            tableInfo.colunms.forEach(colunm => {
                contents.push('');
                contents.push('\t/** '+colunm.comments+' */');
                let colunmType = 'string';
                switch(colunm.type) {
                    case 'NUMBER' :
                        colunmType = 'number';
                        break;
                }
                contents.push('\t'+toCamelCase(colunm.name, false)+'?: '+colunmType+';');
            });
            contents.push('}');
            break;
        default :
            fileName = null;
            break;
    }

    if (fileName != null) {
        fs.writeFile(
            fileName,
            contents.join("\n"),
            'utf-8',
            () => {
                callBack(1);
            }
        );
    } else {
        callBack(0);
    }
}

let totalSuccess = 0 ;
function runFormatProcess(dirName, extList, callBack ) {
    if (extList.length > 0) {
        const ext = extList.pop();
        runFormat(dirName, ext, function(success) {
            totalSuccess += success;
            runFormatProcess(dirName, extList, callBack);
        });
    } else {
        callBack(totalSuccess);
    }
}

function showLogo(callBack) {
    fs.readFile('./banner.txt', 'utf8', (error, jsonFile) => {
        const logo = jsonFile;
        fs.readFile('./package.json', 'utf8', (error, jsonFile) => {
            const packageInfo = JSON.parse(jsonFile);
            console.log(logo.magenta + "\n" + (packageInfo.description +" " + packageInfo.version).red);
            callBack();
        });
    });
}

var myArgs = process.argv.slice(2);

var tableInfo = {
    name : '',
    comments : '',
    pkeys : [],
    colunms : [{
        name : '',
        type : '',
        comments : ''
    }]
};

showLogo(() => {
    if (myArgs.length > 0) {
        const folderName = myArgs[0];
        const question = [];
        question.push("Select Type of Generate!!( READ SQL from "+folderName.red+".sql )");
        question.push("xml - "+folderName.red+".xml");
        question.push("mapper - "+folderName.red+"Mapper.java");
        question.push("vo - "+folderName.red+"Vo.java");
        question.push("interface - "+folderName.red+".ts");
        question.push("Generate Types (xml mapper vo interface all) - ? ");
        rl.question(question.join("\n"), function (formatType) {
            rl.close();
            const formatList = [];
            if (formatType == '') {
                formatType = 'all';
            }
            if (formatType.indexOf('all') > -1) {
                formatList.push('xml');
                formatList.push('mapper');
                formatList.push('vo');
                formatList.push('interface');
            } else {
                const formatTypeList = formatType.split(' ');
                formatTypeList.forEach(function(typeStr) {
                    switch(typeStr) {
                        case 'xml' :
                        case 'mapper' :
                        case 'vo' :
                        case 'interface' :
                            if (formatList.indexOf(typeStr) === -1) {
                                formatList.push(typeStr);
                            }
                            break;
                    }
                });
            }
            if (formatList.length > 0) {
                fs.readFile(folderName + '.sql', 'utf-8', function (err, buf) {
                    var lines = buf.toString().split("\n");
                    tableInfo.colunms = [];
                    tableInfo.pkeys = [];
                    lines.forEach((line,idx) => {
                        var found = null;
                        if (line.startsWith('CREATE TABLE ') && (found = line.match(/([A-Z_]+)[ \(]*[\r\n]*$/))) {
                            if (tableInfo.name != '') {
                                throw 'Cannot use in muti-table!!';
                            }
                            tableInfo.name = found[1].trim();
                        } else if (line.startsWith('COMMENT ON TABLE ') && (found = line.match(/\'([^\']+)\'/))) {
                            tableInfo.comments = found[1].trim();
                        } else if (line.startsWith('COMMENT ON COLUMN ') && (found = line.match(/([A-Z][A-Z0-9_]+) IS \'([^\']+)\'/))) {
                            const name = found[1].trim();
                            const comments = found[2].trim();
                            tableInfo.colunms.forEach(colunm => {
                                if (colunm.name === name) {
                                    colunm.comments = comments;
                                }
                            });
                        } else if (line.startsWith('PRIMARY KEY (')) {
                            let nextIdx = idx + 1;
                            let pkLine = lines[nextIdx];
                            while((found = pkLine.match(/([A-Z][A-Z0-9_]+)/))) {
                                tableInfo.pkeys.push(found[1].trim());
                                nextIdx++;
                                pkLine = lines[nextIdx];
                            }
                        } else if (line.startsWith('\t') && (found = line.match(/([A-Z][A-Z0-9_]+) (TIMESTAMP|VARCHAR|NUMBER|CHAR|DATE)/))) {
                            tableInfo.colunms.push({
                                name : found[1] || '',
                                type : found[2] || '',
                                comments : 'Unknown'
                            });
                        }
                    });

                    console.log(tableInfo);
                    
                    runFormatProcess(folderName,formatList, function(cnt) {
                        console.log('Success Format '.green + ' - ' + (cnt + " cnt").red);
                        console.log(toCamelCase(tableInfo.name, true).green + ' - ' + tableInfo.comments );
                    });
                });
            } else {
                console.log('missing argument!!'.red);
            }
        });
    } else {
        console.log('missing argument!!'.red);
    }
});