Package weka.experiment
Class DatabaseUtils
- java.lang.Object
-
- weka.experiment.DatabaseUtils
-
- All Implemented Interfaces:
java.io.Serializable
,RevisionHandler
- Direct Known Subclasses:
DatabaseConnection
,DatabaseResultListener
,DbUtils
,InstanceQuery
public class DatabaseUtils extends java.lang.Object implements java.io.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 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 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
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 java.lang.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.
-
Method Summary
All Methods Static Methods Instance Methods Concrete Methods Modifier and Type Method Description static java.lang.String
arrayToString(java.lang.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(java.sql.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.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.java.lang.String
debugTipText()
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.boolean
getDebug()
Gets whether there should be printed some debugging output to stderr or not.java.lang.String
getKeywords()
Returns the currently stored keywords (as comma-separated list).java.lang.String
getKeywordsMaskChar()
Returns the currently set mask character.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
getRevision()
Returns the revision string.int
getSupportedCursorScrollType()
Returns the type of scrolling that the cursor supports, -1 if not supported or not connected.java.lang.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(java.lang.String s)
Checks whether the given string is a reserved keyword.java.lang.String
maskKeyword(java.lang.String s)
If the given string is a keyword, then the mask character will be appended and returned.java.lang.String
passwordTipText()
Returns the tip text for this property.static java.lang.String
processKeyString(java.lang.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(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.java.sql.ResultSet
select(java.lang.String query)
Executes a SQL SELECT query that returns a ResultSet.void
setDatabaseURL(java.lang.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(java.lang.String value)
Sets the keywords (comma-separated list) to use.void
setKeywordsMaskChar(java.lang.String value)
Sets the mask character to append to table or attribute names that are a reserved keyword.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.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.int
update(java.lang.String query)
Executes a SQL DDL query or an INSERT, DELETE or UPDATE.java.lang.String
usernameTipText()
Returns the tip text for this property.
-
-
-
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
public static final java.lang.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
-
-
Method Detail
-
translateDBColumnType
public 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()). 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 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.
-
debugTipText
public java.lang.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 java.lang.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(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
-
passwordTipText
public java.lang.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(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.
-
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'
-
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(java.lang.String query) throws java.sql.SQLException
Executes a SQL query. Caller must clean up manually withclose()
.- Parameters:
query
- the SQL query- Returns:
- true if the query generated results
- Throws:
java.sql.SQLException
- if an error occurs- See Also:
close()
-
getResultSet
public java.sql.ResultSet getResultSet() throws java.sql.SQLException
Gets the results generated by a previous query. Caller must clean up manually withclose(ResultSet)
. Returns null if object has been deserialized.- Returns:
- the result set.
- Throws:
java.sql.SQLException
- if an error occurs- See Also:
close(ResultSet)
-
update
public int update(java.lang.String query) throws java.sql.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:
java.sql.SQLException
- if an error occurs
-
select
public java.sql.ResultSet select(java.lang.String query) throws java.sql.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:
java.sql.SQLException
- if an error occurs
-
close
public void close(java.sql.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(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.
-
processKeyString
public static java.lang.String processKeyString(java.lang.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 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 storedrp
- the ResultProducer who will generate the result if requiredkey
- 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 storedrp
- the ResultProducer who will generate the result if requiredkey
- the key for the resultresult
- the result to store- Throws:
java.lang.Exception
- if an error occurs
-
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 resultstableName
- the name of the resultsTable- Returns:
- the name of the created results table
- Throws:
java.lang.Exception
- if an error occurs.
-
setKeywords
public void setKeywords(java.lang.String value)
Sets the keywords (comma-separated list) to use.- Parameters:
value
- the list of keywords
-
getKeywords
public java.lang.String getKeywords()
Returns the currently stored keywords (as comma-separated list).- Returns:
- the list of keywords
-
setKeywordsMaskChar
public void setKeywordsMaskChar(java.lang.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 java.lang.String getKeywordsMaskChar()
Returns the currently set mask character.- Returns:
- the character
-
isKeyword
public boolean isKeyword(java.lang.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 java.lang.String maskKeyword(java.lang.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 java.lang.String getRevision()
Returns the revision string.- Specified by:
getRevision
in interfaceRevisionHandler
- Returns:
- the revision
-
-