OHLCPointDao.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.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.quantcomponents.core.model.BarSize;
import com.quantcomponents.marketdata.IOHLCPoint;
import com.quantcomponents.marketdata.OHLCPoint;
import com.quantcomponents.series.jdbc.IOHLCPointDao;

public class OHLCPointDao implements IOHLCPointDao {
	public static final String TABLE_NAME = "OHLC";
	public static final String SELECT_FIELDS = "BAR_SIZE, DATE_TIME," +
			" P_OPEN, P_HIGH, P_LOW, P_CLOSE, VOLUME, P_WAP, TICK_COUNT, LAST_UPDATE";
	public static final String INSERT_FIELDS = "SDB_ID, " + SELECT_FIELDS;
	private final Connection connection;
	
	public OHLCPointDao(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 BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," + 
					"SDB_ID VARCHAR(200)," +
					"BAR_SIZE VARCHAR(20)," +
					"DATE_TIME TIMESTAMP," +
					"P_OPEN DECIMAL(30,10)," +
					"P_HIGH DECIMAL(30,10)," +
					"P_LOW DECIMAL(30,10)," +
					"P_CLOSE DECIMAL(30,10)," +
					"VOLUME BIGINT," +
					"P_WAP DECIMAL(30,10)," +
					"TICK_COUNT INT," +
					"LAST_UPDATE TIMESTAMP)");
			stmt.execute();
			connection.commit();
		}
		rs.close();
	}

	@Override
	public void save(String stockDatabaseId, IOHLCPoint item) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("INSERT INTO " + TABLE_NAME +
				" (" + INSERT_FIELDS + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		stmt.setString(1, stockDatabaseId);
		stmt.setString(2, item.getBarSize().name());
		stmt.setTimestamp(3, new Timestamp(item.getIndex().getTime()));
		stmt.setDouble(4, item.getOpen());
		stmt.setDouble(5, item.getHigh());
		stmt.setDouble(6, item.getLow());
		stmt.setDouble(7, item.getClose());
		stmt.setLong(8, item.getVolume());
		stmt.setDouble(9, item.getWAP());
		stmt.setInt(10, item.getCount());
		if (item.getLastUpdate() != null) {
			stmt.setTimestamp(11, new Timestamp(item.getLastUpdate().getTime()));
		} else {
			stmt.setNull(11, Types.TIMESTAMP);
		}
		stmt.execute();
	}

	@Override
	public void update(String stockDatabaseId, IOHLCPoint existingItem, IOHLCPoint newItem) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("UPDATE " + TABLE_NAME + " SET " +
				"P_OPEN = ?, P_HIGH = ?, P_LOW = ?, P_CLOSE = ?, VOLUME = ?, P_WAP = ?, TICK_COUNT = ?, LAST_UPDATE = ?" +
				" WHERE SDB_ID = ? AND DATE_TIME = ?");
		// updated fields
		stmt.setDouble(1, newItem.getOpen());
		stmt.setDouble(2, newItem.getHigh());
		stmt.setDouble(3, newItem.getLow());
		stmt.setDouble(4, newItem.getClose());
		stmt.setLong(5, newItem.getVolume());
		stmt.setDouble(6, newItem.getWAP());
		stmt.setInt(7, newItem.getCount());
		if (newItem.getLastUpdate() != null) {
			stmt.setTimestamp(8, new Timestamp(newItem.getLastUpdate().getTime()));
		} else {
			stmt.setNull(8, Types.TIMESTAMP);
		}
		// selection criteria
		stmt.setString(9, stockDatabaseId);
		stmt.setTimestamp(10, new Timestamp(newItem.getIndex().getTime()));
		int rows = stmt.executeUpdate();
		if (rows == 0) {
			throw new IllegalArgumentException("This item is not stored in the database: " + stockDatabaseId + ";" + newItem.getIndex());
		}
		if (rows > 1) {
			throw new IllegalArgumentException("Multiple items for these parameters: " + stockDatabaseId + ";" + newItem.getIndex());
		}
	}

	@Override
	public List<IOHLCPoint> find(String stockDatabaseId) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement("SELECT " + SELECT_FIELDS + " FROM " + TABLE_NAME + " WHERE SDB_ID = ? ORDER BY DATE_TIME");
		stmt.setString(1, stockDatabaseId);
		List<IOHLCPoint> result = new LinkedList<IOHLCPoint>();
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			BarSize barSize = BarSize.valueOf(rs.getString(1));
			Date date = new Date(rs.getTimestamp(2).getTime());
			Double open = rs.getDouble(3);
			Double high = rs.getDouble(4);
			Double low = rs.getDouble(5);
			Double close = rs.getDouble(6);
			Long volume = rs.getLong(7);
			Double wap = rs.getDouble(8);
			Integer count = rs.getInt(9);
			OHLCPoint point = new OHLCPoint(barSize, date, open, high, low, close, volume, wap, count);
			Timestamp lastUpdateTs = rs.getTimestamp(10);
			if (lastUpdateTs != null) {
				point.setLastUpdate(new Date(lastUpdateTs.getTime()));
			}
			result.add(point);
		}
		rs.close();
		return result;
	}

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

	@Override
	public void flush() throws SQLException {
		connection.commit();
	}

}