beagle r4403 - trunk/beagle/beagled

[kkubasik svn gnome org]

Author: kkubasik
Date: Sat Jan 19 01:28:45 2008
New Revision: 4403
URL: http://svn.gnome.org/viewvc/beagle?rev=4403&view=rev

Log:
Do some SqliteCommand caching so that commands will be prepared to increase performance. NEEDS LOTS OF TESTING as this might have some threading issues, or just not handle transactions 100%

Modified:
   trunk/beagle/beagled/FileAttributesStore_Sqlite.cs
   trunk/beagle/beagled/SqliteUtils.cs
   trunk/beagle/beagled/TextCache.cs

Modified: trunk/beagle/beagled/FileAttributesStore_Sqlite.cs
==============================================================================
--- trunk/beagle/beagled/FileAttributesStore_Sqlite.cs	(original)
+++ trunk/beagle/beagled/FileAttributesStore_Sqlite.cs	Sat Jan 19 01:28:45 2008
@@ -56,7 +56,9 @@
 		private SqliteConnection connection;
 		private BitArray path_flags;
 		private int transaction_count = 0;
-
+		public SqliteCommand ReadCommand;
+		public SqliteCommand InsertCommand;
+		public SqliteCommand DeleteCommand;
 		enum TransactionState {
 			None,
 			Requested,
@@ -181,6 +183,15 @@
 				Logger.Log.Debug ("Loaded {0} records from {1} in {2:0.000}s", 
 						 count, GetDbPath (directory), (dt2 - dt1).TotalSeconds);
 			}
+			ReadCommand = new SqliteCommand(this.connection);
+			ReadCommand.CommandText = "SELECT unique_id, directory, filename, last_mtime, last_attrtime, filter_name, filter_version " +
+				"FROM file_attributes WHERE directory= dir AND filename= fname";
+			InsertCommand = new SqliteCommand(this.connection);
+			InsertCommand.CommandText = "INSERT OR REPLACE INTO file_attributes " +
+							" (unique_id, directory, filename, last_mtime, last_attrtime, filter_name, filter_version) " +
+							" VALUES (@unique_id, @directory, @filename, @last_mtime, @last_attrtime, @filter_name, @filter_version)";
+			DeleteCommand = new SqliteCommand(this.connection);
+			DeleteCommand.CommandText = "DELETE FROM file_attributes WHERE directory= directory AND filename= filename";
 		}
 
 		///////////////////////////////////////////////////////////////////
@@ -247,7 +258,7 @@
 			if (path != null && path != "/" && path.EndsWith ("/"))
 				path = path.TrimEnd ('/');
 
-			SqliteCommand command;
+			//SqliteCommand command;
 			SqliteDataReader reader;
 
 			if (! GetPathFlag (path))
@@ -260,16 +271,18 @@
 			string directory = FileSystem.GetDirectoryNameRootOk (path).Replace ("'", "''");
 			string filename = Path.GetFileName (path).Replace ("'", "''");
 			lock (connection) {
-				command = SqliteUtils.QueryCommand (connection,
-								    "directory='{0}' AND filename='{1}'",
-								    directory, filename);
-				reader = SqliteUtils.ExecuteReaderOrWait (command);
+				//command = SqliteUtils.QueryCommand (connection,
+								 //   "directory='{0}' AND filename='{1}'",
+								//    directory, filename);
+				ReadCommand.Parameters.AddWithValue ("@dir",directory);
+				ReadCommand.Parameters.AddWithValue ("@fname",filename);
+				reader = SqliteUtils.ExecuteReaderOrWait (ReadCommand);
 				
 				if (SqliteUtils.ReadOrWait (reader))
 					attr = GetFromReader (reader);
 					
 				reader.Close ();
-				command.Dispose ();
+				//command.Dispose ();
 			}
 
 			return attr;
@@ -292,19 +305,19 @@
 				if (filter_name == null)
 					filter_name = "";
 				filter_name = filter_name.Replace ("'", "''");
-
-				ret = SqliteUtils.DoNonQuery (connection,
-							"INSERT OR REPLACE INTO file_attributes " +
-							" (unique_id, directory, filename, last_mtime, last_attrtime, filter_name, filter_version) " +
-							" VALUES (@unique_id, @directory, @filename, @last_mtime, @last_attrtime, @filter_name, @filter_version)",
-							new string [] {"@unique_id", "@directory", "@filename", "@last_mtime", "@last_attrtime", "@filter_name", "@filter_version"},
-							new object [] {
+				string[] param= new string [] {"@unique_id", "@directory", "@filename", "@last_mtime", "@last_attrtime", "@filter_name", "@filter_version"};
+				object[] vals =	new object [] {
 								GuidFu.ToShortString (fa.UniqueId),
 								fa.Directory.Replace ("'", "''"), fa.Filename.Replace ("'", "''"),
 								StringFu.DateTimeToString (fa.LastWriteTime),
 								StringFu.DateTimeToString (fa.LastAttrTime),
 								filter_name,
-								fa.FilterVersion});
+								fa.FilterVersion};
+				for (int i=0; i<param.Length; i++){
+					InsertCommand.Parameters.AddWithValue (param[i], vals[i]);
+				}
+				ret = SqliteUtils.DoNonQuery (InsertCommand);
+				
 			}
 
 			return (ret != 0);
@@ -327,11 +340,9 @@
 
 				// If a transaction has been requested, start it now.
 				MaybeStartTransaction ();
-
-				SqliteUtils.DoNonQuery (connection,
-							"DELETE FROM file_attributes WHERE directory= directory AND filename= filename",
-							new string [] {"@directory", "@filename"},
-							new object [] {directory, filename});
+				DeleteCommand.Parameters.AddWithValue ("@directory", directory);
+				DeleteCommand.Parameters.AddWithValue ("@filename", filename);
+				SqliteUtils.DoNonQuery (DeleteCommand);
 			}
 		}
 

Modified: trunk/beagle/beagled/SqliteUtils.cs
==============================================================================
--- trunk/beagle/beagled/SqliteUtils.cs	(original)
+++ trunk/beagle/beagled/SqliteUtils.cs	Sat Jan 19 01:28:45 2008
@@ -72,6 +72,22 @@
 			return DoNonQuery (connection, command_text, null, null);
 		}
 
+		public static int DoNonQuery ( SqliteCommand command)
+		{
+			int ret = 0;
+			while (true) {
+					try {
+						ret = command.ExecuteNonQuery ();
+						break;
+					} catch (SqliteBusyException ex) {
+						Thread.Sleep (50);
+					} catch (Exception e) {
+						Log.Error (e, "SQL that caused the exception: {0}",command.CommandText);
+						throw;
+					}
+				}
+			return ret;
+		}
 		public static SqliteCommand QueryCommand (SqliteConnection connection, string where_format, params object [] where_args)
 		{
 			SqliteCommand command;

Modified: trunk/beagle/beagled/TextCache.cs
==============================================================================
--- trunk/beagle/beagled/TextCache.cs	(original)
+++ trunk/beagle/beagled/TextCache.cs	Sat Jan 19 01:28:45 2008
@@ -50,7 +50,10 @@
 
 		private const string SELF_CACHE_TAG = "*self*";
 		private const string BLOB_TAG = "*blob*";
-
+		public SqliteCommand InsertCommand;
+		public SqliteCommand LookupPathCommand;
+		public SqliteCommand LookupDataCommand;
+		public SqliteCommand DeleteCommand;
 		private string text_cache_dir;
 		internal string TextCacheDir {
 			get { return text_cache_dir; }
@@ -166,8 +169,23 @@
 							"  data     BLOB                     " +
 							")");
 			}
+			this.initCommands();
 		}
 
+		private void initCommands(){
+			InsertCommand = new SqliteCommand(this.connection);
+			InsertCommand.CommandText = "INSERT OR REPLACE INTO textcache_data (uri, filename, data) VALUES (@uri,@filename,@data)";
+			LookupPathCommand = new SqliteCommand(this.connection);
+			LookupPathCommand.CommandText = "SELECT filename FROM textcache_data WHERE uri= uri";
+			LookupDataCommand = new SqliteCommand(this.connection);
+			LookupDataCommand.CommandText = "SELECT filename, data FROM textcache_data WHERE uri= uri";
+			DeleteCommand = new SqliteCommand(this.connection);
+			DeleteCommand.CommandText = "DELETE FROM textcache_data WHERE uri= uri";
+			//InsertCommand.Parameters.Add("uri");
+			//InsertCommand.Parameters.Add("filename");
+			//InsertCommand.Parameters.Add("data");
+			
+		}
 		private SqliteConnection Open (string db_filename)
 		{
 			SqliteConnection connection = new SqliteConnection ();
@@ -194,10 +212,14 @@
 		{
 			lock (connection) {
 				MaybeStartTransaction_Unlocked ();
-				SqliteUtils.DoNonQuery (connection,
-							"INSERT OR REPLACE INTO textcache_data (uri, filename, data) VALUES (@uri,@filename,@data)",
-							new string [] {"@uri", "@filename", "@data"},
-							new object [] {UriToString (uri), filename, data});
+				InsertCommand.Parameters.AddWithValue("@uri",UriToString (uri));
+				InsertCommand.Parameters.AddWithValue("@filename",filename);
+				InsertCommand.Parameters.AddWithValue("@data", data);
+				SqliteUtils.DoNonQuery (InsertCommand);
+//				SqliteUtils.DoNonQuery (connection,
+//							"INSERT OR REPLACE INTO textcache_data (uri, filename, data) VALUES (@uri,@filename,@data)",
+//							new string [] {"@uri", "@filename", "@data"},
+//							new object [] {UriToString (uri), filename, data});
 			}
 		}
 
@@ -210,18 +232,18 @@
 		// Returns raw path as stored in the db i.e. relative path wrt the text_cache_dir
 		private string LookupPathRawUnlocked (Uri uri)
 		{
-			SqliteCommand command;
+			//SqliteCommand command;
 			SqliteDataReader reader = null;
 			string path = null;
-
-			command = NewCommand ("SELECT filename FROM textcache_data WHERE uri='{0}'", 
-			                      UriToString (uri));
-			reader = SqliteUtils.ExecuteReaderOrWait (command);
+			LookupPathCommand.Parameters.AddWithValue("@uri", UriToString(uri));
+			//command = NewCommand ("SELECT filename FROM textcache_data WHERE uri='{0}'", 
+			//                     UriToString (uri));
+			reader = SqliteUtils.ExecuteReaderOrWait (LookupPathCommand);
 			if (SqliteUtils.ReadOrWait (reader))
 				path = reader.GetString (0);
 			reader.Close ();
-			command.Dispose ();
-
+			//command.Dispose ();
+			
 			return path;
 		}
 
@@ -441,15 +463,16 @@
 		// If self_cache is true when called, then self_cache will be set upon return
 		public TextReader GetReader (Uri uri, ref bool self_cache)
 		{
-			SqliteCommand command;
+			//SqliteCommand command;
 			SqliteDataReader reader = null;
 			byte[] blob = null;
 			string filename = null;
 
 			lock (connection) {
-				command = NewCommand ("SELECT filename, data FROM textcache_data WHERE uri='{0}'", 
-				                      UriToString (uri));
-				reader = SqliteUtils.ExecuteReaderOrWait (command);
+				//command = NewCommand ("SELECT filename, data FROM textcache_data WHERE uri='{0}'", 
+				//                      UriToString (uri));
+				LookupDataCommand.Parameters.AddWithValue("@uri",UriToString (uri));
+				reader = SqliteUtils.ExecuteReaderOrWait (LookupDataCommand);
 				if (! SqliteUtils.ReadOrWait (reader)) {
 					if (self_cache)
 						self_cache = false;
@@ -460,7 +483,7 @@
 				if (! reader.IsDBNull (1))
 					blob = reader.GetValue (1) as byte [];
 				reader.Close ();
-				command.Dispose ();
+				//command.Dispose ();
 			}
 
 			if (filename == SELF_CACHE_TAG) {
@@ -500,10 +523,8 @@
 				string path = LookupPathRawUnlocked (uri);
 				if (path != null) {
 					MaybeStartTransaction_Unlocked ();
-					SqliteUtils.DoNonQuery (connection,
-								"DELETE FROM textcache_data WHERE uri= uri", 
-								new string [] {"@uri"},
-								new object [] {UriToString (uri)}); 
+					DeleteCommand.Parameters.AddWithValue("@uri", UriToString (uri));
+					SqliteUtils.DoNonQuery (DeleteCommand);
 					if (path != SELF_CACHE_TAG && path != BLOB_TAG)
 						File.Delete (Path.Combine (text_cache_dir, path));
 				}