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 *******************************************************************************/
11package org.eclipse.test.internal.performance.results.db;
12
13import java.sql.Connection;
14import java.sql.PreparedStatement;
15import java.sql.ResultSet;
16import java.sql.SQLException;
17import java.sql.Timestamp;
18
19import org.eclipse.test.internal.performance.InternalDimensions;
20import org.eclipse.test.internal.performance.data.Dim;
21import org.eclipse.test.internal.performance.db.SQL;
22
23/**
24 * Specific implementation for massive database requests.
25 */
26public class SQL_Results extends SQL {
27
28private PreparedStatement queryBuildAllScenarios,
29	queryBuildScenarios,
30	queryScenarioSummaries,
31	queryAllComments,
32	queryScenariosBuilds,
33	queryScenarioDataPoints,
34	queryScenarioTimestampDataPoints,
35	queryScenarioBuildDataPoints,
36	queryDimScalars,
37	queryAllVariations;
38
39SQL_Results(Connection con) throws SQLException {
40	    super(con);
41	    // TODO Auto-generated constructor stub
42    }
43
44protected 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 */
70ResultSet 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 */
83ResultSet 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 */
102ResultSet 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 */
122ResultSet 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 */
142ResultSet 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 */
174ResultSet 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}
192ResultSet 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 */
217ResultSet 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 */
247ResultSet 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