makeDbColumnEncryptionSQL.java

package norainu;

import java.io.File;
import java.util.List;

import org.apache.commons.io.FileUtils;

/**
 * CSVで読み取ってSQLを作成<br/>
 *
 * @author test
 *
 */
public class Main {

    static final String SQL_ALTER_TABLE;
    static final String SQL_FUNCTION;
    static final String SQL_TRRIGER;

    static {
        StringBuilder sbAlter = new StringBuilder();
        StringBuilder sbFunc = new StringBuilder();
        StringBuilder sbTrigger = new StringBuilder();
        sbAlter.append("\n");
        sbAlter.append("-- テーブル定義を変更\n");
        sbAlter.append("ALTER TABLE %TABLE% ALTER COLUMN %COLUMN% TYPE text;\n");
        SQL_ALTER_TABLE = sbAlter.toString();

        sbFunc.append("\n");
        sbFunc.append("-- トリガー関数を定義\n");
        sbFunc.append("CREATE OR REPLACE FUNCTION %TABLE%_trigger_func_%COLUMN%()\n");
        sbFunc.append("  RETURNS trigger AS\n");
        sbFunc.append("$BODY$ \n");
        sbFunc.append("BEGIN \n");
        sbFunc.append("NEW.%COLUMN% := encode(pgp_sym_encrypt(NEW.%COLUMN%,'hoge'),'base64'); \n");
        sbFunc.append("RETURN NEW; \n");
        sbFunc.append("END ;$BODY$\n");
        sbFunc.append("  LANGUAGE plpgsql VOLATILE\n");
        sbFunc.append("  COST 100;\n");
        SQL_FUNCTION = sbFunc.toString();

        sbTrigger.append("\n");
        sbTrigger.append("-- トリガー関数を設定\n");
        sbTrigger.append("CREATE TRIGGER encrypt_%TABLE%_%COLUMN% BEFORE INSERT OR UPDATE OF %COLUMN%\n");
        sbTrigger.append("   ON %TABLE% FOR EACH ROW\n");
        sbTrigger.append("   EXECUTE PROCEDURE public.%TABLE%_trigger_func_%COLUMN%();\n");
        SQL_TRRIGER = sbTrigger.toString();
    }

    static StringBuilder makeOutputSb(String filepath) {
        StringBuilder sb = new StringBuilder();
        sb.append("-- Generate DB encryption SQL...");
        try {
            @SuppressWarnings("unchecked")
            List<String> strList = FileUtils.readLines(new File(filepath), "utf-8");
            for (String line : strList) {
                String[] tableColumn = line.split(",");
                if (tableColumn != null && tableColumn.length == 2) {
                    sb.append("\n-- ##############################################################\n");
                    sb.append("-- %TABLE%の%COLUMN%についてSQLを生成します\n".replaceAll("%TABLE%", tableColumn[0]).replaceAll("%COLUMN%", tableColumn[1]));
                    sb.append(SQL_ALTER_TABLE.replaceAll("%TABLE%", tableColumn[0]).replaceAll("%COLUMN%", tableColumn[1]));
                    sb.append(SQL_FUNCTION.replaceAll("%TABLE%", tableColumn[0]).replaceAll("%COLUMN%", tableColumn[1]));
                    sb.append(SQL_TRRIGER.replaceAll("%TABLE%", tableColumn[0]).replaceAll("%COLUMN%", tableColumn[1]));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sb;
    }

    static void writeFile(String filepath, StringBuilder sb) {
        try {
            FileUtils.writeStringToFile(new File(filepath), sb.toString(), "utf-8");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * csvを受け取ってsqlを生成します<br/>
     * csvの文字コード:UTF-8<br/>
     * csvの形式:テーブル名,カラム名<br/>
     *
     * @param args インプットcsvファイルパス、アウトプットSQLファイルパス
     */
    public static void main(String[] args) {
        if (args.length != 2) {
            System.out.println("Usage: java Main <targetfile> <outtargetfile>");
            System.exit(1);
        }

        StringBuilder sb = makeOutputSb(args[0]);
        writeFile(args[1], sb);
        System.out.println(sb.toString());
    }

}