1 files changed, 78 insertions, 0 deletions
diff --git a/src/jeopardy/DBModel.java b/src/jeopardy/DBModel.java
new file mode 100644
index 0000000..07abecc
--- /dev/null
+++ b/src/jeopardy/DBModel.java
@@ -0,0 +1,78 @@
+package jeopardy;
+import java.sql.*;
+import java.util.ArrayList;
+
+
+
+public class DBModel implements Model {
+	private Connection con;
+	private PreparedStatement createUserStmnt;
+	private PreparedStatement checkUserStmnt;
+	private PreparedStatement getValidCategoriesStmnt;
+	
+	public DBModel(String url) {
+		try {
+			con = DriverManager.getConnection(url);
+			createUserStmnt = con.prepareStatement("INSERT INTO `users` (`name`) VALUES (?)");
+			checkUserStmnt = con.prepareStatement("SELECT COUNT(*) FROM `users` WHERE `name` = ?");
+			getValidCategoriesStmnt = con.prepareStatement( // TODO: this has to support more than 6 players
+					"SELECT categories.name FROM aqpairs, categories WHERE aqpairs.used = 0 AND aqpairs.category = categories.id AND aqpairs.id NOT IN "
+					+ "(SELECT DISTINCT aqpairs.id FROM aqpairs, authors, users WHERE aqpairs.used = 0 AND users.name IN (?, ?, ?, ?, ?, ?) AND users.id = authors.author AND authors.aqpair = aqpairs.id )"
+					+ " GROUP BY category HAVING COUNT(aqpairs.id) >= 2");
+		}
+		catch (SQLException e) {
+			e.printStackTrace();
+			System.exit(1);
+		}
+	}
+
+	
+
+	public void createUser(String name) {
+		try {
+			createUserStmnt.setString(1, name);
+			createUserStmnt.execute();
+		}
+		catch (SQLException e) {
+			e.printStackTrace();
+			System.exit(1);
+		}
+	}
+
+	public boolean userExists(String name) {
+		try {
+			checkUserStmnt.setString(1, name);
+			ResultSet r = checkUserStmnt.executeQuery();
+			r.next();
+			return (r.getInt(1) == 1);
+		}
+		catch (SQLException e) {
+			e.printStackTrace();
+			System.exit(1);
+			return false;
+		}
+	}
+	
+	public ArrayList<String> validCategoriesForUsers(ArrayList<String> users)
+	{
+		try {
+			for(int i = 0; i < 6; i++) {
+				getValidCategoriesStmnt.setString(i + 1, users.size() <= i ? "" : users.get(i));
+			}
+			ResultSet r = getValidCategoriesStmnt.executeQuery();
+			ArrayList<String> result = new ArrayList<String>();
+			while(r.next()) {
+				result.add(r.getString(1));
+			}
+			return result;
+		}
+		catch (SQLException e) {
+			e.printStackTrace();
+			System.exit(1);
+			return null;
+		}
+	}
+	
+	
+	
+}