banshee r5168 - in trunk/banshee: . src/Core/Banshee.Services/Banshee.Configuration src/Core/Banshee.Services/Banshee.Database

[blorentz svn gnome org]

Author: blorentz
Date: Thu Mar 26 20:11:57 2009
New Revision: 5168
URL: http://svn.gnome.org/viewvc/banshee?rev=5168&view=rev

Log:
2009-03-26  Bertrand Lorentz  <bertrand lorentz gmail com>

	* src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs:
	* src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs:
	Automatically analyze the database when the number of records in a 
	table has changed significantly since last analysis. This should ensure
	optimal use of the indices (BGO #555937).



Modified:
   trunk/banshee/ChangeLog
   trunk/banshee/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs
   trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs

Modified: trunk/banshee/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs
==============================================================================
--- trunk/banshee/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs	(original)
+++ trunk/banshee/src/Core/Banshee.Services/Banshee.Configuration/DatabaseConfigurationClient.cs	Thu Mar 26 20:11:57 2009
@@ -37,11 +37,8 @@
 {
     public class DatabaseConfigurationClient : IConfigurationClient
     {
-        private static DatabaseConfigurationClient instance = new DatabaseConfigurationClient (
-            ServiceManager.DbConnection);
-        
         public static DatabaseConfigurationClient Client {
-            get { return instance; }
+            get { return ServiceManager.DbConnection.Configuration; }
         }
         
         private readonly BansheeDbConnection connection;

Modified: trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs
==============================================================================
--- trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs	(original)
+++ trunk/banshee/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs	Thu Mar 26 20:11:57 2009
@@ -37,12 +37,17 @@
 
 using Banshee.Base;
 using Banshee.ServiceStack;
+using Banshee.Configuration;
 
 namespace Banshee.Database
 {
     public sealed class BansheeDbConnection : HyenaSqliteConnection, IInitializeService, IRequiredService
     {
         private BansheeDbFormatMigrator migrator;
+        private DatabaseConfigurationClient configuration;
+        public DatabaseConfigurationClient Configuration {
+            get { return configuration; }
+        }
 
         public BansheeDbConnection () : base (DatabaseFile)
         {
@@ -59,6 +64,7 @@
             Log.DebugFormat ("Opened SQLite connection to {0}", DatabaseFile);
 
             migrator = new BansheeDbFormatMigrator (this);
+            configuration = new DatabaseConfigurationClient (this);
             
             if (Banshee.Base.ApplicationContext.CommandLine.Contains ("debug-sql")) {
                 Hyena.Data.Sqlite.HyenaSqliteCommand.LogAll = true;
@@ -72,11 +78,48 @@
                 migrator.Migrate ();
                 migrator = null;
 
+                try {
+                    OptimizeDatabase ();
+                } catch (Exception e) {
+                    Log.Exception ("Error determining if ANALYZE is necessary", e);
+                }
+                
                 // Update cached sorting keys
                 SortKeyUpdater.Update ();
             }
         }
 
+        private void OptimizeDatabase ()
+        {
+            bool needs_analyze = false;
+            long analyze_threshold = configuration.Get<long> ("Database", "AnalyzeThreshold", 100);
+            string [] tables_with_indexes = {"CoreTracks", "CoreArtists", "CoreAlbums",
+                "CorePlaylistEntries", "CoreSmartPlaylistEntries", "PodcastItems", "PodcastEnclosures",
+                "PodcastSyndications", "CoverArtDownloads"};
+            
+            if (TableExists ("sqlite_stat1")) {
+                foreach (string table_name in tables_with_indexes) {
+                    long count = Query<long> (String.Format ("SELECT COUNT(*) FROM {0}", table_name));
+                    string stat = Query<string> ("SELECT stat FROM sqlite_stat1 WHERE tbl = ? LIMIT 1", table_name);
+                    // stat contains space-separated integers,
+                    // the first is the number of records in the table
+                    long items_indexed = long.Parse (stat.Split (' ')[0]);
+                    
+                    if (Math.Abs (count - items_indexed) > analyze_threshold) {
+                        needs_analyze = true;
+                        break;
+                    }
+                }
+            } else {
+                needs_analyze = true;
+            }
+            
+            if (needs_analyze) {
+                Log.DebugFormat ("Running ANALYZE against database to improve performance");
+                Execute ("ANALYZE");
+            }
+        }
+ 
         public BansheeDbFormatMigrator Migrator {
             get { lock (this) { return migrator; } }
         }