1 /******************************************************************************* 2 * Copyright (c) 2000, 2016 IBM Corporation and others. 3 * 4 * This program and the accompanying materials are made 5 * available under the terms of the Eclipse Public License 2.0 which accompanies this distribution, and is available at 6 * https://www.eclipse.org/legal/epl-2.0/ 7 * 8 * SPDX-License-Identifier: EPL-2.0 9 * 10 * Contributors: IBM Corporation - initial API and implementation 11 *******************************************************************************/ 12 13 package org.eclipse.test.internal.performance.db; 14 15 import java.math.BigDecimal; 16 import java.sql.Connection; 17 import java.sql.PreparedStatement; 18 import java.sql.ResultSet; 19 import java.sql.SQLException; 20 import java.sql.Statement; 21 import java.sql.Timestamp; 22 23 /* 24 * Any SQL should only be used here. 25 */ 26 public class SQL { 27 28 private boolean fCompatibility = false; 29 30 protected Connection fConnection; 31 32 private PreparedStatement fInsertVariation, fInsertScenario, fInsertSample, fInsertDataPoint, fInsertScalar; 33 private PreparedStatement fQueryComment, fInsertComment, fQueryComment2; 34 private PreparedStatement fQueryVariation, fQueryVariations, fQueryScenario, fQueryAllScenarios, fQueryDatapoints, 35 fQueryScalars; 36 private PreparedStatement fInsertSummaryEntry, fUpdateScenarioShortName, fQuerySummaryEntry, fQueryGlobalSummaryEntries, 37 fQuerySummaryEntries; 38 private PreparedStatement fInsertFailure, fQueryFailure; 39 SQL(Connection con)40 protected SQL(Connection con) throws SQLException { 41 fConnection = con; 42 43 boolean needsUpgrade = true; 44 boolean needsInitialization = true; 45 boolean needsFailures = true; 46 boolean needsComments = true; 47 48 try (Statement statement = fConnection.createStatement()) { 49 try (ResultSet rs = statement.executeQuery("select SYS.SYSTABLES.TABLENAME from SYS.SYSTABLES where SYS.SYSTABLES.TABLENAME not like 'SYS%'")) { //$NON-NLS-1$ 50 while (rs.next()) { 51 String tablename = rs.getString(1); 52 if ("SUMMARYENTRY".equals(tablename)) //$NON-NLS-1$ 53 needsUpgrade = false; 54 else if ("CONFIG_ORG".equals(tablename)) //$NON-NLS-1$ 55 fCompatibility = true; 56 else if ("VARIATION".equals(tablename)) //$NON-NLS-1$ 57 needsInitialization = false; 58 else if ("FAILURE".equals(tablename)) //$NON-NLS-1$ 59 needsFailures = false; 60 else if ("COMMENT".equals(tablename)) //$NON-NLS-1$ 61 needsComments = false; 62 } 63 } 64 if (!fCompatibility) { 65 // check whether table SAMPLE still has the CONFIG_ID column 66 try (ResultSet rs = statement.executeQuery("select count(*) from SYS.SYSTABLES, SYS.SYSCOLUMNS where SYS.SYSTABLES.TABLENAME = 'SAMPLE' and " + //$NON-NLS-1$ 67 "SYS.SYSTABLES.TABLEID = SYS.SYSCOLUMNS.REFERENCEID and SYS.SYSCOLUMNS.COLUMNNAME = 'CONFIG_ID' ")) { //$NON-NLS-1$ 68 if (rs.next() && rs.getInt(1) == 1) 69 fCompatibility = true; 70 } 71 } 72 } 73 74 if (needsInitialization) 75 initialize(); 76 else { 77 if (needsUpgrade) 78 upgradeDB(); 79 else if (needsFailures) 80 addFailureTable(); 81 if (needsComments) 82 addCommentTable(); 83 } 84 } 85 dispose()86 protected void dispose() throws SQLException { 87 if (fInsertVariation != null) 88 fInsertVariation.close(); 89 if (fInsertScenario != null) 90 fInsertScenario.close(); 91 if (fInsertSample != null) 92 fInsertSample.close(); 93 if (fInsertDataPoint != null) 94 fInsertDataPoint.close(); 95 if (fInsertScalar != null) 96 fInsertScalar.close(); 97 if (fInsertSummaryEntry != null) 98 fInsertSummaryEntry.close(); 99 if (fInsertFailure != null) 100 fInsertFailure.close(); 101 if (fInsertComment != null) 102 fInsertComment.close(); 103 if (fUpdateScenarioShortName != null) 104 fUpdateScenarioShortName.close(); 105 if (fQueryDatapoints != null) 106 fQueryDatapoints.close(); 107 if (fQueryScalars != null) 108 fQueryScalars.close(); 109 if (fQueryVariation != null) 110 fQueryVariation.close(); 111 if (fQueryScenario != null) 112 fQueryScenario.close(); 113 if (fQueryAllScenarios != null) 114 fQueryAllScenarios.close(); 115 if (fQueryVariations != null) 116 fQueryVariations.close(); 117 if (fQueryGlobalSummaryEntries != null) 118 fQueryGlobalSummaryEntries.close(); 119 if (fQuerySummaryEntries != null) 120 fQuerySummaryEntries.close(); 121 if (fQueryFailure != null) 122 fQueryFailure.close(); 123 if (fQueryComment != null) 124 fQueryComment.close(); 125 if (fQueryComment2 != null) 126 fQueryComment2.close(); 127 } 128 initialize()129 private void initialize() throws SQLException { 130 try (Statement stmt = fConnection.createStatement()) { 131 stmt.executeUpdate("create table VARIATION (" + //$NON-NLS-1$ 132 "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$ 133 "KEYVALPAIRS varchar(10000) not null " + //$NON-NLS-1$ 134 ")" //$NON-NLS-1$ 135 ); 136 stmt.executeUpdate("create table SCENARIO (" + //$NON-NLS-1$ 137 "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$ 138 "NAME varchar(256) not null," + //$NON-NLS-1$ 139 "SHORT_NAME varchar(40)" + //$NON-NLS-1$ 140 ")" //$NON-NLS-1$ 141 ); 142 stmt.executeUpdate("create table SAMPLE (" + //$NON-NLS-1$ 143 "ID int not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$ 144 "VARIATION_ID int not null," + //$NON-NLS-1$ 145 "SCENARIO_ID int not null," + //$NON-NLS-1$ 146 "STARTTIME timestamp" + //$NON-NLS-1$ 147 ")" //$NON-NLS-1$ 148 ); 149 stmt.executeUpdate("create table DATAPOINT (" + //$NON-NLS-1$ 150 "ID int not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$ 151 "SAMPLE_ID int not null," + //$NON-NLS-1$ 152 "SEQ int," + //$NON-NLS-1$ 153 "STEP int" + //$NON-NLS-1$ 154 ")" //$NON-NLS-1$ 155 ); 156 stmt.executeUpdate("create table SCALAR (" + //$NON-NLS-1$ 157 "DATAPOINT_ID int not null," + //$NON-NLS-1$ 158 "DIM_ID int not null," + //$NON-NLS-1$ 159 "VALUE bigint" + //$NON-NLS-1$ 160 ")" //$NON-NLS-1$ 161 ); 162 stmt.executeUpdate("create table SUMMARYENTRY (" + //$NON-NLS-1$ 163 "VARIATION_ID int not null," + //$NON-NLS-1$ 164 "SCENARIO_ID int not null," + //$NON-NLS-1$ 165 "DIM_ID int not null," + //$NON-NLS-1$ 166 "IS_GLOBAL smallint not null," + //$NON-NLS-1$ 167 "COMMENT_ID int not null" + //$NON-NLS-1$ 168 ")" //$NON-NLS-1$ 169 ); 170 stmt.executeUpdate("create table FAILURE (" + //$NON-NLS-1$ 171 "VARIATION_ID int not null," + //$NON-NLS-1$ 172 "SCENARIO_ID int not null," + //$NON-NLS-1$ 173 "MESSAGE varchar(1000) not null" + //$NON-NLS-1$ 174 ")" //$NON-NLS-1$ 175 ); 176 stmt.executeUpdate("create table COMMENT (" + //$NON-NLS-1$ 177 "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$ 178 "KIND int not null," + //$NON-NLS-1$ 179 "TEXT varchar(400) not null" + //$NON-NLS-1$ 180 ")" //$NON-NLS-1$ 181 ); 182 183 // Primary/unique 184 stmt.executeUpdate("alter table VARIATION add constraint VA_KVP primary key (KEYVALPAIRS)"); //$NON-NLS-1$ 185 stmt.executeUpdate("alter table SCENARIO add constraint SC_NAME primary key (NAME)"); //$NON-NLS-1$ 186 stmt.executeUpdate("alter table SAMPLE add constraint SA_ID primary key (ID)"); //$NON-NLS-1$ 187 stmt.executeUpdate("alter table DATAPOINT add constraint DP_ID primary key (ID)"); //$NON-NLS-1$ 188 189 // Foreign 190 stmt.executeUpdate("alter table SAMPLE add constraint SAMPLE_CONSTRAINT " + //$NON-NLS-1$ 191 "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$ 192 stmt.executeUpdate("alter table SAMPLE add constraint SAMPLE_CONSTRAINT2 " + //$NON-NLS-1$ 193 "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$ 194 stmt.executeUpdate("alter table DATAPOINT add constraint DP_CONSTRAINT " + //$NON-NLS-1$ 195 "foreign key (SAMPLE_ID) references SAMPLE (ID)"); //$NON-NLS-1$ 196 stmt.executeUpdate("alter table SCALAR add constraint SCALAR_CONSTRAINT " + //$NON-NLS-1$ 197 "foreign key (DATAPOINT_ID) references DATAPOINT (ID)"); //$NON-NLS-1$ 198 199 stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT " + //$NON-NLS-1$ 200 "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$ 201 stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT2 " + //$NON-NLS-1$ 202 "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$ 203 204 stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT " + //$NON-NLS-1$ 205 "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$ 206 stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT2 " + //$NON-NLS-1$ 207 "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$ 208 209 fConnection.commit(); 210 211 } 212 } 213 upgradeDB()214 private void upgradeDB() throws SQLException { 215 try (Statement stmt = fConnection.createStatement();){ 216 stmt.executeUpdate("create table SUMMARYENTRY (" + //$NON-NLS-1$ 217 "VARIATION_ID int not null," + //$NON-NLS-1$ 218 "SCENARIO_ID int not null," + //$NON-NLS-1$ 219 "DIM_ID int not null," + //$NON-NLS-1$ 220 "IS_GLOBAL smallint not null," + //$NON-NLS-1$ 221 "COMMENT_ID int not null" + //$NON-NLS-1$ 222 ")" //$NON-NLS-1$ 223 ); 224 stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT " + //$NON-NLS-1$ 225 "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$ 226 stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT2 " + //$NON-NLS-1$ 227 "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$ 228 229 stmt.executeUpdate("alter table SCENARIO add column SHORT_NAME varchar(40)"); //$NON-NLS-1$ 230 231 fConnection.commit(); 232 233 } 234 } 235 addCommentTable()236 private void addCommentTable() throws SQLException { 237 try (Statement stmt = fConnection.createStatement()){ 238 stmt.executeUpdate("create table COMMENT (" + //$NON-NLS-1$ 239 "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$ 240 "KIND int not null," + //$NON-NLS-1$ 241 "TEXT varchar(400) not null" + //$NON-NLS-1$ 242 ")" //$NON-NLS-1$ 243 ); 244 245 stmt.executeUpdate("alter table SUMMARYENTRY add column COMMENT_ID int not null default 0"); //$NON-NLS-1$ 246 247 fConnection.commit(); 248 249 } 250 } 251 addFailureTable()252 private void addFailureTable() throws SQLException { 253 try (Statement stmt = fConnection.createStatement();){ 254 stmt.executeUpdate("create table FAILURE (" + //$NON-NLS-1$ 255 "VARIATION_ID int not null," + //$NON-NLS-1$ 256 "SCENARIO_ID int not null," + //$NON-NLS-1$ 257 "MESSAGE varchar(1000) not null" + //$NON-NLS-1$ 258 ")" //$NON-NLS-1$ 259 ); 260 261 stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT " + //$NON-NLS-1$ 262 "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$ 263 stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT2 " + //$NON-NLS-1$ 264 "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$ 265 266 fConnection.commit(); 267 268 } 269 } 270 create(PreparedStatement stmt)271 static int create(PreparedStatement stmt) throws SQLException { 272 stmt.executeUpdate(); 273 try (ResultSet rs = stmt.getGeneratedKeys()) { 274 if (rs != null) { 275 if (rs.next()) { 276 BigDecimal idColVar = rs.getBigDecimal(1); 277 return idColVar.intValue(); 278 } 279 } 280 } 281 return 0; 282 } 283 getScenario(String scenarioPattern)284 int getScenario(String scenarioPattern) throws SQLException { 285 if (fQueryScenario == null) 286 fQueryScenario = fConnection.prepareStatement("select ID from SCENARIO where NAME = ?"); //$NON-NLS-1$ 287 fQueryScenario.setString(1, scenarioPattern); 288 try (ResultSet result = fQueryScenario.executeQuery()) { 289 while (result.next()) 290 return result.getInt(1); 291 } 292 if (fInsertScenario == null) 293 fInsertScenario = fConnection.prepareStatement( 294 "insert into SCENARIO (NAME) values (?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$ 295 fInsertScenario.setString(1, scenarioPattern); 296 return create(fInsertScenario); 297 } 298 getVariations(Variations variations)299 int getVariations(Variations variations) throws SQLException { 300 if (fQueryVariation == null) 301 fQueryVariation = fConnection.prepareStatement("select ID from VARIATION where KEYVALPAIRS = ?"); //$NON-NLS-1$ 302 String exactMatchString = variations.toExactMatchString(); 303 fQueryVariation.setString(1, exactMatchString); 304 try (ResultSet result = fQueryVariation.executeQuery()) { 305 while (result.next()) 306 return result.getInt(1); 307 } 308 if (fInsertVariation == null) 309 fInsertVariation = fConnection.prepareStatement( 310 "insert into VARIATION (KEYVALPAIRS) values (?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$ 311 fInsertVariation.setString(1, exactMatchString); 312 return create(fInsertVariation); 313 } 314 createSample(int variation_id, int scenario_id, Timestamp starttime)315 int createSample(int variation_id, int scenario_id, Timestamp starttime) throws SQLException { 316 if (fInsertSample == null) { 317 if (fCompatibility) { 318 // since we cannot remove table columns in cloudscape we have to 319 // provide a non-null value for CONFIG_ID 320 fInsertSample = fConnection 321 .prepareStatement( 322 "insert into SAMPLE (VARIATION_ID, SCENARIO_ID, STARTTIME, CONFIG_ID) values (?, ?, ?, 0)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$ 323 } else { 324 fInsertSample = fConnection 325 .prepareStatement( 326 "insert into SAMPLE (VARIATION_ID, SCENARIO_ID, STARTTIME) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$ 327 } 328 } 329 fInsertSample.setInt(1, variation_id); 330 fInsertSample.setInt(2, scenario_id); 331 fInsertSample.setTimestamp(3, starttime); 332 return create(fInsertSample); 333 } 334 createDataPoint(int sample_id, int seq, int step)335 int createDataPoint(int sample_id, int seq, int step) throws SQLException { 336 if (fInsertDataPoint == null) 337 fInsertDataPoint = fConnection.prepareStatement( 338 "insert into DATAPOINT (SAMPLE_ID, SEQ, STEP) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$ 339 fInsertDataPoint.setInt(1, sample_id); 340 fInsertDataPoint.setInt(2, seq); 341 fInsertDataPoint.setInt(3, step); 342 return create(fInsertDataPoint); 343 } 344 insertScalar(int datapoint_id, int dim_id, long value)345 void insertScalar(int datapoint_id, int dim_id, long value) throws SQLException { 346 if (fInsertScalar == null) 347 fInsertScalar = fConnection.prepareStatement("insert into SCALAR values (?, ?, ?)"); //$NON-NLS-1$ 348 fInsertScalar.setInt(1, datapoint_id); 349 fInsertScalar.setInt(2, dim_id); 350 fInsertScalar.setLong(3, value); 351 fInsertScalar.executeUpdate(); 352 } 353 queryDataPoints(Variations variations, String scenarioName)354 ResultSet queryDataPoints(Variations variations, String scenarioName) throws SQLException { 355 if (fQueryDatapoints == null) 356 fQueryDatapoints = fConnection 357 .prepareStatement("select DATAPOINT.ID, DATAPOINT.STEP from VARIATION, SCENARIO, SAMPLE, DATAPOINT " + //$NON-NLS-1$ 358 "where " + //$NON-NLS-1$ 359 "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS = ? and " + //$NON-NLS-1$ 360 "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ? and " + //$NON-NLS-1$ 361 "DATAPOINT.SAMPLE_ID = SAMPLE.ID " //$NON-NLS-1$ 362 ); 363 fQueryDatapoints.setString(1, variations.toExactMatchString()); 364 fQueryDatapoints.setString(2, scenarioName); 365 return fQueryDatapoints.executeQuery(); 366 } 367 queryScalars(int datapointId)368 ResultSet queryScalars(int datapointId) throws SQLException { 369 if (fQueryScalars == null) 370 fQueryScalars = fConnection 371 .prepareStatement("select SCALAR.DIM_ID, SCALAR.VALUE from SCALAR where SCALAR.DATAPOINT_ID = ?"); //$NON-NLS-1$ 372 fQueryScalars.setInt(1, datapointId); 373 return fQueryScalars.executeQuery(); 374 } 375 376 /* 377 * Returns SCENARIO.NAME 378 */ queryScenarios(Variations variations, String scenarioPattern)379 ResultSet queryScenarios(Variations variations, String scenarioPattern) throws SQLException { 380 if (fQueryAllScenarios == null) 381 fQueryAllScenarios = fConnection 382 .prepareStatement("select distinct SCENARIO.NAME from SCENARIO, SAMPLE, VARIATION where " + //$NON-NLS-1$ 383 "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ 384 "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" //$NON-NLS-1$ 385 ); 386 fQueryAllScenarios.setString(1, variations.toQueryPattern()); 387 fQueryAllScenarios.setString(2, scenarioPattern); 388 return fQueryAllScenarios.executeQuery(); 389 } 390 391 /* 392 * Returns VARIATION.KEYVALPAIRS 393 */ queryVariations(String variations, String scenarioPattern)394 ResultSet queryVariations(String variations, String scenarioPattern) throws SQLException { 395 if (fQueryVariations == null) 396 fQueryVariations = fConnection 397 .prepareStatement("select distinct VARIATION.KEYVALPAIRS from VARIATION, SAMPLE, SCENARIO where " + //$NON-NLS-1$ 398 "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ 399 "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" //$NON-NLS-1$ 400 ); 401 fQueryVariations.setString(1, variations); 402 fQueryVariations.setString(2, scenarioPattern); 403 return fQueryVariations.executeQuery(); 404 } 405 createSummaryEntry(int variation_id, int scenario_id, int dim_id, boolean isGlobal, int comment_id)406 void createSummaryEntry(int variation_id, int scenario_id, int dim_id, boolean isGlobal, int comment_id) throws SQLException { 407 if (fQuerySummaryEntry == null) 408 fQuerySummaryEntry = fConnection 409 .prepareStatement("select count(*) from SUMMARYENTRY where VARIATION_ID = ? and SCENARIO_ID = ? and DIM_ID = ? and IS_GLOBAL = ? and COMMENT_ID = ?"); //$NON-NLS-1$ 410 fQuerySummaryEntry.setInt(1, variation_id); 411 fQuerySummaryEntry.setInt(2, scenario_id); 412 fQuerySummaryEntry.setInt(3, dim_id); 413 fQuerySummaryEntry.setShort(4, (short) (isGlobal ? 1 : 0)); 414 fQuerySummaryEntry.setInt(5, comment_id); 415 try (ResultSet result = fQuerySummaryEntry.executeQuery()) { 416 if (result.next() && result.getInt(1) > 0) 417 return; 418 } 419 if (fInsertSummaryEntry == null) 420 fInsertSummaryEntry = fConnection 421 .prepareStatement("insert into SUMMARYENTRY (VARIATION_ID, SCENARIO_ID, DIM_ID, IS_GLOBAL, COMMENT_ID) values (?, ?, ?, ?, ?)"); //$NON-NLS-1$ 422 fInsertSummaryEntry.setInt(1, variation_id); 423 fInsertSummaryEntry.setInt(2, scenario_id); 424 fInsertSummaryEntry.setInt(3, dim_id); 425 fInsertSummaryEntry.setShort(4, (short) (isGlobal ? 1 : 0)); 426 fInsertSummaryEntry.setInt(5, comment_id); 427 fInsertSummaryEntry.executeUpdate(); 428 } 429 setScenarioShortName(int scenario_id, String shortName)430 public void setScenarioShortName(int scenario_id, String shortName) throws SQLException { 431 if (shortName.length() >= 40) 432 shortName = shortName.substring(0, 40); 433 if (fUpdateScenarioShortName == null) 434 fUpdateScenarioShortName = fConnection.prepareStatement("update SCENARIO set SHORT_NAME = ? where SCENARIO.ID = ?"); //$NON-NLS-1$ 435 fUpdateScenarioShortName.setString(1, shortName); 436 fUpdateScenarioShortName.setInt(2, scenario_id); 437 fUpdateScenarioShortName.executeUpdate(); 438 } 439 queryGlobalSummaryEntries(Variations variations)440 ResultSet queryGlobalSummaryEntries(Variations variations) throws SQLException { 441 if (fQueryGlobalSummaryEntries == null) 442 fQueryGlobalSummaryEntries = fConnection 443 .prepareStatement("select distinct SCENARIO.NAME, SCENARIO.SHORT_NAME, SUMMARYENTRY.DIM_ID, SUMMARYENTRY.IS_GLOBAL, SUMMARYENTRY.COMMENT_ID " + //$NON-NLS-1$ 444 "from VARIATION, SCENARIO, SUMMARYENTRY " + //$NON-NLS-1$ 445 "where SUMMARYENTRY.VARIATION_ID = VARIATION.ID " + //$NON-NLS-1$ 446 "and VARIATION.KEYVALPAIRS LIKE ? " + //$NON-NLS-1$ 447 "and SUMMARYENTRY.SCENARIO_ID = SCENARIO.ID " + //$NON-NLS-1$ 448 "and SUMMARYENTRY.IS_GLOBAL = 1 " + //$NON-NLS-1$ 449 "order by SCENARIO.NAME" //$NON-NLS-1$ 450 ); 451 fQueryGlobalSummaryEntries.setString(1, variations.toExactMatchString()); 452 return fQueryGlobalSummaryEntries.executeQuery(); 453 } 454 querySummaryEntries(Variations variations, String scenarioPattern)455 ResultSet querySummaryEntries(Variations variations, String scenarioPattern) throws SQLException { 456 if (fQuerySummaryEntries == null) 457 fQuerySummaryEntries = fConnection 458 .prepareStatement("select distinct SCENARIO.NAME, SCENARIO.SHORT_NAME, SUMMARYENTRY.DIM_ID, SUMMARYENTRY.IS_GLOBAL, SUMMARYENTRY.COMMENT_ID " + //$NON-NLS-1$ 459 "from VARIATION, SCENARIO, SUMMARYENTRY " + //$NON-NLS-1$ 460 "where SUMMARYENTRY.VARIATION_ID = VARIATION.ID " + //$NON-NLS-1$ 461 "and VARIATION.KEYVALPAIRS LIKE ? " + //$NON-NLS-1$ 462 "and SUMMARYENTRY.SCENARIO_ID = SCENARIO.ID " + //$NON-NLS-1$ 463 "and SCENARIO.NAME like ? " + //$NON-NLS-1$ 464 "order by SCENARIO.NAME" //$NON-NLS-1$ 465 ); 466 fQuerySummaryEntries.setString(1, variations.toExactMatchString()); 467 fQuerySummaryEntries.setString(2, scenarioPattern); 468 return fQuerySummaryEntries.executeQuery(); 469 } 470 insertFailure(int variation_id, int scenario_id, String message)471 void insertFailure(int variation_id, int scenario_id, String message) throws SQLException { 472 if (fInsertFailure == null) 473 fInsertFailure = fConnection.prepareStatement("insert into FAILURE values (?, ?, ?)"); //$NON-NLS-1$ 474 fInsertFailure.setInt(1, variation_id); 475 fInsertFailure.setInt(2, scenario_id); 476 fInsertFailure.setString(3, message); 477 fInsertFailure.executeUpdate(); 478 } 479 queryFailure(Variations variations, String scenarioPattern)480 public ResultSet queryFailure(Variations variations, String scenarioPattern) throws SQLException { 481 if (fQueryFailure == null) 482 fQueryFailure = fConnection 483 .prepareStatement("select SCENARIO.NAME, FAILURE.MESSAGE from FAILURE, VARIATION, SCENARIO where " + //$NON-NLS-1$ 484 "FAILURE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$ 485 "FAILURE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" //$NON-NLS-1$ 486 ); 487 fQueryFailure.setString(1, variations.toExactMatchString()); 488 fQueryFailure.setString(2, scenarioPattern); 489 return fQueryFailure.executeQuery(); 490 } 491 getCommentId(int commentKind, String comment)492 int getCommentId(int commentKind, String comment) throws SQLException { 493 if (comment.length() > 400) 494 comment = comment.substring(0, 400); 495 if (fQueryComment == null) 496 fQueryComment = fConnection.prepareStatement("select ID from COMMENT where KIND = ? and TEXT = ?"); //$NON-NLS-1$ 497 fQueryComment.setInt(1, commentKind); 498 fQueryComment.setString(2, comment); 499 try (ResultSet result = fQueryComment.executeQuery()) { 500 while (result.next()) 501 return result.getInt(1); 502 } 503 if (fInsertComment == null) 504 fInsertComment = fConnection.prepareStatement( 505 "insert into COMMENT (KIND, TEXT) values (?, ?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$ 506 fInsertComment.setInt(1, commentKind); 507 fInsertComment.setString(2, comment); 508 return create(fInsertComment); 509 } 510 getComment(int comment_id)511 public ResultSet getComment(int comment_id) throws SQLException { 512 if (fQueryComment2 == null) 513 fQueryComment2 = fConnection.prepareStatement("select KIND, TEXT from COMMENT where ID = ?"); //$NON-NLS-1$ 514 fQueryComment2.setInt(1, comment_id); 515 return fQueryComment2.executeQuery(); 516 } 517 } 518