Added database upgrade code to force COLLATE NOCASE

diff --git a/trunk/Pithos.Core/Agents/StatusAgent.cs b/trunk/Pithos.Core/Agents/StatusAgent.cs
index 1f3b8f7..d02871b 100644 (file)
--- a/trunk/Pithos.Core/Agents/StatusAgent.cs
+++ b/trunk/Pithos.Core/Agents/StatusAgent.cs
@@ -100,15 +100,16 @@ namespace Pithos.Core.Agents
 
             var source = GetConfiguration(_pithosDataPath);
             ActiveRecordStarter.Initialize(source,typeof(FileState),typeof(FileTag));
-            
-            ActiveRecordStarter.UpdateSchema();
+
+            UpgradeDatabase();
+
 
 
             if (!File.Exists(dbPath))
                 ActiveRecordStarter.CreateSchema();
 
             CreateTrigger();
-            
+
         }
 
 
@@ -135,6 +136,81 @@ namespace Pithos.Core.Agents
             }
         }
 
+        private T? GetNull<T>(string commandText,SQLiteConnection connection) where T:struct 
+        {
+            using (var command= new SQLiteCommand(commandText, connection))
+            {
+                var result = command.ExecuteScalar();
+                if (result == null)
+                    return null;
+                return (T)result;
+            }
+        }
+
+        private T Get<T>(string commandText,SQLiteConnection connection) 
+        {
+            using (var command= new SQLiteCommand(commandText, connection))
+            {
+                var result = command.ExecuteScalar();
+                if (result == null)
+                    return default(T);
+                return (T)result;
+            }
+        }
+
+        private int Run(string commandText,SQLiteConnection connection)
+        {
+            using (var command= new SQLiteCommand(commandText, connection))
+            {
+                var result=command.ExecuteNonQuery();
+                return result;
+            }
+        }
+
+        private void UpgradeDatabase()
+        {
+            const string hasVersionText = "select 1 from sqlite_master where name='Version'";
+
+            const string getVersionCmd = "select Version from version where Id=1";
+
+            const string createVersionCmd = "create table Version(Id integer,Version TEXT);\n" +
+                                            "INSERT INTO VERSION (Id,Version) VALUES(1,'0.0.0.0');";
+
+            const string upgradeText = "PRAGMA writable_schema = 1;\n" +
+                                   "UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE FileState (Id UNIQUEIDENTIFIER not null, ObjectID TEXT COLLATE NOCASE, FilePath TEXT unique COLLATE NOCASE, OverlayStatus INTEGER, FileStatus INTEGER, ConflictReason TEXT, Checksum TEXT COLLATE NOCASE, ETag TEXT not null COLLATE NOCASE, LastMD5 TEXT not null COLLATE NOCASE, LastWriteDate DATETIME, LastLength INTEGER, Version INTEGER, VersionTimeStamp DATETIME, IsShared INTEGER, SharedBy TEXT, ShareWrite INTEGER, IsFolder INTEGER, Modified DATETIME, primary key (Id),unique (FilePath))' WHERE NAME = 'FileState';\n" +
+                                   "PRAGMA writable_schema = 0;\n" +
+                                   "VACUUM;";
+
+            using (var connection = GetConnection())
+            {
+                var hasVersion = false;
+                hasVersion = GetNull<long>(hasVersionText, connection).HasValue;
+
+                var storedVersion = new Version();
+
+                if (hasVersion)
+                {
+                    var versionTxt = Get<string>(getVersionCmd, connection);
+                    storedVersion = new Version(versionTxt);
+                }
+                else
+                    Run(createVersionCmd, connection);
+
+                var actualVersion = Assembly.GetEntryAssembly().GetName().Version;
+                if (!hasVersion || actualVersion > storedVersion)
+                    Run(upgradeText, connection);
+
+                if (actualVersion != storedVersion)
+                    using (var updateVersionCmd = new SQLiteCommand("UPDATE VERSION SET Version=:version where ID=1",
+                                                                 connection))
+                    {
+                        updateVersionCmd.Parameters.AddWithValue(":version", actualVersion.ToString());
+                        var result = updateVersionCmd.ExecuteNonQuery();
+                        Debug.Assert(result > 0);
+                    }
+            }
+        }
+
         private void CreateTrigger()
         {
             using (var connection = GetConnection())