[tasque] [sqlite] Caches task fields. (BGO #607816)

[Mario Carrion <mcarrion src gnome org>]

commit 9b1c5934ee8f768b5634563512f86f5186fd812c
Author: Didier Vidal <didier-devel melix net>
Date:   Wed Mar 31 15:49:36 2010 -0400

    [sqlite] Caches task fields. (BGO #607816)

 ChangeLog                            |    8 +++
 src/Backends/Sqlite/SqliteBackend.cs |   49 ++++++++++++--------
 src/Backends/Sqlite/SqliteTask.cs    |   83 ++++++++++++++++++----------------
 3 files changed, 81 insertions(+), 59 deletions(-)
---
diff --git a/ChangeLog b/ChangeLog
index e431749..28d63c2 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,11 @@
+2010-03-31  Mario Carrion  <mario carrion mx>
+
+	* src/Backends/Sqlite/SqliteBackend.cs:
+	* src/Backends/Sqlite/SqliteTask.cs: Caches task fields to accelerate
+	backend.
+
+	Patch by Didier Vidal. Fixes BGO #607816
+
 2010-03-30  Mario Carrion  <mario carrion mx>
 
 	* TRANSLATORS: Added file.
diff --git a/src/Backends/Sqlite/SqliteBackend.cs b/src/Backends/Sqlite/SqliteBackend.cs
index 4e7ec7a..417f6b2 100644
--- a/src/Backends/Sqlite/SqliteBackend.cs
+++ b/src/Backends/Sqlite/SqliteBackend.cs
@@ -243,10 +243,10 @@ namespace Tasque.Backends.Sqlite
 			bool hasValues = false;
 			
 			string command = "SELECT id FROM Categories";
-        	SqliteCommand cmd = db.Connection.CreateCommand();
-        	cmd.CommandText = command;
-        	SqliteDataReader dataReader = cmd.ExecuteReader();
-        	while(dataReader.Read()) {
+			SqliteCommand cmd = db.Connection.CreateCommand();
+			cmd.CommandText = command;
+			SqliteDataReader dataReader = cmd.ExecuteReader();
+			while(dataReader.Read()) {
 			    int id = dataReader.GetInt32(0);
 				hasValues = true;
 				
@@ -255,10 +255,10 @@ namespace Tasque.Backends.Sqlite
 					defaultCategory = newCategory;
 				iter = categoryListStore.Append ();
 				categoryListStore.SetValue (iter, 0, newCategory);				
-        	}
-
-        	dataReader.Close();
-        	cmd.Dispose();
+			}
+			
+			dataReader.Close();
+			cmd.Dispose();
 
 			if(!hasValues)
 			{
@@ -286,23 +286,32 @@ namespace Tasque.Backends.Sqlite
 			Gtk.TreeIter iter;
 			SqliteTask newTask;
 			bool hasValues = false;
-			
-			string command = "SELECT id FROM Tasks";
-        	SqliteCommand cmd = db.Connection.CreateCommand();
-        	cmd.CommandText = command;
-        	SqliteDataReader dataReader = cmd.ExecuteReader();
-        	while(dataReader.Read()) {
-			    int id = dataReader.GetInt32(0);
+
+			string command = "SELECT id,Category,Name,DueDate,CompletionDate,Priority, State FROM Tasks";
+			SqliteCommand cmd = db.Connection.CreateCommand();
+			cmd.CommandText = command;
+			SqliteDataReader dataReader = cmd.ExecuteReader();
+			while(dataReader.Read()) {
+				int id = dataReader.GetInt32(0);
+				int category = dataReader.GetInt32(1);
+				string name = dataReader.GetString(2);
+				int dueDate = dataReader.GetInt32(3);
+				int completionDate = dataReader.GetInt32(4);
+				int priority = dataReader.GetInt32(5);
+				int state = dataReader.GetInt32(6);
+
 				hasValues = true;
-				
-				newTask = new SqliteTask(this, id);
+
+				newTask = new SqliteTask(this, id, category,
+				                         name, dueDate, completionDate,
+				                         priority, state);
 				iter = taskStore.AppendNode();
 				taskStore.SetValue (iter, 0, newTask);
 				taskIters [newTask.SqliteId] = iter;
-        	}
+			}
 
-        	dataReader.Close();
-        	cmd.Dispose();
+			dataReader.Close();
+			cmd.Dispose();
 
 			if(!hasValues)
 			{
diff --git a/src/Backends/Sqlite/SqliteTask.cs b/src/Backends/Sqlite/SqliteTask.cs
index a9f5d42..9ede1b1 100644
--- a/src/Backends/Sqlite/SqliteTask.cs
+++ b/src/Backends/Sqlite/SqliteTask.cs
@@ -12,22 +12,41 @@ namespace Tasque.Backends.Sqlite
 	{
 		private SqliteBackend backend;
 		private int id;
+		private int category;
+		private string name;
+		private long dueDate;
+		private long completionDate;
+		private int priority;
+		private int state;
 
 		public SqliteTask(SqliteBackend backend, string name)
 		{
 			this.backend = backend;
 			Logger.Debug("Creating New Task Object : {0} (id={1})", name, id);
 			name = backend.SanitizeText (name);
+			this.name = name;
+			this.dueDate =  Database.FromDateTime(DateTime.MinValue);
+			this.completionDate = Database.FromDateTime(DateTime.MinValue);
+			this.category = 0;
+			this.priority = (int)(TaskPriority.None);
+			this.state = (int)TaskState.Active;
 			string command = String.Format("INSERT INTO Tasks (Name, DueDate, CompletionDate, Priority, State, Category, ExternalID) values ('{0}','{1}', '{2}','{3}', '{4}', '{5}', '{6}'); SELECT last_insert_rowid();", 
-								name, Database.FromDateTime(DateTime.MinValue), Database.FromDateTime(DateTime.MinValue), 
-								((int)(TaskPriority.None)), ((int)TaskState.Active), 0, string.Empty );
+								name, dueDate, completionDate,
+								priority, state, category, string.Empty);
 			this.id = Convert.ToInt32 (backend.Database.ExecuteScalar (command));
 		}
-		
-		public SqliteTask (SqliteBackend backend, int id)
+
+		public SqliteTask (SqliteBackend backend, int id, int category, string name, 
+		                   long dueDate, long completionDate, int priority, int state)
 		{
 			this.backend = backend;
 			this.id = id;
+			this.category = category;
+			this.name = name;
+			this.dueDate = dueDate;
+			this.completionDate = completionDate;
+			this.priority = priority;
+			this.state = state;
 		}		
 		
 		#region Public Properties
@@ -44,12 +63,10 @@ namespace Tasque.Backends.Sqlite
 		
 		public override string Name
 		{
-			get {
-				string command = String.Format("SELECT Name FROM Tasks where ID='{0}'", id);
-				return backend.Database.GetSingleString(command);
-			}
+			get { return this.name; }
 			set {
 				string name = backend.SanitizeText (value);
+				this.name = name;
 				string command = String.Format("UPDATE Tasks set Name='{0}' where ID='{1}'", name, id);
 				backend.Database.ExecuteScalar(command);
 				backend.UpdateTask(this);
@@ -58,12 +75,10 @@ namespace Tasque.Backends.Sqlite
 		
 		public override DateTime DueDate
 		{
-			get {
-				string command = String.Format("SELECT DueDate FROM Tasks where ID='{0}'", id);
-				return backend.Database.GetDateTime(command);
-			}
+			get { return Database.ToDateTime(this.dueDate); }
 			set {
-				string command = String.Format("UPDATE Tasks set DueDate='{0}' where ID='{1}'", Database.FromDateTime(value), id);
+			        this.dueDate = Database.FromDateTime(value);
+				string command = String.Format("UPDATE Tasks set DueDate='{0}' where ID='{1}'", this.dueDate, id);
 				backend.Database.ExecuteScalar(command);
 				backend.UpdateTask(this);				
 			}
@@ -72,14 +87,12 @@ namespace Tasque.Backends.Sqlite
 		
 		public override DateTime CompletionDate
 		{
-			get {
-				string command = String.Format("SELECT CompletionDate FROM Tasks where ID='{0}'", id);
-				return backend.Database.GetDateTime(command);
-			}
+			get { return Database.ToDateTime(this.completionDate); }
 			set {
-				string command = String.Format("UPDATE Tasks set CompletionDate='{0}' where ID='{1}'", Database.FromDateTime(value), id);
+				this.completionDate = Database.FromDateTime(value);
+				string command = String.Format("UPDATE Tasks set CompletionDate='{0}' where ID='{1}'", this.completionDate, id);
 				backend.Database.ExecuteScalar(command);
-				backend.UpdateTask(this);				
+				backend.UpdateTask(this);
 			}
 		}		
 		
@@ -96,14 +109,12 @@ namespace Tasque.Backends.Sqlite
 		
 		public override TaskPriority Priority
 		{
-			get {
-				string command = String.Format("SELECT Priority FROM Tasks where ID='{0}'", id);
-				return (TaskPriority)backend.Database.GetSingleInt(command);
-			}
+			get { return (TaskPriority) this.priority; }
 			set {
-				string command = String.Format("UPDATE Tasks set Priority='{0}' where ID='{1}'", ((int)value), id);
+				this.priority = (int) value;
+				string command = String.Format("UPDATE Tasks set Priority='{0}' where ID='{1}'", this.priority, id);
 				backend.Database.ExecuteScalar(command);
-				backend.UpdateTask(this);				
+				backend.UpdateTask(this);
 			}
 		}
 
@@ -127,27 +138,21 @@ namespace Tasque.Backends.Sqlite
 		
 		public TaskState LocalState
 		{
-			get {
-				string command = String.Format("SELECT State FROM Tasks where ID='{0}'", id);
-				return (TaskState)backend.Database.GetSingleInt(command);
-			}
+			get { return (TaskState) this.state; }
 			set {
-				string command = String.Format("UPDATE Tasks set State='{0}' where ID='{1}'", ((int)value), id);
+				this.state = (int) value;
+				string command = String.Format("UPDATE Tasks set State='{0}' where ID='{1}'", this.state, id);
 				backend.Database.ExecuteScalar(command);
-				backend.UpdateTask(this);				
+				backend.UpdateTask(this);
 			}
 		}
 
 		public override ICategory Category
 		{
-			get {
-				string command = String.Format("SELECT Category FROM Tasks where ID='{0}'", id);
-				int catID = backend.Database.GetSingleInt(command);
-				SqliteCategory sqCat = new SqliteCategory(backend, catID);
-				return sqCat;
-			}
+			get { return new SqliteCategory(backend, this.category); }
 			set {
- 				string command = String.Format("UPDATE Tasks set Category='{0}' where ID='{1}'", ((int)(value as SqliteCategory).ID), id);
+				this.category = (int)(value as SqliteCategory).ID;
+ 				string command = String.Format("UPDATE Tasks set Category='{0}' where ID='{1}'", category, id);
 				backend.Database.ExecuteScalar(command);
 				backend.UpdateTask(this);
 			}
@@ -166,7 +171,7 @@ namespace Tasque.Backends.Sqlite
 					int taskId = dataReader.GetInt32(0);
 					string text = dataReader.GetString(1);
 					notes.Add (new SqliteNote (taskId, text));
-        			}
+				}
 
 				return notes;
 			}