|
1
11 package org.eclipse.test.internal.performance.db;
12
13 import java.math.BigDecimal  ;
14 import java.sql.Connection ;
15 import java.sql.PreparedStatement ;
16 import java.sql.ResultSet ;
17 import java.sql.SQLException ;
18 import java.sql.Statement ;
19 import java.sql.Timestamp ;
20
21
24 public class SQL {
25
26 private boolean fCompatibility= false;
27
28 private Connection fConnection;
29
30 private PreparedStatement fInsertVariation, fInsertScenario, fInsertSample, fInsertDataPoint, fInsertScalar;
31 private PreparedStatement fQueryComment, fInsertComment, fQueryComment2;
32 private PreparedStatement fQueryVariation, fQueryVariations, fQueryScenario, fQueryAllScenarios, fQueryDatapoints,
33 fQueryScalars;
34 private PreparedStatement fInsertSummaryEntry, fUpdateScenarioShortName, fQuerySummaryEntry, fQueryGlobalSummaryEntries,
35 fQuerySummaryEntries;
36 private PreparedStatement fInsertFailure, fQueryFailure;
37
38 SQL(Connection con) throws SQLException {
39 fConnection= con;
40
41 boolean needsUpgrade= true;
42 boolean needsInitialization= true;
43 boolean needsFailures= true;
44 boolean needsComments= true;
45
46 Statement statement= fConnection.createStatement();
47 ResultSet rs= statement.executeQuery("select SYS.SYSTABLES.TABLENAME from SYS.SYSTABLES where SYS.SYSTABLES.TABLENAME not like 'SYS%'"); while (rs.next()) {
49 String tablename= rs.getString(1);
50 if ("SUMMARYENTRY".equals(tablename)) needsUpgrade= false;
52 else if ("CONFIG_ORG".equals(tablename)) fCompatibility= true;
54 else if ("VARIATION".equals(tablename)) needsInitialization= false;
56 else if ("FAILURE".equals(tablename)) needsFailures= false;
58 else if ("COMMENT".equals(tablename)) needsComments= false;
60 }
61 if (!fCompatibility) {
62 rs= statement.executeQuery("select count(*) from SYS.SYSTABLES, SYS.SYSCOLUMNS where SYS.SYSTABLES.TABLENAME = 'SAMPLE' and " + "SYS.SYSTABLES.TABLEID = SYS.SYSCOLUMNS.REFERENCEID and SYS.SYSCOLUMNS.COLUMNNAME = 'CONFIG_ID' "); if (rs.next() && rs.getInt(1) == 1)
66 fCompatibility= true;
67 }
68
69 if (needsInitialization)
70 initialize();
71 else {
72 if (needsUpgrade)
73 upgradeDB();
74 else if (needsFailures)
75 addFailureTable();
76 if (needsComments)
77 addCommentTable();
78 }
79 }
80
81 public void dispose() throws SQLException {
82 if (fInsertVariation != null)
83 fInsertVariation.close();
84 if (fInsertScenario != null)
85 fInsertScenario.close();
86 if (fInsertSample != null)
87 fInsertSample.close();
88 if (fInsertDataPoint != null)
89 fInsertDataPoint.close();
90 if (fInsertScalar != null)
91 fInsertScalar.close();
92 if (fInsertSummaryEntry != null)
93 fInsertSummaryEntry.close();
94 if (fInsertFailure != null)
95 fInsertFailure.close();
96 if (fInsertComment != null)
97 fInsertComment.close();
98 if (fUpdateScenarioShortName != null)
99 fUpdateScenarioShortName.close();
100 if (fQueryDatapoints != null)
101 fQueryDatapoints.close();
102 if (fQueryScalars != null)
103 fQueryScalars.close();
104 if (fQueryVariation != null)
105 fQueryVariation.close();
106 if (fQueryScenario != null)
107 fQueryScenario.close();
108 if (fQueryAllScenarios != null)
109 fQueryAllScenarios.close();
110 if (fQueryVariations != null)
111 fQueryVariations.close();
112 if (fQueryGlobalSummaryEntries != null)
113 fQueryGlobalSummaryEntries.close();
114 if (fQuerySummaryEntries != null)
115 fQuerySummaryEntries.close();
116 if (fQueryFailure != null)
117 fQueryFailure.close();
118 if (fQueryComment != null)
119 fQueryComment.close();
120 if (fQueryComment2 != null)
121 fQueryComment2.close();
122 }
123
124 private void initialize() throws SQLException {
125 Statement stmt= null;
126 try {
127 stmt= fConnection.createStatement();
128 stmt.executeUpdate("create table VARIATION (" + "ID int unique not null GENERATED ALWAYS AS IDENTITY," + "KEYVALPAIRS varchar(10000) not null " + ")" );
133 stmt.executeUpdate("create table SCENARIO (" + "ID int unique not null GENERATED ALWAYS AS IDENTITY," + "NAME varchar(256) not null," + "SHORT_NAME varchar(40)" + ")" );
139 stmt.executeUpdate("create table SAMPLE (" + "ID int not null GENERATED ALWAYS AS IDENTITY," + "VARIATION_ID int not null," + "SCENARIO_ID int not null," + "STARTTIME timestamp" + ")" );
146 stmt.executeUpdate("create table DATAPOINT (" + "ID int not null GENERATED ALWAYS AS IDENTITY," + "SAMPLE_ID int not null," + "SEQ int," + "STEP int" + ")" );
153 stmt.executeUpdate("create table SCALAR (" + "DATAPOINT_ID int not null," + "DIM_ID int not null," + "VALUE bigint" + ")" );
159 stmt.executeUpdate("create table SUMMARYENTRY (" + "VARIATION_ID int not null," + "SCENARIO_ID int not null," + "DIM_ID int not null," + "IS_GLOBAL smallint not null," + "COMMENT_ID int not null" + ")" );
167 stmt.executeUpdate("create table FAILURE (" + "VARIATION_ID int not null," + "SCENARIO_ID int not null," + "MESSAGE varchar(1000) not null" + ")" );
173 stmt.executeUpdate("create table COMMENT (" + "ID int unique not null GENERATED ALWAYS AS IDENTITY," + "KIND int not null," + "TEXT varchar(400) not null" + ")" );
179
180 stmt.executeUpdate("alter table VARIATION add constraint VA_KVP primary key (KEYVALPAIRS)"); stmt.executeUpdate("alter table SCENARIO add constraint SC_NAME primary key (NAME)"); stmt.executeUpdate("alter table SAMPLE add constraint SA_ID primary key (ID)"); stmt.executeUpdate("alter table DATAPOINT add constraint DP_ID primary key (ID)");
186 stmt.executeUpdate("alter table SAMPLE add constraint SAMPLE_CONSTRAINT " + "foreign key (VARIATION_ID) references VARIATION (ID)"); stmt.executeUpdate("alter table SAMPLE add constraint SAMPLE_CONSTRAINT2 " + "foreign key (SCENARIO_ID) references SCENARIO (ID)"); stmt.executeUpdate("alter table DATAPOINT add constraint DP_CONSTRAINT " + "foreign key (SAMPLE_ID) references SAMPLE (ID)"); stmt.executeUpdate("alter table SCALAR add constraint SCALAR_CONSTRAINT " + "foreign key (DATAPOINT_ID) references DATAPOINT (ID)");
196 stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT " + "foreign key (VARIATION_ID) references VARIATION (ID)"); stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT2 " + "foreign key (SCENARIO_ID) references SCENARIO (ID)");
201 stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT " + "foreign key (VARIATION_ID) references VARIATION (ID)"); stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT2 " + "foreign key (SCENARIO_ID) references SCENARIO (ID)");
206 fConnection.commit();
207
208 } finally {
209 if (stmt != null)
210 stmt.close();
211 }
212 }
213
214 private void upgradeDB() throws SQLException {
215 Statement stmt= null;
216 try {
217 stmt= fConnection.createStatement();
218
219 stmt.executeUpdate("create table SUMMARYENTRY (" + "VARIATION_ID int not null," + "SCENARIO_ID int not null," + "DIM_ID int not null," + "IS_GLOBAL smallint not null," + "COMMENT_ID int not null" + ")" );
227 stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT " + "foreign key (VARIATION_ID) references VARIATION (ID)"); stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT2 " + "foreign key (SCENARIO_ID) references SCENARIO (ID)");
232 stmt.executeUpdate("alter table SCENARIO add column SHORT_NAME varchar(40)");
234 fConnection.commit();
235
236 } finally {
237 if (stmt != null)
238 stmt.close();
239 }
240 }
241
242 private void addCommentTable() throws SQLException {
243 Statement stmt= null;
244 try {
245 stmt= fConnection.createStatement();
246
247 stmt.executeUpdate("create table COMMENT (" + "ID int unique not null GENERATED ALWAYS AS IDENTITY," + "KIND int not null," + "TEXT varchar(400) not null" + ")" );
253
254 stmt.executeUpdate("alter table SUMMARYENTRY add column COMMENT_ID int not null default 0");
256 fConnection.commit();
257
258 } finally {
259 if (stmt != null)
260 stmt.close();
261 }
262 }
263
264 private void addFailureTable() throws SQLException {
265 Statement stmt= null;
266 try {
267 stmt= fConnection.createStatement();
268
269 stmt.executeUpdate("create table FAILURE (" + "VARIATION_ID int not null," + "SCENARIO_ID int not null," + "MESSAGE varchar(1000) not null" + ")" );
275
276 stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT " + "foreign key (VARIATION_ID) references VARIATION (ID)"); stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT2 " + "foreign key (SCENARIO_ID) references SCENARIO (ID)");
281 fConnection.commit();
282
283 } finally {
284 if (stmt != null)
285 stmt.close();
286 }
287 }
288
289 static int create(PreparedStatement stmt) throws SQLException {
290 stmt.executeUpdate();
291 ResultSet rs= stmt.getGeneratedKeys();
292 if (rs != null) {
293 try {
294 if (rs.next()) {
295 BigDecimal idColVar= rs.getBigDecimal(1);
296 return idColVar.intValue();
297 }
298 } finally {
299 rs.close();
300 }
301 }
302 return 0;
303 }
304
305 int getScenario(String scenarioPattern) throws SQLException {
306 if (fQueryScenario == null)
307 fQueryScenario= fConnection.prepareStatement("select ID from SCENARIO where NAME = ?"); fQueryScenario.setString(1, scenarioPattern);
309 ResultSet result= fQueryScenario.executeQuery();
310 while (result.next())
311 return result.getInt(1);
312
313 if (fInsertScenario == null)
314 fInsertScenario= fConnection.prepareStatement("insert into SCENARIO (NAME) values (?)", Statement.RETURN_GENERATED_KEYS); fInsertScenario.setString(1, scenarioPattern);
316 return create(fInsertScenario);
317 }
318
319 int getVariations(Variations variations) throws SQLException {
320 if (fQueryVariation == null)
321 fQueryVariation= fConnection.prepareStatement("select ID from VARIATION where KEYVALPAIRS = ?"); String exactMatchString= variations.toExactMatchString();
323 fQueryVariation.setString(1, exactMatchString);
324 ResultSet result= fQueryVariation.executeQuery();
325 while (result.next())
326 return result.getInt(1);
327
328 if (fInsertVariation == null)
329 fInsertVariation= fConnection.prepareStatement("insert into VARIATION (KEYVALPAIRS) values (?)", Statement.RETURN_GENERATED_KEYS); fInsertVariation.setString(1, exactMatchString);
331 return create(fInsertVariation);
332 }
333
334 int createSample(int variation_id, int scenario_id, Timestamp starttime) throws SQLException {
335 if (fInsertSample == null) {
336 if (fCompatibility) {
337 fInsertSample= fConnection.prepareStatement("insert into SAMPLE (VARIATION_ID, SCENARIO_ID, STARTTIME, CONFIG_ID) values (?, ?, ?, 0)", Statement.RETURN_GENERATED_KEYS); } else {
341 fInsertSample= fConnection.prepareStatement("insert into SAMPLE (VARIATION_ID, SCENARIO_ID, STARTTIME) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); }
343 }
344 fInsertSample.setInt(1, variation_id);
345 fInsertSample.setInt(2, scenario_id);
346 fInsertSample.setTimestamp(3, starttime);
347 return create(fInsertSample);
348 }
349
350 int createDataPoint(int sample_id, int seq, int step) throws SQLException {
351 if (fInsertDataPoint == null)
352 fInsertDataPoint= fConnection.prepareStatement("insert into DATAPOINT (SAMPLE_ID, SEQ, STEP) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); fInsertDataPoint.setInt(1, sample_id);
354 fInsertDataPoint.setInt(2, seq);
355 fInsertDataPoint.setInt(3, step);
356 return create(fInsertDataPoint);
357 }
358
359 void insertScalar(int datapoint_id, int dim_id, long value) throws SQLException {
360 if (fInsertScalar == null)
361 fInsertScalar= fConnection.prepareStatement("insert into SCALAR values (?, ?, ?)"); fInsertScalar.setInt(1, datapoint_id);
363 fInsertScalar.setInt(2, dim_id);
364 fInsertScalar.setLong(3, value);
365 fInsertScalar.executeUpdate();
366 }
367
368 ResultSet queryDataPoints(Variations variations, String scenarioName) throws SQLException {
369 if (fQueryDatapoints == null)
370 fQueryDatapoints= fConnection.prepareStatement("select DATAPOINT.ID, DATAPOINT.STEP from VARIATION, SCENARIO, SAMPLE, DATAPOINT " + "where " + "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS = ? and " + "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ? and " + "DATAPOINT.SAMPLE_ID = SAMPLE.ID " );
376 fQueryDatapoints.setString(1, variations.toExactMatchString());
377 fQueryDatapoints.setString(2, scenarioName);
378 return fQueryDatapoints.executeQuery();
379 }
380
381 ResultSet queryScalars(int datapointId) throws SQLException {
382 if (fQueryScalars == null)
383 fQueryScalars= fConnection.prepareStatement("select SCALAR.DIM_ID, SCALAR.VALUE from SCALAR where SCALAR.DATAPOINT_ID = ?"); fQueryScalars.setInt(1, datapointId);
385 return fQueryScalars.executeQuery();
386 }
387
388
391 ResultSet queryScenarios(Variations variations, String scenarioPattern) throws SQLException {
392 if (fQueryAllScenarios == null)
393 fQueryAllScenarios= fConnection.prepareStatement("select distinct SCENARIO.NAME from SCENARIO, SAMPLE, VARIATION where " + "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" );
397 fQueryAllScenarios.setString(1, variations.toQueryPattern());
398 fQueryAllScenarios.setString(2, scenarioPattern);
399 return fQueryAllScenarios.executeQuery();
400 }
401
402
405 ResultSet queryVariations(String variations, String scenarioPattern) throws SQLException {
406 if (fQueryVariations == null)
407 fQueryVariations= fConnection.prepareStatement("select distinct VARIATION.KEYVALPAIRS from VARIATION, SAMPLE, SCENARIO where " + "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" );
411 fQueryVariations.setString(1, variations);
412 fQueryVariations.setString(2, scenarioPattern);
413 return fQueryVariations.executeQuery();
414 }
415
416 void createSummaryEntry(int variation_id, int scenario_id, int dim_id, boolean isGlobal, int comment_id) throws SQLException {
417 if (fQuerySummaryEntry == null)
418 fQuerySummaryEntry= fConnection.prepareStatement(
419 "select count(*) from SUMMARYENTRY where VARIATION_ID = ? and SCENARIO_ID = ? and DIM_ID = ? and IS_GLOBAL = ? and COMMENT_ID = ?"); fQuerySummaryEntry.setInt(1, variation_id);
421 fQuerySummaryEntry.setInt(2, scenario_id);
422 fQuerySummaryEntry.setInt(3, dim_id);
423 fQuerySummaryEntry.setShort(4, (short) (isGlobal ? 1 : 0));
424 fQuerySummaryEntry.setInt(5, comment_id);
425 ResultSet result= fQuerySummaryEntry.executeQuery();
426 if (result.next() && result.getInt(1) > 0)
427 return;
428
429 if (fInsertSummaryEntry == null)
430 fInsertSummaryEntry= fConnection.prepareStatement("insert into SUMMARYENTRY (VARIATION_ID, SCENARIO_ID, DIM_ID, IS_GLOBAL, COMMENT_ID) values (?, ?, ?, ?, ?)"); fInsertSummaryEntry.setInt(1, variation_id);
432 fInsertSummaryEntry.setInt(2, scenario_id);
433 fInsertSummaryEntry.setInt(3, dim_id);
434 fInsertSummaryEntry.setShort(4, (short) (isGlobal ? 1 : 0));
435 fInsertSummaryEntry.setInt(5, comment_id);
436 fInsertSummaryEntry.executeUpdate();
437 }
438
439 public void setScenarioShortName(int scenario_id, String shortName) throws SQLException {
440 if (shortName.length() >= 40)
441 shortName= shortName.substring(0, 40);
442 if (fUpdateScenarioShortName == null)
443 fUpdateScenarioShortName= fConnection.prepareStatement("update SCENARIO set SHORT_NAME = ? where SCENARIO.ID = ?"); fUpdateScenarioShortName.setString(1, shortName);
445 fUpdateScenarioShortName.setInt(2, scenario_id);
446 fUpdateScenarioShortName.executeUpdate();
447 }
448
449 ResultSet queryGlobalSummaryEntries(Variations variations) throws SQLException {
450 if (fQueryGlobalSummaryEntries == null)
451 fQueryGlobalSummaryEntries= fConnection.prepareStatement(
452 "select distinct SCENARIO.NAME, SCENARIO.SHORT_NAME, SUMMARYENTRY.DIM_ID, SUMMARYENTRY.IS_GLOBAL, SUMMARYENTRY.COMMENT_ID " + "from VARIATION, SCENARIO, SUMMARYENTRY " + "where SUMMARYENTRY.VARIATION_ID = VARIATION.ID " + "and VARIATION.KEYVALPAIRS LIKE ? " + "and SUMMARYENTRY.SCENARIO_ID = SCENARIO.ID " + "and SUMMARYENTRY.IS_GLOBAL = 1 " + "order by SCENARIO.NAME" );
460 fQueryGlobalSummaryEntries.setString(1, variations.toExactMatchString());
461 return fQueryGlobalSummaryEntries.executeQuery();
462 }
463
464 ResultSet querySummaryEntries(Variations variations, String scenarioPattern) throws SQLException {
465 if (fQuerySummaryEntries == null)
466 fQuerySummaryEntries= fConnection.prepareStatement(
467 "select distinct SCENARIO.NAME, SCENARIO.SHORT_NAME, SUMMARYENTRY.DIM_ID, SUMMARYENTRY.IS_GLOBAL, SUMMARYENTRY.COMMENT_ID " + "from VARIATION, SCENARIO, SUMMARYENTRY " + "where SUMMARYENTRY.VARIATION_ID = VARIATION.ID " + "and VARIATION.KEYVALPAIRS LIKE ? " + "and SUMMARYENTRY.SCENARIO_ID = SCENARIO.ID " + "and SCENARIO.NAME like ? " + "order by SCENARIO.NAME" );
475 fQuerySummaryEntries.setString(1, variations.toExactMatchString());
476 fQuerySummaryEntries.setString(2, scenarioPattern);
477 return fQuerySummaryEntries.executeQuery();
478 }
479
480 void insertFailure(int variation_id, int scenario_id, String message) throws SQLException {
481 if (fInsertFailure == null)
482 fInsertFailure= fConnection.prepareStatement("insert into FAILURE values (?, ?, ?)"); fInsertFailure.setInt(1, variation_id);
484 fInsertFailure.setInt(2, scenario_id);
485 fInsertFailure.setString(3, message);
486 fInsertFailure.executeUpdate();
487 }
488
489 public ResultSet queryFailure(Variations variations, String scenarioPattern) throws SQLException {
490 if (fQueryFailure == null)
491 fQueryFailure= fConnection.prepareStatement("select SCENARIO.NAME, FAILURE.MESSAGE from FAILURE, VARIATION, SCENARIO where " + "FAILURE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + "FAILURE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" );
495 fQueryFailure.setString(1, variations.toExactMatchString());
496 fQueryFailure.setString(2, scenarioPattern);
497 return fQueryFailure.executeQuery();
498 }
499
500 int getCommentId(int commentKind, String comment) throws SQLException {
501 if (comment.length() > 400)
502 comment= comment.substring(0, 400);
503 if (fQueryComment == null)
504 fQueryComment= fConnection.prepareStatement("select ID from COMMENT where KIND = ? and TEXT = ?"); fQueryComment.setInt(1, commentKind);
506 fQueryComment.setString(2, comment);
507 ResultSet result= fQueryComment.executeQuery();
508 while (result.next())
509 return result.getInt(1);
510
511 if (fInsertComment == null)
512 fInsertComment= fConnection.prepareStatement("insert into COMMENT (KIND, TEXT) values (?, ?)", Statement.RETURN_GENERATED_KEYS); fInsertComment.setInt(1, commentKind);
514 fInsertComment.setString(2, comment);
515 return create(fInsertComment);
516 }
517
518 public ResultSet getComment(int comment_id) throws SQLException {
519 if (fQueryComment2 == null)
520 fQueryComment2= fConnection.prepareStatement("select KIND, TEXT from COMMENT where ID = ?"); fQueryComment2.setInt(1, comment_id);
522 return fQueryComment2.executeQuery();
523 }
524 }
525 |
Popular Tags |
Java API By Example, From Geeks To Geeks.