[banshee] [Database] Add db schema validation tests

[Gabriel Burt <gburt src gnome org>]

commit 4dfcfab189e0fdf2d68ff8c52b8b353bd4c387bb
Author: Gabriel Burt <gabriel burt gmail com>
Date:   Mon Nov 1 18:14:17 2010 -0500

    [Database] Add db schema validation tests
    
    Also add --validate-db-schema command line argument that will run the
    validator on your real, live Banshee db.  The validator just checks that
    the db has the same tables as a new db, with the same columns, and the
    same indexes.  Fixes bgo#603668

 .../Banshee.Database/BansheeDbConnection.cs        |   63 ++++++++++++++++++++
 .../Banshee.Services/Banshee.Database/Tests.cs     |    3 +
 src/Hyena                                          |    2 +-
 tests/data/banshee-1.0.0.db                        |  Bin 0 -> 41984 bytes
 4 files changed, 67 insertions(+), 1 deletions(-)
---
diff --git a/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs b/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs
index c984474..dab8f53 100644
--- a/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs
+++ b/src/Core/Banshee.Services/Banshee.Database/BansheeDbConnection.cs
@@ -27,12 +27,15 @@
 //
 
 using System;
+using System.Linq;
+using System.Collections.Generic;
 using System.IO;
 using System.Data;
 using System.Threading;
 
 using Hyena;
 using Hyena.Data;
+using Hyena.Jobs;
 using Hyena.Data.Sqlite;
 
 using Banshee.Base;
@@ -45,12 +48,14 @@ namespace Banshee.Database
     {
         private BansheeDbFormatMigrator migrator;
         private DatabaseConfigurationClient configuration;
+        private bool validate_schema = false;
         public DatabaseConfigurationClient Configuration {
             get { return configuration; }
         }
 
         public BansheeDbConnection () : this (DatabaseFile)
         {
+            validate_schema = ApplicationContext.CommandLine.Contains ("validate-db-schema");
         }
 
         internal BansheeDbConnection (string db_path) : base (db_path)
@@ -78,6 +83,11 @@ namespace Banshee.Database
             }
         }
 
+        internal IEnumerable<string> SortedTableColumns (string table)
+        {
+            return GetSchema (table).Keys.OrderBy (n => n);
+        }
+
         void IInitializeService.Initialize ()
         {
             lock (this) {
@@ -103,6 +113,10 @@ namespace Banshee.Database
             if (Banshee.Metrics.BansheeMetrics.EnableCollection.Get ()) {
                 Banshee.Metrics.BansheeMetrics.Start ();
             }
+
+            if (validate_schema) {
+                ValidateSchema ();
+            }
         }
 
         private void OptimizeDatabase ()
@@ -142,6 +156,55 @@ namespace Banshee.Database
             get { lock (this) { return migrator; } }
         }
 
+        public bool ValidateSchema ()
+        {
+            bool is_valid = true;
+            var new_db_path = Paths.GetTempFileName (Paths.TempDir);
+            var new_db = new BansheeDbConnection (new_db_path);
+            ((IInitializeService)new_db).Initialize ();
+
+            Hyena.Log.DebugFormat ("Validating db schema for {0}", DbPath);
+
+            var tables = new_db.QueryEnumerable<string> (
+                "select name from sqlite_master where type='table' order by name"
+            );
+
+            foreach (var table in tables) {
+                if (!TableExists (table)) {
+                    Log.ErrorFormat ("Table {0} does not exist!", table);
+                    is_valid = false;
+                } else {
+                    var a = new_db.SortedTableColumns (table);
+                    var b = SortedTableColumns (table);
+
+                    a.Except (b).ForEach (c => { is_valid = false; Hyena.Log.ErrorFormat ("Table {0} should contain column {1}", table, c); });
+                    b.Except (a).ForEach (c => Hyena.Log.DebugFormat ("Table {0} has extra (probably obsolete) column {1}", table, c));
+                }
+            }
+
+            using (var reader = new_db.Query (
+                "select name,sql from sqlite_master where type='index' AND name NOT LIKE 'sqlite_autoindex%' order by name")) {
+                while (reader.Read ()) {
+                    string name = (string)reader[0];
+                    string sql = (string)reader[1];
+                    if (!IndexExists (name)) {
+                        Log.ErrorFormat ("Index {0} does not exist!", name);
+                        is_valid = false;
+                    } else {
+                        string our_sql = Query<string> ("select sql from sqlite_master where type='index' and name=?", name);
+                        if (our_sql != sql) {
+                            Log.ErrorFormat ("Index definition of {0} differs, should be `{1}` but is `{2}`", name, sql, our_sql);
+                            is_valid = false;
+                        }
+                    }
+                }
+            }
+
+            Hyena.Log.DebugFormat ("Done validating db schema for {0}", DbPath);
+            System.IO.File.Delete (new_db_path);
+            return is_valid;
+        }
+
         public static string DatabaseFile {
             get {
                 if (ApplicationContext.CommandLine.Contains ("db")) {
diff --git a/src/Core/Banshee.Services/Banshee.Database/Tests.cs b/src/Core/Banshee.Services/Banshee.Database/Tests.cs
index e2a459d..95ca53a 100644
--- a/src/Core/Banshee.Services/Banshee.Database/Tests.cs
+++ b/src/Core/Banshee.Services/Banshee.Database/Tests.cs
@@ -44,6 +44,8 @@ namespace Banshee.Database
         [Test]
         public void Migrate ()
         {
+            Paths.ApplicationName = Application.InternalName;
+
             foreach (string file in Directory.GetFiles (Path.Combine (TestsDir, "data"))) {
                 if (file.EndsWith (".db")) {
                     var db_file = file + ".test-tmp-copy";
@@ -55,6 +57,7 @@ namespace Banshee.Database
                         var db = new BansheeDbConnection (db_file);
                         SortKeyUpdater.Disable = true;
                         ((IInitializeService)db).Initialize ();
+                        Assert.IsTrue (db.ValidateSchema ());
                     } catch (Exception e) {
                         Assert.Fail (String.Format ("Failed to migrate db: {0}", e));
                     } finally {
diff --git a/src/Hyena b/src/Hyena
index 6115ff1..cc2a241 160000
--- a/src/Hyena
+++ b/src/Hyena
@@ -1 +1 @@
-Subproject commit 6115ff1eb54368923d3f37cbc124897ecf617d4c
+Subproject commit cc2a241a93c05e7c2c34af87b0434944fa7ce27f
diff --git a/tests/data/banshee-1.0.0.db b/tests/data/banshee-1.0.0.db
new file mode 100644
index 0000000..cb0c71c
Binary files /dev/null and b/tests/data/banshee-1.0.0.db differ