/*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
/*
* DatabaseUtils.java
* Copyright (C) 1999-2012 University of Waikato, Hamilton, New Zealand
*
*/
package weka.experiment;
import java.io.File;
import java.io.FileInputStream;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Collections;
import java.util.HashSet;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;
import weka.core.RevisionHandler;
import weka.core.RevisionUtils;
import weka.core.Utils;
/**
* DatabaseUtils provides utility functions for accessing the experiment
* database. The jdbc
* driver and database to be used default to "jdbc.idbDriver" and
* "jdbc:idb=experiments.prp". These may be changed by creating
* a java properties file called DatabaseUtils.props in user.home or
* the current directory. eg:<p>
*
* <code><pre>
* jdbcDriver=jdbc.idbDriver
* jdbcURL=jdbc:idb=experiments.prp
* </pre></code><p>
*
* @author Len Trigg (trigg@cs.waikato.ac.nz)
* @version $Revision: 8034 $
*/
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 HashSet<String> m_Keywords = new HashSet<String>();
/** the character to mask SQL keywords (by appending this character). */
protected String m_KeywordsMaskChar = "_";
/**
* Reads properties and sets up the database drivers.
*
* @throws Exception if an error occurs
*/
public DatabaseUtils() throws Exception {
this((Properties) null);
}
/**
* Reads the properties from the specified file and sets up the database drivers.
*
* @param propsFile the props file to load, ignored if null or pointing
* to a directory
* @throws Exception if an error occurs
*/
public DatabaseUtils(File propsFile) throws Exception {
this(loadProperties(propsFile));
}
/**
* Uses the specified properties to set up the database drivers.
*
* @param props the properties to use, ignored if null
* @throws Exception if an error occurs
*/
public DatabaseUtils(Properties props) throws Exception {
if (DRIVERS_ERRORS == null)
DRIVERS_ERRORS = new Vector();
initialize(props);
}
/**
* Initializes the database connection.
*
* @param propsFile the props file to load, ignored if null or pointing
* to a directory
*/
public void initialize(File propsFile) {
initialize(loadProperties(propsFile));
}
/**
* Initializes the database connection.
*
* @param props the properties to obtain the parameters from,
* ignored if null
*/
public void initialize(Properties props) {
try {
if (props != null)
PROPERTIES = props;
else
PROPERTIES = Utils.readProperties(PROPERTY_FILE);
// Register the drivers in jdbc DriverManager
String drivers = PROPERTIES.getProperty("jdbcDriver", "jdbc.idbDriver");
if (drivers == null) {
throw new Exception("No database drivers (JDBC) specified");
}
// The call to newInstance() is necessary on some platforms
// (with some java VM implementations)
StringTokenizer st = new StringTokenizer(drivers, ", ");
while (st.hasMoreTokens()) {
String driver = st.nextToken();
boolean result;
try {
Class.forName(driver);
DRIVERS.addElement(driver);
result = true;
}
catch (Exception e) {
result = false;
}
if (m_Debug || (!result && !DRIVERS_ERRORS.contains(driver)))
System.err.println(
"Trying to add database driver (JDBC): " + driver
+ " - " + (result ? "Success!" : "Warning, not in CLASSPATH?"));
if (!result)
DRIVERS_ERRORS.add(driver);
}
}
catch (Exception ex) {
System.err.println("Problem reading properties. Fix before continuing.");
System.err.println(ex);
}
m_DatabaseURL = PROPERTIES.getProperty("jdbcURL", "jdbc:idb=experiments.prp");
m_stringType = PROPERTIES.getProperty("CREATE_STRING", "LONGVARCHAR");
m_intType = PROPERTIES.getProperty("CREATE_INT", "INT");
m_doubleType = PROPERTIES.getProperty("CREATE_DOUBLE", "DOUBLE");
m_checkForUpperCaseNames = PROPERTIES.getProperty(
"checkUpperCaseNames", "false").equals("true");
m_checkForLowerCaseNames = PROPERTIES.getProperty(
"checkLowerCaseNames", "false").equals("true");
m_setAutoCommit = PROPERTIES.getProperty(
"setAutoCommit", "false").equals("true");
m_createIndex = PROPERTIES.getProperty(
"createIndex", "false").equals("true");
setKeywords(PROPERTIES.getProperty(
"Keywords", "AND,ASC,BY,DESC,FROM,GROUP,INSERT,ORDER,SELECT,UPDATE,WHERE"));
setKeywordsMaskChar(PROPERTIES.getProperty("KeywordsMaskChar", "_"));
}
/**
* returns key column headings in their original case. Used for
* those databases that create uppercase column names.
*
* @param columnName the column to retrieve the original case for
* @return the original case
*/
public String attributeCaseFix(String columnName){
if (m_checkForUpperCaseNames) {
String ucname = columnName.toUpperCase();
if (ucname.equals(EXP_TYPE_COL.toUpperCase())) {
return EXP_TYPE_COL;
} else if (ucname.equals(EXP_SETUP_COL.toUpperCase())) {
return EXP_SETUP_COL;
} else if (ucname.equals(EXP_RESULT_COL.toUpperCase())) {
return EXP_RESULT_COL;
} else {
return columnName;
}
}
else if (m_checkForLowerCaseNames) {
String ucname = columnName.toLowerCase();
if (ucname.equals(EXP_TYPE_COL.toLowerCase())) {
return EXP_TYPE_COL;
} else if (ucname.equals(EXP_SETUP_COL.toLowerCase())) {
return EXP_SETUP_COL;
} else if (ucname.equals(EXP_RESULT_COL.toLowerCase())) {
return EXP_RESULT_COL;
} else {
return columnName;
}
}
else {
return columnName;
}
}
/**
* translates the column data type string to an integer value that indicates
* which data type / get()-Method to use in order to retrieve values from the
* database (see DatabaseUtils.Properties, InstanceQuery()). Blanks in the type
* are replaced with underscores "_", since Java property names can't contain blanks.
*
* @param type the column type as retrieved with
* java.sql.MetaData.getColumnTypeName(int)
* @return an integer value that indicates
* which data type / get()-Method to use in order to
* retrieve values from the
*/
public int translateDBColumnType(String type) {
try {
// Oracle, e.g., has datatypes like "DOUBLE PRECISION"
// BUT property names can't have blanks in the name (unless escaped with
// a backslash), hence also check for names where the blanks are
// replaced with underscores "_":
String value = PROPERTIES.getProperty(type);
String typeUnderscore = type.replaceAll(" ", "_");
if (value == null)
value = PROPERTIES.getProperty(typeUnderscore);
return Integer.parseInt(value);
} catch (NumberFormatException e) {
throw new IllegalArgumentException(
"Unknown data type: " + type + ". "
+ "Add entry in " + PROPERTY_FILE + ".\n"
+ "If the type contains blanks, either escape them with a backslash "
+ "or use underscores instead of blanks.");
}
}
/**
* Converts an array of objects to a string by inserting a space
* between each element. Null elements are printed as ?
*
* @param array the array of objects
* @return a value of type 'String'
*/
public static String arrayToString(Object[] array) {
String result = "";
if (array == null) {
result = "<null>";
} else {
for (int i = 0; i < array.length; i++) {
if (array[i] == null) {
result += " ?";
} else {
result += " " + array[i];
}
}
}
return result;
}
/**
* Returns the name associated with a SQL type.
*
* @param type the SQL type
* @return the name of the type
*/
public static String typeName(int type) {
switch (type) {
case Types.BIGINT :
return "BIGINT ";
case Types.BINARY:
return "BINARY";
case Types.BIT:
return "BIT";
case Types.CHAR:
return "CHAR";
case Types.DATE:
return "DATE";
case Types.DECIMAL:
return "DECIMAL";
case Types.DOUBLE:
return "DOUBLE";
case Types.FLOAT:
return "FLOAT";
case Types.INTEGER:
return "INTEGER";
case Types.LONGVARBINARY:
return "LONGVARBINARY";
case Types.LONGVARCHAR:
return "LONGVARCHAR";
case Types.NULL:
return "NULL";
case Types.NUMERIC:
return "NUMERIC";
case Types.OTHER:
return "OTHER";
case Types.REAL:
return "REAL";
case Types.SMALLINT:
return "SMALLINT";
case Types.TIME:
return "TIME";
case Types.TIMESTAMP:
return "TIMESTAMP";
case Types.TINYINT:
return "TINYINT";
case Types.VARBINARY:
return "VARBINARY";
case Types.VARCHAR:
return "VARCHAR";
default:
return "Unknown";
}
}
/**
* Returns the tip text for this property.
*
* @return tip text for this property suitable for
* displaying in the explorer/experimenter gui
*/
public String databaseURLTipText() {
return "Set the URL to the database.";
}
/**
* Get the value of DatabaseURL.
*
* @return Value of DatabaseURL.
*/
public String getDatabaseURL() {
return m_DatabaseURL;
}
/**
* Set the value of DatabaseURL.
*
* @param newDatabaseURL Value to assign to DatabaseURL.
*/
public void setDatabaseURL(String newDatabaseURL) {
m_DatabaseURL = newDatabaseURL;
}
/**
* Returns the tip text for this property.
*
* @return tip text for this property suitable for
* displaying in the explorer/experimenter gui
*/
public String debugTipText() {
return "Whether debug information is printed.";
}
/**
* Sets whether there should be printed some debugging output to stderr or not.
*
* @param d true if output should be printed
*/
public void setDebug(boolean d) {
m_Debug = d;
}
/**
* Gets whether there should be printed some debugging output to stderr or not.
*
* @return true if output should be printed
*/
public boolean getDebug() {
return m_Debug;
}
/**
* Returns the tip text for this property.
*
* @return tip text for this property suitable for
* displaying in the explorer/experimenter gui
*/
public String usernameTipText() {
return "The user to use for connecting to the database.";
}
/**
* Set the database username.
*
* @param username Username for Database.
*/
public void setUsername(String username){
m_userName = username;
}
/**
* Get the database username.
*
* @return Database username
*/
public String getUsername(){
return m_userName;
}
/**
* Returns the tip text for this property.
*
* @return tip text for this property suitable for
* displaying in the explorer/experimenter gui
*/
public String passwordTipText() {
return "The password to use for connecting to the database.";
}
/**
* Set the database password.
*
* @param password Password for Database.
*/
public void setPassword(String password){
m_password = password;
}
/**
* Get the database password.
*
* @return Password for Database.
*/
public String getPassword(){
return m_password;
}
/**
* Opens a connection to the database.
*
* @throws Exception if an error occurs
*/
public void connectToDatabase() throws Exception {
if (m_Debug) {
System.err.println("Connecting to " + m_DatabaseURL);
}
if (m_Connection == null) {
if (m_userName.equals("")) {
try {
m_Connection = DriverManager.getConnection(m_DatabaseURL);
} catch (java.sql.SQLException e) {
// Try loading the drivers
for (int i = 0; i < DRIVERS.size(); i++) {
try {
Class.forName((String)DRIVERS.elementAt(i));
} catch (Exception ex) {
// Drop through
}
}
m_Connection = DriverManager.getConnection(m_DatabaseURL);
}
} else {
try {
m_Connection = DriverManager.getConnection(m_DatabaseURL, m_userName,
m_password);
} catch (java.sql.SQLException e) {
// Try loading the drivers
for (int i = 0; i < DRIVERS.size(); i++) {
try {
Class.forName((String)DRIVERS.elementAt(i));
} catch (Exception ex) {
// Drop through
}
}
m_Connection = DriverManager.getConnection(m_DatabaseURL, m_userName,
m_password);
}
}
}
m_Connection.setAutoCommit(m_setAutoCommit);
}
/**
* Closes the connection to the database.
*
* @throws Exception if an error occurs
*/
public void disconnectFromDatabase() throws Exception {
if (m_Debug) {
System.err.println("Disconnecting from " + m_DatabaseURL);
}
if (m_Connection != null) {
m_Connection.close();
m_Connection = null;
}
}
/**
* Returns true if a database connection is active.
*
* @return a value of type 'boolean'
*/
public boolean isConnected() {
return (m_Connection != null);
}
/**
* Returns whether the cursors only support forward movement or are
* scroll sensitive (with ResultSet.CONCUR_READ_ONLY concurrency).
* Returns always false if not connected
*
* @return true if connected and the cursor is scroll-sensitive
* @see ResultSet#TYPE_SCROLL_SENSITIVE
* @see ResultSet#TYPE_FORWARD_ONLY
* @see ResultSet#CONCUR_READ_ONLY
*/
public boolean isCursorScrollSensitive() {
boolean result;
result = false;
try {
if (isConnected())
result = m_Connection.getMetaData().supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}
catch (Exception e) {
// ignored
}
return result;
}
/**
* Checks whether cursors are scrollable in general, false otherwise
* (also if not connected).
*
* @return true if scrollable and connected
* @see #getSupportedCursorScrollType()
*/
public boolean isCursorScrollable() {
return (getSupportedCursorScrollType() != -1);
}
/**
* Returns the type of scrolling that the cursor supports, -1 if not
* supported or not connected. Checks first for TYPE_SCROLL_SENSITIVE
* and then for TYPE_SCROLL_INSENSITIVE. In both cases CONCUR_READ_ONLY
* as concurrency is used.
*
* @return the scroll type, or -1 if not connected or no scrolling supported
* @see ResultSet#TYPE_SCROLL_SENSITIVE
* @see ResultSet#TYPE_SCROLL_INSENSITIVE
*/
public int getSupportedCursorScrollType() {
int result;
result = -1;
try {
if (isConnected()) {
if (m_Connection.getMetaData().supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY))
result = ResultSet.TYPE_SCROLL_SENSITIVE;
if (result == -1) {
if (m_Connection.getMetaData().supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY))
result = ResultSet.TYPE_SCROLL_INSENSITIVE;
}
}
}
catch (Exception e) {
// ignored
}
return result;
}
/**
* Executes a SQL query. Caller must clean up manually with
* <code>close()</code>.
*
* @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 <code>close(ResultSet)</code>. 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)
? "<null>"
: EXP_RESULT_PREFIX
+ tableName));
}
return (tableName == null) ? tableName : EXP_RESULT_PREFIX + tableName;
}
/**
* Creates a results table for the supplied result producer.
*
* @param rp the ResultProducer generating the results
* @param tableName the name of the resultsTable
* @return the name of the created results table
* @throws Exception if an error occurs.
*/
public String createResultsTable(ResultProducer rp, String tableName)
throws Exception {
if (m_Debug) {
System.err.println("Creating results table " + tableName + "...");
}
String query = "CREATE TABLE " + tableName + " ( ";
// Loop over the key fields
String [] names = rp.getKeyNames();
Object [] types = rp.getKeyTypes();
if (names.length != types.length) {
throw new Exception("key names types differ in length");
}
for (int i = 0; i < names.length; i++) {
query += "Key_" + names[i] + " ";
if (types[i] instanceof Double) {
query += m_doubleType;
} else if (types[i] instanceof String) {
// Workaround for MySQL (doesn't support LONGVARCHAR)
// 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 += "TEXT ";
} else { */
//query += "LONGVARCHAR ";
query += m_stringType+" ";
//}
} else {
throw new Exception("Unknown/unsupported field type in key");
}
query += ", ";
}
// Loop over the result fields
names = rp.getResultNames();
types = rp.getResultTypes();
if (names.length != types.length) {
throw new Exception("result names and types differ in length");
}
for (int i = 0; i < names.length; i++) {
query += names[i] + " ";
if (types[i] instanceof Double) {
query += m_doubleType;
} else if (types[i] instanceof String) {
// Workaround for MySQL (doesn't support LONGVARCHAR)
// 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().
equals("InstantDB JDBC Driver"))) {
query += "TEXT ";
} else {*/
//query += "LONGVARCHAR ";
query += m_stringType+" ";
//}
} else {
throw new Exception("Unknown/unsupported field type in key");
}
if (i < names.length - 1) {
query += ", ";
}
}
query += " )";
update(query);
if (m_Debug)
System.err.println("table created");
close();
if (m_createIndex) {
query = "CREATE UNIQUE INDEX Key_IDX ON "+ tableName +" (";
String [] keyNames = rp.getKeyNames();
boolean first = true;
for (int i = 0; i < keyNames.length; i++) {
if (keyNames[i] != null) {
if (first) {
first = false;
query += "Key_" + keyNames[i];
} else {
query += ",Key_" + keyNames[i];
}
}
}
query += ")";
update(query);
}
return tableName;
}
/**
* Sets the keywords (comma-separated list) to use.
*
* @param value the list of keywords
*/
public void setKeywords(String value) {
String[] keywords;
int i;
m_Keywords.clear();
keywords = value.replaceAll(" ", "").split(",");
for (i = 0; i < keywords.length; i++)
m_Keywords.add(keywords[i].toUpperCase());
}
/**
* Returns the currently stored keywords (as comma-separated list).
*
* @return the list of keywords
*/
public String getKeywords() {
String result;
Vector<String> list;
int i;
list = new Vector<String>(m_Keywords);
Collections.sort(list);
result = "";
for (i = 0; i < list.size(); i++) {
if (i > 0)
result += ",";
result += list.get(i);
}
return result;
}
/**
* Sets the mask character to append to table or attribute names that
* are a reserved keyword.
*
* @param value the new character
*/
public void setKeywordsMaskChar(String value) {
m_KeywordsMaskChar = value;
}
/**
* Returns the currently set mask character.
*
* @return the character
*/
public String getKeywordsMaskChar() {
return m_KeywordsMaskChar;
}
/**
* Checks whether the given string is a reserved keyword.
*
* @param s the string to check
* @return true if the string is a keyword
* @see #m_Keywords
*/
public boolean isKeyword(String s) {
return m_Keywords.contains(s.toUpperCase());
}
/**
* If the given string is a keyword, then the mask character will be
* appended and returned. Otherwise, the same string will be returned
* unchanged.
*
* @param s the string to check
* @return the potentially masked string
* @see #m_KeywordsMaskChar
* @see #isKeyword(String)
*/
public String maskKeyword(String s) {
if (isKeyword(s))
return s + m_KeywordsMaskChar;
else
return s;
}
/**
* Returns the revision string.
*
* @return the revision
*/
public String getRevision() {
return RevisionUtils.extract("$Revision: 8034 $");
}
/**
* Loads a properties file from an external file.
*
* @param propsFile the properties file to load, ignored if null or
* pointing to a directory
* @return the properties, null if ignored or an error occurred
*/
private static Properties loadProperties(File propsFile) {
Properties result;
Properties defaultProps = null;
try {
defaultProps = Utils.readProperties(PROPERTY_FILE);
} catch (Exception ex) {
System.err.println("Warning, unable to read default properties file(s).");
ex.printStackTrace();
}
if (propsFile == null)
return defaultProps;
if (!propsFile.exists() || propsFile.isDirectory())
return defaultProps;
try {
result = new Properties(defaultProps);
result.load(new FileInputStream(propsFile));
}
catch (Exception e) {
result = null;
System.err.println("Failed to load properties file (DatabaseUtils.java) '"
+ propsFile + "':");
e.printStackTrace();
}
return result;
}
}