| /******************************************************************************* |
| * Copyright (c) 2000, 2017 IBM Corporation and others. |
| * All rights reserved. This program and the accompanying materials |
| * are made available under the terms of the Eclipse Public License v1.0 |
| * which accompanies this distribution, and is available at |
| * http://www.eclipse.org/legal/epl-v10.html |
| * |
| * Contributors: |
| * IBM Corporation - initial API and implementation |
| *******************************************************************************/ |
| package org.eclipse.test.internal.performance.results.db; |
| |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Timestamp; |
| |
| import org.eclipse.test.internal.performance.InternalDimensions; |
| import org.eclipse.test.internal.performance.data.Dim; |
| import org.eclipse.test.internal.performance.db.SQL; |
| |
| /** |
| * Specific implementation for massive database requests. |
| */ |
| public class SQL_Results extends SQL { |
| |
| private PreparedStatement queryBuildAllScenarios, |
| queryBuildScenarios, |
| queryScenarioSummaries, |
| queryAllComments, |
| queryScenariosBuilds, |
| queryScenarioDataPoints, |
| queryScenarioTimestampDataPoints, |
| queryScenarioBuildDataPoints, |
| queryDimScalars, |
| queryAllVariations; |
| |
| SQL_Results(Connection con) throws SQLException { |
| super(con); |
| // TODO Auto-generated constructor stub |
| } |
| |
| @Override |
| protected void dispose() throws SQLException { |
| super.dispose(); |
| if (this.queryBuildScenarios != null) |
| this.queryBuildScenarios.close(); |
| if (this.queryBuildAllScenarios != null) |
| this.queryBuildAllScenarios.close(); |
| if (this.queryScenarioSummaries != null) |
| this.queryScenarioSummaries.close(); |
| if (this.queryAllComments != null) |
| this.queryAllComments.close(); |
| if (this.queryScenariosBuilds != null) |
| this.queryScenariosBuilds.close(); |
| if (this.queryScenarioDataPoints != null) |
| this.queryScenarioDataPoints.close(); |
| if (this.queryDimScalars != null) |
| this.queryDimScalars.close(); |
| if (this.queryAllVariations != null) |
| this.queryAllVariations.close(); |
| } |
| |
| /** |
| * Get all comments from database |
| * |
| * @return A set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryAllComments() throws SQLException { |
| if (this.queryAllComments == null) |
| this.queryAllComments = this.fConnection.prepareStatement("select ID, KIND, TEXT from COMMENT"); //$NON-NLS-1$ |
| return this.queryAllComments.executeQuery(); |
| } |
| |
| /** |
| * Get all variations from database. |
| * |
| * @param configPattern The pattern for all the concerned configurations |
| * @return A set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryAllVariations(String configPattern) throws SQLException { |
| long start = System.currentTimeMillis(); |
| if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print("[SQL query (config pattern="+configPattern); //$NON-NLS-1$ |
| if (this.queryAllVariations == null) { |
| this.queryAllVariations = this.fConnection.prepareStatement("select KEYVALPAIRS from VARIATION where KEYVALPAIRS like ? order by KEYVALPAIRS"); //$NON-NLS-1$ |
| } |
| this.queryAllVariations.setString(1, "%"+configPattern+"%"); //$NON-NLS-1$ //$NON-NLS-2$ |
| ResultSet resultSet = this.queryAllVariations.executeQuery(); |
| if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print(")=" + (System.currentTimeMillis() - start) + "ms]"); //$NON-NLS-1$ //$NON-NLS-2$ |
| return resultSet; |
| } |
| |
| /** |
| * Query all scenarios corresponding to the default scenario pattern |
| * |
| * @param scenarioPattern The pattern for all the concerned scenarios |
| * @return Set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryBuildAllScenarios(String scenarioPattern) throws SQLException { |
| if (this.queryBuildAllScenarios == null) { |
| String statement = "select distinct SCENARIO.ID, SCENARIO.NAME , SCENARIO.SHORT_NAME from SCENARIO where " + //$NON-NLS-1$ |
| "SCENARIO.NAME LIKE ? " + //$NON-NLS-1$ |
| "order by SCENARIO.NAME"; //$NON-NLS-1$ |
| this.queryBuildAllScenarios = this.fConnection.prepareStatement(statement); |
| } |
| this.queryBuildAllScenarios.setString(1, scenarioPattern); |
| return this.queryBuildAllScenarios.executeQuery(); |
| } |
| |
| /** |
| * Query all scenarios corresponding to a given scenario pattern |
| * and for a specific build name. |
| * |
| * @param scenarioPattern The pattern for all the concerned scenarios |
| * @param buildName The name of the concerned build |
| * @return Set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryBuildScenarios(String scenarioPattern, String buildName) throws SQLException { |
| if (this.queryBuildScenarios == null) { |
| String statement = "select distinct SCENARIO.ID, SCENARIO.NAME , SCENARIO.SHORT_NAME from SCENARIO, SAMPLE, VARIATION where " + //$NON-NLS-1$ |
| "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ |
| "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ? " + //$NON-NLS-1$ |
| "order by SCENARIO.NAME"; //$NON-NLS-1$ |
| this.queryBuildScenarios = this.fConnection.prepareStatement(statement); |
| } |
| this.queryBuildScenarios.setString(1, "|build=" + buildName + '%'); //$NON-NLS-1$ |
| this.queryBuildScenarios.setString(2, scenarioPattern); |
| return this.queryBuildScenarios.executeQuery(); |
| } |
| |
| /** |
| * Query all scalars for a given data point. |
| * |
| * @param datapointId The id of the data point |
| * @return Set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryDimScalars(int datapointId) throws SQLException { |
| if (this.queryDimScalars == null) { |
| StringBuilder buffer = new StringBuilder("select DIM_ID, VALUE from SCALAR where "); //$NON-NLS-1$ |
| buffer.append("DATAPOINT_ID = ? and "); //$NON-NLS-1$ |
| Dim[] dimensions = DB_Results.getResultsDimensions(); |
| int length = dimensions.length; |
| for (int i=0; i<length; i++) { |
| if (i==0) { |
| buffer.append("("); |
| } else { |
| buffer.append(" or "); |
| } |
| buffer.append("DIM_ID = "); |
| buffer.append(dimensions[i].getId()); |
| } |
| buffer.append(") order by DIM_ID"); |
| this.queryDimScalars = this.fConnection.prepareStatement(buffer.toString()); |
| } |
| this.queryDimScalars.setInt(1, datapointId); |
| return this.queryDimScalars.executeQuery(); |
| } |
| |
| /** |
| * Get all data points for a given scenario and configuration. |
| * |
| * @param config The name of the concerned configuration |
| * @param scenarioID The id of the scenario |
| * @param lastBuildName Name of the last build on which data were stored locally |
| * @param lastBuildTime Date in ms of the last build on which data were stored locally |
| * @return A set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryScenarioTimestampDataPoints(String config, int scenarioID, String lastBuildName, long lastBuildTime) throws SQLException { |
| if (DB_Results.LOG) DB_Results.LOG_WRITER.starts(" + SQL query (config="+config+", scenario ID="+scenarioID+", build name="+lastBuildName); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ |
| if (this.queryScenarioTimestampDataPoints== null) { |
| String statement = "select DATAPOINT.ID, DATAPOINT.STEP, VARIATION.KEYVALPAIRS from SAMPLE, DATAPOINT, VARIATION where " + //$NON-NLS-1$ |
| "SAMPLE.SCENARIO_ID = ? and " + //$NON-NLS-1$ |
| "DATAPOINT.SAMPLE_ID = SAMPLE.ID and " + //$NON-NLS-1$ |
| "SAMPLE.STARTTIME > ? and " + //$NON-NLS-1$ |
| "SAMPLE.VARIATION_ID = VARIATION.ID " + //$NON-NLS-1$ |
| "ORDER BY DATAPOINT.ID, DATAPOINT.STEP"; //$NON-NLS-1$ |
| this.queryScenarioTimestampDataPoints = this.fConnection.prepareStatement(statement); |
| } |
| this.queryScenarioTimestampDataPoints.setInt(1, scenarioID); |
| // 10/29/2015, dw. Not sure why this timestamp was set 5 hours in future. |
| // UTC? Location of previous performance machines? |
| // But, should not be required, AFAIK. |
| // Timestamp timestamp = new Timestamp(lastBuildTime+(5*3600L*1000)); // create a time-stamp 5h after the given build time |
| Timestamp timestamp = new Timestamp(lastBuildTime); |
| this.queryScenarioTimestampDataPoints.setTimestamp(2, timestamp); |
| ResultSet resultSet = this.queryScenarioTimestampDataPoints.executeQuery(); |
| if (DB_Results.LOG) DB_Results.LOG_WRITER.ends(")"); //$NON-NLS-1$ |
| return resultSet; |
| } |
| ResultSet queryScenarioBuildDataPoints(String config, int scenarioID, String buildName) throws SQLException { |
| if (DB_Results.LOG) DB_Results.LOG_WRITER.starts(" + SQL query (config="+config+", scenario ID="+scenarioID+", build name="+buildName); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ |
| if (this.queryScenarioBuildDataPoints== null) { |
| String statement = "select DATAPOINT.ID, DATAPOINT.STEP, VARIATION.KEYVALPAIRS from SAMPLE, DATAPOINT, VARIATION where " + //$NON-NLS-1$ |
| "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ |
| "SAMPLE.SCENARIO_ID = ? and " + //$NON-NLS-1$ |
| "DATAPOINT.SAMPLE_ID = SAMPLE.ID " + //$NON-NLS-1$ |
| "ORDER BY DATAPOINT.ID, DATAPOINT.STEP"; //$NON-NLS-1$ |
| this.queryScenarioBuildDataPoints = this.fConnection.prepareStatement(statement); |
| } |
| this.queryScenarioBuildDataPoints.setString(1, "|build=" + buildName + '%'); //$NON-NLS-1$ |
| this.queryScenarioBuildDataPoints.setInt(2, scenarioID); |
| ResultSet resultSet = this.queryScenarioBuildDataPoints.executeQuery(); |
| if (DB_Results.LOG) DB_Results.LOG_WRITER.ends(")"); //$NON-NLS-1$ |
| return resultSet; |
| } |
| |
| /** |
| * Get all data points for a given scenario and configuration. |
| * |
| * @param config The name of the concerned configuration |
| * @param scenarioID The id of the scenario |
| * @return A set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryScenarioDataPoints(String config, int scenarioID) throws SQLException { |
| long start = System.currentTimeMillis(); |
| if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print("[SQL query (config="+config+", scenario ID="+scenarioID); //$NON-NLS-1$ //$NON-NLS-2$ |
| if (this.queryScenarioDataPoints== null) { |
| String statement = "select DATAPOINT.ID, DATAPOINT.STEP, VARIATION.KEYVALPAIRS from VARIATION, SAMPLE, DATAPOINT where " + //$NON-NLS-1$ |
| "VARIATION.KEYVALPAIRS like ? and SAMPLE.VARIATION_ID = VARIATION.ID and " + //$NON-NLS-1$ |
| "SAMPLE.SCENARIO_ID = ? and " + //$NON-NLS-1$ |
| "DATAPOINT.SAMPLE_ID = SAMPLE.ID " + //$NON-NLS-1$ |
| "ORDER BY DATAPOINT.ID, DATAPOINT.STEP"; //$NON-NLS-1$ |
| this.queryScenarioDataPoints = this.fConnection.prepareStatement(statement); |
| } |
| this.queryScenarioDataPoints.setString(1, "%"+config+"%"); //$NON-NLS-1$ //$NON-NLS-2$ |
| this.queryScenarioDataPoints.setInt(2, scenarioID); |
| ResultSet resultSet = this.queryScenarioDataPoints.executeQuery(); |
| if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print(")=" + (System.currentTimeMillis() - start) + "ms]"); //$NON-NLS-1$ //$NON-NLS-2$ |
| return resultSet; |
| } |
| |
| /** |
| * Query all summaries from database for a given scenario, |
| * configuration and builds. |
| * |
| * @param config The name of the concerned configuration |
| * @param scenarioID The id of the scenario |
| * @param builds The list of builds to get summaries. When <code>null</code> |
| * summaries for all DB builds will be read. |
| * |
| * @return Set of the query result |
| * @throws SQLException |
| */ |
| ResultSet queryScenarioSummaries(int scenarioID, String config, String[] builds) throws SQLException { |
| int length = builds==null ? 0 : builds.length; |
| String buildPattern; |
| switch (length) { |
| case 0: |
| buildPattern = "%"; //$NON-NLS-1$ |
| break; |
| case 1: |
| if (builds == null) { |
| throw new Error("Impossible for builds to be null at this point. Programming/Compiler error?"); |
| } |
| buildPattern = builds[0]; |
| break; |
| default: |
| if (builds == null) { |
| throw new RuntimeException("builds unexpectedly null at this point. Programming error?"); |
| } |
| StringBuilder buffer = new StringBuilder(); |
| loop: for (int idx=0; idx < builds[0].length(); idx++) { |
| char ch = builds[0].charAt(idx); |
| for (int i=1; i<length; i++) { |
| if (idx == builds[i].length()) { |
| break loop; |
| } |
| if (builds[i].charAt(idx) != ch) { |
| buffer.append('_'); |
| continue loop; |
| } |
| } |
| buffer.append(ch); |
| } |
| buffer.append("%"); //$NON-NLS-1$ |
| buildPattern = buffer.toString(); |
| break; |
| } |
| if (this.queryScenarioSummaries == null) { |
| this.queryScenarioSummaries= this.fConnection.prepareStatement("select KEYVALPAIRS , IS_GLOBAL, COMMENT_ID, DIM_ID from VARIATION, SUMMARYENTRY where " + //$NON-NLS-1$ |
| "KEYVALPAIRS like ? and " + //$NON-NLS-1$ |
| "VARIATION_ID = VARIATION.ID and " + //$NON-NLS-1$ |
| "SCENARIO_ID = ? and " + //$NON-NLS-1$ |
| "(DIM_ID = "+InternalDimensions.ELAPSED_PROCESS.getId()+" or DIM_ID = 0)" + //$NON-NLS-1$ //$NON-NLS-2$ |
| " order by VARIATION_ID, DIM_ID"); //$NON-NLS-1$ |
| } |
| this.queryScenarioSummaries.setString(1, "|build="+buildPattern+"||config="+ config + "||jvm=8.0|"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ |
| this.queryScenarioSummaries.setInt(2, scenarioID); |
| return this.queryScenarioSummaries.executeQuery(); |
| } |
| |
| } |
