com.hangum.tadpole.commons.sql/src/com/hangum/tadpole/engine/query/sql/TadpoleSystem_ExecutedSQL.java

   1 /*******************************************************************************
   2  * Copyright (c) 2013 hangum.
   3  * All rights reserved. This program and the accompanying materials
   4  * are made available under the terms of the GNU Lesser Public License v2.1
   5  * which accompanies this distribution, and is available at
   6  * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
   7  *
   8  * Contributors:
   9  *     hangum - initial API and implementation
  10  ******************************************************************************/
  11 package com.hangum.tadpole.engine.query.sql;
  12
  13 import java.sql.SQLException;
  14 import java.sql.Timestamp;
  15 import java.text.DateFormat;
  16 import java.text.SimpleDateFormat;
  17 import java.util.ArrayList;
  18 import java.util.Date;
  19 import java.util.HashMap;
  20 import java.util.List;
  21 import java.util.Map;
  22
  23 import org.apache.commons.lang.StringUtils;
  24 import org.apache.log4j.Logger;
  25
  26 import com.hangum.tadpole.commons.dialogs.message.dao.RequestResultDAO;
  27 import com.hangum.tadpole.commons.exception.TadpoleSQLManagerException;
  28 import com.hangum.tadpole.commons.libs.core.define.PublicTadpoleDefine;
  29 import com.hangum.tadpole.commons.libs.core.utils.LicenseValidator;
  30 import com.hangum.tadpole.commons.util.ApplicationArgumentUtils;
  31 import com.hangum.tadpole.engine.initialize.TadpoleEngineUserDB;
  32 import com.hangum.tadpole.engine.manager.TadpoleSQLManager;
  33 import com.hangum.tadpole.engine.query.dao.system.ExecutedSQLResultDataDAO;
  34 import com.hangum.tadpole.engine.query.dao.system.ExecutedSqlResourceDAO;
  35 import com.hangum.tadpole.engine.query.dao.system.ExecutedSqlResourceDataDAO;
  36 import com.hangum.tadpole.engine.query.dao.system.UserDBDAO;
  37 import com.hangum.tadpole.engine.restful.TadpoleException;
  38 import com.hangum.tadpole.engine.sql.util.export.CSVExpoter;
  39 import com.hangum.tadpole.engine.sql.util.resultset.QueryExecuteResultDTO;
  40 import com.hangum.tadpole.engine.utils.TimeZoneUtil;
  41 import com.ibatis.sqlmap.client.SqlMapClient;
  42
  43 /**
  44  * executed_sql_resource관련 테이블 쿼리.
  45  * 해당 테이블은 사용자 sql 실행쿼리에 관한 정보를 가지고 있습니다.
  46  *
  47  * @author hangum
  48  *
  49  */
  50 public class TadpoleSystem_ExecutedSQL {
  51         /**
  52          * Logger for this class
  53          */
  54         private static final Logger logger = Logger.getLogger(TadpoleSystem_ExecutedSQL.class);
  55
  56         /**
  57          * save execute history
  58          *
  59          * @param user_seq
  60          * @param userDB
  61          * @param reqResultDAO
  62          * @param rsDAO
  63          * @return
  64          */
  65         public static long insertExecuteHistory(final int user_seq, final UserDBDAO userDB, final RequestResultDAO reqResultDAO, final QueryExecuteResultDTO rsDAO) {
  66                 long longHistorySeq = -1;
  67
  68                 if(LicenseValidator.getLicense().isValidate()) {
  69                         try {
  70
  71                                 String strExecuteResultData = "";
  72                                 if(rsDAO != null) {
  73                                         if(PublicTadpoleDefine.YES_NO.YES.name().equals(rsDAO.getUserDB().getIs_result_save())) {
  74                                                 strExecuteResultData = CSVExpoter.makeContent(true, rsDAO, ',', "UTF-8");
  75                                         }
  76                                 }
  77
  78                                 longHistorySeq = TadpoleSystem_ExecutedSQL.saveExecuteSQUeryResource(
  79                                                                 user_seq,
  80                                                                 userDB,
  81                                                                 PublicTadpoleDefine.EXECUTE_SQL_TYPE.EDITOR,
  82                                                                 reqResultDAO,
  83                                                                 strExecuteResultData
  84                                                                 );
  85
  86
  87                         } catch(Exception e) {
  88                                 logger.error("save the user query", e); //$NON-NLS-1$
  89                         }
  90                 }
  91
  92                 return longHistorySeq;
  93         }
  94
  95         /**
  96          * save execute history
  97          *
  98          * @param user_seq
  99          * @param userDB
 100          * @param reqResultDAO
 101          * @param te
 102          * @return
 103          */
 104         public static long insertExecuteHistory(final int user_seq, final UserDBDAO userDB, final RequestResultDAO reqResultDAO) {
 105                 long longHistorySeq = -1;
 106
 107                 if(LicenseValidator.getLicense().isValidate()) {
 108                         try {
 109
 110                                 longHistorySeq = TadpoleSystem_ExecutedSQL.saveExecuteSQUeryResource(
 111                                                                 user_seq,
 112                                                                 userDB,
 113                                                                 PublicTadpoleDefine.EXECUTE_SQL_TYPE.EDITOR,
 114                                                                 reqResultDAO,
 115                                                                 ""
 116                                                                 );
 117
 118
 119                         } catch(Exception e) {
 120                                 logger.error("save the user query", e); //$NON-NLS-1$
 121                         }
 122                 }
 123
 124                 return longHistorySeq;
 125         }
 126
 127 //      /**
 128 //       * 모든 sql 히스토리 조회
 129 //       *
 130 //       * @param strEmail
 131 //       * @param strType
 132 //       * @param startTime
 133 //       * @param endTime
 134 //       * @param duringExecute
 135 //       * @param strSearch
 136 //       * @return
 137 //       * @throws TadpoleSQLManagerException
 138 //       * @throws SQLException
 139 //       */
 140 //      public static List<RequestResultDAO> getAllExecuteQueryHistoryDetail(String strEmail, String strType, long startTime, long endTime, int duringExecute, String strSearch, int _indexStart, int _indexEnd) throws TadpoleSQLManagerException, SQLException {
 141 //              return getExecuteQueryHistoryDetail(strEmail, strType, "", startTime, endTime, duringExecute, strSearch, _indexStart, _indexEnd);
 142 //      }
 143
 144         /**
 145          * 쿼리 실행 히스토리 디테일 창을 얻습니다.
 146          *
 147          * @param strEmail
 148          * @param strType
 149          * @param strResultType
 150          * @param dbSeq
 151          * @param startTime
 152          * @param endTime
 153          * @param duringExecute
 154          * @param strSearch
 155          * @param _indexStart
 156          * @param _indexEnd
 157          * @return
 158          * @throws TadpoleSQLManagerException
 159          * @throws SQLException
 160          */
 161         public static List<RequestResultDAO> getExecuteQueryHistoryDetail(String strEmail, String strType, String strResultType, String dbSeq, long startTime, long endTime, int duringExecute, String strSearch, int _indexStart, int _indexEnd) throws TadpoleSQLManagerException, SQLException {
 162                 if(!LicenseValidator.getLicense().isValidate()) {
 163                         return new ArrayList<RequestResultDAO>();
 164                 }
 165
 166                 List<RequestResultDAO> returnSQLHistory = new ArrayList<RequestResultDAO>();
 167
 168                 Map<String, Object> queryMap = new HashMap<String, Object>();
 169                 queryMap.put("email",   strEmail);
 170                 if(!"".equals(dbSeq)) queryMap.put("db_seq",    dbSeq);
 171                 if(!"All".equals(strType)) queryMap.put("type", strType);
 172                 if(!"All".equals(strResultType)) {
 173                         queryMap.put("tdb_result_code", strResultType);
 174                 }
 175
 176                 if(ApplicationArgumentUtils.isDBServer()) {
 177                         Date date = new Date(TimeZoneUtil.chageTimeZone(startTime));
 178                         DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 179                         queryMap.put("startTime",  formatter.format(date));
 180
 181                         Date dateendTime = new Date(TimeZoneUtil.chageTimeZone(endTime));
 182                         queryMap.put("endTime", formatter.format(dateendTime));
 183                 } else {
 184                         queryMap.put("startTime",  startTime);
 185                         queryMap.put("endTime", endTime);
 186                 }
 187
 188                 queryMap.put("duration", duringExecute);
 189                 queryMap.put("strSearch", strSearch);
 190                 queryMap.put("_indexStart", _indexStart);
 191                 queryMap.put("_indexEnd", _indexEnd);
 192
 193                 SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleEngineUserDB.getUserDB());
 194                 List<java.util.Map> listResourceData = sqlClient.queryForList("getExecuteQueryHistoryDetail", queryMap);
 195
 196                 for (Map resultMap : listResourceData) {
 197                         long seq = (Long)resultMap.get("executed_sql_resource_seq");
 198
 199                         Long startdateexecute = 0l;
 200                         String strHeadSQLText = StringUtils.trimToEmpty((String)resultMap.get("tdb_sql_head"));
 201                         String strSQLText = (String)resultMap.get("sql_data");
 202                         Long enddateexecute = 0l;
 203
 204                         if(ApplicationArgumentUtils.isDBServer()) {
 205                                 startdateexecute = ((Timestamp)resultMap.get("startdateexecute")).getTime();
 206                                 enddateexecute  = ((Timestamp)resultMap.get("enddateexecute")).getTime();
 207                         } else {
 208                                 startdateexecute = (Long)resultMap.get("startdateexecute");
 209                                 enddateexecute = (Long)resultMap.get("enddateexecute");
 210                         }
 211
 212                         int row = (Integer)resultMap.get("row");
 213                         String result = (String)resultMap.get("result");
 214
 215                         String userName =  resultMap.get("name") == null?"":(String)resultMap.get("name");
 216                         String userEmail = resultMap.get("email") == null?"":(String)resultMap.get("email");
 217                         String strFullName = "".equals(userEmail)?"":userName+"("+ userEmail+")";
 218
 219                         String dbName = (String) resultMap.get("display_name");
 220
 221                         String ipAddress = (String) resultMap.get("ipaddress");
 222                         int dbSeq2 = (Integer) resultMap.get("dbseq");
 223
 224                         int tdb_result_code = 0;
 225                         String strMessage = (String)resultMap.get("message");
 226                         int duration = (Integer) resultMap.get("duration");
 227                         String strDescription = (String)resultMap.get("description");
 228
 229                         String strResultSaveYn = (String)resultMap.get("result_save_yn");
 230
 231                         RequestResultDAO dao = new RequestResultDAO(
 232                                         duration,strFullName, dbName, new Timestamp(startdateexecute),
 233                                         strHeadSQLText, strSQLText, new Timestamp(enddateexecute), row, result, tdb_result_code, strMessage,
 234                                         ipAddress, dbSeq2, strDescription);
 235                         dao.setSeq(seq);
 236
 237                         String strExecuteType = (String)resultMap.get("types");
 238                         dao.setEXECUSTE_SQL_TYPE(PublicTadpoleDefine.EXECUTE_SQL_TYPE.valueOf(strExecuteType));
 239                         dao.setResult_save_yn(strResultSaveYn);
 240
 241                         returnSQLHistory.add(dao);
 242                 }
 243
 244                 return returnSQLHistory;
 245         }
 246
 247         /**
 248          * execute query
 249          *
 250          * @param seq
 251          * @return
 252          * @throws TadpoleSQLManagerException
 253          * @throws SQLException
 254          */
 255         public static List<ExecutedSQLResultDataDAO> getExecuteResultData(long seq) throws TadpoleSQLManagerException, SQLException {
 256                 SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleEngineUserDB.getUserDB());
 257
 258                 return sqlClient.queryForList("getExecuteResultData", seq);
 259         }
 260
 261         /**
 262          * 마지막 실행했떤 쿼리 20개를 리턴합니다.
 263          *
 264          * @param user_seq
 265          * @param dbSeq
 266          * @return
 267          * @throws TadpoleSQLManagerException, SQLException
 268          */
 269         public static List<RequestResultDAO> getExecuteQueryHistory(int user_seq, int dbSeq, String filter) throws TadpoleSQLManagerException, SQLException {
 270                 List<RequestResultDAO> returnSQLHistory = new ArrayList<RequestResultDAO>();
 271
 272                 Map<String, Object> queryMap = new HashMap<String, Object>();
 273                 queryMap.put("user_seq",user_seq);
 274                 queryMap.put("db_seq",  dbSeq);
 275                 queryMap.put("filter", "%" + filter + "%");
 276                 queryMap.put("count",   20);
 277
 278                 SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleEngineUserDB.getUserDB());
 279                 List<java.util.Map> listResourceData =  sqlClient.queryForList("getExecuteQueryHistory", queryMap);
 280
 281                 for (Map resultMap : listResourceData) {
 282                         long seq                                = (Long)resultMap.get("executed_sql_resource_seq");
 283
 284                         Long startdateexecute   = 0l;
 285                         // This case sqlite
 286                         if(resultMap.get("startdateexecute") instanceof Long) {
 287                                 startdateexecute = (Long)resultMap.get("startdateexecute");
 288                         // This case mysql
 289                         } else {
 290                                 startdateexecute = ((Timestamp)resultMap.get("startdateexecute")).getTime();
 291                         }
 292
 293                         String strHeadSQLText = StringUtils.trimToEmpty((String)resultMap.get("tdb_sql_head"));
 294                         String strSQLText               = (String)resultMap.get("sql_data");
 295                         Long enddateexecute     = 0l;
 296                         // This case sqlite
 297                         if(resultMap.get("enddateexecute") instanceof Long) {
 298                                 enddateexecute = (Long)resultMap.get("enddateexecute");
 299                         // This case mysql
 300                         } else {
 301                                 enddateexecute = ((Timestamp)resultMap.get("enddateexecute")).getTime();
 302                         }
 303
 304                         int tdb_result_code = 0;
 305                         String strMessage               = (String)resultMap.get("message");
 306
 307                         int row                         = (Integer)resultMap.get("row");
 308                         String result           = (String)resultMap.get("result");
 309
 310                         int duration            = (Integer)resultMap.get("duration");
 311
 312                         RequestResultDAO dao
 313                                 = new RequestResultDAO(duration, new Timestamp(startdateexecute), strHeadSQLText, strSQLText, new Timestamp(enddateexecute), row, result, tdb_result_code, strMessage);
 314                         dao.setSeq(seq);
 315                         returnSQLHistory.add(dao);
 316                 }
 317
 318                 return returnSQLHistory;
 319         }
 320
 321         /**
 322          * save sqlhistory
 323          *
 324          * @param user_seq
 325          * @param userDB
 326          * @param sqlType
 327          * @param requestResultDAO
 328          * @param strExecuteResultData
 329          */
 330         public static long saveExecuteSQUeryResource(final int user_seq, final UserDBDAO userDB, final PublicTadpoleDefine.EXECUTE_SQL_TYPE sqlType, final RequestResultDAO requestResultDAO, final String strExecuteResultData) throws TadpoleSQLManagerException, SQLException {
 331                 if(PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getIs_profile())) {
 332                         ExecutedSqlResourceDAO executeSQLResourceDao = new ExecutedSqlResourceDAO();
 333                         executeSQLResourceDao.setDb_seq(userDB.getSeq());
 334                         executeSQLResourceDao.setUser_seq(user_seq);
 335                         executeSQLResourceDao.setTypes(sqlType.toString());
 336
 337                         executeSQLResourceDao.setStartDateExecute(requestResultDAO.getStartDateExecute());
 338                         executeSQLResourceDao.setEndDateExecute(requestResultDAO.getEndDateExecute());
 339                         long duration = 0l;
 340                         try {
 341                                 duration = requestResultDAO.getEndDateExecute().getTime() - requestResultDAO.getStartDateExecute().getTime();
 342                         } catch(Exception e){}
 343                         executeSQLResourceDao.setDuration(Integer.parseInt(""+duration));
 344
 345                         executeSQLResourceDao.setRow(requestResultDAO.getRows());
 346                         executeSQLResourceDao.setResult(requestResultDAO.getResult());
 347
 348                         if(requestResultDAO.getException() instanceof TadpoleException) {
 349                                 TadpoleException te = (TadpoleException)requestResultDAO.getException();
 350                                 executeSQLResourceDao.setTdb_result_code(te.getErrorCode());
 351                         }
 352
 353                         executeSQLResourceDao.setTdb_result_code(requestResultDAO.getTdb_result_code());
 354                         executeSQLResourceDao.setMessage(requestResultDAO.getMesssage());
 355                         executeSQLResourceDao.setIpAddress(requestResultDAO.getIpAddress());
 356                         if(!"".equals(strExecuteResultData)) {
 357                                 executeSQLResourceDao.setResult_save_yn(PublicTadpoleDefine.YES_NO.YES.name());
 358                         }
 359
 360                         // 기존에 등록 되어 있는지 검사한다
 361                         SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleEngineUserDB.getUserDB());
 362                         ExecutedSqlResourceDAO executeSQL =  (ExecutedSqlResourceDAO)sqlClient.insert("userExecuteSQLResourceInsert", executeSQLResourceDao); //$NON-NLS-1$
 363
 364                         insertResourceSQLData(executeSQL.getSeq(), requestResultDAO.getStartDateExecute(), requestResultDAO.getTdb_sql_head(), requestResultDAO.getSql_text());
 365
 366                         if(!"".equals(strExecuteResultData)) {
 367                                 insertResourceResultData(executeSQL.getSeq(), requestResultDAO.getStartDateExecute(), strExecuteResultData);
 368                         }
 369
 370                         return executeSQL.getSeq();
 371                 }
 372
 373                 return -1;
 374         }
 375
 376         /**
 377          * insert execute sql data
 378          *
 379          * @param seq
 380          * @param startDateExecute
 381          * @param tdb_sql_head
 382          * @param sql_text
 383          * @throws TadpoleSQLManagerException, SQLException
 384          */
 385         private static void insertResourceSQLData(final long seq, final Timestamp startDateExecute, final String tdb_sql_head, String sql_text) throws TadpoleSQLManagerException, SQLException {
 386                 SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleEngineUserDB.getUserDB());
 387
 388                 // content data를 저장합니다.
 389                 ExecutedSqlResourceDataDAO dataDao = new ExecutedSqlResourceDataDAO();
 390                 dataDao.setExecuted_sql_resource_seq(seq);
 391                 dataDao.setStartDateExecute(startDateExecute);
 392                 dataDao.setTdb_sql_head(tdb_sql_head);
 393                 dataDao.setSql_data(sql_text);
 394
 395                 sqlClient.insert("userExecuteSQLResourceDataInsert", dataDao); //$NON-NLS-1$
 396         }
 397
 398         /**
 399          * insert execute sql result data
 400          *
 401          * @param seq
 402          * @param startDateExecute
 403          * @param contents
 404          * @throws TadpoleSQLManagerException, SQLException
 405          */
 406         public static void insertResourceResultData(final long seq, final Timestamp startDateExecute, final String contents) throws TadpoleSQLManagerException, SQLException {
 407                 SqlMapClient sqlClient = TadpoleSQLManager.getInstance(TadpoleEngineUserDB.getUserDB());
 408
 409                 // content data를 저장합니다.
 410                 ExecutedSQLResultDataDAO dataDao = new ExecutedSQLResultDataDAO();
 411                 dataDao.setExecuted_sql_resource_seq(seq);
 412                 dataDao.setStartDateExecute(startDateExecute);
 413                 dataDao.setResult_data(contents);
 414
 415                 sqlClient.insert("InsertExecuteSQLResultData", dataDao); //$NON-NLS-1$
 416         }
 417
 418 }