Java tutorial
/** * NOTE: This copyright does *not* cover user programs that use Hyperic * program services by normal system calls through the application * program interfaces provided as part of the Hyperic Plug-in Development * Kit or the Hyperic Client Development Kit - this is merely considered * normal use of the program, and does *not* fall under the heading of * "derived work". * * Copyright (C) [2010], VMware, Inc. * This file is part of Hyperic. * * Hyperic is free software; you can redistribute it and/or modify * it under the terms version 2 of the GNU General Public License as * published by the Free Software Foundation. This program is distributed * in the hope that it will be useful, but WITHOUT ANY WARRANTY; without * even the implied warranty of MERCHANTABILITY or FITNESS FOR A * PARTICULAR PURPOSE. See the GNU General Public License for more * details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 * USA. * */ package org.hyperic.hq.measurement.server.session; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hibernate.SessionFactory; import org.hibernate.engine.SessionFactoryImplementor; import org.hyperic.hibernate.dialect.HQDialect; import org.hyperic.hq.measurement.MeasurementConstants; import org.hyperic.hq.measurement.shared.MeasTabManagerUtil; import org.hyperic.util.TimeUtil; import org.hyperic.util.jdbc.DBUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.stereotype.Repository; /** * @author jhickey * */ @Repository public class DataCompressionDAO { private JdbcTemplate jdbcTemplate; private SessionFactory sessionFactory; private final Log log = LogFactory.getLog(DataCompressionDAO.class); private static final String MEAS_VIEW = MeasTabManagerUtil.MEAS_VIEW; private static final String TAB_DATA = MeasurementConstants.TAB_DATA; @Autowired public DataCompressionDAO(JdbcTemplate jdbcTemplate, SessionFactory sessionFactory) { this.jdbcTemplate = jdbcTemplate; this.sessionFactory = sessionFactory; } public void createMetricDataViews() { final String UNION_BODY = "SELECT * FROM HQ_METRIC_DATA_0D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_0D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_1D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_1D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_2D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_2D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_3D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_3D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_4D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_4D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_5D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_5D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_6D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_6D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_7D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_7D_1S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_8D_0S UNION ALL " + "SELECT * FROM HQ_METRIC_DATA_8D_1S"; final String HQ_METRIC_DATA_VIEW = "CREATE VIEW " + MEAS_VIEW + " AS " + UNION_BODY; final String EAM_METRIC_DATA_VIEW = "CREATE VIEW " + TAB_DATA + " AS " + UNION_BODY + " UNION ALL SELECT * FROM HQ_METRIC_DATA_COMPAT"; try { HQDialect dialect = (HQDialect) ((SessionFactoryImplementor) sessionFactory).getDialect(); Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement(); if (!dialect.viewExists(stmt, TAB_DATA)) { jdbcTemplate.execute(EAM_METRIC_DATA_VIEW); } if (!dialect.viewExists(stmt, MEAS_VIEW)) { jdbcTemplate.execute(HQ_METRIC_DATA_VIEW); } } catch (DataAccessException e) { log.debug("Error Creating Metric Data Views", e); } catch (SQLException e) { log.debug("Error Creating Metric Data Views", e); } } public void truncateMeasurementData(long truncateBefore) { // we can't get any accurate metric tablenames if truncateBefore // is less than the base point in time which is used for the // tablename calculations if (truncateBefore < MeasTabManagerUtil.getBaseTime()) { return; } long currtime = System.currentTimeMillis(); String currTable = MeasTabManagerUtil.getMeasTabname(currtime); long currTruncTime = truncateBefore; // just in case truncateBefore is in the middle of a table currTruncTime = MeasTabManagerUtil.getPrevMeasTabTime(currTruncTime); String delTable = MeasTabManagerUtil.getMeasTabname(currTruncTime); if (delTable.equals(currTable)) { currTruncTime = MeasTabManagerUtil.getPrevMeasTabTime(currTruncTime); delTable = MeasTabManagerUtil.getMeasTabname(currTruncTime); } log.debug("Truncating tables, starting with -> " + delTable + " (currTable -> " + currTable + ")\n"); HQDialect dialect = (HQDialect) ((SessionFactoryImplementor) sessionFactory).getDialect(); while (!currTable.equals(delTable) && truncateBefore > currTruncTime) { try { log.debug("Truncating table " + delTable); jdbcTemplate.execute("truncate table " + delTable); String sql = dialect.getOptimizeStmt(delTable, 0); jdbcTemplate.execute(sql); } catch (DataAccessException e) { log.error(e.getMessage(), e); } finally { currTruncTime = MeasTabManagerUtil.getPrevMeasTabTime(currTruncTime); delTable = MeasTabManagerUtil.getMeasTabname(currTruncTime); } } } public void purgeMeasurements(String tableName, final long startWindow, final long endWindow) { log.debug("Purging data between " + TimeUtil.toString(startWindow) + " and " + TimeUtil.toString(endWindow) + " in " + tableName); final String sql = "DELETE FROM " + tableName + " WHERE timestamp BETWEEN ? AND ?"; jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement stmt = con.prepareStatement(sql); stmt.setLong(1, startWindow); stmt.setLong(2, endWindow); return stmt; } }); } /** * Get the oldest timestamp in the database. */ public long getMinTimestamp(String dataTable) { return jdbcTemplate.queryForLong("SELECT MIN(timestamp) FROM " + dataTable); } public void compactData(final String fromTable, final String toTable, final long begin, final long end) { log.info("Compressing from: " + fromTable + " to " + toTable); try { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { String minMax; if (fromTable.endsWith(TAB_DATA)) { minMax = "AVG(value), MIN(value), MAX(value) "; } else { minMax = "AVG(value), MIN(minvalue), MAX(maxvalue) "; } PreparedStatement insStmt = con.prepareStatement( "INSERT INTO " + toTable + " (measurement_id, timestamp, value, minvalue, maxvalue)" + " (SELECT measurement_id, ? AS timestamp, " + minMax + "FROM " + fromTable + " WHERE timestamp >= ? AND timestamp < ? " + "GROUP BY measurement_id)"); insStmt.setLong(1, begin); insStmt.setLong(2, begin); insStmt.setLong(3, end); return insStmt; } }); } catch (DataAccessException e) { // Just log the error and continue log.debug("SQL exception when inserting data " + " at " + TimeUtil.toString(begin), e); } } /** * Get the most recent measurement. */ public long getMaxTimestamp(String dataTable) { Connection connection; try { connection = jdbcTemplate.getDataSource().getConnection(); } catch (SQLException e) { throw jdbcTemplate.getExceptionTranslator().translate("Obtaining connection from DataSource", null, e); } String sql; try { if (DBUtil.isPostgreSQL(connection)) { // Postgres handles this much better sql = "SELECT timestamp FROM " + dataTable + " ORDER BY timestamp DESC LIMIT 1"; } else { sql = "SELECT MAX(timestamp) FROM " + dataTable; } } catch (SQLException e) { throw jdbcTemplate.getExceptionTranslator().translate("Determining if the database is PostGres", null, e); } return jdbcTemplate.query(sql, new ResultSetExtractor<Long>() { public Long extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { return rs.getLong(1); } else { // New installation return 0l; } } }); } public String getMeasurementUnionStatement(long begin) { return MeasurementUnionStatementBuilder.getUnionStatement((begin - MeasurementConstants.HOUR), begin, (HQDialect) ((SessionFactoryImplementor) sessionFactory).getDialect()); } }