[chronojump] DB Migration from 2.34 to 2.35 done in Windows with a transaction and not with column RENAME TO

[Xavier de Blas <xaviblas src gnome org>]

commit 0c43794a4f788c9cf4f961b1cda6c9ef50f02d12
Author: Xavier de Blas <xaviblas gmail com>
Date:   Fri Feb 25 12:32:17 2022 +0100

    DB Migration from 2.34 to 2.35 done in Windows with a transaction and not with column RENAME TO

 src/sqlite/forceSensor.cs | 27 +++++++++++++++++++++++++++
 src/sqlite/main.cs        | 42 +++++++++++++++++++++++++++++++++++++++++-
 2 files changed, 68 insertions(+), 1 deletion(-)
---
diff --git a/src/sqlite/forceSensor.cs b/src/sqlite/forceSensor.cs
index 38d98cb74..f7536151d 100644
--- a/src/sqlite/forceSensor.cs
+++ b/src/sqlite/forceSensor.cs
@@ -275,6 +275,33 @@ class SqliteForceSensor : Sqlite
                return array;
        }
 
+       //this method is here to have a createTable that does not change in future versions
+       protected internal static void createTable_windows_forceSensor_db_2_34_migration
+               (SqliteCommand mycmd, string migrateToTable) //needed for migration from 2_34 to 2.35 on 
windows
+       {
+               mycmd.CommandText =
+                       "DROP TABLE IF EXISTS \"" + migrateToTable +
+                       "\"; CREATE TABLE \"" + migrateToTable + "\" ( " +
+                       "uniqueID INTEGER PRIMARY KEY, " +
+                       "personID INT, " +
+                       "sessionID INT, " +
+                       "exerciseID INT, " +
+                       "captureOption TEXT, " + //ForceSensor.CaptureOptions {NORMAL, ABS, INVERTED}
+                       "angle INT, " +         //angle can be different than the defaultAngle on exercise
+                       "laterality TEXT, " +   //"Both" "Right" "Left". stored in english
+                       "filename TEXT, " +
+                       "url TEXT, " +          //URL of data files. stored as relative
+                       "datetime TEXT, " +     //2019-07-11_15-01-44
+                       "comments TEXT, " +
+                       "videoURL TEXT, " +     //URL of video of signals. stored as relative
+                       "stiffness FLOAT DEFAULT -1, " +        //this is the important, next one is needed 
for recalculate, but note that some bands can have changed or being deleted
+                       "stiffnessString TEXT, " + //uniqueID*active of ElasticBand separated by ';' or empty 
if exerciseID ! elastic
+                       "maxForceRaw FLOAT, " +
+                       "maxAvgForce1s FLOAT)";
+               LogB.SQL(mycmd.CommandText.ToString());
+               mycmd.ExecuteNonQuery();
+       }
+
        /*
         * this import converts all the forceSensor files into SQL rows with a column pointing the file
         * persons have to be recognized/created (if is not possible to get the person then an Unknown person 
is created)
diff --git a/src/sqlite/main.cs b/src/sqlite/main.cs
index a4565c392..90c38486a 100644
--- a/src/sqlite/main.cs
+++ b/src/sqlite/main.cs
@@ -3169,7 +3169,40 @@ class Sqlite
                                        if(columnExists(true, Constants.ForceSensorTable, "maxForceRAW", 
true))
                                        {
                                                LogB.SQL("renaming column maxForceRAW...");
-                                               executeSQL("ALTER TABLE " + Constants.ForceSensorTable + " 
RENAME COLUMN maxForceRAW TO maxForceRaw;");
+
+                                               //on Windows with cerbero compilation, sqlite implementation 
is very old (previous to 3.25.0)
+                                               //so there is no RENAME COLUMN and we need to do it in old way
+                                               if(! UtilAll.IsWindows())
+                                                       renameColumnLinuxOrMac (Constants.ForceSensorTable, 
"maxForceRAW", "maxForceRaw");
+                                               else
+                                               {
+                                                       using(SqliteTransaction tr = dbcon.BeginTransaction())
+                                                       {
+                                                               using (SqliteCommand dbcmdTr = 
dbcon.CreateCommand())
+                                                               {
+                                                                       dbcmdTr.Transaction = tr;
+
+                                                                       //1. create table temp (dropping it 
first if exists)
+                                                                       
SqliteForceSensor.createTable_windows_forceSensor_db_2_34_migration (dbcmdTr, "forceSensorTemp");
+
+                                                                       //2. copy data
+                                                                       dbcmdTr.CommandText = "INSERT INTO 
forceSensorTemp SELECT * from forceSensor";
+                                                                       
LogB.SQL(dbcmdTr.CommandText.ToString());
+                                                                       dbcmdTr.ExecuteNonQuery();
+
+                                                                       //3. drop initial table
+                                                                       dbcmdTr.CommandText = "DROP TABLE 
forceSensor";
+                                                                       
LogB.SQL(dbcmdTr.CommandText.ToString());
+                                                                       dbcmdTr.ExecuteNonQuery();
+
+                                                                       //4. rename table (this works on old 
sqlite implementations, tested on our cerbero)
+                                                                       dbcmdTr.CommandText = "ALTER TABLE 
forceSensorTemp RENAME TO forceSensor";
+                                                                       
LogB.SQL(dbcmdTr.CommandText.ToString());
+                                                                       dbcmdTr.ExecuteNonQuery();
+                                                               }
+                                                               tr.Commit();
+                                                       }
+                                               }
                                                LogB.SQL("renamed.");
                                        }
                                } catch {
@@ -3652,6 +3685,13 @@ class Sqlite
                return exists;
        }
 
+       //on Windows with cerbero compilation, sqlite implementation is very old (previous to 3.25.0)
+       //so there is no RENAME COLUMN and we need to do it in old way
+       private static void renameColumnLinuxOrMac (string table, string cOld, string cNew)
+       {
+               executeSQL("ALTER TABLE " + table + " RENAME COLUMN \"" + cOld + "\" TO \"" + cNew + "\";");
+       }
+
        public static bool Exists(bool dbconOpened, string tableName, string findName)
        {
                return (ExistsDo(dbconOpened, tableName, findName) != -1);