1 /******************************************************************************* 2 * Copyright (c) 2000, 2009 IBM Corporation and others. 3 * All rights reserved. This program and the accompanying materials 4 * are made available under the terms of the Eclipse Public License v1.0 5 * which accompanies this distribution, and is available at 6 * http://www.eclipse.org/legal/epl-v10.html 7 * 8 * Contributors: 9 * IBM Corporation - initial API and implementation 10 *******************************************************************************/ 11 package org.eclipse.test.internal.performance.results.db; 12 13 import java.sql.Connection; 14 import java.sql.PreparedStatement; 15 import java.sql.ResultSet; 16 import java.sql.SQLException; 17 import java.sql.Timestamp; 18 19 import org.eclipse.test.internal.performance.InternalDimensions; 20 import org.eclipse.test.internal.performance.data.Dim; 21 import org.eclipse.test.internal.performance.db.SQL; 22 23 /** 24 * Specific implementation for massive database requests. 25 */ 26 public class SQL_Results extends SQL { 27 28 private PreparedStatement queryBuildAllScenarios, 29 queryBuildScenarios, 30 queryScenarioSummaries, 31 queryAllComments, 32 queryScenariosBuilds, 33 queryScenarioDataPoints, 34 queryScenarioTimestampDataPoints, 35 queryScenarioBuildDataPoints, 36 queryDimScalars, 37 queryAllVariations; 38 39 SQL_Results(Connection con) throws SQLException { 40 super(con); 41 // TODO Auto-generated constructor stub 42 } 43 44 protected void dispose() throws SQLException { 45 super.dispose(); 46 if (this.queryBuildScenarios != null) 47 this.queryBuildScenarios.close(); 48 if (this.queryBuildAllScenarios != null) 49 this.queryBuildAllScenarios.close(); 50 if (this.queryScenarioSummaries != null) 51 this.queryScenarioSummaries.close(); 52 if (this.queryAllComments != null) 53 this.queryAllComments.close(); 54 if (this.queryScenariosBuilds != null) 55 this.queryScenariosBuilds.close(); 56 if (this.queryScenarioDataPoints != null) 57 this.queryScenarioDataPoints.close(); 58 if (this.queryDimScalars != null) 59 this.queryDimScalars.close(); 60 if (this.queryAllVariations != null) 61 this.queryAllVariations.close(); 62 } 63 64 /** 65 * Get all comments from database 66 * 67 * @return A set of the query result 68 * @throws SQLException 69 */ 70 ResultSet queryAllComments() throws SQLException { 71 if (this.queryAllComments == null) 72 this.queryAllComments = this.fConnection.prepareStatement("select ID, KIND, TEXT from COMMENT"); //$NON-NLS-1$ 73 return this.queryAllComments.executeQuery(); 74 } 75 76 /** 77 * Get all variations from database. 78 * 79 * @param configPattern The pattern for all the concerned configurations 80 * @return A set of the query result 81 * @throws SQLException 82 */ 83 ResultSet queryAllVariations(String configPattern) throws SQLException { 84 long start = System.currentTimeMillis(); 85 if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print("[SQL query (config pattern="+configPattern); //$NON-NLS-1$ 86 if (this.queryAllVariations == null) { 87 this.queryAllVariations = this.fConnection.prepareStatement("select KEYVALPAIRS from VARIATION where KEYVALPAIRS like ? order by KEYVALPAIRS"); //$NON-NLS-1$ 88 } 89 this.queryAllVariations.setString(1, "%"+configPattern+"%"); //$NON-NLS-1$ //$NON-NLS-2$ 90 ResultSet resultSet = this.queryAllVariations.executeQuery(); 91 if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print(")=" + (System.currentTimeMillis() - start) + "ms]"); //$NON-NLS-1$ //$NON-NLS-2$ 92 return resultSet; 93 } 94 95 /** 96 * Query all scenarios corresponding to the default scenario pattern 97 * 98 * @param scenarioPattern The pattern for all the concerned scenarios 99 * @return Set of the query result 100 * @throws SQLException 101 */ 102 ResultSet queryBuildAllScenarios(String scenarioPattern) throws SQLException { 103 if (this.queryBuildAllScenarios == null) { 104 String statement = "select distinct SCENARIO.ID, SCENARIO.NAME , SCENARIO.SHORT_NAME from SCENARIO where " + //$NON-NLS-1$ 105 "SCENARIO.NAME LIKE ? " + //$NON-NLS-1$ 106 "order by SCENARIO.NAME"; //$NON-NLS-1$ 107 this.queryBuildAllScenarios = this.fConnection.prepareStatement(statement); 108 } 109 this.queryBuildAllScenarios.setString(1, scenarioPattern); 110 return this.queryBuildAllScenarios.executeQuery(); 111 } 112 113 /** 114 * Query all scenarios corresponding to a given scenario pattern 115 * and for a specific build name. 116 * 117 * @param scenarioPattern The pattern for all the concerned scenarios 118 * @param buildName The name of the concerned build 119 * @return Set of the query result 120 * @throws SQLException 121 */ 122 ResultSet queryBuildScenarios(String scenarioPattern, String buildName) throws SQLException { 123 if (this.queryBuildScenarios == null) { 124 String statement = "select distinct SCENARIO.ID, SCENARIO.NAME , SCENARIO.SHORT_NAME from SCENARIO, SAMPLE, VARIATION where " + //$NON-NLS-1$ 125 "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ 126 "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ? " + //$NON-NLS-1$ 127 "order by SCENARIO.NAME"; //$NON-NLS-1$ 128 this.queryBuildScenarios = this.fConnection.prepareStatement(statement); 129 } 130 this.queryBuildScenarios.setString(1, "|build=" + buildName + '%'); //$NON-NLS-1$ 131 this.queryBuildScenarios.setString(2, scenarioPattern); 132 return this.queryBuildScenarios.executeQuery(); 133 } 134 135 /** 136 * Query all scalars for a given data point. 137 * 138 * @param datapointId The id of the data point 139 * @return Set of the query result 140 * @throws SQLException 141 */ 142 ResultSet queryDimScalars(int datapointId) throws SQLException { 143 if (this.queryDimScalars == null) { 144 StringBuffer buffer = new StringBuffer("select DIM_ID, VALUE from SCALAR where "); //$NON-NLS-1$ 145 buffer.append("DATAPOINT_ID = ? and "); //$NON-NLS-1$ 146 Dim[] dimensions = DB_Results.getResultsDimensions(); 147 int length = dimensions.length; 148 for (int i=0; i<length; i++) { 149 if (i==0) { 150 buffer.append("("); 151 } else { 152 buffer.append(" or "); 153 } 154 buffer.append("DIM_ID = "); 155 buffer.append(dimensions[i].getId()); 156 } 157 buffer.append(") order by DIM_ID"); 158 this.queryDimScalars = this.fConnection.prepareStatement(buffer.toString()); 159 } 160 this.queryDimScalars.setInt(1, datapointId); 161 return this.queryDimScalars.executeQuery(); 162 } 163 164 /** 165 * Get all data points for a given scenario and configuration. 166 * 167 * @param config The name of the concerned configuration 168 * @param scenarioID The id of the scenario 169 * @param lastBuildName Name of the last build on which data were stored locally 170 * @param lastBuildTime Date in ms of the last build on which data were stored locally 171 * @return A set of the query result 172 * @throws SQLException 173 */ 174 ResultSet queryScenarioTimestampDataPoints(String config, int scenarioID, String lastBuildName, long lastBuildTime) throws SQLException { 175 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$ 176 if (this.queryScenarioTimestampDataPoints== null) { 177 String statement = "select DATAPOINT.ID, DATAPOINT.STEP, VARIATION.KEYVALPAIRS from SAMPLE, DATAPOINT, VARIATION where " + //$NON-NLS-1$ 178 "SAMPLE.SCENARIO_ID = ? and " + //$NON-NLS-1$ 179 "DATAPOINT.SAMPLE_ID = SAMPLE.ID and " + //$NON-NLS-1$ 180 "SAMPLE.STARTTIME > ? and " + //$NON-NLS-1$ 181 "SAMPLE.VARIATION_ID = VARIATION.ID " + //$NON-NLS-1$ 182 "ORDER BY DATAPOINT.ID, DATAPOINT.STEP"; //$NON-NLS-1$ 183 this.queryScenarioTimestampDataPoints = this.fConnection.prepareStatement(statement); 184 } 185 this.queryScenarioTimestampDataPoints.setInt(1, scenarioID); 186 Timestamp timestamp = new Timestamp(lastBuildTime+(5*3600L*1000)); // create a time-stamp 5h after the given build time 187 this.queryScenarioTimestampDataPoints.setTimestamp(2, timestamp); 188 ResultSet resultSet = this.queryScenarioTimestampDataPoints.executeQuery(); 189 if (DB_Results.LOG) DB_Results.LOG_WRITER.ends(")"); //$NON-NLS-1$ 190 return resultSet; 191 } 192 ResultSet queryScenarioBuildDataPoints(String config, int scenarioID, String buildName) throws SQLException { 193 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$ 194 if (this.queryScenarioBuildDataPoints== null) { 195 String statement = "select DATAPOINT.ID, DATAPOINT.STEP, VARIATION.KEYVALPAIRS from SAMPLE, DATAPOINT, VARIATION where " + //$NON-NLS-1$ 196 "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ 197 "SAMPLE.SCENARIO_ID = ? and " + //$NON-NLS-1$ 198 "DATAPOINT.SAMPLE_ID = SAMPLE.ID " + //$NON-NLS-1$ 199 "ORDER BY DATAPOINT.ID, DATAPOINT.STEP"; //$NON-NLS-1$ 200 this.queryScenarioBuildDataPoints = this.fConnection.prepareStatement(statement); 201 } 202 this.queryScenarioBuildDataPoints.setString(1, "|build=" + buildName + '%'); //$NON-NLS-1$ 203 this.queryScenarioBuildDataPoints.setInt(2, scenarioID); 204 ResultSet resultSet = this.queryScenarioBuildDataPoints.executeQuery(); 205 if (DB_Results.LOG) DB_Results.LOG_WRITER.ends(")"); //$NON-NLS-1$ 206 return resultSet; 207 } 208 209 /** 210 * Get all data points for a given scenario and configuration. 211 * 212 * @param config The name of the concerned configuration 213 * @param scenarioID The id of the scenario 214 * @return A set of the query result 215 * @throws SQLException 216 */ 217 ResultSet queryScenarioDataPoints(String config, int scenarioID) throws SQLException { 218 long start = System.currentTimeMillis(); 219 if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print("[SQL query (config="+config+", scenario ID="+scenarioID); //$NON-NLS-1$ //$NON-NLS-2$ 220 if (this.queryScenarioDataPoints== null) { 221 String statement = "select DATAPOINT.ID, DATAPOINT.STEP, VARIATION.KEYVALPAIRS from VARIATION, SAMPLE, DATAPOINT where " + //$NON-NLS-1$ 222 "VARIATION.KEYVALPAIRS like ? and SAMPLE.VARIATION_ID = VARIATION.ID and " + //$NON-NLS-1$ 223 "SAMPLE.SCENARIO_ID = ? and " + //$NON-NLS-1$ 224 "DATAPOINT.SAMPLE_ID = SAMPLE.ID " + //$NON-NLS-1$ 225 "ORDER BY DATAPOINT.ID, DATAPOINT.STEP"; //$NON-NLS-1$ 226 this.queryScenarioDataPoints = this.fConnection.prepareStatement(statement); 227 } 228 this.queryScenarioDataPoints.setString(1, "%"+config+"%"); //$NON-NLS-1$ //$NON-NLS-2$ 229 this.queryScenarioDataPoints.setInt(2, scenarioID); 230 ResultSet resultSet = this.queryScenarioDataPoints.executeQuery(); 231 if (DB_Results.DEBUG) DB_Results.DEBUG_WRITER.print(")=" + (System.currentTimeMillis() - start) + "ms]"); //$NON-NLS-1$ //$NON-NLS-2$ 232 return resultSet; 233 } 234 235 /** 236 * Query all summaries from database for a given scenario, 237 * configuration and builds. 238 * 239 * @param config The name of the concerned configuration 240 * @param scenarioID The id of the scenario 241 * @param builds The list of builds to get summaries. When <code>null</code> 242 * summaries for all DB builds will be read. 243 * 244 * @return Set of the query result 245 * @throws SQLException 246 */ 247 ResultSet queryScenarioSummaries(int scenarioID, String config, String[] builds) throws SQLException { 248 int length = builds==null ? 0 : builds.length; 249 String buildPattern; 250 switch (length) { 251 case 0: 252 buildPattern = "%"; //$NON-NLS-1$ 253 break; 254 case 1: 255 buildPattern = builds[0]; 256 break; 257 default: 258 StringBuffer buffer = new StringBuffer(); 259 loop: for (int idx=0; idx < builds[0].length(); idx++) { 260 char ch = builds[0].charAt(idx); 261 for (int i=1; i<length; i++) { 262 if (idx == builds[i].length()) { 263 break loop; 264 } 265 if (builds[i].charAt(idx) != ch) { 266 buffer.append('_'); 267 continue loop; 268 } 269 } 270 buffer.append(ch); 271 } 272 buffer.append("%"); //$NON-NLS-1$ 273 buildPattern = buffer.toString(); 274 break; 275 } 276 if (this.queryScenarioSummaries == null) { 277 this.queryScenarioSummaries= this.fConnection.prepareStatement("select KEYVALPAIRS , IS_GLOBAL, COMMENT_ID, DIM_ID from VARIATION, SUMMARYENTRY where " + //$NON-NLS-1$ 278 "KEYVALPAIRS like ? and " + //$NON-NLS-1$ 279 "VARIATION_ID = VARIATION.ID and " + //$NON-NLS-1$ 280 "SCENARIO_ID = ? and " + //$NON-NLS-1$ 281 "(DIM_ID = "+InternalDimensions.ELAPSED_PROCESS.getId()+" or DIM_ID = 0)" + //$NON-NLS-1$ //$NON-NLS-2$ 282 " order by VARIATION_ID, DIM_ID"); //$NON-NLS-1$ 283 } 284 this.queryScenarioSummaries.setString(1, "|build="+buildPattern+"||config="+ config + "||jvm=sun|"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 285 this.queryScenarioSummaries.setInt(2, scenarioID); 286 return this.queryScenarioSummaries.executeQuery(); 287 } 288 289 } 290
