Venice 0.751beta

nz.org.venice.quote
Class DatabaseManager

java.lang.Object
  extended bynz.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()

Venice 0.751beta