*
*
* jdbcDriver=jdbc.idbDriver
* jdbcURL=jdbc:idb=experiments.prp
*
*
* @author Len Trigg (trigg@cs.waikato.ac.nz)
* @version $Revision: 5238 $
*/
public class DatabaseUtils
implements Serializable, RevisionHandler {
/** for serialization. */
static final long serialVersionUID = -8252351994547116729L;
/** The name of the table containing the index to experiments. */
public static final String EXP_INDEX_TABLE = "Experiment_index";
/** The name of the column containing the experiment type (ResultProducer). */
public static final String EXP_TYPE_COL = "Experiment_type";
/** The name of the column containing the experiment setup (parameters). */
public static final String EXP_SETUP_COL = "Experiment_setup";
/** The name of the column containing the results table name. */
public static final String EXP_RESULT_COL = "Result_table";
/** The prefix for result table names. */
public static final String EXP_RESULT_PREFIX = "Results";
/** The name of the properties file. */
public final static String PROPERTY_FILE = "weka/experiment/DatabaseUtils.props";
/** Holds the jdbc drivers to be used (only to stop them being gc'ed). */
protected Vector DRIVERS = new Vector();
/** keeping track of drivers that couldn't be loaded. */
protected static Vector DRIVERS_ERRORS;
/** Properties associated with the database connection. */
protected Properties PROPERTIES;
/* Type mapping used for reading experiment results */
/** Type mapping for STRING used for reading experiment results. */
public static final int STRING = 0;
/** Type mapping for BOOL used for reading experiment results. */
public static final int BOOL = 1;
/** Type mapping for DOUBLE used for reading experiment results. */
public static final int DOUBLE = 2;
/** Type mapping for BYTE used for reading experiment results. */
public static final int BYTE = 3;
/** Type mapping for SHORT used for reading experiment results. */
public static final int SHORT = 4;
/** Type mapping for INTEGER used for reading experiment results. */
public static final int INTEGER = 5;
/** Type mapping for LONG used for reading experiment results. */
public static final int LONG = 6;
/** Type mapping for FLOAT used for reading experiment results. */
public static final int FLOAT = 7;
/** Type mapping for DATE used for reading experiment results. */
public static final int DATE = 8;
/** Type mapping for TEXT used for reading, e.g., text blobs. */
public static final int TEXT = 9;
/** Type mapping for TIME used for reading TIME columns. */
public static final int TIME = 10;
/** Database URL. */
protected String m_DatabaseURL;
/** The prepared statement used for database queries. */
protected transient PreparedStatement m_PreparedStatement;
/** The database connection. */
protected transient Connection m_Connection;
/** True if debugging output should be printed. */
protected boolean m_Debug = false;
/** Database username. */
protected String m_userName = "";
/** Database Password. */
protected String m_password = "";
/* mappings used for creating Tables. Can be overridden in DatabaseUtils.props*/
/** string type for the create table statement. */
protected String m_stringType = "LONGVARCHAR";
/** integer type for the create table statement. */
protected String m_intType = "INT";
/** double type for the create table statement. */
protected String m_doubleType = "DOUBLE";
/** For databases where Tables and Columns are created in upper case. */
protected boolean m_checkForUpperCaseNames = false;
/** For databases where Tables and Columns are created in lower case. */
protected boolean m_checkForLowerCaseNames = false;
/** setAutoCommit on the database? */
protected boolean m_setAutoCommit = true;
/** create index on the database? */
protected boolean m_createIndex = false;
/** the keywords for the current database type. */
protected HashSetclose().
*
* @param query the SQL query
* @return true if the query generated results
* @throws SQLException if an error occurs
* @see #close()
*/
public boolean execute(String query) throws SQLException {
if (!isConnected())
throw new IllegalStateException("Not connected, please connect first!");
if (!isCursorScrollable())
m_PreparedStatement = m_Connection.prepareStatement(
query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
else
m_PreparedStatement = m_Connection.prepareStatement(
query, getSupportedCursorScrollType(), ResultSet.CONCUR_READ_ONLY);
return(m_PreparedStatement.execute());
}
/**
* Gets the results generated by a previous query. Caller must clean up
* manually with close(ResultSet). Returns null if object has
* been deserialized.
*
* @return the result set.
* @throws SQLException if an error occurs
* @see #close(ResultSet)
*/
public ResultSet getResultSet() throws SQLException {
if (m_PreparedStatement != null)
return m_PreparedStatement.getResultSet();
else
return null;
}
/**
* Executes a SQL DDL query or an INSERT, DELETE or UPDATE.
*
* @param query the SQL DDL query
* @return the number of affected rows
* @throws SQLException if an error occurs
*/
public int update(String query) throws SQLException {
if (!isConnected())
throw new IllegalStateException("Not connected, please connect first!");
Statement statement;
if (!isCursorScrollable())
statement = m_Connection.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
else
statement = m_Connection.createStatement(
getSupportedCursorScrollType(), ResultSet.CONCUR_READ_ONLY);
int result = statement.executeUpdate(query);
statement.close();
return result;
}
/**
* Executes a SQL SELECT query that returns a ResultSet. Note: the ResultSet
* object must be closed by the caller.
*
* @param query the SQL query
* @return the generated ResultSet
* @throws SQLException if an error occurs
*/
public ResultSet select(String query) throws SQLException {
if (!isConnected())
throw new IllegalStateException("Not connected, please connect first!");
Statement statement;
if (!isCursorScrollable())
statement = m_Connection.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
else
statement = m_Connection.createStatement(
getSupportedCursorScrollType(), ResultSet.CONCUR_READ_ONLY);
ResultSet result = statement.executeQuery(query);
return result;
}
/**
* closes the ResultSet and the statement that generated the ResultSet to
* avoid memory leaks in JDBC drivers - in contrast to the JDBC specs, a lot
* of JDBC drives don't clean up correctly.
*
* @param rs the ResultSet to clean up
*/
public void close(ResultSet rs) {
try {
Statement statement = rs.getStatement();
rs.close();
statement.close();
statement = null;
rs = null;
}
catch (Exception e) {
// ignored
}
}
/**
* closes the m_PreparedStatement to avoid memory leaks.
*/
public void close() {
if (m_PreparedStatement != null) {
try {
m_PreparedStatement.close();
m_PreparedStatement = null;
}
catch (Exception e) {
// ignored
}
}
}
/**
* Checks that a given table exists.
*
* @param tableName the name of the table to look for.
* @return true if the table exists.
* @throws Exception if an error occurs.
*/
public boolean tableExists(String tableName) throws Exception {
if (!isConnected())
throw new IllegalStateException("Not connected, please connect first!");
if (m_Debug) {
System.err.println("Checking if table " + tableName + " exists...");
}
DatabaseMetaData dbmd = m_Connection.getMetaData();
ResultSet rs;
if (m_checkForUpperCaseNames) {
rs = dbmd.getTables (null, null, tableName.toUpperCase(), null);
} else if (m_checkForLowerCaseNames) {
rs = dbmd.getTables (null, null, tableName.toLowerCase(), null);
} else {
rs = dbmd.getTables (null, null, tableName, null);
}
boolean tableExists = rs.next();
if (rs.next()) {
throw new Exception("This table seems to exist more than once!");
}
rs.close();
if (m_Debug) {
if (tableExists) {
System.err.println("... " + tableName + " exists");
} else {
System.err.println("... " + tableName + " does not exist");
}
}
return tableExists;
}
/**
* processes the string in such a way that it can be stored in the
* database, i.e., it changes backslashes into slashes and doubles single
* quotes.
*
* @param s the string to work on
* @return the processed string
*/
public static String processKeyString(String s) {
return s.replaceAll("\\\\", "/").replaceAll("'", "''");
}
/**
* Executes a database query to see whether a result for the supplied key
* is already in the database.
*
* @param tableName the name of the table to search for the key in
* @param rp the ResultProducer who will generate the result if
* required
* @param key the key for the result
* @return true if the result with that key is in the database
* already
* @throws Exception if an error occurs
*/
protected boolean isKeyInTable(String tableName,
ResultProducer rp,
Object[] key)
throws Exception {
String query = "SELECT Key_Run"
+ " FROM " + tableName;
String [] keyNames = rp.getKeyNames();
if (keyNames.length != key.length) {
throw new Exception("Key names and key values of different lengths");
}
boolean first = true;
for (int i = 0; i < key.length; i++) {
if (key[i] != null) {
if (first) {
query += " WHERE ";
first = false;
} else {
query += " AND ";
}
query += "Key_" + keyNames[i] + '=';
if (key[i] instanceof String) {
query += "'" + processKeyString(key[i].toString()) + "'";
} else {
query += key[i].toString();
}
}
}
boolean retval = false;
ResultSet rs = select(query);
if (rs.next()) {
retval = true;
if (rs.next()) {
throw new Exception("More than one result entry "
+ "for result key: " + query);
}
}
close(rs);
return retval;
}
/**
* Executes a database query to extract a result for the supplied key
* from the database.
*
* @param tableName the name of the table where the result is stored
* @param rp the ResultProducer who will generate the result if
* required
* @param key the key for the result
* @return true if the result with that key is in the database
* already
* @throws Exception if an error occurs
*/
public Object[] getResultFromTable(String tableName,
ResultProducer rp,
Object [] key)
throws Exception {
String query = "SELECT ";
String [] resultNames = rp.getResultNames();
for (int i = 0; i < resultNames.length; i++) {
if (i != 0) {
query += ", ";
}
query += resultNames[i];
}
query += " FROM " + tableName;
String [] keyNames = rp.getKeyNames();
if (keyNames.length != key.length) {
throw new Exception("Key names and key values of different lengths");
}
boolean first = true;
for (int i = 0; i < key.length; i++) {
if (key[i] != null) {
if (first) {
query += " WHERE ";
first = false;
} else {
query += " AND ";
}
query += "Key_" + keyNames[i] + '=';
if (key[i] instanceof String) {
query += "'" + processKeyString(key[i].toString()) + "'";
} else {
query += key[i].toString();
}
}
}
ResultSet rs = select(query);
ResultSetMetaData md = rs.getMetaData();
int numAttributes = md.getColumnCount();
if (!rs.next()) {
throw new Exception("No result for query: " + query);
}
// Extract the columns for the result
Object [] result = new Object [numAttributes];
for(int i = 1; i <= numAttributes; i++) {
switch (translateDBColumnType(md.getColumnTypeName(i))) {
case STRING :
result[i - 1] = rs.getString(i);
if (rs.wasNull()) {
result[i - 1] = null;
}
break;
case FLOAT:
case DOUBLE:
result[i - 1] = new Double(rs.getDouble(i));
if (rs.wasNull()) {
result[i - 1] = null;
}
break;
default:
throw new Exception("Unhandled SQL result type (field " + (i + 1)
+ "): "
+ DatabaseUtils.typeName(md.getColumnType(i)));
}
}
if (rs.next()) {
throw new Exception("More than one result entry "
+ "for result key: " + query);
}
close(rs);
return result;
}
/**
* Executes a database query to insert a result for the supplied key
* into the database.
*
* @param tableName the name of the table where the result is stored
* @param rp the ResultProducer who will generate the result if
* required
* @param key the key for the result
* @param result the result to store
* @throws Exception if an error occurs
*/
public void putResultInTable(String tableName,
ResultProducer rp,
Object [] key,
Object [] result)
throws Exception {
String query = "INSERT INTO " + tableName
+ " VALUES ( ";
// Add the results to the table
for (int i = 0; i < key.length; i++) {
if (i != 0) {
query += ',';
}
if (key[i] != null) {
if (key[i] instanceof String) {
query += "'" + processKeyString(key[i].toString()) + "'";
} else if (key[i] instanceof Double) {
query += safeDoubleToString((Double)key[i]);
} else {
query += key[i].toString();
}
} else {
query += "NULL";
}
}
for (int i = 0; i < result.length; i++) {
query += ',';
if (result[i] != null) {
if (result[i] instanceof String) {
query += "'" + result[i].toString() + "'";
} else if (result[i] instanceof Double) {
query += safeDoubleToString((Double)result[i]);
} else {
query += result[i].toString();
//!!
//System.err.println("res: "+ result[i].toString());
}
} else {
query += "NULL";
}
}
query += ')';
if (m_Debug) {
System.err.println("Submitting result: " + query);
}
update(query);
close();
}
/**
* Inserts a + if the double is in scientific notation.
* MySQL doesn't understand the number otherwise.
*
* @param number the number to convert
* @return the number as string
*/
private String safeDoubleToString(Double number) {
// NaN is treated as NULL
if (number.isNaN())
return "NULL";
String orig = number.toString();
int pos = orig.indexOf('E');
if ((pos == -1) || (orig.charAt(pos + 1) == '-')) {
return orig;
} else {
StringBuffer buff = new StringBuffer(orig);
buff.insert(pos + 1, '+');
return new String(buff);
}
}
/**
* Returns true if the experiment index exists.
*
* @return true if the index exists
* @throws Exception if an error occurs
*/
public boolean experimentIndexExists() throws Exception {
return tableExists(EXP_INDEX_TABLE);
}
/**
* Attempts to create the experiment index table.
*
* @throws Exception if an error occurs.
*/
public void createExperimentIndex() throws Exception {
if (m_Debug) {
System.err.println("Creating experiment index table...");
}
String query;
// Workaround for MySQL (doesn't support LONGVARBINARY)
// Also for InstantDB which attempts to interpret numbers when storing
// in LONGVARBINARY
/* if (m_Connection.getMetaData().getDriverName().
equals("Mark Matthews' MySQL Driver")
|| (m_Connection.getMetaData().getDriverName().
indexOf("InstantDB JDBC Driver") != -1)) {
query = "CREATE TABLE " + EXP_INDEX_TABLE
+ " ( " + EXP_TYPE_COL + " TEXT,"
+ " " + EXP_SETUP_COL + " TEXT,"
+ " " + EXP_RESULT_COL + " INT )";
} else { */
query = "CREATE TABLE " + EXP_INDEX_TABLE
+ " ( " + EXP_TYPE_COL + " "+ m_stringType+","
+ " " + EXP_SETUP_COL + " "+ m_stringType+","
+ " " + EXP_RESULT_COL + " "+ m_intType+" )";
// }
// Other possible fields:
// creator user name (from System properties)
// creation date
update(query);
close();
}
/**
* Attempts to insert a results entry for the table into the
* experiment index.
*
* @param rp the ResultProducer generating the results
* @return the name of the created results table
* @throws Exception if an error occurs.
*/
public String createExperimentIndexEntry(ResultProducer rp)
throws Exception {
if (m_Debug) {
System.err.println("Creating experiment index entry...");
}
// Execute compound transaction
int numRows = 0;
// Workaround for MySQL (doesn't support transactions)
/* if (m_Connection.getMetaData().getDriverName().
equals("Mark Matthews' MySQL Driver")) {
m_Statement.execute("LOCK TABLES " + EXP_INDEX_TABLE + " WRITE");
System.err.println("LOCKING TABLE");
} else {*/
//}
// Get the number of rows
String query = "SELECT COUNT(*) FROM " + EXP_INDEX_TABLE;
ResultSet rs = select(query);
if (m_Debug) {
System.err.println("...getting number of rows");
}
if (rs.next()) {
numRows = rs.getInt(1);
}
close(rs);
// Add an entry in the index table
String expType = rp.getClass().getName();
String expParams = rp.getCompatibilityState();
query = "INSERT INTO " + EXP_INDEX_TABLE
+" VALUES ('"
+ expType + "', '" + expParams
+ "', " + numRows + " )";
if (update(query) > 0) {
if (m_Debug) {
System.err.println("...create returned resultset");
}
}
close();
// Finished compound transaction
// Workaround for MySQL (doesn't support transactions)
/* if (m_Connection.getMetaData().getDriverName().
equals("Mark Matthews' MySQL Driver")) {
m_Statement.execute("UNLOCK TABLES");
System.err.println("UNLOCKING TABLE");
} else { */
if (!m_setAutoCommit) {
m_Connection.commit();
m_Connection.setAutoCommit(true);
}
//}
String tableName = getResultsTableName(rp);
if (tableName == null) {
throw new Exception("Problem adding experiment index entry");
}
// Drop any existing table by that name (shouldn't occur unless
// the experiment index is destroyed, in which case the experimental
// conditions of the existing table are unknown)
try {
query = "DROP TABLE " + tableName;
if (m_Debug) {
System.err.println(query);
}
update(query);
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
return tableName;
}
/**
* Gets the name of the experiment table that stores results from a
* particular ResultProducer.
*
* @param rp the ResultProducer
* @return the name of the table where the results for this
* ResultProducer are stored, or null if there is no
* table for this ResultProducer.
* @throws Exception if an error occurs
*/
public String getResultsTableName(ResultProducer rp) throws Exception {
// Get the experiment table name, or create a new table if necessary.
if (m_Debug) {
System.err.println("Getting results table name...");
}
String expType = rp.getClass().getName();
String expParams = rp.getCompatibilityState();
String query = "SELECT " + EXP_RESULT_COL
+ " FROM " + EXP_INDEX_TABLE
+ " WHERE " + EXP_TYPE_COL + "='" + expType
+ "' AND " + EXP_SETUP_COL + "='" + expParams + "'";
String tableName = null;
ResultSet rs = select(query);
if (rs.next()) {
tableName = rs.getString(1);
if (rs.next()) {
throw new Exception("More than one index entry "
+ "for experiment config: " + query);
}
}
close(rs);
if (m_Debug) {
System.err.println("...results table = " + ((tableName == null)
? "