y9iUeQ1C

1/**
2  * MongoFDW를 AgensGraph에 구성 참고용 개발 소스
3  * 기타 library, package는 명시하지 않음. 단지 AgensGraph를 사용하기 위해 jdbc driver를 포함시킨 내용만 기술
4  * 
5  * @author HyeonSu Jeon
6  */
7  
8  
9  import net.bitnine.agensgraph.deps.org.json.simple.JSONArray;
10  import net.bitnine.agensgraph.deps.org.json.simple.JSONObject;
11  
12  /**
13	 * MongoDB ip address
14	 */
15	@Value("${spring.data.mongodb.host}") String host;
16	
17	/**
18	 * MongoDB port number
19	 */
20	@Value("${spring.data.mongodb.port}") Integer port;
21  
22  /**
23	 * mongo_fdw name
24	 */
25	@Value("${spring.datasource.mongofdw}") String mongo_fdw;
26	
27	/**
28	 * mongo_server name
29	 */
30	@Value("${spring.datasource.mongoserver}") String mongo_server;
31  ...
32  
33  /**
34	 * MongoFDW 설정 메소드
35   * AgensGraph에 MongoFDW가 extension 되어있는지 체크 후 필요한 부분만 설치
36	 * 
37	 * @param	mongo_fdw		  mongo_fdw name
38	 * @param	mongo_server	mongo_server name
39	 * @return	no return. 수행 결과 후 end 로그 추가
40	 */
41	public void mongoFDW(String mongo_fdw, String mongo_server) {
42		logger.info("[mongoFDW()] start");
43		StringBuffer query    = new StringBuffer();
44		StringBuffer chkQuery = new StringBuffer();
45		JSONArray    chkRslt  = new JSONArray();
46		
47    // MongoFDW Extension checking query
48		chkQuery.append(" SELECT   "
49        +     "          count(phase2.wrapper_id) AS wrapper_cnt "
50				+ 		"        , count(phase2.server_id) AS server_cnt "
51				+ 		"        , count(mapping.oid) AS mapping_cnt "
52				+ 		" FROM "
53				+ 		"     (SELECT    phase1.wrapper_id AS wrapper_id "
54				+ 		"              , server.oid AS server_id "
55				+ 		"      FROM "
56				+ 		"         (SELECT  wrapper.oid AS wrapper_id "
57				+ 		"          FROM    pg_authid authid "
58				+ 		"          LEFT OUTER JOIN pg_foreign_data_wrapper wrapper "
59				+ 		"          ON authid.oid = wrapper.fdwowner "
60				+ 		"          WHERE authid.rolname = ? AND wrapper.fdwname = ?) phase1 "
61				+ 		"      LEFT OUTER JOIN pg_foreign_server server "
62				+ 		"      ON phase1.wrapper_id = server.srvfdw) phase2 "
63				+ 		" LEFT OUTER JOIN pg_user_mapping mapping "
64				+ 		" ON phase2.server_id = mapping.umserver ");
65        
66		Map<String,Object> paramList = new LinkedHashMap<String,Object>();
67		paramList.put("user",    user);
68		paramList.put("fdwname", mongo_fdw);
69		
70    // execute MongoFDW extension checking query
71		chkRslt = qs.doQuery(chkQuery.toString(), paramList);
72		logger.info("chkRslt >> "+chkRslt);
73    
74		int wrapper_cnt  = Integer.parseInt(((JSONObject)chkRslt.get(0)).get("wrapper_cnt").toString());
75		int server_cnt   = Integer.parseInt(((JSONObject)chkRslt.get(0)).get("server_cnt").toString());
76		int mapping_cnt  = Integer.parseInt(((JSONObject)chkRslt.get(0)).get("mapping_cnt").toString());
77		
78		if(wrapper_cnt <= 0) {
79			// 1) MongoFDW Extension
80			query = new StringBuffer();
81			query.append(" CREATE EXTENSION IF NOT EXISTS mongo_fdw ");
82			paramList = new LinkedHashMap<String,Object>();
83			
84			try {
85				qs.doExecute(query.toString(), paramList);
86			}catch(Exception e) {
87				logger.error("Error : "+e+", Checking mongoFDW library .so file.");
88			}
89		}
90		
91		if(server_cnt <= 0) {
92			// 2) MongoFDW Server
93			// preparedstatement로 처리 불가. 명시적으로 parameters를 쿼리에 심어줘야 함.
94			query = new StringBuffer();
95			query.append(" CREATE SERVER "+mongo_server+" FOREIGN DATA WRAPPER mongo_fdw " + 
96						 " OPTIONS(address '"+host+"', port '"+port+"') ");
97			paramList = new LinkedHashMap<String,Object>();
98			
99			try {
100				qs.doExecute(query.toString(), paramList);	
101			}catch(Exception e) {
102				logger.error("Error : "+e+", Checking the mongofdw server. (or already the server had created.)");
103			}				
104		}
105		
106		if(mapping_cnt <= 0) {
107			// 3) MongoFDW user mapping
108			query = new StringBuffer();
109			paramList = new LinkedHashMap<String,Object>();
110			query.append("CREATE USER MAPPING FOR ctias SERVER "+mongo_server+"");
111			
112			try {
113				qs.doExecute(query.toString(), paramList);	
114			}catch(Exception e) {
115				logger.error("Error : "+e+", Checking count. 'select * from pg_user_mapping' table");
116			}
117			
118		}
119		
120		paramList = new LinkedHashMap<String,Object>();
121		
122		// public.c_table1
123		query = new StringBuffer();
124		query.append(" CREATE FOREIGN TABLE IF NOT EXISTS public.c_table1 "
125				+ 	 " (name TEXT) "
126				+    " SERVER mongo_server OPTIONS (database 'THREAT_GR', collection 'groups') ");
127		qs.doExecute(query.toString(), paramList);
128
129		logger.info("[mongoFDW()] end");
130	}