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: 13477 $
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.
  `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

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

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

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