[chronojump] DB: 1.68, 1.69 force sensor capture on SQL and import old data
- From: Xavier de Blas <xaviblas src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [chronojump] DB: 1.68, 1.69 force sensor capture on SQL and import old data
- Date: Fri, 6 Sep 2019 13:06:38 +0000 (UTC)
commit a1607cc52bad5eebc6d9db862fb7932208d813df
Author: Xavier de Blas <xaviblas gmail com>
Date: Fri Sep 6 15:05:11 2019 +0200
DB: 1.68, 1.69 force sensor capture on SQL and import old data
po/POTFILES.in | 1 +
src/constants.cs | 1 +
src/forceSensor.cs | 61 +++++++++++++++++++--
src/gui/forceSensor.cs | 46 +++++++++++-----
src/gui/person.cs | 2 +-
src/sqlite/forceSensor.cs | 130 +++++++++++++++++++++++++++++++++++++++++++-
src/sqlite/main.cs | 22 +++++++-
src/sqlite/person.cs | 4 +-
src/sqlite/personSession.cs | 11 +++-
9 files changed, 251 insertions(+), 27 deletions(-)
---
diff --git a/po/POTFILES.in b/po/POTFILES.in
index 5ed8627b..decc54fa 100644
--- a/po/POTFILES.in
+++ b/po/POTFILES.in
@@ -108,6 +108,7 @@ src/serverPing.cs
src/sprint.cs
src/sqlite/country.cs
src/sqlite/encoder.cs
+src/sqlite/forceSensor.cs
src/sqlite/main.cs
src/sqlite/personSession.cs
src/sqlite/server.cs
diff --git a/src/constants.cs b/src/constants.cs
index 4c088644..06b0187d 100644
--- a/src/constants.cs
+++ b/src/constants.cs
@@ -157,6 +157,7 @@ public class Constants
public const string Encoder1RMTable = "encoder1RM";
public const string ExecuteAutoTable = "executeAuto";
public const string TriggerTable = "trigger";
+ public const string ForceSensorTable = "forceSensor";
public const string ForceSensorExerciseTable = "forceSensorExercise";
public const string ForceRFDTable = "forceRFD";
diff --git a/src/forceSensor.cs b/src/forceSensor.cs
index 7f31bdad..029f73d1 100644
--- a/src/forceSensor.cs
+++ b/src/forceSensor.cs
@@ -26,7 +26,55 @@ using Mono.Unix;
public class ForceSensor
{
public enum CaptureOptions { NORMAL, ABS, INVERTED }
+ public static int AngleUndefined = -1000;
+ private int uniqueID;
+ private int personID;
+ private int sessionID;
+ private int exerciseID;
+ private int angle;
+ private string laterality;
+ private string filename;
+ private string url; //relative
+ private string dateTime;
+ private string comments;
+ private string videoURL;
+
+ //constructor
+ public ForceSensor(int uniqueID, int personID, int sessionID, int exerciseID, int angle,
+ string laterality, string filename, string url, string dateTime, string comments,
string videoURL)
+ {
+ this.uniqueID = uniqueID;
+ this.personID = personID;
+ this.sessionID = sessionID;
+ this.exerciseID = exerciseID;
+ this.angle = angle;
+ this.laterality = laterality;
+ this.filename = filename;
+ this.url = url;
+ this.dateTime = dateTime;
+ this.comments = comments;
+ this.videoURL = videoURL;
+ }
+
+ public void InsertSQL(bool dbconOpened)
+ {
+ SqliteForceSensor.Insert(dbconOpened, toSQLInsertString());
+ }
+
+ private string toSQLInsertString()
+ {
+ string uniqueIDStr = "NULL";
+ if(uniqueID != -1)
+ uniqueIDStr = uniqueID.ToString();
+
+ return
+ "(" + uniqueIDStr + ", " + personID + ", " + sessionID + ", " + exerciseID + ", " +
+ angle + ", \"" + laterality + "\", \"" + filename + "\", \"" + url + "\", \"" +
dateTime + "\", \"" +
+ comments + "\", \"" + videoURL + "\")";
+ }
+
+ //static methods
public static double ForceWithCaptureOptionsAndBW (double force, CaptureOptions fsco, int
percentBodyWeight, double personWeight)
{
if(percentBodyWeight > 0 && personWeight > 0)
@@ -39,6 +87,7 @@ public class ForceSensor
return force;
}
+
}
public class ForceSensorExercise
@@ -333,6 +382,7 @@ public class ForceSensorCapturePoints
RealWidthG *= 2;
outsideGraph = true;
}
+
if(p.Y < 0)
{
RealHeightG *= 2;
@@ -343,6 +393,7 @@ public class ForceSensorCapturePoints
RealHeightGNeg *= 2;
outsideGraph = true;
}
+
return outsideGraph;
}
// this is called at load signal, checks if last X is outside the graph and max/min force
@@ -1064,14 +1115,16 @@ public class ForceSensorAnalyzeInstant
//we need this class because we started using forcesensor without database (only text files)
public class ForceSensorLoadTryToAssignPersonAndMore
{
+ private bool dbconOpened;
private string filename; //filename comes without extension
private int currentSessionID; //we get a person if already exists on that session
public string Exercise;
public string Laterality;
public string Comment;
- public ForceSensorLoadTryToAssignPersonAndMore(string filename, int currentSessionID)
+ public ForceSensorLoadTryToAssignPersonAndMore(bool dbconOpened, string filename, int
currentSessionID)
{
+ this.dbconOpened = dbconOpened;
this.filename = filename;
this.currentSessionID = currentSessionID;
@@ -1086,8 +1139,8 @@ public class ForceSensorLoadTryToAssignPersonAndMore
if(personName == "")
return new Person(-1);
- Person p = SqlitePerson.SelectByName(personName);
- if(SqlitePersonSession.PersonSelectExistsInSession(p.UniqueID, currentSessionID))
+ Person p = SqlitePerson.SelectByName(dbconOpened, personName);
+ if(SqlitePersonSession.PersonSelectExistsInSession(dbconOpened, p.UniqueID, currentSessionID))
return p;
return new Person(-1);
@@ -1111,7 +1164,7 @@ public class ForceSensorLoadTryToAssignPersonAndMore
else if(strFull.Length >= 5)
{
//strFull[1] is the exercise, but check that it existst on database
- if(Sqlite.Exists(false, Constants.ForceSensorExerciseTable, strFull[1]))
+ if(Sqlite.Exists(dbconOpened, Constants.ForceSensorExerciseTable, strFull[1]))
Exercise = strFull[1];
if(
diff --git a/src/gui/forceSensor.cs b/src/gui/forceSensor.cs
index 9425b00e..b0427266 100644
--- a/src/gui/forceSensor.cs
+++ b/src/gui/forceSensor.cs
@@ -764,18 +764,16 @@ public partial class ChronoJumpWindow
forceCaptureStartMark = true;
forceSensorTimeStart = DateTime.Now; //to have an active count of capture time
+ DateTime forceSensorTimeStartCapture = forceSensorTimeStart; //to have same DateTime on
filename and on sql datetime
capturingForce = arduinoCaptureStatus.CAPTURING;
+ string captureComment = getCaptureComment(); //includes "_" if it's no empty
Util.CreateForceSensorSessionDirIfNeeded (currentSession.UniqueID);
//done at on_buttons_force_sensor_clicked()
//assignCurrentForceSensorExercise();
- string fileNamePre = currentPerson.Name + "_" +
- Catalog.GetString(currentForceSensorExercise.Name) + "_" +
- getLaterality() + "_" +
- getCaptureComment() + //includes "_" if it's no empty
- UtilDate.ToFile(DateTime.Now);
+ string fileNamePre = currentPerson.UniqueID + "_" + currentPerson.Name + "_" +
UtilDate.ToFile(forceSensorTimeStartCapture);
ForceSensor.CaptureOptions forceSensorCaptureOption = getForceSensorCaptureOptions();
@@ -896,7 +894,12 @@ public partial class ChronoJumpWindow
((IDisposable)writer).Dispose();
capturingForce = arduinoCaptureStatus.STOP;
- //port.Close();
+ ForceSensor forceSensor = new ForceSensor(-1, currentPerson.UniqueID, currentSession.UniqueID,
+ currentForceSensorExercise.UniqueID, ForceSensor.AngleUndefined,
getLaterality(false),
+ Util.GetLastPartOfPath(fileName), //filename
+ Util.MakeURLrelative(Util.GetForceSensorSessionDir(currentSession.UniqueID)),
//url
+ UtilDate.ToFile(forceSensorTimeStartCapture), captureComment, "");
//dateTime, comment, videoURL
+ forceSensor.InsertSQL(false);
if(forceProcessCancel || forceProcessError)
Util.FileDelete(fileName);
@@ -1241,7 +1244,7 @@ LogB.Information(" re R ");
lastForceSensorFile =
Util.RemoveExtension(Util.GetLastPartOfPath(filechooser.Filename));
//try to change currentPerson on loading set
- ForceSensorLoadTryToAssignPersonAndMore fslt = new
ForceSensorLoadTryToAssignPersonAndMore(lastForceSensorFile, currentSession.UniqueID);
+ ForceSensorLoadTryToAssignPersonAndMore fslt = new
ForceSensorLoadTryToAssignPersonAndMore(false, lastForceSensorFile, currentSession.UniqueID);
Person p = fslt.GetPerson();
if(p.UniqueID != -1)
{
@@ -1284,6 +1287,18 @@ LogB.Information(" re R ");
filechooser.Destroy ();
}
+ private void on_button_force_sensor_capture_recalculate_clicked (object o, EventArgs args)
+ {
+ if(! Util.FileExists(lastForceSensorFullPath))
+ {
+ new DialogMessage(Constants.MessageTypes.WARNING, Constants.FileNotFoundStr());
+ return;
+ }
+
+ if(lastForceSensorFullPath != null && lastForceSensorFullPath != "")
+ forceSensorCopyTempAndDoGraphs();
+ }
+
private void on_button_force_sensor_analyze_recalculate_clicked (object o, EventArgs args)
{
if(! Util.FileExists(lastForceSensorFullPath))
@@ -2002,14 +2017,20 @@ LogB.Information(" re R ");
return ForceSensor.CaptureOptions.NORMAL;
}
- private string getLaterality()
+ private string getLaterality(bool translated)
{
+ string lat = "";
if(radio_force_sensor_laterality_both.Active)
- return Catalog.GetString(Constants.ForceSensorLateralityBoth);
+ lat = Constants.ForceSensorLateralityBoth;
else if(radio_force_sensor_laterality_l.Active)
- return Catalog.GetString(Constants.ForceSensorLateralityLeft);
+ lat = Constants.ForceSensorLateralityLeft;
else //if(radio_force_sensor_laterality_r.Active)
- return Catalog.GetString(Constants.ForceSensorLateralityRight);
+ lat = Constants.ForceSensorLateralityRight;
+
+ if(translated)
+ return Catalog.GetString(lat);
+
+ return lat;
}
private void setLaterality(string s)
{
@@ -2024,9 +2045,6 @@ LogB.Information(" re R ");
private string getCaptureComment()
{
string s = Util.MakeValidSQL(textview_force_sensor_capture_comment.Buffer.Text);
- if(s != "")
- s += "_";
-
return s;
}
diff --git a/src/gui/person.cs b/src/gui/person.cs
index 01fbc293..94a71989 100644
--- a/src/gui/person.cs
+++ b/src/gui/person.cs
@@ -1720,7 +1720,7 @@ public class PersonAddModifyWindow
if(p.UniqueID != -1)
{
personLoadable = ! SqlitePersonSession.PersonSelectExistsInSession(
- p.UniqueID, currentSession.UniqueID);
+ false, p.UniqueID, currentSession.UniqueID);
loadingPerson = p;
}
}
diff --git a/src/sqlite/forceSensor.cs b/src/sqlite/forceSensor.cs
index 9fc66c33..b34d3baf 100644
--- a/src/sqlite/forceSensor.cs
+++ b/src/sqlite/forceSensor.cs
@@ -22,7 +22,126 @@ using System;
//using System.Data;
using System.Collections;
using System.Collections.Generic; //List<T>
+using System.IO; //DirectoryInfo
using Mono.Data.Sqlite;
+using System.Text.RegularExpressions; //Regex
+using Mono.Unix;
+
+class SqliteForceSensor : Sqlite
+{
+ private static string table = Constants.ForceSensorTable;
+
+ public SqliteForceSensor() {
+ }
+
+ ~SqliteForceSensor() {}
+
+ /*
+ * create and initialize tables
+ */
+
+ protected internal static void createTable()
+ {
+ dbcmd.CommandText =
+ "CREATE TABLE " + table + " ( " +
+ "uniqueID INTEGER PRIMARY KEY, " +
+ "personID INT, " +
+ "sessionID INT, " +
+ "exerciseID INT, " +
+ "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
+ LogB.SQL(dbcmd.CommandText.ToString());
+ dbcmd.ExecuteNonQuery();
+ }
+
+ public static void Insert (bool dbconOpened, string insertString)
+ {
+ if(! dbconOpened)
+ Sqlite.Open();
+
+ dbcmd.CommandText = "INSERT INTO " + table +
+ " (uniqueID, personID, sessionID, exerciseID, angle, laterality, filename,
url, dateTime, comments, videoURL)" +
+ " VALUES " + insertString;
+ LogB.SQL(dbcmd.CommandText.ToString());
+ dbcmd.ExecuteNonQuery(); //TODO uncomment this again
+
+ if(! dbconOpened)
+ Sqlite.Close();
+ }
+
+ protected internal static void import_from_1_68_to_1_69() //database is opened
+ {
+ LogB.PrintAllThreads = true; //TODO: remove this
+ LogB.Information("at import_from_1_68_to_1_69()");
+ //LogB.Information("Sqlite isOpened: " + Sqlite.IsOpened.ToString());
+
+ string forceSensorDir = Util.GetForceSensorDir();
+
+ int unknownExerciseID = Sqlite.ExistsAndGetUniqueID(true, Constants.ForceSensorExerciseTable,
Catalog.GetString("Unknown"));
+
+ DirectoryInfo [] sessions = new DirectoryInfo(forceSensorDir).GetDirectories();
+ foreach (DirectoryInfo session in sessions) //session.Name will be the UniqueID
+ {
+ foreach (FileInfo file in session.GetFiles())
+ {
+ string fileWithoutExtension =
Util.RemoveExtension(Util.GetLastPartOfPath(file.Name));
+ ForceSensorLoadTryToAssignPersonAndMore fslt =
+ new ForceSensorLoadTryToAssignPersonAndMore(true,
fileWithoutExtension, Convert.ToInt32(session.Name));
+ //TODO: no se si session.ToString() és la manera de saber el nom del
DirectoryInfo
+
+ Person p = fslt.GetPerson();
+ if(p.UniqueID == -1)
+ continue;
+
+ if(! Util.IsNumber(session.Name, false))
+ continue;
+
+ //at the beginning exercise was not written on the filename, because force
sensor started without exercises on sql
+ //"person name_2017-11-11_19-35-55.csv"
+ //if cannot found exercise, assign to Unknown
+ int exerciseID = -1;
+ if(fslt.Exercise != "")
+ exerciseID = ExistsAndGetUniqueID(true,
Constants.ForceSensorExerciseTable, fslt.Exercise);
+
+ if(fslt.Exercise == "" || exerciseID == -1)
+ {
+ if(unknownExerciseID == -1)
+ unknownExerciseID = SqliteForceSensorExercise.Insert (true,
-1, Catalog.GetString("Unknown"), 0, "", 0, "", false);
+
+ exerciseID = unknownExerciseID;
+ }
+
+ //laterality (in English)
+ string lat = fslt.Laterality;
+ if(lat == Catalog.GetString(Constants.ForceSensorLateralityRight))
+ lat = Constants.ForceSensorLateralityRight;
+ else if(lat == Catalog.GetString(Constants.ForceSensorLateralityLeft))
+ lat = Constants.ForceSensorLateralityLeft;
+ else
+ lat = Constants.ForceSensorLateralityBoth;
+
+ string parsedDate = UtilDate.ToFile(DateTime.MinValue);
+ Match match = Regex.Match(file.Name, @"(\d+-\d+-\d+_\d+-\d+-\d+)");
+ if(match.Groups.Count == 2)
+ parsedDate = match.Value;
+
+ ForceSensor forceSensor = new ForceSensor(-1, p.UniqueID,
Convert.ToInt32(session.Name), exerciseID, ForceSensor.AngleUndefined, lat,
+ //file.Name,
+ p.UniqueID + "_" + p.Name + "_" + parsedDate, //filename
+
Util.MakeURLrelative(Util.GetForceSensorSessionDir(Convert.ToInt32(session.Name))), //laterality, filename,
url
+ parsedDate, fslt.Comment, "");
+ forceSensor.InsertSQL(true);
+ }
+ }
+
+ LogB.PrintAllThreads = false; //TODO: remove this
+ }
+}
class SqliteForceSensorExercise : Sqlite
{
@@ -48,12 +167,13 @@ class SqliteForceSensorExercise : Sqlite
"angleDefault INT, " +
"description TEXT, " +
"tareBeforeCapture INT)";
+ LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
}
//undefined defaultAngle will be 1000
//note execution can have a different angle than the default angle
- public static void Insert (bool dbconOpened, int uniqueID, string name, int percentBodyWeight,
+ public static int Insert (bool dbconOpened, int uniqueID, string name, int percentBodyWeight,
string resistance, int angleDefault, string description, bool tareBeforeCapture)
{
if(! dbconOpened)
@@ -64,15 +184,21 @@ class SqliteForceSensorExercise : Sqlite
uniqueIDStr = uniqueID.ToString();
dbcmd.CommandText = "INSERT INTO " + table +
- " (uniqueID, name, percentBodyWeight, resistance, angleDefault, description)"
+
+ " (uniqueID, name, percentBodyWeight, resistance, angleDefault, description,
tareBeforeCapture)" +
" VALUES (" + uniqueIDStr + ", \"" + name + "\", " + percentBodyWeight + ",
\"" +
resistance + "\", " + angleDefault + ", \"" + description + "\", " +
Util.BoolToInt(tareBeforeCapture).ToString() + ")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
+ string myString = @"select last_insert_rowid()";
+ dbcmd.CommandText = myString;
+ int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since
`ExecuteScalar` returns an object.
+
if(! dbconOpened)
Sqlite.Close();
+
+ return myLast;
}
public static void Update (bool dbconOpened, ForceSensorExercise ex)
diff --git a/src/sqlite/main.cs b/src/sqlite/main.cs
index 1980623a..acb78df1 100644
--- a/src/sqlite/main.cs
+++ b/src/sqlite/main.cs
@@ -125,7 +125,7 @@ class Sqlite
/*
* Important, change this if there's any update to database
*/
- static string lastChronojumpDatabaseVersion = "1.67";
+ static string lastChronojumpDatabaseVersion = "1.69";
public Sqlite() {
}
@@ -2424,6 +2424,22 @@ class Sqlite
executeSQL("ALTER TABLE " + Constants.ForceSensorExerciseTable + " ADD COLUMN
tareBeforeCapture INT NOT NULL DEFAULT 0;");
currentVersion = updateVersion("1.67");
}
+ if(currentVersion == "1.67")
+ {
+ LogB.SQL("Created table: ForceSensor");
+
+ SqliteForceSensor.createTable();
+
+ currentVersion = updateVersion("1.68");
+ }
+ if(currentVersion == "1.68")
+ {
+ LogB.SQL("Imported force sensor text files into SQL");
+
+ SqliteForceSensor.import_from_1_68_to_1_69();
+
+ currentVersion = updateVersion("1.69");
+ }
// --- add more updates here
@@ -2597,6 +2613,8 @@ class Sqlite
SqliteChronopicRegister.createTableChronopicRegister();
SqliteTrigger.createTableTrigger();
+ //forceSensor
+ SqliteForceSensor.createTable();
SqliteForceSensorExercise.createTable();
SqliteForceSensorRFD.createTable();
SqliteForceSensorRFD.InsertDefaultValues(true);
@@ -2611,6 +2629,8 @@ class Sqlite
SqliteJson.createTableUploadExhibitionTestTemp ();
//changes [from - to - desc]
+ //1.68 - 1.69 Converted DB to 1.69 Imported force sensor text files into SQL
+ //1.67 - 1.68 Converted DB to 1.68 Created table: ForceSensor
//1.66 - 1.67 Converted DB to 1.67 ALTER TABLE Constants.ForceSensorExerciseTable ADD COLUMN
tareBeforeCapture INT
//1.65 - 1.66 Converted DB to 1.66 Added to preferences: encoderCaptureSecondaryVariableShow
//1.64 - 1.65 Converted DB to 1.65 Added to preferences: encoderCaptureSecondaryVariable
diff --git a/src/sqlite/person.cs b/src/sqlite/person.cs
index 3659c813..41915901 100644
--- a/src/sqlite/person.cs
+++ b/src/sqlite/person.cs
@@ -94,8 +94,8 @@ class SqlitePerson : Sqlite
public static Person Select(int uniqueID) {
return Select(false, " WHERE uniqueID = " + uniqueID);
}
- public static Person SelectByName(string name) {
- return Select(false, " WHERE name = \"" + name + "\"");
+ public static Person SelectByName(bool dbconOpened, string name) {
+ return Select(dbconOpened, " WHERE name = \"" + name + "\"");
}
public static Person SelectByRFID(string rfid) {
return Select(false, " WHERE future1 = \"" + rfid + "\"");
diff --git a/src/sqlite/personSession.cs b/src/sqlite/personSession.cs
index de5e0748..451bc8ba 100644
--- a/src/sqlite/personSession.cs
+++ b/src/sqlite/personSession.cs
@@ -172,9 +172,11 @@ class SqlitePersonSession : Sqlite
Sqlite.Close();
}
- public static bool PersonSelectExistsInSession(int myPersonID, int mySessionID)
+ public static bool PersonSelectExistsInSession(bool dbconOpened, int myPersonID, int mySessionID)
{
- Sqlite.Open();
+ if(! dbconOpened)
+ Sqlite.Open();
+
dbcmd.CommandText = "SELECT * FROM " + Constants.PersonSessionTable +
" WHERE personID == " + myPersonID +
" AND sessionID == " + mySessionID ;
@@ -190,7 +192,10 @@ class SqlitePersonSession : Sqlite
exists = true;
reader.Close();
- Sqlite.Close();
+
+ if(! dbconOpened)
+ Sqlite.Close();
+
return exists;
}
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
