nz.org.venice.quote
Class DatabaseManager

java.lang.Object
  nz.org.venice.quote.DatabaseManager

public class DatabaseManager

extends java.lang.Object

Provides functionality to manage database connections and ensures the relevant tables exist. Classes manage their own queries separately.

Author:

Mark Hummel

See Also:

DatabaseQuoteSource, DatabaseAlertReader, DatabaseAlertWriter

Field Summary

static int	ALERT_BOUND_TYPE_COLUMN
static java.lang.String	ALERT_BOUND_TYPE_FIELD
static int	ALERT_DATESET_COLUMN
static int	ALERT_ENABLED_COLUMN
static java.lang.String	ALERT_ENABLED_FIELD
static int	ALERT_END_DATE_COLUMN
static java.lang.String	ALERT_END_DATE_FIELD
static int	ALERT_HOST_COLUMN
static java.lang.String	ALERT_HOST_FIELD
static int	ALERT_MAX_HOST_LEN
static int	ALERT_MAX_TARGET_EXP_LEN
static int	ALERT_MAX_USER_LEN
static int	ALERT_START_DATE_COLUMN
static java.lang.String	ALERT_START_DATE_FIELD
static int	ALERT_SYMBOL_COLUMN
static java.lang.String	ALERT_SYMBOL_FIELD
static java.lang.String	ALERT_TABLE_NAME
static int	ALERT_TARGET_COLUMN
static java.lang.String	ALERT_TARGET_FIELD
static int	ALERT_TARGET_TYPE_COLUMN
static java.lang.String	ALERT_TARGET_TYPE_FIELD
static int	ALERT_USER_COLUMN
static java.lang.String	ALERT_USER_FIELD
static int	ALERT_UUID_COLUMN
static int	DATE_COLUMN
static java.lang.String	DATE_FIELD
static int	DAY_CLOSE_COLUMN
static java.lang.String	DAY_CLOSE_FIELD
static int	DAY_HIGH_COLUMN
static java.lang.String	DAY_HIGH_FIELD
static int	DAY_LOW_COLUMN
static java.lang.String	DAY_LOW_FIELD
static int	DAY_OPEN_COLUMN
static java.lang.String	DAY_OPEN_FIELD
static int	DAY_VOLUME_COLUMN
static java.lang.String	DAY_VOLUME_FIELD
static java.lang.String	DESTINATION_CURRENCY_FIELD
static java.lang.String	END_DATE_ALERT_TABLE_NAME
static java.lang.String	EXCHANGE_RATE_FIELD
static java.lang.String	EXCHANGE_TABLE_NAME
static int	EXTERNAL External database.
static java.lang.String	GONDOLA_ALERT_TABLE_NAME
static int	HSQLDB Hypersonic SQL Database.
static java.lang.String	HSQLDB_SOFTWARE
static int	INTERNAL Internal database.
static java.lang.String	LOOKUP_TABLE_NAME
static int	MYSQL MySQL Database.
static java.lang.String	MYSQL_SOFTWARE
static java.lang.String	NAME_FIELD
static java.lang.String	OHLCV_ALERT_TABLE_NAME
static int	OTHER Any generic SQL Database.
static int	POSTGRESQL PostgreSQL Database.
static java.lang.String	POSTGRESQL_SOFTWARE
static java.lang.String	SHARE_TABLE_NAME
static java.lang.String	SOURCE_CURRENCY_FIELD
static java.lang.String	START_DATE_ALERT_TABLE_NAME
static int	SYMBOL_COLUMN
static java.lang.String	SYMBOL_FIELD

Constructor Summary

DatabaseManager(java.lang.String fileName)
Create a new quote source to connect to an internal HSQL database stored in the given file.

DatabaseManager(java.lang.String software, java.lang.String driver, java.lang.String host, java.lang.String port, java.lang.String database, java.lang.String username, java.lang.String password)
Creates a new database connection.

Method Summary

java.lang.String	addField(java.lang.String field)
java.lang.String	addField(java.lang.String field, boolean last)
java.lang.String	addField(java.lang.String field, java.lang.String type)
java.lang.String	addField(java.lang.String field, java.lang.String type, boolean last)
java.lang.String	addQuotedField(java.lang.String field)
java.lang.String	addQuotedField(java.lang.String field, boolean last)
protected java.lang.String	buildDatePresentQuery(TradingDate date) Return the SQL clause for detecting whether the given date appears in the table.
protected java.lang.String	buildSymbolPresentQuery(Symbol symbol) Return the SQL clause for detecting whether the given symbol appears in the table.
java.sql.Statement	createStatement()
void	executeUpdateTransaction(java.util.List queries)
boolean	getConnection() Return True if a connection to the database was established and the database exists with the right tables.
java.lang.String	getHost()
java.util.List	getQueries(java.lang.String transactionName)
java.lang.String	getUserName()
java.lang.String	getUUID()
java.lang.String	left(java.lang.String field, int length) Return the SQL clause for returning the left most characters in a string.
java.lang.String	replaceParameter(java.lang.String query, java.lang.String parameterName, java.lang.String parameterValue)
void	shutdown() Shutdown the database.
boolean	supportForSingleRowUpdatesOnly()
boolean	supportForTransactions()
java.lang.String	toSQLDateString(TradingDate date) Return a date string that can be used as part of an SQL query.

Methods inherited from class java.lang.Object

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

Field Detail

MYSQL

public static final int MYSQL

MySQL Database.

See Also:

Constant Field Values

POSTGRESQL

public static final int POSTGRESQL

PostgreSQL Database.

See Also:

Constant Field Values

HSQLDB

public static final int HSQLDB

Hypersonic SQL Database.

See Also:

Constant Field Values

OTHER

public static final int OTHER

Any generic SQL Database.

See Also:

Constant Field Values

INTERNAL

public static final int INTERNAL

Internal database.

See Also:

Constant Field Values

EXTERNAL

public static final int EXTERNAL

External database.

See Also:

Constant Field Values

MYSQL_SOFTWARE

public static final java.lang.String MYSQL_SOFTWARE

See Also:

Constant Field Values

POSTGRESQL_SOFTWARE

public static final java.lang.String POSTGRESQL_SOFTWARE

See Also:

Constant Field Values

HSQLDB_SOFTWARE

public static final java.lang.String HSQLDB_SOFTWARE

See Also:

Constant Field Values

SHARE_TABLE_NAME

public static final java.lang.String SHARE_TABLE_NAME

See Also:

Constant Field Values

DATE_FIELD

public static final java.lang.String DATE_FIELD

See Also:

Constant Field Values

SYMBOL_FIELD

public static final java.lang.String SYMBOL_FIELD

See Also:

Constant Field Values

DAY_OPEN_FIELD

public static final java.lang.String DAY_OPEN_FIELD

See Also:

Constant Field Values

DAY_CLOSE_FIELD

public static final java.lang.String DAY_CLOSE_FIELD

See Also:

Constant Field Values

DAY_HIGH_FIELD

public static final java.lang.String DAY_HIGH_FIELD

See Also:

Constant Field Values

DAY_LOW_FIELD

public static final java.lang.String DAY_LOW_FIELD

See Also:

Constant Field Values

DAY_VOLUME_FIELD

public static final java.lang.String DAY_VOLUME_FIELD

See Also:

Constant Field Values

DATE_COLUMN

public static final int DATE_COLUMN

See Also:

Constant Field Values

SYMBOL_COLUMN

public static final int SYMBOL_COLUMN

See Also:

Constant Field Values

DAY_OPEN_COLUMN

public static final int DAY_OPEN_COLUMN

See Also:

Constant Field Values

DAY_CLOSE_COLUMN

public static final int DAY_CLOSE_COLUMN

See Also:

Constant Field Values

DAY_HIGH_COLUMN

public static final int DAY_HIGH_COLUMN

See Also:

Constant Field Values

DAY_LOW_COLUMN

public static final int DAY_LOW_COLUMN

See Also:

Constant Field Values

DAY_VOLUME_COLUMN

public static final int DAY_VOLUME_COLUMN

See Also:

Constant Field Values

LOOKUP_TABLE_NAME

public static final java.lang.String LOOKUP_TABLE_NAME

See Also:

Constant Field Values

NAME_FIELD

public static final java.lang.String NAME_FIELD

See Also:

Constant Field Values

EXCHANGE_TABLE_NAME

public static final java.lang.String EXCHANGE_TABLE_NAME

See Also:

Constant Field Values

SOURCE_CURRENCY_FIELD

public static final java.lang.String SOURCE_CURRENCY_FIELD

See Also:

Constant Field Values

DESTINATION_CURRENCY_FIELD

public static final java.lang.String DESTINATION_CURRENCY_FIELD

See Also:

Constant Field Values

EXCHANGE_RATE_FIELD

public static final java.lang.String EXCHANGE_RATE_FIELD

See Also:

Constant Field Values

ALERT_TABLE_NAME

public static final java.lang.String ALERT_TABLE_NAME

See Also:

Constant Field Values

OHLCV_ALERT_TABLE_NAME

public static final java.lang.String OHLCV_ALERT_TABLE_NAME

See Also:

Constant Field Values

GONDOLA_ALERT_TABLE_NAME

public static final java.lang.String GONDOLA_ALERT_TABLE_NAME

See Also:

Constant Field Values

START_DATE_ALERT_TABLE_NAME

public static final java.lang.String START_DATE_ALERT_TABLE_NAME

See Also:

Constant Field Values

END_DATE_ALERT_TABLE_NAME

public static final java.lang.String END_DATE_ALERT_TABLE_NAME

See Also:

Constant Field Values

ALERT_UUID_COLUMN

public static final int ALERT_UUID_COLUMN

See Also:

Constant Field Values

ALERT_HOST_COLUMN

public static final int ALERT_HOST_COLUMN

See Also:

Constant Field Values

ALERT_USER_COLUMN

public static final int ALERT_USER_COLUMN

See Also:

Constant Field Values

ALERT_SYMBOL_COLUMN

public static final int ALERT_SYMBOL_COLUMN

See Also:

Constant Field Values

ALERT_START_DATE_COLUMN

public static final int ALERT_START_DATE_COLUMN

See Also:

Constant Field Values

ALERT_END_DATE_COLUMN

public static final int ALERT_END_DATE_COLUMN

See Also:

Constant Field Values

ALERT_TARGET_COLUMN

public static final int ALERT_TARGET_COLUMN

See Also:

Constant Field Values

ALERT_BOUND_TYPE_COLUMN

public static final int ALERT_BOUND_TYPE_COLUMN

See Also:

Constant Field Values

ALERT_TARGET_TYPE_COLUMN

public static final int ALERT_TARGET_TYPE_COLUMN

See Also:

Constant Field Values

ALERT_ENABLED_COLUMN

public static final int ALERT_ENABLED_COLUMN

See Also:

Constant Field Values

ALERT_DATESET_COLUMN

public static final int ALERT_DATESET_COLUMN

See Also:

Constant Field Values

ALERT_HOST_FIELD

public static final java.lang.String ALERT_HOST_FIELD

See Also:

Constant Field Values

ALERT_USER_FIELD

public static final java.lang.String ALERT_USER_FIELD

See Also:

Constant Field Values

ALERT_SYMBOL_FIELD

public static final java.lang.String ALERT_SYMBOL_FIELD

See Also:

Constant Field Values

ALERT_START_DATE_FIELD

public static final java.lang.String ALERT_START_DATE_FIELD

See Also:

Constant Field Values

ALERT_END_DATE_FIELD

public static final java.lang.String ALERT_END_DATE_FIELD

See Also:

Constant Field Values

ALERT_TARGET_FIELD

public static final java.lang.String ALERT_TARGET_FIELD

See Also:

Constant Field Values

ALERT_BOUND_TYPE_FIELD

public static final java.lang.String ALERT_BOUND_TYPE_FIELD

See Also:

Constant Field Values

ALERT_TARGET_TYPE_FIELD

public static final java.lang.String ALERT_TARGET_TYPE_FIELD

See Also:

Constant Field Values

ALERT_ENABLED_FIELD

public static final java.lang.String ALERT_ENABLED_FIELD

See Also:

Constant Field Values

ALERT_MAX_TARGET_EXP_LEN

public static final int ALERT_MAX_TARGET_EXP_LEN

See Also:

Constant Field Values

ALERT_MAX_HOST_LEN

public static final int ALERT_MAX_HOST_LEN

See Also:

Constant Field Values

ALERT_MAX_USER_LEN

public static final int ALERT_MAX_USER_LEN

See Also:

Constant Field Values

Constructor Detail

DatabaseManager

public DatabaseManager(java.lang.String software,
                       java.lang.String driver,
                       java.lang.String host,
                       java.lang.String port,
                       java.lang.String database,
                       java.lang.String username,
                       java.lang.String password)

Creates a new database connection.

Parameters:

software - the database software

driver - the class name for the driver to connect to the database

host - the host location of the database

port - the port of the database

database - the name of the database

username - the user login

password - the password for the login

DatabaseManager

public DatabaseManager(java.lang.String fileName)

Create a new quote source to connect to an internal HSQL database stored in the given file.

Parameters:

fileName - name of database file

Method Detail

getConnection

public boolean getConnection()

Return True if a connection to the database was established and the database exists with the right tables.

getHost

public java.lang.String getHost()

getUserName

public java.lang.String getUserName()

addField

public java.lang.String addField(java.lang.String field,
                                 java.lang.String type,
                                 boolean last)

addField

public java.lang.String addField(java.lang.String field,
                                 java.lang.String type)

addField

public java.lang.String addField(java.lang.String field,
                                 boolean last)

addField

public java.lang.String addField(java.lang.String field)

addQuotedField

public java.lang.String addQuotedField(java.lang.String field,
                                       boolean last)

addQuotedField

public java.lang.String addQuotedField(java.lang.String field)

shutdown

public void shutdown()

Shutdown the database. Only used for the internal database.

left

public java.lang.String left(java.lang.String field,
                             int length)

Return the SQL clause for returning the left most characters in a string. This function is needed because there seems no portable way of doing this.

Parameters:

field - the field to extract

length - the number of left most characters to extract

Returns:

the SQL clause for performing LEFT(string, letters)

createStatement

public java.sql.Statement createStatement()

toSQLDateString

public java.lang.String toSQLDateString(TradingDate date)

Return a date string that can be used as part of an SQL query. E.g. 2000-12-03.

Parameters:

date - Date.

Returns:

Date string ready for SQL query.

buildSymbolPresentQuery

protected java.lang.String buildSymbolPresentQuery(Symbol symbol)

Return the SQL clause for detecting whether the given symbol appears in the table.

Parameters:

symbol - the symbol

Returns:

the SQL clause

buildDatePresentQuery

protected java.lang.String buildDatePresentQuery(TradingDate date)

Return the SQL clause for detecting whether the given date appears in the table.

Parameters:

date - the date

Returns:

the SQL clause

supportForSingleRowUpdatesOnly

public boolean supportForSingleRowUpdatesOnly()

Returns:

false if the database does not allow multiple row inserts in a single statement. i.e. to insert or update two rows requires two SQL statements.

supportForTransactions

public boolean supportForTransactions()

Returns:

true if the database supports transactions .

executeUpdateTransaction

public void executeUpdateTransaction(java.util.List queries)
                              throws java.sql.SQLException

Throws:

java.sql.SQLException

getQueries

public java.util.List getQueries(java.lang.String transactionName)

replaceParameter

public java.lang.String replaceParameter(java.lang.String query,
                                         java.lang.String parameterName,
                                         java.lang.String parameterValue)

getUUID

public java.lang.String getUUID()