Author: echatellier Date: 2012-08-22 11:46:46 +0200 (Wed, 22 Aug 2012) New Revision: 2885 Url: http://chorem.org/repositories/revision/jtimer/2885 Log: Improve jdbc storage Modified: branches/2.0.0-evol-499-storage/pom.xml branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/JTimer.java branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/data/TimerCore.java branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/entities/TimerTask.java branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/io/AbstractSaver.java branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/storage/Storage.java branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksCellRenderer.java branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksModel.java Modified: branches/2.0.0-evol-499-storage/pom.xml =================================================================== --- branches/2.0.0-evol-499-storage/pom.xml 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/pom.xml 2012-08-22 09:46:46 UTC (rev 2885) @@ -5,7 +5,7 @@ <parent> <groupId>org.nuiton</groupId> <artifactId>mavenpom4redmine</artifactId> - <version>3.3.2</version> + <version>3.3.6</version> </parent> <groupId>org.chorem</groupId> @@ -199,7 +199,7 @@ <dependency> <groupId>org.nuiton</groupId> <artifactId>nuiton-utils</artifactId> - <version>2.5.1</version> + <version>2.5.3</version> <scope>compile</scope> <exclusions> <exclusion> @@ -231,7 +231,7 @@ <dependency> <groupId>org.swinglabs.swingx</groupId> <artifactId>swingx-core</artifactId> - <version>1.6.3</version> + <version>1.6.4</version> <scope>compile</scope> </dependency> <dependency> @@ -259,7 +259,7 @@ <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> - <version>1.3.167</version> + <version>1.3.168</version> <scope>runtime</scope> </dependency> @@ -285,7 +285,7 @@ <dependency> <groupId>org.testng</groupId> <artifactId>testng</artifactId> - <version>6.5.2</version> + <version>6.7</version> <scope>test</scope> <exclusions> <exclusion> Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/JTimer.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/JTimer.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/JTimer.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -509,7 +509,6 @@ @Override protected void shutdown() { log.debug("Shutdown called"); - core.exit(); // save context // super, sauve le context des fenetres, etc... Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/data/TimerCore.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/data/TimerCore.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/data/TimerCore.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -56,11 +56,9 @@ /** Timer data. */ protected TimerDataManager data; + /** Data storage. */ protected Storage storage; - /** saver io controller. */ - protected Saver saver; - /** * Constructor. */ @@ -76,13 +74,6 @@ // add commmon vetoable CommonVetoable commonVetoable = new CommonVetoable(data); data.addVetoableDataEventListener(commonVetoable); - - // init saver implementation - saver = JTimerFactory.getFileSaver(); - if (saver != null) { - data.addVetoableDataEventListener(saver); - data.addDataEventListener(saver); - } } /** @@ -99,18 +90,18 @@ log.info("Init core"); } - try { - saver.lock(); + //try { + //saver.lock(); load(); initSucceded = true; - } catch (DataLockingException e) { + /*} catch (DataLockingException e) { // can occurs if data save directory is locked if (log.isDebugEnabled()) { log.error("Data can't have been loaded"); } - } + }*/ return initSucceded; } @@ -143,13 +134,13 @@ log.info("Load local data"); } - Collection<TimerProject> projects = saver.load(); + //Collection<TimerProject> projects = saver.load(); // sort loaded collection - List<TimerProject> projectsList = new ArrayList<TimerProject>(projects); - Collections.sort(projectsList); + //List<TimerProject> projectsList = new ArrayList<TimerProject>(projects); + //Collections.sort(projectsList); - data.addAllProjects(projectsList); + data.addAllProjects(null); } /** @@ -161,12 +152,12 @@ } // unlock fs directory - try { + /*try { saver.unlock(); } catch (DataLockingException e) { if (log.isErrorEnabled()) { log.error("Error on unlocking", e); } - } + }*/ } } Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/entities/TimerTask.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/entities/TimerTask.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/entities/TimerTask.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -31,7 +31,6 @@ import java.util.List; import java.util.SortedMap; import java.util.TreeMap; -import java.util.UUID; import org.chorem.jtimer.utils.DailySortedMap; @@ -50,9 +49,6 @@ /** serialVersionUID */ private static final long serialVersionUID = -7590755569706702695L; - /** Task uuid used to managed task equality. */ - protected String uuid = UUID.randomUUID().toString(); - /** Task number. */ protected int number; @@ -332,7 +328,7 @@ */ @Override public int hashCode() { - int result = uuid.hashCode(); + int result = number % 31; return result; } @@ -346,7 +342,7 @@ return false; } - return uuid.equals(((TimerTask)o).uuid); + return number == ((TimerTask)o).getNumber(); } /** Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/io/AbstractSaver.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/io/AbstractSaver.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/io/AbstractSaver.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -47,7 +47,7 @@ public abstract class AbstractSaver extends TimerTask implements Saver { /** log. */ - private static Log log = LogFactory.getLog(GTimerIncrementalSaver.class); + private static Log log = LogFactory.getLog(AbstractSaver.class); /** Backup file extension. */ public static final String BACKUP_EXTENSION = ".tmp"; Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/storage/Storage.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/storage/Storage.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/storage/Storage.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -6,9 +6,12 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.List; +import java.util.Map; +import java.util.SortedMap; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; @@ -24,7 +27,7 @@ * <ul> * <li>Task * <li>TaskTime - * <li>Version (contenant la version du shema en cas de migration)</li> + * <li>Version (contenant la version du schema en cas de migration)</li> * </ul> * * @author echatellier @@ -34,6 +37,7 @@ private static final Log log = LogFactory.getLog(Storage.class); protected static final String TABLE_TASK = "task"; + protected static final String TABLE_TIME = "tasktime"; protected static final String TABLE_VERSION = "version"; protected JTimerConfig config; @@ -78,7 +82,7 @@ conn.setAutoCommit(true); // test to create schema if it not already exists - boolean schemaExists = shemaExists(conn); + boolean schemaExists = schemaExists(conn); if (!schemaExists) { if (log.isInfoEnabled()) { log.info("Creating new database schema"); @@ -115,7 +119,7 @@ * @param conn sql connection * @return schema exists */ - protected boolean shemaExists(Connection conn) { + protected boolean schemaExists(Connection conn) { boolean found = false; Statement statement = null; try { @@ -141,13 +145,18 @@ try { statement = conn.createStatement(); statement.executeUpdate("CREATE TABLE " + TABLE_VERSION + - "(VERSION VARCHAR(10))"); + "(version VARCHAR(10))"); statement.executeUpdate("CREATE TABLE " + TABLE_TASK + - "(ID LONG NOT NULL AUTO_INCREMENT PRIMARY KEY," + - " NAME VARCHAR(255) NOT NULL," + - " PARENT LONG DEFAULT 0," + - " HIDDEN BOOLEAN," + - " NOTE TEXT)"); + "(id LONG NOT NULL AUTO_INCREMENT PRIMARY KEY," + + " name VARCHAR(255) NOT NULL," + + " parent LONG DEFAULT 0," + + " hidden BOOLEAN," + + " note TEXT)"); + statement.executeUpdate("CREATE TABLE " + TABLE_TIME + + "(taskid LONG NOT NULL REFERENCES " + TABLE_TASK + "(id)," + + " date DATE," + + " duration LONG," + + " PRIMARY KEY (taskid, date))"); } catch (SQLException ex) { throw new StorageException("Can't create schema", ex); } finally { @@ -178,12 +187,38 @@ } return result; } - + /** * Find all project. (task with no parent, parent = 0). * * @return all projects */ + public List<TimerProject> getProjects() { + List<TimerProject> projects = new ArrayList<TimerProject>(); + PreparedStatement statement = null; + try { + statement = connection.prepareStatement("SELECT * FROM " + TABLE_TASK + + " WHERE parent = 0 ORDER BY id"); + ResultSet rs = statement.executeQuery(); + while (rs.next()) { + TimerProject project = new TimerProject(); + project.setNumber(rs.getInt("id")); + project.setName(rs.getString("name")); + projects.add(project); + } + } catch (SQLException ex) { + throw new StorageException("Can't get project", ex); + } finally { + closeStatement(statement); + } + return projects; + } + + /** + * Find all project. (task with no parent, parent = 0). + * + * @return all projects + */ public TimerProject getProject(int offset) { TimerProject project = null; PreparedStatement statement = null; @@ -229,12 +264,12 @@ return result; } - public TimerTask getTask(TimerTask parent, int offset) { + public TimerTask getTasks(TimerTask parent, int offset) { TimerTask task = null; PreparedStatement statement = null; try { statement = connection.prepareStatement("SELECT * FROM " + TABLE_TASK + - " WHERE parent = ? ORDER BY ID LIMIT 1 OFFSET ?"); + " WHERE parent = ? ORDER BY id LIMIT 1 OFFSET ?"); statement.setLong(1, parent.getNumber()); statement.setLong(2, offset); ResultSet rs = statement.executeQuery(); @@ -250,6 +285,34 @@ } return task; } + + /** + * Find sub tasks.. + * + * @param parent parent task + * @return all sub tasks + */ + public List<TimerTask> getTasks(TimerTask parent) { + List<TimerTask> tasks = new ArrayList<TimerTask>(); + PreparedStatement statement = null; + try { + statement = connection.prepareStatement("SELECT * FROM " + TABLE_TASK + + " WHERE parent = ? ORDER BY id"); + statement.setLong(1, parent.getNumber()); + ResultSet rs = statement.executeQuery(); + while (rs.next()) { + TimerTask task = new TimerTask(); + task.setNumber(rs.getInt("id")); + task.setName(rs.getString("name")); + tasks.add(task); + } + } catch (SQLException ex) { + throw new StorageException("Can't get task", ex); + } finally { + closeStatement(statement); + } + return tasks; + } @Override public void addProject(TimerProject project) { @@ -257,7 +320,7 @@ try { statement = connection.prepareStatement("INSERT INTO " + TABLE_TASK + "(name, parent, hidden, note)" + - " VALUES (?, ?, ?, ?)"); + " VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); statement.setString(1, project.getName()); statement.setLong(2, 0); statement.setBoolean(3, project.isClosed()); @@ -282,7 +345,7 @@ try { statement = connection.prepareStatement("INSERT INTO " + TABLE_TASK + "(name, parent, hidden, note)" + - " VALUES (?, ?, ?, ?)"); + " VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); statement.setString(1, task.getName()); statement.setLong(2, task.getParent().getNumber()); statement.setBoolean(3, task.isClosed()); @@ -324,15 +387,46 @@ statement.setLong(5, task.getNumber()); statement.executeUpdate(); } catch (SQLException ex) { - throw new StorageException("Can't add project", ex); + throw new StorageException("Can't modify task", ex); } finally { closeStatement(statement); } } + protected void saveTaskTimes(TimerTask task) throws SQLException { + + // delete all + PreparedStatement statement = null; + try { + statement = connection.prepareStatement("DELETE " + TABLE_TIME + + " WHERE TASKID = ?"); + statement.setInt(1, task.getNumber()); + statement.executeUpdate(); + } finally { + closeStatement(statement); + } + + // add new + try { + statement = connection.prepareStatement("INSERT INTO " + TABLE_TIME + + "(taskid, date, duration)" + + " VALUES(?, ?, ?)"); + SortedMap<Date, Long> taskTimes = task.getAllDaysAndTimes(); + for (Map.Entry<Date, Long> taskTime : taskTimes.entrySet()) { + statement.setLong(1, task.getNumber()); + statement.setDate(2, new java.sql.Date(taskTime.getKey().getTime())); + statement.setLong(3, taskTime.getValue()); + + statement.executeUpdate(); + } + } finally { + closeStatement(statement); + } + } + @Override public void deleteProject(TimerProject project) { - deleteProject(project); + deleteTask(project); } @Override @@ -344,7 +438,7 @@ statement.setLong(1, task.getNumber()); statement.executeUpdate(); } catch (SQLException ex) { - throw new StorageException("Can't add project", ex); + throw new StorageException("Can't delete project", ex); } finally { closeStatement(statement); } @@ -357,7 +451,11 @@ @Override public void setTaskTime(TimerTask task, Date date, Long time) { - + try { + saveTaskTimes(task); + } catch (SQLException ex) { + throw new StorageException("Can't set task time", ex); + } } @Override Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksCellRenderer.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksCellRenderer.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksCellRenderer.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -41,13 +41,11 @@ import javax.swing.ImageIcon; import javax.swing.JTree; -import javax.swing.tree.DefaultMutableTreeNode; import javax.swing.tree.DefaultTreeCellRenderer; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.chorem.jtimer.data.DataEventListener; -import org.chorem.jtimer.data.TimerCore; import org.chorem.jtimer.entities.TimerProject; import org.chorem.jtimer.entities.TimerTask; import org.jdesktop.swingx.JXTreeTable; Modified: branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksModel.java =================================================================== --- branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksModel.java 2012-07-13 09:13:42 UTC (rev 2884) +++ branches/2.0.0-evol-499-storage/src/main/java/org/chorem/jtimer/ui/treetable/ProjectsAndTasksModel.java 2012-08-22 09:46:46 UTC (rev 2885) @@ -34,7 +34,6 @@ import javax.swing.SwingUtilities; import javax.swing.table.TableColumn; -import javax.swing.tree.DefaultMutableTreeNode; import javax.swing.tree.TreePath; import org.apache.commons.lang3.time.DurationFormatUtils; @@ -168,13 +167,43 @@ return value; } - /* - * @see org.jdesktop.swingx.treetable.DefaultTreeTableModel#isCellEditable(java.lang.Object, int) + /** + * Recupere la sous liste: data.getProjectsList() si parent = root + * getSubTasks() sinon. + * + * @param parent parent to task sublist + * @param noCache disable use of cached result and result caching + * @return filtered list */ - @Override - public boolean isCellEditable(Object object, int column) { - // non editable - return false; + protected List<TimerTask> getSubTaskFor(Object parent, boolean noCache) { + + List<TimerTask> result = subTasksCache.get(parent); + if (result == null || noCache) { + result = new ArrayList<TimerTask>(); + + // get correct list + if (parent == root) { // case root node + List<TimerProject> projects = storage.getProjects(); + result.addAll(projects); + } else { // not root node + List<TimerTask> tasks = storage.getTasks((TimerTask)parent); + result.addAll(tasks); + } + + // Since sort is not supported by the table, do a manual sorting. + result = TimerTaskHelper.sortTask(result); + + if (!noCache) { + // cache tasks name + for (TimerTask task : result) { + taskNameCache.put(task, task.getName()); + } + + subTasksCache.put(parent, result); + } + } + + return result; } /* @@ -183,15 +212,7 @@ @Override public Object getChild(Object parent, int index) { - Object child = null; - if (parent == root) { - TimerProject p = storage.getProject(index); - child = p; - } else { - TimerTask parentTask = (TimerTask)parent; - TimerTask t = storage.getTask(parentTask, index); - child = t; - } + Object child = getSubTaskFor(parent, false).get(index); return child; } @@ -201,14 +222,7 @@ @Override public int getChildCount(Object parent) { - int childCount = 0; - if (parent == root) { - childCount = storage.getProjectsCount(); - } else { - TimerTask parentTask = (TimerTask)parent; - childCount = storage.getTasksCount(parentTask); - } - + int childCount = getSubTaskFor(parent, false).size(); return childCount; } @@ -217,7 +231,8 @@ */ @Override public int getIndexOfChild(Object parent, Object child) { - throw new RuntimeException("Not yet implemented"); + int index = getSubTaskFor(parent, false).indexOf(child); + return index; } /* @@ -253,11 +268,12 @@ TimerTask pathLastComponent = (TimerTask) path.getLastPathComponent(); boolean updated = false; - // get childreen without cache in case of add operation + // get children without cache in case of add operation // delete operation MUST use cached result - int childCount = getChildCount(pathLastComponent); + List<TimerTask> subTasks = getSubTaskFor(pathLastComponent, operation == OPERATION_ADD); + int childCount = subTasks.size(); for (int childIndex = 0; !updated && childIndex < childCount; ++childIndex) { - TimerTask taskUO = (TimerTask)getChild(pathLastComponent, childIndex); + TimerTask taskUO = subTasks.get(childIndex); if (task.equals(taskUO)) {