diff options
Diffstat (limited to 'src')
6 files changed, 239 insertions, 57 deletions
diff --git a/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java b/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java index 7818a83..2258d36 100644 --- a/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java +++ b/src/main/java/edu/brown/cs/student/term/DatabaseQuerier.java @@ -2,6 +2,7 @@ package edu.brown.cs.student.term; import edu.brown.cs.student.term.trade.Trade; import java.sql.*; +import java.time.Instant; import java.util.ArrayList; import java.util.List; @@ -28,41 +29,60 @@ public class DatabaseQuerier{ } /** - * Gets the names of all stocks in the database for the past two weeks from end date - * @param endDate - the end data for the 2 week period in unix time - * @return + * Gets the names of all stocks traded between start and end ddate + * @param startDate - the start date + * @param endDate - the end date + * @return a list of stock names * @throws SQLException */ - public List<String> getRecentStocks(double endDate) throws SQLException { + public List<String> getRecentStocks(Instant startDate, Instant endDate) throws SQLException { List<String> stocks = new ArrayList<>(); PreparedStatement prep = conn.prepareStatement( - "SELECT DISTINCT stock_name FROM trades (WHERE trade_timestamp < ?)"); + "SELECT DISTINCT stock_name FROM trades WHERE trade_timestamp <= ? AND trade_timestamp >= ?"); - prep.setDouble(1, endDate); + prep.setLong(1, endDate.toEpochMilli()); + prep.setLong(2, startDate.toEpochMilli()); ResultSet rs = prep.executeQuery(); while(rs.next()){ stocks.add(rs.getString(1)); } - - System.out.println(stocks.size()); - for (String s: stocks) { - System.out.println(s); - } return stocks; } //TODO: Fill these in - public List<List<Trade>> getAllTradesByStock() throws SQLException { + public List<List<Trade>> getAllTradesByStock(Instant startDate, Instant endDate) throws SQLException { + List<List<Trade>> allTrades = new ArrayList<>(); + List<String> stocks = getRecentStocks(startDate, endDate); + //get the buys and sells for each stock + for(String stock: stocks){ + allTrades.add(getTradeByStock(stock, 1, startDate, endDate)); + allTrades.add(getTradeByStock(stock, 0, startDate, endDate)); + } + return allTrades; + } + + public List<Trade> getTradeByStock(String stock, int isBuy, Instant startDate, Instant endDate) throws SQLException{ + List<Trade> trades = new ArrayList<>(); PreparedStatement prep = conn.prepareStatement( - ""); - return null; - } + "SELECT * FROM trades WHERE (stock_name = ? AND is_buy = ?) " + + "AND (trade_timestamp <= ? AND trade_timestamp >= ?) ORDER BY trade_timestamp"); + + prep.setString(1, stock); + prep.setInt(2, isBuy); + prep.setLong(3, endDate.toEpochMilli()); + prep.setLong(4, startDate.toEpochMilli()); + ResultSet rs = prep.executeQuery(); + + while(rs.next()){ + trades.add(new Trade(rs.getInt(1), rs.getString(2), + rs.getString(3), rs.getLong(4), rs.getInt(5), + rs.getInt(6))); + } - public List<Trade> getTradeByStock() throws SQLException{ - return null; + return trades; } diff --git a/src/main/java/edu/brown/cs/student/term/Main.java b/src/main/java/edu/brown/cs/student/term/Main.java index a6a9764..268a725 100644 --- a/src/main/java/edu/brown/cs/student/term/Main.java +++ b/src/main/java/edu/brown/cs/student/term/Main.java @@ -2,7 +2,7 @@ package edu.brown.cs.student.term; import edu.brown.cs.student.term.repl.Command; import edu.brown.cs.student.term.repl.REPL; -import edu.brown.cs.student.term.repl.SetupCommand; +import edu.brown.cs.student.term.repl.commands.SetupCommand; import joptsimple.OptionParser; import joptsimple.OptionSet; diff --git a/src/main/java/edu/brown/cs/student/term/repl/SetupCommand.java b/src/main/java/edu/brown/cs/student/term/repl/SetupCommand.java deleted file mode 100644 index 1d84f49..0000000 --- a/src/main/java/edu/brown/cs/student/term/repl/SetupCommand.java +++ /dev/null @@ -1,36 +0,0 @@ -package edu.brown.cs.student.term.repl; - -import edu.brown.cs.student.term.DatabaseQuerier; - -public class SetupCommand implements Command { - - private String error; - private static DatabaseQuerier dq; - - @Override - /** - * Sets up connection to the database - * Returns an empty string if no errors, non empty with error message otherwise - */ - public String run(String[] args) { - error = ""; - if(args.length == 1){ - try{ - dq = new DatabaseQuerier(args[0]); - } catch (Exception e) { - error = "ERROR: Could not connect to database. Ensure this is a valid database."; - System.out.println(error); - return error; - } - } else { - error = "ERROR: Incorrect number of arguments for setup command"; - System.out.println(error); - return error; - } - return error; - } - - public static DatabaseQuerier getDq() { - return dq; - } -} diff --git a/src/main/java/edu/brown/cs/student/term/repl/commands/SetupCommand.java b/src/main/java/edu/brown/cs/student/term/repl/commands/SetupCommand.java new file mode 100644 index 0000000..e19117c --- /dev/null +++ b/src/main/java/edu/brown/cs/student/term/repl/commands/SetupCommand.java @@ -0,0 +1,71 @@ +package edu.brown.cs.student.term.repl.commands; + +import edu.brown.cs.student.term.DatabaseQuerier; +import edu.brown.cs.student.term.repl.Command; +import edu.brown.cs.student.term.trade.Trade; + +import java.time.Instant; +import java.time.ZoneId; +import java.time.ZonedDateTime; +import java.util.List; + +public class SetupCommand implements Command { + + private String error; + private static DatabaseQuerier dq; + + @Override + /** + * Sets up connection to the database + * Returns an empty string if no errors, non empty with error message otherwise + */ + public String run(String[] args) { + error = ""; + if(args.length == 1){ + try{ + dq = new DatabaseQuerier(args[0]); + } catch (Exception e) { + error = "ERROR: Could not connect to database. Ensure this is a valid database."; + System.out.println(error); + return error; + } + } else { + error = "ERROR: Incorrect number of arguments for setup command"; + System.out.println(error); + return error; + } + + try{ + /** Just for testing purposes **/ + //12 am on 3/12 in UTC + Instant start = Instant.parse("2021-03-12T05:00:00.00Z"); + //12 am on 3/27 in UTC + Instant end = Instant.parse("2021-03-27T05:00:00.00Z"); + + System.out.println(end.toEpochMilli()); + System.out.println(start.getEpochSecond()); + + ZonedDateTime zdt = ZonedDateTime.ofInstant(start, ZoneId.of("America/New_York")); + System.out.println(zdt.toString()); + List<List<Trade>> trades = dq.getAllTradesByStock(start, end); + + int sum = 0; + for(List<Trade> t: trades){ + System.out.println(t); + sum += t.size(); + } + + System.out.println("num of trades: " + sum); + + + } catch(Exception e){ + e.printStackTrace(); + } + + return error; + } + + public static DatabaseQuerier getDq() { + return dq; + } +} diff --git a/src/main/java/edu/brown/cs/student/term/trade/Trade.java b/src/main/java/edu/brown/cs/student/term/trade/Trade.java index 831f9f1..500cc19 100644 --- a/src/main/java/edu/brown/cs/student/term/trade/Trade.java +++ b/src/main/java/edu/brown/cs/student/term/trade/Trade.java @@ -4,16 +4,18 @@ public class Trade { private int id; private String stock; - private boolean isBuy; + private int isBuy; private double timestamp; private String holder; + private int numShares; - public Trade(int id, String stockName, boolean buy, double ts, String holderName){ + public Trade(int id, String stockName, String holderName, double ts, int buy, int shares){ this.id = id; this.stock = stockName; this.isBuy = buy; this.timestamp = ts; this.holder = holderName; + this.numShares = shares; } public int getId() { @@ -21,7 +23,11 @@ public class Trade { } public boolean isBuy() { - return isBuy; + if(isBuy == 1){ + return true; + } else { + return false; + } } public double getTimestamp() { @@ -44,6 +50,7 @@ public class Trade { ", isBuy=" + isBuy + ", timestamp=" + timestamp + ", holder='" + holder + '\'' + + ", numShares=" + numShares + '}'; } } diff --git a/src/test/java/edu/brown/cs/student/DBQuerierTest.java b/src/test/java/edu/brown/cs/student/DBQuerierTest.java new file mode 100644 index 0000000..0d365ec --- /dev/null +++ b/src/test/java/edu/brown/cs/student/DBQuerierTest.java @@ -0,0 +1,120 @@ +package edu.brown.cs.student; + +import java.io.PrintStream; +import java.sql.SQLException; +import java.time.Instant; +import java.util.ArrayList; +import java.util.List; + +import edu.brown.cs.student.term.DatabaseQuerier; +import edu.brown.cs.student.term.repl.commands.SetupCommand; +import edu.brown.cs.student.term.trade.Trade; +import org.junit.After; +import org.junit.Before; +import org.junit.Test; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +public class DBQuerierTest { + + /** these should span the entire mock dataset */ + //12 am on 3/11 in UTC + private Instant start = Instant.parse("2021-03-11T05:00:00.00Z"); + //12 am on 3/28 in UTC + private Instant end = Instant.parse("2021-03-28T05:00:00.00Z"); + + private DatabaseQuerier db; + + @Before + public void setUp() { + try{ + db = new DatabaseQuerier("data/mock_trades.sqlite3"); + } catch(Exception e){ + System.out.println("DBQuerier Test, couldn't connect to db???"); + } + } + + /* + * try{ + + } catch(Exception e) { + System.out.println("Error in test"); + }*/ + + @After + public void tearDown() { + db = null; + } + + @Test + public void testNonExistentStock(){ + setUp(); + try{ + List<Trade> fakeStockList = db.getTradeByStock("NONO", 1, start, end); + assertTrue(fakeStockList.isEmpty()); + + } catch(Exception e) { + System.out.println("Error in test"); + } + tearDown(); + } + + @Test + public void testFlippedDates(){ + setUp(); + try{ + List<Trade> gmeBadDatesList = db.getTradeByStock("GME", 1, end, start); + assertTrue(gmeBadDatesList.isEmpty()); + + } catch(Exception e) { + System.out.println("Error in test"); + } + tearDown(); + } + + @Test + public void testTradeByStockNameBuy(){ + setUp(); + try{ + List<Trade> gmeBuyList = db.getTradeByStock("GME", 1, start, end); + assertEquals(gmeBuyList.size(), 6); + assertEquals(gmeBuyList.get(0).getId(), 482); + assertEquals(gmeBuyList.get(3).getId(), 149); + assertEquals(gmeBuyList.get(4).getId(), 275); + assertEquals(gmeBuyList.get(5).getId(), 30); + + + List<Trade> teslaBuyList = db.getTradeByStock("TSLA", 1, start, end); + assertEquals(teslaBuyList.size(), 16); + assertEquals(teslaBuyList.get(0).getId(), 328); + assertEquals(teslaBuyList.get(7).getId(), 241); + assertEquals(teslaBuyList.get(15).getId(), 774); + + } catch(Exception e) { + System.out.println("Error in testTradeByStockName"); + } + + tearDown(); + } + + @Test + public void testTradeByNameOrdering(){ + setUp(); + + try{ + List<Trade> gmeSellList = db.getTradeByStock("GME", 0, start, end); + for(int i = 1; i < gmeSellList.size(); i++){ + assertTrue(gmeSellList.get(i-1).getTimestamp() < gmeSellList.get(i).getTimestamp()); + } + + List<Trade> amznBuyList = db.getTradeByStock("AMZN", 1, start, end); + for(int i = 1; i < amznBuyList.size(); i++){ + assertTrue(amznBuyList.get(i-1).getTimestamp() < amznBuyList.get(i).getTimestamp()); + } + + } catch(Exception e) { + System.out.println("Error in test"); + } + tearDown(); + } +} |