Created
April 5, 2017 06:57
-
-
Save modeverv/6dee79c3ebd051f362325876bbde44a3 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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()); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment