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