StockDatabaseHeaderDao.java

/*******************************************************************************
 * Copyright (c) 2013 Luigi Sgro. All rights reserved. This
 * program and the accompanying materials are made available under the terms of
 * the Eclipse Public License v1.0 which accompanies this distribution, and is
 * available at http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *     Luigi Sgro - initial API and implementation
 ******************************************************************************/
package com.quantcomponents.series.jdbc.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Currency;
import java.util.HashSet;
import java.util.Set;
import java.util.TimeZone;

import com.quantcomponents.core.model.BarSize;
import com.quantcomponents.core.model.BareDate;
import com.quantcomponents.core.model.DataType;
import com.quantcomponents.core.model.IContractDesc;
import com.quantcomponents.core.model.IContract;
import com.quantcomponents.core.model.IdentifierType;
import com.quantcomponents.core.model.OptionRight;
import com.quantcomponents.core.model.SecurityType;
import com.quantcomponents.core.model.beans.ContractDescBean;
import com.quantcomponents.core.model.beans.ContractBean;
import com.quantcomponents.series.jdbc.IStockDatabaseHeaderDao;
import com.quantcomponents.series.jdbc.StockDatabaseHeader;

public class StockDatabaseHeaderDao implements IStockDatabaseHeaderDao {
	public static final String TABLE_NAME = "STOCK_DATABASE";
	public static final String FIELDS = "ID, TIME_STAMP, TIMEZONE, DATA_TYPE, BAR_SIZE, AFTER_HOURS," +
			" C_SYMBOL, C_SEC_TYPE, C_EXP_DATE, C_STRIKE, C_OPTION_RIGHT, C_MULTIPLIER, C_EXCHANGE," +
			" C_PRIM_EXCH, C_CURRENCY, C_ID_TYPE, C_ID, C_BROKER_ID, C_DESC_LONG_NAME, C_DESC_TIMEZONE";
	private final Connection connection;
	
	public StockDatabaseHeaderDao(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void initDb() throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM SYS.SYSTABLES WHERE TABLENAME = '" + TABLE_NAME + "'");
		ResultSet rs = stmt.executeQuery();
		rs.next();
		int numberOfTables = rs.getInt(1);
		if (numberOfTables == 0) {
			stmt = connection.prepareStatement("CREATE TABLE " + TABLE_NAME +
					" (ID VARCHAR(200) PRIMARY KEY," + 
					"TIME_STAMP TIMESTAMP," +
					"TIMEZONE VARCHAR(100)," +
					"DATA_TYPE VARCHAR(50)," +
					"BAR_SIZE VARCHAR(20)," +
					"AFTER_HOURS SMALLINT," +
					"C_SYMBOL VARCHAR(10)," +
					"C_SEC_TYPE VARCHAR(10)," +
					"C_EXP_DATE CHAR(8)," +
					"C_STRIKE DECIMAL(30,10)," +
					"C_OPTION_RIGHT CHAR(4)," +
					"C_MULTIPLIER INTEGER," +
					"C_EXCHANGE VARCHAR(100)," +
					"C_PRIM_EXCH VARCHAR(100)," +
					"C_CURRENCY CHAR(3)," +
					"C_ID_TYPE VARCHAR(10)," +
					"C_ID VARCHAR(100)," +
					"C_BROKER_ID VARCHAR(100)," +
					"C_DESC_LONG_NAME VARCHAR(100)," +
					"C_DESC_TIMEZONE VARCHAR(100))");
			stmt.execute();
			connection.commit();
		}
		rs.close();
	}

	@Override
	public void save(StockDatabaseHeader item) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("INSERT INTO " + TABLE_NAME +
				" (" + FIELDS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
		stmt.setString(1, item.id);
		if (item.timestamp != null) {
			stmt.setTimestamp(2, new Timestamp(item.timestamp));
		} else  {
			stmt.setNull(2, Types.TIMESTAMP);
		}
		if (item.timeZone != null) {
			stmt.setString(3, item.timeZone.getID());
		} else {
			stmt.setNull(3, Types.VARCHAR);
		}
		stmt.setString(4, item.dataType.name());
		stmt.setString(5, item.barSize.name());
		stmt.setBoolean(6, item.includeAfterHours);
		stmt.setString(7, item.contract.getSymbol());
		stmt.setString(8, item.contract.getSecurityType().name());
		if (item.contract.getExpiryDate() != null) {
			stmt.setString(9, item.contract.getExpiryDate().getDateRepr());
		} else {
			stmt.setNull(9, Types.VARCHAR);
		}
		if (item.contract.getStrike() != null) {
			stmt.setDouble(10, item.contract.getStrike()); 
		} else {
			stmt.setNull(10, Types.DECIMAL);
		}
		if (item.contract.getOptionRight() != null) {
			stmt.setString(11, item.contract.getOptionRight().name());
		} else {
			stmt.setNull(11, Types.VARCHAR);
		}
		if (item.contract.getMultiplier() != null) {
			stmt.setInt(12, item.contract.getMultiplier());
		} else {
			stmt.setNull(12, Types.INTEGER);
		}
		stmt.setString(13, item.contract.getExchange());
		stmt.setString(14, item.contract.getPrimaryExchange());
		stmt.setString(15, item.contract.getCurrency().getCurrencyCode());
		if (item.contract.getIdentifierType() != null) {
			stmt.setString(16, item.contract.getIdentifierType().name());
		} else {
			stmt.setNull(16, Types.VARCHAR);
		}
		stmt.setString(17, item.contract.getIdentifier());
		stmt.setString(18, item.contract.getBrokerID());
		IContractDesc desc = item.contract.getContractDescription();
		if (desc != null) {
			stmt.setString(19, desc.getLongName());
			stmt.setString(20, desc.getTimeZone().getID());
		} else {
			stmt.setNull(19, Types.VARCHAR);
			stmt.setNull(20, Types.VARCHAR);
		}
		stmt.execute();
	}

	@Override
	public void delete(String id) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("DELETE FROM " + TABLE_NAME + " WHERE ID = ?");
		stmt.setString(1, id);
		stmt.execute();
	}

	@Override
	public StockDatabaseHeader get(String id) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT " + FIELDS + " FROM " + TABLE_NAME + " WHERE ID = ?");
		stmt.setString(1, id);
		ResultSet rs = stmt.executeQuery();
		StockDatabaseHeader result = null;
		if (rs.next()) {
			result = buildHeaderFromResultSet(rs);
		} 
		rs.close();
		return result;
	}

	@Override
	public int countAll() throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT COUNT(*) FROM " + TABLE_NAME);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		int number = rs.getInt(1);
		rs.close();
		return number;
	}

	@Override
	public Set<StockDatabaseHeader> findAll() throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT " + FIELDS + " FROM " + TABLE_NAME);
		return find(stmt);
	}

	@Override
	public Set<StockDatabaseHeader> findByContract(IContract contract) throws SQLException {
		StringBuilder buffer = new StringBuilder();
		buffer.append("SELECT " + FIELDS + 
				" FROM " + TABLE_NAME + 
				" WHERE C_SYMBOL = ? AND C_SEC_TYPE = ? AND C_CURRENCY = ?");
		if (contract.getExchange() != null) {
			buffer.append(" AND C_EXCHANGE = ?");
		}
		if (contract.getPrimaryExchange() != null) {
			buffer.append(" AND C_PRIM_EXCH = ?");
		}
		if (contract.getExpiryDate() != null) {
			buffer.append(" AND C_EXP_DATE = ?");
		}
		if (contract.getOptionRight() != null) {
			buffer.append(" AND C_OPTION_RIGHT = ?");
		}
		if (contract.getStrike() != null) {
			buffer.append(" AND C_STRIKE = ?");
		}
		PreparedStatement stmt = connection.prepareStatement(buffer.toString());
		stmt.setString(1, contract.getSymbol());
		stmt.setString(2, contract.getSecurityType().name());
		stmt.setString(3, contract.getCurrency().getCurrencyCode());
		int fieldNo = 4;
		if (contract.getExchange() != null) {
			stmt.setString(fieldNo++, contract.getExchange());
		}
		if (contract.getPrimaryExchange() != null) {
			stmt.setString(fieldNo++, contract.getPrimaryExchange());
		}
		if (contract.getExpiryDate() != null) {
			stmt.setString(fieldNo++, contract.getExpiryDate().getDateRepr());
		}
		if (contract.getOptionRight() != null) {
			stmt.setString(fieldNo++, contract.getOptionRight().name());
		}
		if (contract.getStrike() != null) {
			stmt.setDouble(fieldNo, contract.getStrike());
		}
		return find(stmt);
	}
	
	@Override
	public void flush() throws SQLException {
		connection.commit();
	}

	private Set<StockDatabaseHeader> find(PreparedStatement stmt) throws SQLException {
		Set<StockDatabaseHeader> result = new HashSet<StockDatabaseHeader>();
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			result.add(buildHeaderFromResultSet(rs));
		}
		rs.close();
		return result;
	}
	
	private StockDatabaseHeader buildHeaderFromResultSet(ResultSet rs) throws SQLException {
		StockDatabaseHeader header = new StockDatabaseHeader();
		header.id = rs.getString(1);
		Timestamp timestamp = rs.getTimestamp(2);
		if (timestamp != null) {
			header.timestamp = timestamp.getTime();
		}
		String tzId = rs.getString(3);
		if (tzId != null) {
			header.timeZone = TimeZone.getTimeZone(tzId);
		} else {
			header.timeZone = TimeZone.getDefault();
		}
		header.dataType = DataType.valueOf(rs.getString(4));
		header.barSize = BarSize.valueOf(rs.getString(5));
		header.includeAfterHours = rs.getBoolean(6);
		
		ContractBean bean = new ContractBean();
		bean.setSymbol(rs.getString(7));
		bean.setSecurityType(SecurityType.valueOf(rs.getString(8)));
		String expiryDateRepr = rs.getString(9);
		if (expiryDateRepr != null) {
			bean.setExpiryDate(new BareDate(expiryDateRepr));
		}
		bean.setStrike(rs.getDouble(10));
		String optRight = rs.getString(11);
		if (optRight != null) {
			bean.setOptionRight(OptionRight.valueOf(optRight));
		}
		bean.setMultiplier(rs.getInt(12));
		bean.setExchange(rs.getString(13));
		bean.setPrimaryExchange(rs.getString(14));
		String curCode = rs.getString(15);
		if (curCode != null) {
			bean.setCurrency(Currency.getInstance(curCode));
		}
		String idType = rs.getString(16);
		if (idType != null) {
			bean.setIdentifierType(IdentifierType.valueOf(idType));
		}
		bean.setIdentifier(rs.getString(17));
		bean.setBrokerID(rs.getString(18));
		String descLongName = rs.getString(19);
		String descTzId = rs.getString(20);
		if (descLongName != null || descTzId != null) {
			ContractDescBean desc = new ContractDescBean();
			desc.setLongName(descLongName);
			if (descTzId != null) {
				desc.setTimeZone(TimeZone.getTimeZone(descTzId));
			}
			bean.setContractDescription(desc);
		}
		header.contract = bean;
			
		return header;
	}
}