package se.chalmers.dat255.grupp12;
import java.sql.*;
import java.util.*;
/**
* Created with IntelliJ IDEA.
* se.chalmers.dat255.grupp12.User: ville
* Date: 9/26/13
* Time: 11:13 AM
* To change this template use File | Settings | File Templates.
*/
public class Database {
private static Database ourInstance = new Database();
private Connection connection;
private static final String TABLENAME_USERS = "users";
private static final String TABLENAME_LISTS = "lists";
private static final String TABLENAME_TASKS = "tasks";
private static final String TABLENAME_ACCESS = "access";
private static final String TABLENAME_ASSIGN = "assigns";
public static Database getInstance() {
return ourInstance;
}
/**
* Database constructor
* Creates a Connection and sets up the SQL tables in the database
*/
private Database() {
try {
Class.forName("org.sqlite.JDBC");
connection = DriverManager.getConnection("jdbc:sqlite:toddler.db");
setUpDB();
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
throw new ExceptionInInitializerError();
}
}
/**
* Get the id of the row in the database handled by a Statement
*
* @param stmnt - The statement modifying the database
* @return - The id of the modified row
* @throws SQLException
*/
private int getRowId(Statement stmnt) throws SQLException {
return stmnt.getGeneratedKeys().getInt(1);
}
/**
* Create a PreparedStatement from a String
* Query Timeout is automatically set to 10 seconds
*
* @param stmnt - A String containing the SQL statement
* @return - The PreparedStatement
*/
private PreparedStatement prepareStatement(String stmnt) {
try {
PreparedStatement ps = connection.prepareStatement(stmnt);
ps.setQueryTimeout(10); // Set timeout to 10 sec.
return ps;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("Cannot create PreparedStatement: " + e.getMessage());
}
}
//
/**
* Automatically sets the int or String variables to the specified PreparedStatement in the order they are given
*
* @param stmnt - The PreparedStatement to be set
* @param vars - A list of objects that is inserted into the statement in the order that they are in the list
* Error if an object is not a String or int
* @throws SQLException
*/
private void setVars(PreparedStatement stmnt, Object... vars) throws SQLException {
for (int i = 0; i < vars.length; i++) {
if (vars[i] instanceof String) {
stmnt.setString(i+1, (String)vars[i]);
} else if (vars[i] instanceof Integer) {
stmnt.setInt(i+1, (Integer)vars[i]);
} else if (vars[i] instanceof Boolean) {
stmnt.setBoolean(i+1,(Boolean)vars[i]);
} else if (vars[i] == null) {
stmnt.setString(i+1, "null");
} else {
throw new UnsupportedOperationException(String.format("setVars doesn't support \"%s\"", vars[i]));
}
}
}
/**
* Set up the tables in the database
*/
private void setUpDB() {
createTable(TABLENAME_USERS, "name string", "mail string");
createTable(TABLENAME_LISTS, "title string");
createTable(TABLENAME_TASKS, "title string","content string", "prio integer", "list_id integer",
"due_date string","isDone integer");
createTable(TABLENAME_ACCESS, "user_id integer", "list_id integer");
createTable(TABLENAME_ASSIGN, "task_id integer", "user_id integer");
}
/**
* Create a table in the database
* The column id is created by default
*
* @param tableName - The name of the table
* @param columns - according to this format: "title string", "prio integer", "list_id integer"
*
*/
private void createTable(String tableName, String... columns) {
try {
Statement stmnt = connection.createStatement();
stmnt.executeUpdate("drop table if exists '" + tableName + "'");
StringBuilder sb = new StringBuilder("");
for (String s : columns) {
sb.append(", ").append(s);
}
String query = "create table '" + tableName +
"' (id integer PRIMARY KEY AUTOINCREMENT" + sb.toString() + ")";
stmnt.executeUpdate(query);
} catch (SQLException e) {
throw new ExceptionInInitializerError("Cannot create table \"" + tableName + "\": " + e.getMessage());
}
}
/**
* Get a User from the database by using a user id
*
* @param id - The id of the user
* @throws SQLException
* @throws DataNotFoundException
*/
public User getUser(int id) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select * from " + TABLENAME_USERS + " where id=?");
stmnt.setInt(1, id);
ResultSet rs = stmnt.executeQuery();
if(!rs.next()){
throw new DataNotFoundException(User.class);
}
return getUser(rs);
}
/**
* Get a User from db by using a user mail
*
* @param mail - The mail of the user
* @throws SQLException
* @throws DataNotFoundException
*/
public User getUser(String mail) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select * from " + TABLENAME_USERS + " where mail=?");
stmnt.setString(1, mail);
ResultSet rs = stmnt.executeQuery();
if(!rs.next()){
throw new DataNotFoundException(User.class);
}
return getUser(rs);
}
/**
* Insert a user to the database
*
* @param user - The user that is to be inserted in the database
* @returns The user, now with id
* @throws SQLException
*/
public User insertUser(User user) throws SQLException {
PreparedStatement stmnt = prepareStatement("insert into " + TABLENAME_USERS + "(name, mail) values(?,?)");
setVars(stmnt, user.getName(), user.getMail());
stmnt.executeUpdate();
user.setId(getRowId(stmnt));
return user;
}
/**
* Remove a user from the database by using a user mail
*
* @param mail - The mail of the user that is to be removed
* @throws SQLException
*/
public void deleteUser(String mail) throws SQLException {
PreparedStatement stmnt = prepareStatement("delete from " + TABLENAME_USERS + " where mail=?");
stmnt.setString(1, mail);
stmnt.executeUpdate();
stmnt = prepareStatement("delete from " + TABLENAME_ACCESS + " where user_id=?");
stmnt.setInt(1, getRowId(stmnt));
stmnt.executeUpdate();
stmnt = prepareStatement("delete from " + TABLENAME_ASSIGN + " where user_id=?");
stmnt.setInt(1, getRowId(stmnt));
stmnt.executeUpdate();
}
/**
* Remove a user and its access- and assign-links from the database
*
* @param id - The id of the user that is to be removed
* @throws SQLException
*/
public void deleteUser(int id) throws SQLException {
PreparedStatement stmnt = prepareStatement("delete from " + TABLENAME_USERS + " where id=?");
stmnt.setInt(1, id);
stmnt.executeUpdate();
stmnt = prepareStatement("delete from " + TABLENAME_ACCESS + " where user_id=?");
stmnt.setInt(1, id);
stmnt.executeUpdate();
stmnt = prepareStatement("delete from " + TABLENAME_ASSIGN + " where user_id=?");
stmnt.setInt(1, id);
stmnt.executeUpdate();
}
/**
* Updates the values of a User in the database
*
* @param oldUser - The user that is to be updated
* @param newUser - A user with the values that should replace the current values on the database
* @throws SQLException
*/
public void updateUser(User oldUser, User newUser) throws SQLException {
PreparedStatement stmnt = prepareStatement("update " + TABLENAME_USERS +
" set name=ifnull(?, name), mail=ifnull(?, mail) where id=?");
setVars(stmnt, newUser.getName(), newUser.getMail(), oldUser.getId());
newUser.setId(oldUser.getId());
stmnt.executeUpdate();
}
/**
* Get all the lists a User has access to, from the database, using the id of the user
*
* @param user - The id of the User
* @return A list of all the lists that the specified user has access to
* @throws SQLException
* @throws DataNotFoundException
*/
public List<TaskList> getLists(User user) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select list_id,title from " + TABLENAME_LISTS + "\n" +
"inner join " + TABLENAME_ACCESS + "\n" +
"on " + TABLENAME_LISTS + ".id = " + TABLENAME_ACCESS + ".list_id\n" +
"inner join " + TABLENAME_USERS + "\n" +
"on " + TABLENAME_ACCESS + ".user_id = " + TABLENAME_USERS + ".id\n" +
"where user_id=?;");
stmnt.setInt(1, user.getId());
ResultSet rs = stmnt.executeQuery();
List<TaskList> list = new ArrayList<TaskList>();
while (rs.next()) {
int id = rs.getInt("list_id");
list.add(getList(id));
}
return list;
}
/**
* Insert a List in the database belonging to the specified user
* Automatically creates an access-link between the user and the list
* with the access being set to MASTER
* Also inserts the tasks belonging to the list to the database
*
* @param list - The list that is to be inserted to the database
* @param user - The user that created the list
* @return The list, now with id
* @throws SQLException
*/
public TaskList insertList(TaskList list, User user) throws SQLException {
PreparedStatement stmnt = prepareStatement("insert into " + TABLENAME_LISTS + "(title) values(?)");
stmnt.setString(1, list.getName());
stmnt.executeUpdate();
list.setId(getRowId(stmnt));
stmnt = prepareStatement("insert into " + TABLENAME_ACCESS + "(user_id, list_id) values(?,?)");
setVars(stmnt, user.getId(), list.getId());
stmnt.executeUpdate();
for(Task t: list.getTasks()){
insertTask(t, list.getId());
}
return list;
}
/**
* Get a User from the database by using a ResultSet
*
* @param rs - A ResultSet containing the needed variables to create the
* User
* @return A User created from the variables in the given ResultSet
* @throws DataNotFoundException
*/
private User getUser(ResultSet rs) throws DataNotFoundException {
try {
User user = new User(rs.getString("name"), rs.getString("mail"));
user.setId(rs.getInt(1));
return user;
} catch (SQLException e) {
throw new DataNotFoundException(User.class);
}
}
/**
* Get a List from the database by using a ResultSet
*
* @param rs - A ResultSet containing the needed variables to create the
* List
* @return A TaskList created from the variables in the given ResultSet
* @throws DataNotFoundException
*/
private TaskList getList(ResultSet rs) throws DataNotFoundException {
try {
TaskList list = new TaskList(rs.getString("title"));
list.setId(rs.getInt(1));
return list;
} catch (SQLException e) {
throw new DataNotFoundException(TaskList.class);
}
}
/**
* Get a Task from the database by using a ResultSet
*
* @param rs - A ResultSet containing the needed variables to create the
* Task
* @return A Task created from the variables in the given ResultSet
* Automatically adds the users assigned to this Task
* @throws DataNotFoundException
*/
private Task getTask(ResultSet rs) throws DataNotFoundException {
try {
Task t = new Task(rs.getString("title"),
rs.getInt("prio"), rs.getString("content"), rs.getBoolean("isDone"));
if (rs.getString("due_date") != null){
t.setDate(Utils.parseDate(rs.getString("due_date")));
}
t.setId(rs.getInt(1));
do {
int userId = rs.getInt("user_id");
if (userId > 0) {
t.assignUser(getUser(userId));
}
} while(rs.next());
return t;
} catch (SQLException e) {
throw new DataNotFoundException(Task.class);
}
}
/**
* Get a List from the database using a list id
*
* @param listId - The id of the List that is to be returned
* @return A list with the specified id
* @throws SQLException
* @throws DataNotFoundException
*/
public TaskList getList(int listId) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select * from " + TABLENAME_LISTS +
" inner join " + TABLENAME_ACCESS +
" on " + TABLENAME_LISTS + ".id = " + TABLENAME_ACCESS + ".list_id" +
" where list_id=?");
stmnt.setInt(1, listId);
ResultSet rs = stmnt.executeQuery();
if(!rs.next()){
throw new DataNotFoundException(TaskList.class);
}
TaskList list = getList(rs);
stmnt = prepareStatement("select * from " + TABLENAME_TASKS +" where list_id=?");
stmnt.setInt(1, listId);
rs = stmnt.executeQuery();
while (rs.next()) {
list.add(getTask(rs.getInt("id")));
}
list.setCollaborators(getUsers(listId));
return list;
}
/**
* Updates a List in the database using a different list
*
* @param listId - The id of the List that is to be updated
* @param list - A list with the values that should replace the current values on the database
* @throws SQLException
*/
public void updateList(int listId, TaskList list) throws SQLException {
PreparedStatement stmnt = prepareStatement("update " + TABLENAME_LISTS +
" set title=ifnull(?, title) where id=?");
setVars(stmnt, list.getName(), listId);
list.setId(listId);
stmnt.executeUpdate();
}
/**
* Removes a list, the tasks in the list and the access-link between users and the list from the database
*
* @param listId - The id of the list
* @throws SQLException
*/
public void deleteList(int listId) throws SQLException {
//Remove list
PreparedStatement stmnt = prepareStatement("delete from " + TABLENAME_LISTS + " where id=?");
stmnt.setInt(1, listId);
stmnt.executeUpdate();
//Remove tasks
stmnt = prepareStatement("delete from " + TABLENAME_TASKS + " where list_id=?");
stmnt.setInt(1,listId);
stmnt.executeUpdate();
//Remove access links
stmnt = prepareStatement("delete from " + TABLENAME_ACCESS + " where list_id=?");
stmnt.setInt(1, listId);
stmnt.executeUpdate();
}
/**
* Inserts a Task to the database that belongs to a specified List
* Inserts a link between the users assigned to the task to the assigns-table
* in the database
*
* @param task - The Task that is to be inserted to the Database
* @param listId - The id of the List that the Task belongs to
* @return The Task, now with id
* @throws SQLException
*/
public Task insertTask(Task task, int listId) throws SQLException {
PreparedStatement stmnt = prepareStatement("insert into " + TABLENAME_TASKS + "(title, content, prio, list_id, isDone) " +
"values(?,?,?,?,?)");
setVars(stmnt, task.getTitle(),task.getContent(), task.getPriority(), listId, task.isDone());
stmnt.executeUpdate();
task.setId(getRowId(stmnt));
for(User u : task.getUsers()){
assignUserToTask(task.getId(), u);
}
return task;
}
/**
* Get a Task from the database using the id of the task
*
* @param taskId - The id of the task
* @return A task created from the values on the database
* @throws SQLException
* @throws DataNotFoundException
*/
public Task getTask(int taskId) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select * from " + TABLENAME_TASKS +
" left outer join " + TABLENAME_ASSIGN +
" on " + TABLENAME_TASKS + ".id = " + TABLENAME_ASSIGN + ".task_id" +
" left outer join " + TABLENAME_USERS +
" on "+ TABLENAME_USERS + ".id = "+ TABLENAME_ASSIGN+".user_id" +
" where " + TABLENAME_TASKS + ".id = ?");
stmnt.setInt(1, taskId);
ResultSet rs = stmnt.executeQuery();
if (rs.next()) {
return getTask(rs);
} else {
throw new DataNotFoundException(Task.class);
}
}
/**
* Updates a Task in the database using a different Task
*
* @param taskId - The id of the List that is to be updated
* @param task - A task with the values that should replace the current values on the database
* @throws SQLException
*/
public void updateTask(int taskId, Task task) throws SQLException {
PreparedStatement stmnt = prepareStatement("update " + TABLENAME_TASKS +
" set title=ifnull(?, title), content=ifnull(?, content), prio=ifnull(?, prio), " +
" isDone=ifnull(?, isDone), due_date=ifnull(?, due_date) where id=?");
setVars(stmnt, task.getTitle(), task.getContent(), task.getPriority(), task.isDone(), Utils.parseDate(task.getDate()), taskId);
task.setId(taskId);
stmnt.executeUpdate();
}
/**
* Removes a Task from the database and the Assigns belonging to that Task
*
* @param taskId - The id of the task that is to be removed
* @throws SQLException
*/
public void deleteTask(int taskId) throws SQLException {
//Remove Task
PreparedStatement stmnt = prepareStatement("delete from " + TABLENAME_TASKS + " where id=?");
stmnt.setInt(1,taskId);
stmnt.executeUpdate();
//Remove assigns
stmnt = prepareStatement("delete from " + TABLENAME_ASSIGN + " where task_id=?");
stmnt.setInt(1, taskId);
stmnt.executeUpdate();
}
/**
* Creates a binding between a User and a Task
*
* @param taskId - The id of the Task that the user will be assigned to
* @param user - The User that will be assigned to the Task
* @throws SQLException
*/
public void assignUserToTask(int taskId, User user) throws SQLException{
PreparedStatement stmnt = prepareStatement("insert into " + TABLENAME_ASSIGN +
"(user_id, task_id) values(?,?)");
setVars(stmnt, user.getId(), taskId);
stmnt.executeUpdate();
}
/**
* Removes a binding between a user and a task
*
* @param taskId - The id of the task
* @param userId - The id of the user
* @throws SQLException
*/
public void removeUserFromTask(int taskId, int userId) throws SQLException {
PreparedStatement stmnt = prepareStatement("delete from " + TABLENAME_ASSIGN +
" where task_id=? and user_id=?");
setVars(stmnt, taskId, userId);
stmnt.executeUpdate();
}
/**
* Removes a binding between a user and a list
*
* @param userId - The id of the user to have its access revoked
* @param listId - The id of the list
* @throws SQLException
*/
public void removeUserFromList(int userId, int listId) throws SQLException {
PreparedStatement stmnt = prepareStatement("delete from " + TABLENAME_ACCESS +
" where user_id=? and list_id=?");
setVars(stmnt, userId, listId);
stmnt.executeUpdate();
}
/**
* Get all the users that has access to a list
*
* @param listId the id of the list to look up
* @return a list of users
* @throws SQLException
* @throws DataNotFoundException
*/
public List<User> getUsers(int listId) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select * from " + TABLENAME_USERS +
" inner join " + TABLENAME_ACCESS +
" on " + TABLENAME_ACCESS + ".user_id = " + TABLENAME_USERS + ".id" +
" where " + TABLENAME_ACCESS + ".list_id = ?");
stmnt.setInt(1, listId);
ResultSet rs = stmnt.executeQuery();
List<User> users = new ArrayList<User>();
while (rs.next()) {
users.add(getUser(rs));
}
return users;
}
/**
* Get all the Tasks that a User is assigned to
* @param user - The User
* @return A list of all the tasks that the User is assigned to
* @throws SQLException
* @throws DataNotFoundException
*/
public List<Task> getUserTasks(User user) throws SQLException, DataNotFoundException {
PreparedStatement stmnt = prepareStatement("select * from " + TABLENAME_ASSIGN + " where user_id=?");
stmnt.setInt(1, user.getId());
ResultSet rs = stmnt.executeQuery();
List<Task> tasks = new ArrayList<Task>();
while (rs.next()) {
tasks.add(getTask(rs.getInt("task_id")));
}
return tasks;
}
/**
* Give a User access to a list with an id
*
* @param user - The User to give access to the list
* @param listId - The id of the list
* @throws SQLException
*/
public void assignUserToList(User user, int listId) throws SQLException {
PreparedStatement stmnt = prepareStatement("insert or ignore into " + TABLENAME_ACCESS +
" (id, user_id, list_id) values ((select id from " + TABLENAME_ACCESS + " where user_id = ?" +
" and list_id = ?),?,?)");
setVars(stmnt, user.getId(), listId, user.getId(), listId);
stmnt.executeUpdate();
}
/**
* Close the connection between the server and the database
*/
public void close() {
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}