Examples with DatabaseInfo com.varsql.core.db.valueobject.DatabaseInfo used on opensource projects

Example 1 with DatabaseInfo

use of com.varsql.core.db.valueobject.DatabaseInfo in project varsql by varsqlinfo.

the class SQLServiceImpl method sqlData.

/**
 * @Method Name  : sqlData
 * @Method 설명 : 쿼리 데이터 보기.
 * @작성자   : ytkim
 * @작성일   : 2015. 4. 9.
 * @변경이력  :
 * @param sqlExecuteInfo
 * @param req
 * @return
 * @throws Exception
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public ResponseResult sqlData(SqlExecuteDTO sqlExecuteInfo, HttpServletRequest req) throws Exception {
    Map sqlParamMap = VartechUtils.jsonStringToObject(sqlExecuteInfo.getSqlParam(), HashMap.class);
    DatabaseInfo dbinfo = SecurityUtil.userDBInfo(sqlExecuteInfo.getConuid());
    ResponseResult parseInfo = SqlSourceBuilder.parseResponseResult(sqlExecuteInfo.getSql(), sqlParamMap, DBType.getDBType(sqlExecuteInfo.getDbType()));
    List<SqlSource> sqlList = parseInfo.getItems();
    int limit = sqlExecuteInfo.getLimit();
    if (!SecurityUtil.isAdmin()) {
        sqlExecuteInfo.setLimit(limit > dbinfo.getMaxSelectCount() ? dbinfo.getMaxSelectCount() : limit);
    }
    ArrayList<SqlSourceResultVO> reLst = new ArrayList<SqlSourceResultVO>();
    ResponseResult result = new ResponseResult();
    Connection conn = null;
    SqlSourceResultVO ssrv = null;
    long stddt = System.currentTimeMillis();
    String[] mmddHH = DateUtils.dateformat("MM-dd-HH", stddt).split("-");
    SqlLogInfoDTO sqlLogInfo = new SqlLogInfoDTO();
    sqlLogInfo.setVconnid(sqlExecuteInfo.getVconnid());
    sqlLogInfo.setViewid(sqlExecuteInfo.getViewid());
    sqlLogInfo.setStartTime(stddt);
    sqlLogInfo.setSMm(Integer.valueOf(mmddHH[0]));
    sqlLogInfo.setSDd(Integer.valueOf(mmddHH[1]));
    sqlLogInfo.setSHh(Integer.valueOf(mmddHH[2]));
    sqlLogInfo.setUsrIp(VarsqlUtils.getClientIp(req));
    SqlSource tmpSqlSource = null;
    int sqldx = 0, sqlSize = sqlList.size();
    String errorMsg = "";
    try {
        conn = ConnectionFactory.getInstance().getConnection(sqlExecuteInfo.getVconnid());
        if (!StringUtils.isBlank(sqlExecuteInfo.get_requid_())) {
            SqlExecuteManager.getInstance().setStatementInfo(sqlExecuteInfo.get_requid_(), null);
        }
        conn.setAutoCommit(false);
        List<SqlStatisticsEntity> allSqlStatistics = new LinkedList<SqlStatisticsEntity>();
        for (sqldx = 0; sqldx < sqlSize; sqldx++) {
            tmpSqlSource = sqlList.get(sqldx);
            ssrv = new SqlSourceResultVO();
            reLst.add(ssrv);
            tmpSqlSource.setResult(ssrv);
            ssrv.setStarttime(System.currentTimeMillis());
            getRequestSqlData(sqlExecuteInfo, conn, tmpSqlSource, dbinfo, true);
            ssrv.setEndtime(System.currentTimeMillis());
            ssrv.setDelay((ssrv.getEndtime() - ssrv.getStarttime()) / 1000);
            ssrv.setResultMessage((ssrv.getDelay()) + " SECOND : " + StringUtils.escape(ssrv.getResultMessage(), EscapeType.html));
            sqlLogInfo.setStartTime(ssrv.getStarttime());
            sqlLogInfo.setCommandType(tmpSqlSource.getCommandType());
            sqlLogInfo.setEndTime(ssrv.getEndtime());
            allSqlStatistics.add(SqlStatisticsEntity.builder().vconnid(sqlLogInfo.getVconnid()).viewid(sqlLogInfo.getViewid()).startTime(ConvertUtils.longToLocalDateTime(sqlLogInfo.getStartTime())).endTime(ConvertUtils.longToLocalDateTime(sqlLogInfo.getEndTime())).delayTime(sqlLogInfo.getDelayTime()).sMm(sqlLogInfo.getSMm()).sDd(sqlLogInfo.getSDd()).sHh(sqlLogInfo.getSHh()).resultCount(sqlLogInfo.getResultCount()).commandType(sqlLogInfo.getCommandType()).build());
            if (SqlDataConstants.VIEWTYPE.GRID.val().equals(ssrv.getViewType())) {
                break;
            }
        }
        commonServiceImpl.sqlLogInsert(allSqlStatistics);
        result.setItemList(reLst);
        conn.commit();
    } catch (Throwable e) {
        if (conn != null && !conn.isClosed())
            conn.rollback();
        errorMsg = e.getMessage();
        if (e instanceof ResultSetConvertException) {
            result.setResultCode(VarsqlAppCode.EC_SQL_RESULT_CONVERT);
            ssrv = ((ResultSetConvertException) e).getSsrv();
            if (ssrv != null) {
                ssrv = new SqlSourceResultVO();
            }
            ssrv.setViewType(SqlDataConstants.VIEWTYPE.GRID.val());
        } else {
            boolean ssrvNullFlag = false;
            if (ssrv == null) {
                ssrvNullFlag = true;
                ssrv = new SqlSourceResultVO();
            }
            ssrv.setEndtime(System.currentTimeMillis());
            String tmpMsg = parseInfo.getMessage();
            tmpMsg = (tmpMsg == null || "".equals(tmpMsg) ? "" : StringUtils.escape(parseInfo.getMessage(), EscapeType.html) + "<br/>");
            if (e instanceof ConnectionFactoryException) {
                if (((ConnectionFactoryException) e).getErrorCode() == VarsqlAppCode.EC_DB_POOL_CLOSE) {
                    result.setResultCode(VarsqlAppCode.EC_DB_POOL_CLOSE);
                } else {
                    result.setResultCode(VarsqlAppCode.EC_DB_POOL);
                }
            } else {
                result.setResultCode(VarsqlAppCode.EC_SQL);
            }
            result.setMessage(tmpMsg + StringUtils.escape(ssrv.getResultMessage(), EscapeType.html));
            if (ssrvNullFlag) {
                result.setMessage(errorMsg);
            }
        }
        result.addCustoms("errorLine", sqldx);
        result.setItemOne(tmpSqlSource == null ? sqlList.get(0) : tmpSqlSource);
        LoggerFactory.getLogger("sqlErrorLog").error("sqlData errorLine : {}", sqldx, e);
    } finally {
        if (conn != null && !conn.isClosed()) {
            conn.setAutoCommit(true);
            JdbcUtils.close(conn);
        }
    }
    if (!StringUtils.isBlank(sqlExecuteInfo.get_requid_())) {
        SqlExecuteManager.getInstance().removeStatementInfo(sqlExecuteInfo.get_requid_());
    }
    long enddt = System.currentTimeMillis();
    commonServiceImpl.saveSqlHistory(SqlHistoryEntity.builder().vconnid(sqlLogInfo.getVconnid()).viewid(sqlLogInfo.getViewid()).startTime(ConvertUtils.longToTimestamp(stddt)).endTime(ConvertUtils.longToTimestamp(enddt)).delayTime((int) ((enddt - stddt) / 1000)).logSql(sqlExecuteInfo.getSql()).usrIp(sqlLogInfo.getUsrIp()).errorLog(errorMsg).build());
    return result;
}

Example 2 with DatabaseInfo

use of com.varsql.core.db.valueobject.DatabaseInfo in project varsql by varsqlinfo.

the class DatabaseServiceImpl method schemas.

/**
 * @Method Name  : schemas
 * @Method 설명 : 스키마 정보보기
 * @작성일   : 2015. 4. 10.
 * @작성자   : ytkim
 * @변경이력  :
 * @param databaseParamInfo
 * @return
 * @throws SQLException
 * @throws Exception
 */
public Map schemas(DatabaseParamInfo databaseParamInfo) throws SQLException {
    Map json = new HashMap();
    DatabaseInfo dbinfo = SecurityUtil.userDBInfo(databaseParamInfo.getConuid());
    MetaControlBean dbMetaEnum = MetaControlFactory.getDbInstanceFactory(dbinfo.getType());
    json.put("schema", dbinfo.getSchema());
    json.put("conuid", dbinfo.getConnUUID());
    json.put("type", dbinfo.getType());
    json.put("lazyload", dbinfo.isLazyLoad());
    json.put("schemaList", dbMetaEnum.getSchemas(databaseParamInfo));
    json.put("serviceObject", dbMetaEnum.getServiceMenu());
    return json;
}

Example 3 with DatabaseInfo

use of com.varsql.core.db.valueobject.DatabaseInfo in project varsql by varsqlinfo.

the class DatabaseServiceImpl method findTabInfo.

@Transactional(value = ResourceConfigConstants.APP_TRANSMANAGER, rollbackFor = Throwable.class)
public List findTabInfo() {
    TreeDataSort tds = new TreeDataSort("conuid", "prevConuid");
    try {
        String viewid = SecurityUtil.userViewId();
        List<DBConnTabEntity> tabList = dbConnTabEntityRepository.findAllByViewid(viewid);
        User user = SecurityUtil.loginInfo();
        Map<String, DatabaseInfo> databaseInfo = user.getDatabaseInfo();
        Map<String, String> vconnidNconuid = user.getVconnidNconuid();
        List<String> notExistsVconnid = new ArrayList<>();
        tabList.forEach(item -> {
            String vconnid = item.getVconnid();
            if (vconnidNconuid.containsKey(vconnid)) {
                String conuid = vconnidNconuid.get(vconnid);
                DatabaseInfo di = databaseInfo.get(conuid);
                tds.sortTreeData(DBConnTabResponseDTO.builder().conuid(conuid).name(di.getName()).prevConuid(vconnidNconuid.get(item.getPrevVconnid())).viewYn(item.isViewYn()).build());
            } else {
                notExistsVconnid.add(vconnid);
            }
        });
        if (notExistsVconnid.size() > 0) {
            dbConnTabEntityRepository.deleteAllTabInfo(viewid, notExistsVconnid);
        }
    } catch (Exception e) {
        logger.error("findTabInfo : {} ", e.getMessage(), e);
    }
    List sortList = tds.getSortList();
    return sortList == null ? new ArrayList() : sortList;
}

Example 4 with DatabaseInfo

use of com.varsql.core.db.valueobject.DatabaseInfo in project varsql by varsqlinfo.

the class AuthDAO method getUserDataBaseInfo.

/**
 * @Method Name  : getUserDataBaseInfo
 * @Method 설명 :
 * @작성일   : 2015. 6. 22.
 * @작성자   : ytkim
 * @변경이력  :
 * @return
 * @throws SQLException
 */
public void getUserDataBaseInfo() {
    Connection conn = ConnectionFactory.getInstance().getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        User user = SecurityUtil.loginInfo();
        StringBuffer query = new StringBuffer();
        String dbColumnQuery = "select VCONNID, VNAME, VDBSCHEMA, VDBVERSION, BASETABLE_YN, LAZYLOAD_YN,SCHEMA_VIEW_YN, MAX_SELECT_COUNT, USE_COLUMN_LABEL, b.DB_TYPE from VTCONNECTION a left outer join VTDBTYPE_DRIVER_PROVIDER b  on a.VDRIVER = b.DRIVER_PROVIDER_ID where USE_YN ='Y' and DEL_YN = 'N' AND ";
        query.append(dbColumnQuery);
        AuthorityType tmpAuthority = user.getTopAuthority();
        if (tmpAuthority.equals(AuthorityType.ADMIN)) {
            query.append(" 1 = 1 ");
        } else if (tmpAuthority.equals(AuthorityType.GUEST)) {
            query.append(" 1 != 1 ");
        } else {
            query.append(" A.VCONNID IN ( ");
            query.append(" select d.VCONNID ");
            query.append(" from VTDATABASE_GROUP a inner join VTDATABASE_GROUP_DB b on a.GROUP_ID = b.GROUP_ID ");
            query.append(" inner join VTDATABASE_GROUP_USER c on b.GROUP_ID = c.GROUP_ID ");
            query.append(" inner join VTCONNECTION d on b.VCONNID = d.VCONNID ");
            query.append(" left outer join VTDATABASE_BLOCK_USER e on d.VCONNID = e.VCONNID and c.VIEWID =e.VIEWID ");
            query.append(" where c.VIEWID = '" + user.getViewid() + "'  ");
            query.append(" and e.viewid is null ");
            query.append(" and d.USE_YN = 'Y' ");
            query.append(" group by d.VCONNID ");
            query.append(" ) ");
            if (tmpAuthority.equals(AuthorityType.MANAGER)) {
                query.append(" union ");
                query.append(dbColumnQuery + "  A.VCONNID in ( select VCONNID from VTDATABASE_MANAGER where VIEWID = '" + user.getViewid() + "' ) ");
            }
        }
        Map<String, DatabaseInfo> userDatabaseInfo = new LinkedHashMap<String, DatabaseInfo>();
        if (!tmpAuthority.equals(AuthorityType.GUEST)) {
            pstmt = conn.prepareStatement(query.toString());
            rs = pstmt.executeQuery();
            String vconnid;
            String uuid = "";
            String viewid = user.getViewid();
            Map<String, String> vconnidNconuid = new HashMap<>();
            List<String> newVconnidList = new ArrayList<String>();
            while (rs.next()) {
                vconnid = rs.getString(VarsqlKeyConstants.CONN_ID);
                newVconnidList.add(vconnid);
                uuid = UUIDUtil.vconnidUUID(viewid, vconnid);
                try {
                    userDatabaseInfo.put(uuid, new DatabaseInfo(vconnid, uuid, rs.getString("DB_TYPE"), rs.getString(VarsqlKeyConstants.CONN_NAME), rs.getString(VarsqlKeyConstants.CONN_DBSCHEMA), rs.getString(VarsqlKeyConstants.CONN_BASETABLE_YN), rs.getString(VarsqlKeyConstants.CONN_LAZYLOAD_YN), rs.getLong(VarsqlKeyConstants.CONN_VDBVERSION), rs.getString(VarsqlKeyConstants.CONN_SCHEMA_VIEW_YN), rs.getInt(VarsqlKeyConstants.CONN_MAX_SELECT_COUNT), rs.getString(VarsqlKeyConstants.CONN_USE_COLUMN_LABEL)));
                    vconnidNconuid.put(vconnid, uuid);
                } catch (Exception e) {
                    logger.error("DatabaseInfo not valid : " + vconnid);
                }
            }
            user.setDatabaseInfo(userDatabaseInfo);
            user.setVconnidNconuid(vconnidNconuid);
        }
    } catch (SQLException e) {
        throw new VarsqlAppException("database load exception : " + e.getMessage(), e);
    } finally {
        JdbcUtils.close(conn, pstmt, rs);
    }
}

Example 5 with DatabaseInfo

use of com.varsql.core.db.valueobject.DatabaseInfo in project varsql by varsqlinfo.

the class BaseExecutorTest method testUpdate.

// @Test
// @Ignore
void testUpdate() {
    ConnectionInfo connInfo = getBaseConnection();
    try {
        DatabaseInfo di = getDatabaseInfo(connInfo);
        SqlStatementInfo sqi = new SqlStatementInfo();
        sqi.setSchema("PUBLIC");
        sqi.setConuid(connInfo.getConnid(), TEST_CON_UID, di);
        SQLExecutor baseExecutor = new SqlUpdateExecutor();
        sqi.setSqlParam("{}");
        String insertQuery = getResourceContent("/db/importdata/insertdata.sql");
        sqi.setSql(insertQuery);
        SQLExecuteResult ser = baseExecutor.execute(sqi, new AbstractSQLExecutorHandler() {

            @Override
            public boolean handle(SQLHandlerParameter handleParam) {
                return true;
            }
        });
        System.out.println("testUpdate ser.getTotalCount(): " + ser.getTotalCount());
        System.out.println("testUpdate ser.getResult(): " + ser.getResult());
    } catch (Exception e) {
        e.printStackTrace();
    }
}