Package weka.experiment

# Class DatabaseUtils

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: 11887 $
Author:
  Len Trigg (trigg@cs.waikato.ac.nz)
See Also:
  - Serialized Form

## Field Summary

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

## Method Summary

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.
`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.
`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 Details

### 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 Details

### DatabaseUtils

public DatabaseUtils() throws Exception
Reads properties and sets up the database drivers.

Throws:
  `Exception` - if an error occurs

## Method Details

### 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