|
1
11 package org.eclipse.test.internal.performance.db;
12
13 import java.io.BufferedOutputStream  ;
14 import java.io.FileNotFoundException ;
15 import java.io.FileOutputStream ;
16 import java.io.PrintStream ;
17 import java.sql.Connection ;
18 import java.sql.PreparedStatement ;
19 import java.sql.ResultSet ;
20 import java.sql.ResultSetMetaData ;
21 import java.sql.SQLException ;
22 import java.sql.Statement ;
23
24 import org.eclipse.test.internal.performance.PerformanceTestPlugin;
25 import org.eclipse.test.internal.performance.data.Dim;
26
27
28 public class DBHelpers {
29
30 private Connection fConnection;
31
32
33 public static void main(String [] args) throws SQLException {
34
35
37 DBHelpers db= new DBHelpers();
38
39 String outFile= null;
40 PrintStream ps= null;
42 if (outFile != null) {
43 try {
44 ps= new PrintStream (new BufferedOutputStream (new FileOutputStream (outFile)));
45 } catch (FileNotFoundException e) {
46 System.err.println("can't create output file"); }
48 }
49 if (ps == null)
50 ps= System.out;
51
52 long start= System.currentTimeMillis();
53
54
55 db.dumpSizes(ps);
56
60
64
69
70 System.out.println("time: " + ((System.currentTimeMillis()-start)/1000.0));
72 if (ps != System.out)
73 ps.close();
74 }
75
76 public DBHelpers() {
77 fConnection= DB.getConnection();
78 }
79
80 void renameVariation(String oldName, String newName) throws SQLException {
81 PreparedStatement update= fConnection.prepareStatement("update VARIATION set KEYVALPAIRS = ? where KEYVALPAIRS = ? "); update.setString(1, newName); update.setString(2, oldName); update.executeUpdate();
85 update.close();
86 }
87
88 void dumpSummaries(Variations variations, String scenarioPattern) {
89 SummaryEntry[] summries= DB.querySummaries(variations, scenarioPattern);
90 for (int i= 0; i < summries.length; i++)
91 System.out.println(summries[i]);
92 }
93
94 void count(PrintStream ps) throws SQLException {
95 PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SCALAR where DATAPOINT_ID not in (select DATAPOINT.ID from DATAPOINT)"); ResultSet set= stmt.executeQuery();
97 if (set.next())
98 ps.println("count: " + set.getInt(1)); set.close();
100 stmt.close();
101 }
102
103 void countDimension(PrintStream ps, Dim dim) throws SQLException {
104 PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SCALAR where DIM_ID = ?"); stmt.setInt(1, dim.getId());
106 ResultSet set= stmt.executeQuery();
107 if (set.next())
108 ps.println("dimension " + dim + ": " + set.getInt(1)); }
110
111 void countAllDimensions(PrintStream ps) throws SQLException {
112 PreparedStatement stmt= fConnection.prepareStatement("select distinct DIM_ID from SCALAR"); ResultSet set= stmt.executeQuery();
114 while (set.next()) {
115 Dim dimension= Dim.getDimension(set.getInt(1));
116 if (dimension != null)
117 countDimension(ps, dimension);
118 }
119 }
120
121 int countSamples(PrintStream ps, Variations v) throws SQLException {
122 PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SAMPLE, VARIATION where VARIATION.KEYVALPAIRS = ? and SAMPLE.VARIATION_ID = VARIATION.ID"); stmt.setString(1, v.toExactMatchString());
124 ResultSet set= stmt.executeQuery();
125 int n= 0;
126 if (set.next())
127 n= set.getInt(1);
128 ps.println("samples with variation " + v + ": " + n); return n;
130 }
131
132 void countDatapoints(PrintStream ps, Variations v) throws SQLException {
133 PreparedStatement stmt= fConnection.prepareStatement("select count(*) from DATAPOINT, SAMPLE, VARIATION where VARIATION.KEYVALPAIRS = ? and SAMPLE.VARIATION_ID = VARIATION.ID and DATAPOINT.SAMPLE_ID= SAMPLE.ID"); stmt.setString(1, v.toExactMatchString());
135 ResultSet set= stmt.executeQuery();
136 if (set.next())
137 ps.println("datapoints with variation " + v + ": " + set.getInt(1)); }
139
140 void countScalars(PrintStream ps, Variations v) throws SQLException {
141 PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SCALAR, DATAPOINT, SAMPLE, VARIATION where VARIATION.KEYVALPAIRS = ? and SAMPLE.VARIATION_ID = VARIATION.ID and DATAPOINT.SAMPLE_ID= SAMPLE.ID and DATAPOINT.ID = SCALAR.DATAPOINT_ID"); stmt.setString(1, v.toExactMatchString());
143 ResultSet set= stmt.executeQuery();
144 if (set.next())
145 ps.println("scalars with variation " + v + ": " + set.getInt(1)); }
147
148 void removeSamples(Variations v) throws SQLException {
149
150 boolean delete= true;
151
152 int n= countSamples(System.out, v);
153
154 int variation_id= 0;
155 PreparedStatement stmt= fConnection.prepareStatement("select ID from VARIATION where KEYVALPAIRS = ?"); stmt.setString(1, v.toExactMatchString());
157 ResultSet set= stmt.executeQuery();
158 if (set.next()) {
159 variation_id= set.getInt(1);
160 System.err.println("variation_id: " + variation_id); }
162
163 if (variation_id <= 0) {
164 System.err.println("nothing found for variation " + v); return;
166 }
167
168 PreparedStatement iterSamples= fConnection.prepareStatement("select SAMPLE.ID, SAMPLE.SCENARIO_ID from SAMPLE where SAMPLE.VARIATION_ID = ?"); PreparedStatement iterDatapoints= fConnection.prepareStatement("select DATAPOINT.ID from DATAPOINT where DATAPOINT.SAMPLE_ID = ?");
171 PreparedStatement deleteScalars= fConnection.prepareStatement("delete from SCALAR where DATAPOINT_ID = ?"); PreparedStatement deleteDatapoints= fConnection.prepareStatement("delete from DATAPOINT where SAMPLE_ID = ?"); PreparedStatement deleteSamples= fConnection.prepareStatement("delete from SAMPLE where SAMPLE.ID = ?"); PreparedStatement deleteScenario= fConnection.prepareStatement("delete from SCENARIO where SCENARIO.ID = ?");
176 ResultSet samples= null, datapoints= null, configs= null;
177 iterSamples.setInt(1, variation_id);
178 samples= iterSamples.executeQuery();
179 while (samples.next()) {
180 int sample_id= samples.getInt(1);
181 int scenario_id= samples.getInt(2);
182 System.out.print(n + ": sample(" + sample_id + "):"); iterDatapoints.setInt(1, sample_id);
184 datapoints= iterDatapoints.executeQuery();
185 int dps= 0;
186 while (datapoints.next()) {
187 int dp_id= datapoints.getInt(1);
188 if (delete) {
190 deleteScalars.setInt(1, dp_id);
191 try {
192 deleteScalars.executeUpdate();
193 fConnection.commit();
194 dps++;
195 } catch (SQLException e) {
196 System.err.println("removing scalars: " + e); }
198 }
199 }
200 System.out.println(" dps: " + dps); if (delete) {
202 deleteDatapoints.setInt(1, sample_id);
203 try {
204 deleteDatapoints.executeUpdate();
205 fConnection.commit();
206 } catch (SQLException e1) {
207 System.err.println("removing datapoints: " + e1); }
209
210 deleteSamples.setInt(1, sample_id);
211 try {
212 deleteSamples.executeUpdate();
213 fConnection.commit();
214 } catch (SQLException e) {
215 System.err.println("removing sample: " + e); }
217
218 deleteScenario.setInt(1, scenario_id);
219 try {
220 deleteScenario.executeUpdate();
221 fConnection.commit();
222 } catch (SQLException e) {
223 }
225 }
226 n--;
227 }
228 if (delete) {
229 PreparedStatement deleteSummaries= fConnection.prepareStatement("delete from SUMMARYENTRY where VARIATION_ID = ?"); deleteSummaries.setInt(1, variation_id);
231 deleteSummaries.executeUpdate();
232 deleteSummaries.close();
233
234 PreparedStatement deleteVariation= fConnection.prepareStatement("delete from VARIATION where ID = ?"); deleteVariation.setInt(1, variation_id);
236 try {
237 deleteVariation.executeUpdate();
238 } catch (SQLException e) {
239 System.err.println("removing variation: " + e); }
241 deleteVariation.close();
242 }
243
244 if (configs != null) configs.close();
245 if (samples != null) samples.close();
246 if (datapoints != null) datapoints.close();
247
248 if (iterSamples != null) iterSamples.close();
249 if (iterDatapoints != null) iterDatapoints.close();
250
251 if (deleteSamples != null) deleteSamples.close();
252 if (deleteScenario != null) deleteScenario.close();
253 if (deleteScalars != null) deleteScalars.close();
254 if (deleteDatapoints != null) deleteDatapoints.close();
255 }
256
257 void countSamplesWithNullVariations() throws SQLException {
258 Statement stmt= fConnection.createStatement();
259 ResultSet rs= stmt.executeQuery("select count(*) from SAMPLE where SAMPLE.VARIATION_ID is null"); while (rs.next()) {
261 int config_id= rs.getInt(1);
262 System.out.println("samples with NULL variation: " + config_id); }
264 rs.close();
265 stmt.close();
266 }
267
268 void removeDimension(Dim dim) throws SQLException {
269 PreparedStatement q= fConnection.prepareStatement("delete from SCALAR where SCALAR.DIM_ID = ?"); q.setInt(1, dim.getId());
271 q.executeUpdate();
272 q.close();
273 }
274
275 void dumpScenarios(PrintStream ps, String pattern) throws SQLException {
276 PreparedStatement stmt= fConnection.prepareStatement("select NAME from SCENARIO where NAME like ? order by NAME"); stmt.setString(1, pattern);
278 ResultSet rs= stmt.executeQuery();
279 while (rs.next())
280 ps.println(rs.getString(1));
281 rs.close();
282 stmt.close();
283 }
284
285 void dumpSizes(PrintStream ps) throws SQLException {
286 if (fConnection == null)
287 return;
288 Statement stmt= fConnection.createStatement();
289 try {
290 ResultSet rs= stmt.executeQuery("SELECT sys.systables.tablename FROM sys.systables WHERE sys.systables.tablename NOT LIKE 'SYS%' "); while (rs.next())
292 dumpSize(ps, rs.getString(1));
293 rs.close();
294 } finally {
295 stmt.close();
296 }
297 }
298
299 void dumpSize(PrintStream ps, String table) throws SQLException {
300 Statement stmt= fConnection.createStatement();
301 ResultSet rs= stmt.executeQuery("select Count(*) from " + table); if (rs.next())
303 ps.println(table + ": " + rs.getInt(1)); rs.close();
305 stmt.close();
306 }
307
308 public void dumpAll(PrintStream ps, int maxRow) throws SQLException {
309 if (fConnection == null)
310 return;
311 if (maxRow < 0)
312 maxRow= 1000000;
313 Statement stmt= fConnection.createStatement();
314 try {
315 ResultSet rs= stmt.executeQuery("select SYS.SYSTABLES.TABLENAME from SYS.SYSTABLES where SYS.SYSTABLES.TABLENAME not like 'SYS%' "); while (rs.next()) {
317 dumpTable(ps, rs.getString(1), maxRow);
318 ps.println();
319 }
320 rs.close();
321 } finally {
322 stmt.close();
323 }
324 }
325
326 void dumpTable(PrintStream ps, String tableName, int maxRow) throws SQLException {
327 ps.print(tableName + '(');
328 Statement select= fConnection.createStatement();
329 ResultSet result= select.executeQuery("select * from " + tableName); ResultSetMetaData metaData= result.getMetaData();
331 int n= metaData.getColumnCount();
332 for (int i= 0; i < n; i++) {
333 ps.print(metaData.getColumnLabel(i+1));
334 if (i < n-1)
335 ps.print(", "); }
337 ps.println("):"); for (int r= 0; result.next() && r < maxRow; r++) {
339 for (int i= 0; i < n; i++)
340 ps.print(' ' + result.getString(i+1));
341 ps.println();
342 }
343 select.close();
344 }
345
346 void view(PrintStream ps, Variations v, String scenarioPattern) throws SQLException {
347 Scenario[] scenarios= DB.queryScenarios(v, scenarioPattern, PerformanceTestPlugin.BUILD, null);
348 ps.println(scenarios.length + " Scenarios"); ps.println();
350 for (int s= 0; s < scenarios.length; s++)
351 scenarios[s].dump(ps, PerformanceTestPlugin.BUILD);
352 }
353 }
354 |
Popular Tags |
Java API By Example, From Geeks To Geeks.