java.lang.Object
- weka.experiment.DatabaseUtils

All Implemented Interfaces:

Serializable, RevisionHandler

Direct Known Subclasses:

DatabaseConnection, DatabaseResultListener, DbUtils, InstanceQuery
```
public class DatabaseUtils
extends Object
implements Serializable, RevisionHandler
```
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$

Author:

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

See Also:

Serialized Form

Field Summary

Fields
Modifier and Type	Field	Description
`static int`	`BOOL`	Type mapping for BOOL used for reading experiment results.
`static int`	`BYTE`	Type mapping for BYTE used for reading experiment results.
`static int`	`DATE`	Type mapping for DATE used for reading experiment results.
`static int`	`DOUBLE`	Type mapping for DOUBLE used for reading experiment results.
`static String`	`EXP_INDEX_TABLE`	The name of the table containing the index to experiments.
`static String`	`EXP_RESULT_COL`	The name of the column containing the results table name.
`static String`	`EXP_RESULT_PREFIX`	The prefix for result table names.
`static String`	`EXP_SETUP_COL`	The name of the column containing the experiment setup (parameters).
`static String`	`EXP_TYPE_COL`	The name of the column containing the experiment type (ResultProducer).
`static int`	`FLOAT`	Type mapping for FLOAT used for reading experiment results.
`static int`	`INTEGER`	Type mapping for INTEGER used for reading experiment results.
`static int`	`LONG`	Type mapping for LONG used for reading experiment results.
`static String`	`PROPERTY_FILE`	The name of the properties file.
`static int`	`SHORT`	Type mapping for SHORT used for reading experiment results.
`static int`	`STRING`	Type mapping for STRING used for reading experiment results.
`static int`	`TEXT`	Type mapping for TEXT used for reading, e.g., text blobs.
`static int`	`TIME`	Type mapping for TIME used for reading TIME columns.
`static int`	`TIMESTAMP`	Type mapping for TIMESTAMP used for reading java.sql.Timestamp columns

Constructor Summary

Constructors
Constructor	Description
`DatabaseUtils()`	Reads properties and sets up the database drivers.
`DatabaseUtils(File propsFile)`	Reads the properties from the specified file and sets up the database drivers.
`DatabaseUtils(Properties props)`	Uses the specified properties to set up the database drivers.

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method	Description
`static String`	`arrayToString(Object[] array)`	Converts an array of objects to a string by inserting a space between each element.
`String`	`attributeCaseFix(String columnName)`	returns key column headings in their original case.
`void`	`close()`	closes the m_PreparedStatement to avoid memory leaks.
`void`	`close(ResultSet rs)`	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.
`void`	`connectToDatabase()`	Opens a connection to the database.
`void`	`createExperimentIndex()`	Attempts to create the experiment index table.
`String`	`createExperimentIndexEntry(ResultProducer rp)`	Attempts to insert a results entry for the table into the experiment index.
`String`	`createResultsTable(ResultProducer rp, String tableName)`	Creates a results table for the supplied result producer.
`String`	`databaseURLTipText()`	Returns the tip text for this property.
`String`	`debugTipText()`	Returns the tip text for this property.
`void`	`disconnectFromDatabase()`	Closes the connection to the database.
`boolean`	`execute(String query)`	Executes a SQL query.
`boolean`	`experimentIndexExists()`	Returns true if the experiment index exists.
`String`	`getDatabaseURL()`	Get the value of DatabaseURL.
`boolean`	`getDebug()`	Gets whether there should be printed some debugging output to stderr or not.
`String`	`getKeywords()`	Returns the currently stored keywords (as comma-separated list).
`String`	`getKeywordsMaskChar()`	Returns the currently set mask character.
`String`	`getPassword()`	Get the database password.
`Object[]`	`getResultFromTable(String tableName, ResultProducer rp, Object[] key)`	Executes a database query to extract a result for the supplied key from the database.
`ResultSet`	`getResultSet()`	Gets the results generated by a previous query.
`String`	`getResultsTableName(ResultProducer rp)`	Gets the name of the experiment table that stores results from a particular ResultProducer.
`String`	`getRevision()`	Returns the revision string.
`int`	`getSupportedCursorScrollType()`	Returns the type of scrolling that the cursor supports, -1 if not supported or not connected.
`String`	`getUsername()`	Get the database username.
`void`	`initialize(File propsFile)`	Initializes the database connection.
`void`	`initialize(Properties props)`	Initializes the database connection.
`boolean`	`isConnected()`	Returns true if a database connection is active.
`boolean`	`isCursorScrollable()`	Checks whether cursors are scrollable in general, false otherwise (also if not connected).
`boolean`	`isCursorScrollSensitive()`	Returns whether the cursors only support forward movement or are scroll sensitive (with ResultSet.CONCUR_READ_ONLY concurrency).
`boolean`	`isKeyword(String s)`	Checks whether the given string is a reserved keyword.
`String`	`maskKeyword(String s)`	If the given string is a keyword, then the mask character will be appended and returned.
`String`	`passwordTipText()`	Returns the tip text for this property.
`static String`	`processKeyString(String s)`	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.
`void`	`putResultInTable(String tableName, ResultProducer rp, Object[] key, Object[] result)`	Executes a database query to insert a result for the supplied key into the database.
`ResultSet`	`select(String query)`	Executes a SQL SELECT query that returns a ResultSet.
`void`	`setDatabaseURL(String newDatabaseURL)`	Set the value of DatabaseURL.
`void`	`setDebug(boolean d)`	Sets whether there should be printed some debugging output to stderr or not.
`void`	`setKeywords(String value)`	Sets the keywords (comma-separated list) to use.
`void`	`setKeywordsMaskChar(String value)`	Sets the mask character to append to table or attribute names that are a reserved keyword.
`void`	`setPassword(String password)`	Set the database password.
`void`	`setUsername(String username)`	Set the database username.
`boolean`	`tableExists(String tableName)`	Checks that a given table exists.
`int`	`translateDBColumnType(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 String`	`typeName(int type)`	Returns the name associated with a SQL type.
`int`	`update(String query)`	Executes a SQL DDL query or an INSERT, DELETE or UPDATE.
`String`	`usernameTipText()`	Returns the tip text for this property.

Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Field Detail
  - EXP_INDEX_TABLE
```
public static final 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 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 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 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 String EXP_RESULT_PREFIX
```
    The prefix for result table names.
    
    See Also:
    
    Constant Field Values
  - PROPERTY_FILE
```
public static final String PROPERTY_FILE
```
    The name of the properties file.
    
    See Also:
    
    Constant Field Values
  - STRING
```
public static final int STRING
```
    Type mapping for STRING used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - BOOL
```
public static final int BOOL
```
    Type mapping for BOOL used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - DOUBLE
```
public static final int DOUBLE
```
    Type mapping for DOUBLE used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - BYTE
```
public static final int BYTE
```
    Type mapping for BYTE used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - SHORT
```
public static final int SHORT
```
    Type mapping for SHORT used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - INTEGER
```
public static final int INTEGER
```
    Type mapping for INTEGER used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - LONG
```
public static final int LONG
```
    Type mapping for LONG used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - FLOAT
```
public static final int FLOAT
```
    Type mapping for FLOAT used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - DATE
```
public static final int DATE
```
    Type mapping for DATE used for reading experiment results.
    
    See Also:
    
    Constant Field Values
  - TEXT
```
public static final int TEXT
```
    Type mapping for TEXT used for reading, e.g., text blobs.
    
    See Also:
    
    Constant Field Values
  - TIME
```
public static final int TIME
```
    Type mapping for TIME used for reading TIME columns.
    
    See Also:
    
    Constant Field Values
  - TIMESTAMP
```
public static final int TIMESTAMP
```
    Type mapping for TIMESTAMP used for reading java.sql.Timestamp columns
    
    See Also:
    
    Constant Field Values
- Constructor Detail
  - DatabaseUtils
```
public DatabaseUtils()
              throws Exception
```
    Reads properties and sets up the database drivers.
    
    Throws:
    
    Exception - if an error occurs
  - DatabaseUtils
```
public DatabaseUtils(File propsFile)
              throws Exception
```
    Reads the properties from the specified file and sets up the database drivers.
    
    Parameters:
    
    propsFile - the props file to load, ignored if null or pointing to a directory
    
    Throws:
    
    Exception - if an error occurs
  - DatabaseUtils
```
public DatabaseUtils(Properties props)
              throws Exception
```
    Uses the specified properties to set up the database drivers.
    
    Parameters:
    
    props - the properties to use, ignored if null
    
    Throws:
    
    Exception - if an error occurs
- Method Detail
  - initialize
```
public void initialize(File propsFile)
```
    Initializes the database connection.
    
    Parameters:
    
    propsFile - the props file to load, ignored if null or pointing to a directory
  - initialize
```
public void initialize(Properties props)
```
    Initializes the database connection.
    
    Parameters:
    
    props - the properties to obtain the parameters from, ignored if null
  - attributeCaseFix
```
public String attributeCaseFix(String columnName)
```
    returns key column headings in their original case. Used for those databases that create uppercase column names.
    
    Parameters:
    
    columnName - the column to retrieve the original case for
    
    Returns:
    
    the original case
  - translateDBColumnType
```
public int translateDBColumnType(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()). Blanks in the type are replaced with underscores "_", since Java property names can't contain blanks.
    
    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 String arrayToString(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 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 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 String getDatabaseURL()
```
    Get the value of DatabaseURL.
    
    Returns:
    
    Value of DatabaseURL.
  - setDatabaseURL
```
public void setDatabaseURL(String newDatabaseURL)
```
    Set the value of DatabaseURL.
    
    Parameters:
    
    newDatabaseURL - Value to assign to DatabaseURL.
  - debugTipText
```
public String debugTipText()
```
    Returns the tip text for this property.
    
    Returns:
    
    tip text for this property suitable for displaying in the explorer/experimenter gui
  - setDebug
```
public void setDebug(boolean d)
```
    Sets whether there should be printed some debugging output to stderr or not.
    
    Parameters:
    
    d - true if output should be printed
  - getDebug
```
public boolean getDebug()
```
    Gets whether there should be printed some debugging output to stderr or not.
    
    Returns:
    
    true if output should be printed
  - usernameTipText
```
public String usernameTipText()
```
    Returns the tip text for this property.
    
    Returns:
    
    tip text for this property suitable for displaying in the explorer/experimenter gui
  - setUsername
```
public void setUsername(String username)
```
    Set the database username.
    
    Parameters:
    
    username - Username for Database.
  - getUsername
```
public String getUsername()
```
    Get the database username.
    
    Returns:
    
    Database username
  - passwordTipText
```
public String passwordTipText()
```
    Returns the tip text for this property.
    
    Returns:
    
    tip text for this property suitable for displaying in the explorer/experimenter gui
  - setPassword
```
public void setPassword(String password)
```
    Set the database password.
    
    Parameters:
    
    password - Password for Database.
  - getPassword
```
public String getPassword()
```
    Get the database password.
    
    Returns:
    
    Password for Database.
  - connectToDatabase
```
public void connectToDatabase()
                       throws Exception
```
    Opens a connection to the database.
    
    Throws:
    
    Exception - if an error occurs
  - disconnectFromDatabase
```
public void disconnectFromDatabase()
                            throws Exception
```
    Closes the connection to the database.
    
    Throws:
    
    Exception - if an error occurs
  - isConnected
```
public boolean isConnected()
```
    Returns true if a database connection is active.
    
    Returns:
    
    a value of type 'boolean'
  - isCursorScrollSensitive
```
public boolean isCursorScrollSensitive()
```
    Returns whether the cursors only support forward movement or are scroll sensitive (with ResultSet.CONCUR_READ_ONLY concurrency). Returns always false if not connected
    
    Returns:
    
    true if connected and the cursor is scroll-sensitive
    
    See Also:
    
    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY
  - isCursorScrollable
```
public boolean isCursorScrollable()
```
    Checks whether cursors are scrollable in general, false otherwise (also if not connected).
    
    Returns:
    
    true if scrollable and connected
    
    See Also:
    
    getSupportedCursorScrollType()
  - getSupportedCursorScrollType
```
public int getSupportedCursorScrollType()
```
    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.
    
    Returns:
    
    the scroll type, or -1 if not connected or no scrolling supported
    
    See Also:
    
    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.TYPE_SCROLL_INSENSITIVE
  - execute
```
public boolean execute(String query)
                throws SQLException
```
    Executes a SQL query. Caller must clean up manually with close().
    
    Parameters:
    
    query - the SQL query
    
    Returns:
    
    true if the query generated results
    
    Throws:
    
    SQLException - if an error occurs
    
    See Also:
    
    close()
  - getResultSet
```
public ResultSet getResultSet()
                       throws SQLException
```
    Gets the results generated by a previous query. Caller must clean up manually with close(ResultSet). Returns null if object has been deserialized.
    
    Returns:
    
    the result set.
    
    Throws:
    
    SQLException - if an error occurs
    
    See Also:
    
    close(ResultSet)
  - update
```
public int update(String query)
           throws SQLException
```
    Executes a SQL DDL query or an INSERT, DELETE or UPDATE.
    
    Parameters:
    
    query - the SQL DDL query
    
    Returns:
    
    the number of affected rows
    
    Throws:
    
    SQLException - if an error occurs
  - select
```
public ResultSet select(String query)
                 throws SQLException
```
    Executes a SQL SELECT query that returns a ResultSet. Note: the ResultSet object must be closed by the caller.
    
    Parameters:
    
    query - the SQL query
    
    Returns:
    
    the generated ResultSet
    
    Throws:
    
    SQLException - if an error occurs
  - close
```
public void close(ResultSet rs)
```
    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.
    
    Parameters:
    
    rs - the ResultSet to clean up
  - close
```
public void close()
```
    closes the m_PreparedStatement to avoid memory leaks.
  - tableExists
```
public boolean tableExists(String tableName)
                    throws Exception
```
    Checks that a given table exists.
    
    Parameters:
    
    tableName - the name of the table to look for.
    
    Returns:
    
    true if the table exists.
    
    Throws:
    
    Exception - if an error occurs.
  - processKeyString
```
public static String processKeyString(String s)
```
    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.
    
    Parameters:
    
    s - the string to work on
    
    Returns:
    
    the processed string
  - getResultFromTable
```
public Object[] getResultFromTable(String tableName,
                                   ResultProducer rp,
                                   Object[] key)
                            throws 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:
    
    Exception - if an error occurs
  - putResultInTable
```
public void putResultInTable(String tableName,
                             ResultProducer rp,
                             Object[] key,
                             Object[] result)
                      throws 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
    
    Throws:
    
    Exception - if an error occurs
  - experimentIndexExists
```
public boolean experimentIndexExists()
                              throws Exception
```
    Returns true if the experiment index exists.
    
    Returns:
    
    true if the index exists
    
    Throws:
    
    Exception - if an error occurs
  - createExperimentIndex
```
public void createExperimentIndex()
                           throws Exception
```
    Attempts to create the experiment index table.
    
    Throws:
    
    Exception - if an error occurs.
  - createExperimentIndexEntry
```
public String createExperimentIndexEntry(ResultProducer rp)
                                  throws 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:
    
    Exception - if an error occurs.
  - getResultsTableName
```
public String getResultsTableName(ResultProducer rp)
                           throws 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:
    
    Exception - if an error occurs
  - createResultsTable
```
public String createResultsTable(ResultProducer rp,
                                 String tableName)
                          throws 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:
    
    Exception - if an error occurs.
  - setKeywords
```
public void setKeywords(String value)
```
    Sets the keywords (comma-separated list) to use.
    
    Parameters:
    
    value - the list of keywords
  - getKeywords
```
public String getKeywords()
```
    Returns the currently stored keywords (as comma-separated list).
    
    Returns:
    
    the list of keywords
  - setKeywordsMaskChar
```
public void setKeywordsMaskChar(String value)
```
    Sets the mask character to append to table or attribute names that are a reserved keyword.
    
    Parameters:
    
    value - the new character
  - getKeywordsMaskChar
```
public String getKeywordsMaskChar()
```
    Returns the currently set mask character.
    
    Returns:
    
    the character
  - isKeyword
```
public boolean isKeyword(String s)
```
    Checks whether the given string is a reserved keyword.
    
    Parameters:
    
    s - the string to check
    
    Returns:
    
    true if the string is a keyword
    
    See Also:
    
    m_Keywords
  - maskKeyword
```
public String maskKeyword(String s)
```
    If the given string is a keyword, then the mask character will be appended and returned. Otherwise, the same string will be returned unchanged.
    
    Parameters:
    
    s - the string to check
    
    Returns:
    
    the potentially masked string
    
    See Also:
    
    m_KeywordsMaskChar, isKeyword(String)
  - getRevision
```
public String getRevision()
```
    Returns the revision string.
    
    Specified by:
    
    getRevision in interface RevisionHandler
    
    Returns:
    
    the revision

Class DatabaseUtils

Field Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Field Detail

EXP_INDEX_TABLE

EXP_TYPE_COL

EXP_SETUP_COL

EXP_RESULT_COL

EXP_RESULT_PREFIX

PROPERTY_FILE

STRING

BOOL

DOUBLE

BYTE

SHORT

INTEGER

LONG

FLOAT

DATE

TEXT

TIME

TIMESTAMP

Constructor Detail

DatabaseUtils

DatabaseUtils

DatabaseUtils

Method Detail

initialize

initialize

attributeCaseFix

translateDBColumnType

arrayToString

typeName

databaseURLTipText

getDatabaseURL

setDatabaseURL

debugTipText

setDebug

getDebug

usernameTipText

setUsername

getUsername

passwordTipText

setPassword

getPassword

connectToDatabase

disconnectFromDatabase

isConnected

isCursorScrollSensitive

isCursorScrollable

getSupportedCursorScrollType

execute

getResultSet

update

select

close

close

tableExists

processKeyString

getResultFromTable

putResultInTable

experimentIndexExists

createExperimentIndex

createExperimentIndexEntry

getResultsTableName

createResultsTable

setKeywords

getKeywords

setKeywordsMaskChar

getKeywordsMaskChar

isKeyword

maskKeyword

getRevision