/*******************************************************************************
* Copyright (c) 2005 IBM Corporation and others.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
* IBM Corporation - initial API and implementation
*******************************************************************************/
package org.eclipse.test.internal.performance.db;
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.eclipse.test.internal.performance.PerformanceTestPlugin;
import org.eclipse.test.internal.performance.data.Dim;
public class DBHelpers {
private Connection fConnection;
public static void main(String[] args) throws SQLException {
//System.setProperty("eclipse.perf.dbloc", "net://localhost");
DBHelpers db= new DBHelpers();
String outFile= null;
//outFile= "out.txt"; //$NON-NLS-1$
PrintStream ps= null;
if (outFile != null) {
try {
ps= new PrintStream(new BufferedOutputStream(new FileOutputStream(outFile)));
} catch (FileNotFoundException e) {
System.err.println("can't create output file"); //$NON-NLS-1$
}
}
if (ps == null)
ps= System.out;
long start= System.currentTimeMillis();
db.dumpSizes(ps);
//db.renameVariation("|build=3.0.0_200410130800||config=relengbuildwin2|", "|build=3.0.0_200406251208_200410130800||config=relengbuildwin2|");
//db.dumpTable(ps, "VARIATION", 1000); //$NON-NLS-1$
//db.countSamplesWithNullVariations();
//Variations v= new Variations();
//v.put(PerformanceTestPlugin.CONFIG, "relengbuildwin2"); //$NON-NLS-1$
//v.put(PerformanceTestPlugin.BUILD, "I20041104%"); //$NON-NLS-1$
//db.dumpSummaries(v, null);
//db.removeSamples(v);
//db.countSamples(ps, v);
//db.view(ps, v, "org.eclipse.jdt.core.tests.performance.FullSourceWorkspaceTests#testPerfFullBuild()");
System.out.println("time: " + ((System.currentTimeMillis()-start)/1000.0)); //$NON-NLS-1$
if (ps != System.out)
ps.close();
}
public DBHelpers() {
fConnection= DB.getConnection();
}
void renameVariation(String oldName, String newName) throws SQLException {
PreparedStatement update= fConnection.prepareStatement("update VARIATION set KEYVALPAIRS = ? where KEYVALPAIRS = ? "); //$NON-NLS-1$
update.setString(1, newName);
update.setString(2, oldName);
update.executeUpdate();
update.close();
}
void dumpSummaries(Variations variations, String scenarioPattern) {
SummaryEntry[] summries= DB.querySummaries(variations, scenarioPattern);
for (int i= 0; i < summries.length; i++)
System.out.println(summries[i]);
}
void count(PrintStream ps) throws SQLException {
PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SCALAR where DATAPOINT_ID not in (select DATAPOINT.ID from DATAPOINT)"); //$NON-NLS-1$
ResultSet set= stmt.executeQuery();
if (set.next())
ps.println("count: " + set.getInt(1)); //$NON-NLS-1$
set.close();
stmt.close();
}
void countDimension(PrintStream ps, Dim dim) throws SQLException {
PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SCALAR where DIM_ID = ?"); //$NON-NLS-1$
stmt.setInt(1, dim.getId());
ResultSet set= stmt.executeQuery();
if (set.next())
ps.println("dimension " + dim + ": " + set.getInt(1)); //$NON-NLS-1$ //$NON-NLS-2$
}
void countAllDimensions(PrintStream ps) throws SQLException {
PreparedStatement stmt= fConnection.prepareStatement("select distinct DIM_ID from SCALAR"); //$NON-NLS-1$
ResultSet set= stmt.executeQuery();
while (set.next()) {
Dim dimension= Dim.getDimension(set.getInt(1));
if (dimension != null)
countDimension(ps, dimension);
}
}
int countSamples(PrintStream ps, Variations v) throws SQLException {
PreparedStatement stmt= fConnection.prepareStatement("select count(*) from SAMPLE, VARIATION where VARIATION.KEYVALPAIRS = ? and SAMPLE.VARIATION_ID = VARIATION.ID"); //$NON-NLS-1$
stmt.setString(1, v.toExactMatchString());
ResultSet set= stmt.executeQuery();
int n= 0;
if (set.next())
n= set.getInt(1);
ps.println("samples with variation " + v + ": " + n); //$NON-NLS-1$ //$NON-NLS-2$
return n;
}
void countDatapoints(PrintStream ps, Variations v) throws SQLException {
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"); //$NON-NLS-1$
stmt.setString(1, v.toExactMatchString());
ResultSet set= stmt.executeQuery();
if (set.next())
ps.println("datapoints with variation " + v + ": " + set.getInt(1)); //$NON-NLS-1$ //$NON-NLS-2$
}
void countScalars(PrintStream ps, Variations v) throws SQLException {
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"); //$NON-NLS-1$
stmt.setString(1, v.toExactMatchString());
ResultSet set= stmt.executeQuery();
if (set.next())
ps.println("scalars with variation " + v + ": " + set.getInt(1)); //$NON-NLS-1$ //$NON-NLS-2$
}
void removeSamples(Variations v) throws SQLException {
boolean delete= true;
int n= countSamples(System.out, v);
int variation_id= 0;
PreparedStatement stmt= fConnection.prepareStatement("select ID from VARIATION where KEYVALPAIRS = ?"); //$NON-NLS-1$
stmt.setString(1, v.toExactMatchString());
ResultSet set= stmt.executeQuery();
if (set.next()) {
variation_id= set.getInt(1);
System.err.println("variation_id: " + variation_id); //$NON-NLS-1$
}
if (variation_id <= 0) {
System.err.println("nothing found for variation " + v); //$NON-NLS-1$
return;
}
PreparedStatement iterSamples= fConnection.prepareStatement("select SAMPLE.ID, SAMPLE.SCENARIO_ID from SAMPLE where SAMPLE.VARIATION_ID = ?"); //$NON-NLS-1$
PreparedStatement iterDatapoints= fConnection.prepareStatement("select DATAPOINT.ID from DATAPOINT where DATAPOINT.SAMPLE_ID = ?"); //$NON-NLS-1$
PreparedStatement deleteScalars= fConnection.prepareStatement("delete from SCALAR where DATAPOINT_ID = ?"); //$NON-NLS-1$
PreparedStatement deleteDatapoints= fConnection.prepareStatement("delete from DATAPOINT where SAMPLE_ID = ?"); //$NON-NLS-1$
PreparedStatement deleteSamples= fConnection.prepareStatement("delete from SAMPLE where SAMPLE.ID = ?"); //$NON-NLS-1$
PreparedStatement deleteScenario= fConnection.prepareStatement("delete from SCENARIO where SCENARIO.ID = ?"); //$NON-NLS-1$
ResultSet samples= null, datapoints= null, configs= null;
iterSamples.setInt(1, variation_id);
samples= iterSamples.executeQuery();
while (samples.next()) {
int sample_id= samples.getInt(1);
int scenario_id= samples.getInt(2);
System.out.print(n + ": sample(" + sample_id + "):"); //$NON-NLS-1$ //$NON-NLS-2$
iterDatapoints.setInt(1, sample_id);
datapoints= iterDatapoints.executeQuery();
int dps= 0;
while (datapoints.next()) {
int dp_id= datapoints.getInt(1);
//ps.println(" dp: " + dp_id); //$NON-NLS-1$
if (delete) {
deleteScalars.setInt(1, dp_id);
try {
deleteScalars.executeUpdate();
fConnection.commit();
dps++;
} catch (SQLException e) {
System.err.println("removing scalars: " + e); //$NON-NLS-1$
}
}
}
System.out.println(" dps: " + dps); //$NON-NLS-1$
if (delete) {
deleteDatapoints.setInt(1, sample_id);
try {
deleteDatapoints.executeUpdate();
fConnection.commit();
} catch (SQLException e1) {
System.err.println("removing datapoints: " + e1); //$NON-NLS-1$
}
deleteSamples.setInt(1, sample_id);
try {
deleteSamples.executeUpdate();
fConnection.commit();
} catch (SQLException e) {
System.err.println("removing sample: " + e); //$NON-NLS-1$
}
deleteScenario.setInt(1, scenario_id);
try {
deleteScenario.executeUpdate();
fConnection.commit();
} catch (SQLException e) {
// System.err.println("removing scenario: " + e); //$NON-NLS-1$
}
}
n--;
}
if (delete) {
PreparedStatement deleteSummaries= fConnection.prepareStatement("delete from SUMMARYENTRY where VARIATION_ID = ?"); //$NON-NLS-1$
deleteSummaries.setInt(1, variation_id);
deleteSummaries.executeUpdate();
deleteSummaries.close();
PreparedStatement deleteVariation= fConnection.prepareStatement("delete from VARIATION where ID = ?"); //$NON-NLS-1$
deleteVariation.setInt(1, variation_id);
try {
deleteVariation.executeUpdate();
} catch (SQLException e) {
System.err.println("removing variation: " + e); //$NON-NLS-1$
}
deleteVariation.close();
}
if (configs != null) configs.close();
if (samples != null) samples.close();
if (datapoints != null) datapoints.close();
if (iterSamples != null) iterSamples.close();
if (iterDatapoints != null) iterDatapoints.close();
if (deleteSamples != null) deleteSamples.close();
if (deleteScenario != null) deleteScenario.close();
if (deleteScalars != null) deleteScalars.close();
if (deleteDatapoints != null) deleteDatapoints.close();
}
void countSamplesWithNullVariations() throws SQLException {
Statement stmt= fConnection.createStatement();
ResultSet rs= stmt.executeQuery("select count(*) from SAMPLE where SAMPLE.VARIATION_ID is null"); //$NON-NLS-1$
while (rs.next()) {
int config_id= rs.getInt(1);
System.out.println("samples with NULL variation: " + config_id); //$NON-NLS-1$
}
rs.close();
stmt.close();
}
void removeDimension(Dim dim) throws SQLException {
PreparedStatement q= fConnection.prepareStatement("delete from SCALAR where SCALAR.DIM_ID = ?"); //$NON-NLS-1$
q.setInt(1, dim.getId());
q.executeUpdate();
q.close();
}
void dumpScenarios(PrintStream ps, String pattern) throws SQLException {
PreparedStatement stmt= fConnection.prepareStatement("select NAME from SCENARIO where NAME like ? order by NAME"); //$NON-NLS-1$
stmt.setString(1, pattern);
ResultSet rs= stmt.executeQuery();
while (rs.next())
ps.println(rs.getString(1));
rs.close();
stmt.close();
}
void dumpSizes(PrintStream ps) throws SQLException {
if (fConnection == null)
return;
Statement stmt= fConnection.createStatement();
try {
ResultSet rs= stmt.executeQuery("SELECT sys.systables.tablename FROM sys.systables WHERE sys.systables.tablename NOT LIKE 'SYS%' "); //$NON-NLS-1$
while (rs.next())
dumpSize(ps, rs.getString(1));
rs.close();
} finally {
stmt.close();
}
}
void dumpSize(PrintStream ps, String table) throws SQLException {
Statement stmt= fConnection.createStatement();
ResultSet rs= stmt.executeQuery("select Count(*) from " + table); //$NON-NLS-1$
if (rs.next())
ps.println(table + ": " + rs.getInt(1)); //$NON-NLS-1$
rs.close();
stmt.close();
}
public void dumpAll(PrintStream ps, int maxRow) throws SQLException {
if (fConnection == null)
return;
if (maxRow < 0)
maxRow= 1000000;
Statement stmt= fConnection.createStatement();
try {
ResultSet rs= stmt.executeQuery("select SYS.SYSTABLES.TABLENAME from SYS.SYSTABLES where SYS.SYSTABLES.TABLENAME not like 'SYS%' "); //$NON-NLS-1$
while (rs.next()) {
dumpTable(ps, rs.getString(1), maxRow);
ps.println();
}
rs.close();
} finally {
stmt.close();
}
}
void dumpTable(PrintStream ps, String tableName, int maxRow) throws SQLException {
ps.print(tableName + '(');
Statement select= fConnection.createStatement();
ResultSet result= select.executeQuery("select * from " + tableName); //$NON-NLS-1$
ResultSetMetaData metaData= result.getMetaData();
int n= metaData.getColumnCount();
for (int i= 0; i < n; i++) {
ps.print(metaData.getColumnLabel(i+1));
if (i < n-1)
ps.print(", "); //$NON-NLS-1$
}
ps.println("):"); //$NON-NLS-1$
for (int r= 0; result.next() && r < maxRow; r++) {
for (int i= 0; i < n; i++)
ps.print(' ' + result.getString(i+1));
ps.println();
}
select.close();
}
void view(PrintStream ps, Variations v, String scenarioPattern) throws SQLException {
Scenario[] scenarios= DB.queryScenarios(v, scenarioPattern, PerformanceTestPlugin.BUILD, null);
ps.println(scenarios.length + " Scenarios"); //$NON-NLS-1$
ps.println();
for (int s= 0; s < scenarios.length; s++)
scenarios[s].dump(ps, PerformanceTestPlugin.BUILD);
}
}