weka.experiment
Class DatabaseUtils

java.lang.Object
  weka.experiment.DatabaseUtils

All Implemented Interfaces:

java.io.Serializable

Direct Known Subclasses:

DatabaseResultListener, InstanceQuery

public class DatabaseUtils

extends java.lang.Object

implements java.io.Serializable

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:

 jdbcDriver=jdbc.idbDriver
 jdbcURL=jdbc:idb=experiments.prp
 

Version:

$Revision: 1.17 $

Author:

Len Trigg (trigg@cs.waikato.ac.nz)

See Also:

Serialized Form

Field Summary

static int	BOOL
static int	BYTE
static int	DATE
static int	DOUBLE
protected static java.util.Vector	DRIVERS Holds the jdbc drivers to be used (only to stop them being gc'ed)
static java.lang.String	EXP_INDEX_TABLE The name of the table containing the index to experiments
static java.lang.String	EXP_RESULT_COL The name of the column containing the results table name
static java.lang.String	EXP_RESULT_PREFIX The prefix for result table names
static java.lang.String	EXP_SETUP_COL The name of the column containing the experiment setup (parameters)
static java.lang.String	EXP_TYPE_COL The name of the column containing the experiment type (ResultProducer)
static int	FLOAT
static int	INTEGER
static int	LONG
protected boolean	m_checkForUpperCaseNames
protected java.sql.Connection	m_Connection The database connection
protected boolean	m_createIndex
protected java.lang.String	m_DatabaseURL Database URL
protected boolean	m_Debug True if debugging output should be printed
protected java.lang.String	m_doubleType
protected java.lang.String	m_intType
protected java.lang.String	m_password Database Password
protected java.sql.PreparedStatement	m_PreparedStatement The prepared statement used for database queries.
protected boolean	m_setAutoCommit
protected java.lang.String	m_stringType mappings used for creating Tables.
protected java.lang.String	m_userName Database username
protected static java.util.Properties	PROPERTIES Properties associated with the database connection
protected static java.lang.String	PROPERTY_FILE The name of the properties file
static int	SHORT
static int	STRING

Constructor Summary

DatabaseUtils()
Sets up the database drivers

Method Summary

static java.lang.String	arrayToString(java.lang.Object[] array) Converts an array of objects to a string by inserting a space between each element.
protected java.lang.String	attributeCaseFix(java.lang.String columnName)
void	connectToDatabase() Opens a connection to the database
void	createExperimentIndex() Attempts to create the experiment index table
java.lang.String	createExperimentIndexEntry(ResultProducer rp) Attempts to insert a results entry for the table into the experiment index.
java.lang.String	createResultsTable(ResultProducer rp, java.lang.String tableName) Creates a results table for the supplied result producer.
java.lang.String	databaseURLTipText() Returns the tip text for this property
void	disconnectFromDatabase() Closes the connection to the database.
boolean	execute(java.lang.String query) Executes a SQL query.
boolean	experimentIndexExists() Returns true if the experiment index exists.
java.lang.String	getDatabaseURL() Get the value of DatabaseURL.
java.lang.String	getPassword() Get the database password
java.lang.Object[]	getResultFromTable(java.lang.String tableName, ResultProducer rp, java.lang.Object[] key) Executes a database query to extract a result for the supplied key from the database.
java.sql.ResultSet	getResultSet() Gets the results generated by a previous query.
java.lang.String	getResultsTableName(ResultProducer rp) Gets the name of the experiment table that stores results from a particular ResultProducer.
java.lang.String	getUsername() Get the database username
boolean	isConnected() Returns true if a database connection is active.
protected boolean	isKeyInTable(java.lang.String tableName, ResultProducer rp, java.lang.Object[] key) Executes a database query to see whether a result for the supplied key is already in the database.
void	putResultInTable(java.lang.String tableName, ResultProducer rp, java.lang.Object[] key, java.lang.Object[] result) Executes a database query to insert a result for the supplied key into the database.
private java.lang.String	safeDoubleToString(java.lang.Double number) Inserts a + if the double is in scientific notation.
void	setDatabaseURL(java.lang.String newDatabaseURL) Set the value of DatabaseURL.
void	setPassword(java.lang.String password) Set the database password
void	setUsername(java.lang.String username) Set the database username
boolean	tableExists(java.lang.String tableName) Checks that a given table exists.
(package private) int	translateDBColumnType(java.lang.String type) 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())
static java.lang.String	typeName(int type) Returns the name associated with a SQL type.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

EXP_INDEX_TABLE

public static final java.lang.String EXP_INDEX_TABLE

The name of the table containing the index to experiments

See Also:

Constant Field Values

EXP_TYPE_COL

public static final java.lang.String EXP_TYPE_COL

The name of the column containing the experiment type (ResultProducer)

See Also:

Constant Field Values

EXP_SETUP_COL

public static final java.lang.String EXP_SETUP_COL

The name of the column containing the experiment setup (parameters)

See Also:

Constant Field Values

EXP_RESULT_COL

public static final java.lang.String EXP_RESULT_COL

The name of the column containing the results table name

See Also:

Constant Field Values

EXP_RESULT_PREFIX

public static final java.lang.String EXP_RESULT_PREFIX

The prefix for result table names

See Also:

Constant Field Values

PROPERTY_FILE

protected static java.lang.String PROPERTY_FILE

The name of the properties file

DRIVERS

protected static java.util.Vector DRIVERS

Holds the jdbc drivers to be used (only to stop them being gc'ed)

PROPERTIES

protected static java.util.Properties PROPERTIES

Properties associated with the database connection

STRING

public static final int STRING

See Also:

Constant Field Values

BOOL

public static final int BOOL

See Also:

Constant Field Values

DOUBLE

public static final int DOUBLE

See Also:

Constant Field Values

BYTE

public static final int BYTE

See Also:

Constant Field Values

SHORT

public static final int SHORT

See Also:

Constant Field Values

INTEGER

public static final int INTEGER

See Also:

Constant Field Values

LONG

public static final int LONG

See Also:

Constant Field Values

FLOAT

public static final int FLOAT

See Also:

Constant Field Values

DATE

public static final int DATE

See Also:

Constant Field Values

m_DatabaseURL

protected java.lang.String m_DatabaseURL

Database URL

m_PreparedStatement

protected java.sql.PreparedStatement m_PreparedStatement

The prepared statement used for database queries.

m_Connection

protected java.sql.Connection m_Connection

The database connection

m_Debug

protected boolean m_Debug

True if debugging output should be printed

m_userName

protected java.lang.String m_userName

Database username

m_password

protected java.lang.String m_password

Database Password

m_stringType

protected java.lang.String m_stringType

mappings used for creating Tables. Can be overridden in DatabaseUtils.props

m_intType

protected java.lang.String m_intType

m_doubleType

protected java.lang.String m_doubleType

m_checkForUpperCaseNames

protected boolean m_checkForUpperCaseNames

m_setAutoCommit

protected boolean m_setAutoCommit

m_createIndex

protected boolean m_createIndex

Constructor Detail

DatabaseUtils

public DatabaseUtils()
              throws java.lang.Exception

Sets up the database drivers

Throws:

java.lang.Exception - if an error occurs

Method Detail

attributeCaseFix

protected java.lang.String attributeCaseFix(java.lang.String columnName)

setUsername

public void setUsername(java.lang.String username)

Set the database username

Parameters:

username - Username for Database.

getUsername

public java.lang.String getUsername()

Get the database username

Returns:

Database username

setPassword

public void setPassword(java.lang.String password)

Set the database password

Parameters:

password - Password for Database.

getPassword

public java.lang.String getPassword()

Get the database password

Returns:

Password for Database.

translateDBColumnType

int translateDBColumnType(java.lang.String type)

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())

Parameters:

type - the column type as retrieved with java.sql.MetaData.getColumnTypeName(int)

Returns:

an integer value that indicates which data type / get()-Method to use in order to retrieve values from the

arrayToString

public static java.lang.String arrayToString(java.lang.Object[] array)

Converts an array of objects to a string by inserting a space between each element. Null elements are printed as ?

Parameters:

array - the array of objects

Returns:

a value of type 'String'

typeName

public static java.lang.String typeName(int type)

Returns the name associated with a SQL type.

Parameters:

type - the SQL type

Returns:

the name of the type

databaseURLTipText

public java.lang.String databaseURLTipText()

Returns the tip text for this property

Returns:

tip text for this property suitable for displaying in the explorer/experimenter gui

getDatabaseURL

public java.lang.String getDatabaseURL()

Get the value of DatabaseURL.

Returns:

Value of DatabaseURL.

setDatabaseURL

public void setDatabaseURL(java.lang.String newDatabaseURL)

Set the value of DatabaseURL.

Parameters:

newDatabaseURL - Value to assign to DatabaseURL.

connectToDatabase

public void connectToDatabase()
                       throws java.lang.Exception

Opens a connection to the database

Throws:

java.lang.Exception - if an error occurs

disconnectFromDatabase

public void disconnectFromDatabase()
                            throws java.lang.Exception

Closes the connection to the database.

Throws:

java.lang.Exception - if an error occurs

isConnected

public boolean isConnected()

Returns true if a database connection is active.

Returns:

a value of type 'boolean'

execute

public boolean execute(java.lang.String query)
                throws java.sql.SQLException

Executes a SQL query.

Parameters:

query - the SQL query

Returns:

true if the query generated results

Throws:

java.sql.SQLException - if an error occurs

getResultSet

public java.sql.ResultSet getResultSet()
                                throws java.sql.SQLException

Gets the results generated by a previous query.

Returns:

the result set.

Throws:

java.sql.SQLException - if an error occurs

tableExists

public boolean tableExists(java.lang.String tableName)
                    throws java.lang.Exception

Checks that a given table exists.

Parameters:

tableName - the name of the table to look for.

Returns:

true if the table exists.

Throws:

java.lang.Exception - if an error occurs.

isKeyInTable

protected boolean isKeyInTable(java.lang.String tableName,
                               ResultProducer rp,
                               java.lang.Object[] key)
                        throws java.lang.Exception

Executes a database query to see whether a result for the supplied key is already in the database.

Parameters:

tableName - the name of the table to search for the key in

rp - the ResultProducer who will generate the result if required

key - the key for the result

Returns:

true if the result with that key is in the database already

Throws:

java.lang.Exception - if an error occurs

getResultFromTable

public java.lang.Object[] getResultFromTable(java.lang.String tableName,
                                             ResultProducer rp,
                                             java.lang.Object[] key)
                                      throws java.lang.Exception

Executes a database query to extract a result for the supplied key from the database.

Parameters:

tableName - the name of the table where the result is stored

rp - the ResultProducer who will generate the result if required

key - the key for the result

Returns:

true if the result with that key is in the database already

Throws:

java.lang.Exception - if an error occurs

putResultInTable

public void putResultInTable(java.lang.String tableName,
                             ResultProducer rp,
                             java.lang.Object[] key,
                             java.lang.Object[] result)
                      throws java.lang.Exception

Executes a database query to insert a result for the supplied key into the database.

Parameters:

tableName - the name of the table where the result is stored

rp - the ResultProducer who will generate the result if required

key - the key for the result

result - the result to store

Returns:

true if the result with that key is in the database already

Throws:

java.lang.Exception - if an error occurs

safeDoubleToString

private java.lang.String safeDoubleToString(java.lang.Double number)

Inserts a + if the double is in scientific notation. MySQL doesn't understand the number otherwise.

experimentIndexExists

public boolean experimentIndexExists()
                              throws java.lang.Exception

Returns true if the experiment index exists.

Returns:

true if the index exists

Throws:

java.lang.Exception - if an error occurs

createExperimentIndex

public void createExperimentIndex()
                           throws java.lang.Exception

Attempts to create the experiment index table

Throws:

java.lang.Exception - if an error occurs.

createExperimentIndexEntry

public java.lang.String createExperimentIndexEntry(ResultProducer rp)
                                            throws java.lang.Exception

Attempts to insert a results entry for the table into the experiment index.

Parameters:

rp - the ResultProducer generating the results

Returns:

the name of the created results table

Throws:

java.lang.Exception - if an error occurs.

getResultsTableName

public java.lang.String getResultsTableName(ResultProducer rp)
                                     throws java.lang.Exception

Gets the name of the experiment table that stores results from a particular ResultProducer.

Parameters:

rp - the ResultProducer

Returns:

the name of the table where the results for this ResultProducer are stored, or null if there is no table for this ResultProducer.

Throws:

java.lang.Exception - if an error occurs

createResultsTable

public java.lang.String createResultsTable(ResultProducer rp,
                                           java.lang.String tableName)
                                    throws java.lang.Exception

Creates a results table for the supplied result producer.

Parameters:

rp - the ResultProducer generating the results

tableName - the name of the resultsTable

Returns:

the name of the created results table

Throws:

java.lang.Exception - if an error occurs.