[chronojump] DB: 1.70, 1, 71 Table runEncoder and import existing files
- From: Xavier de Blas <xaviblas src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [chronojump] DB: 1.70, 1, 71 Table runEncoder and import existing files
- Date: Tue, 17 Sep 2019 11:31:02 +0000 (UTC)
commit 3a87fda96bc5f73c65c71dbf39b1b363563ae293
Author: Xavier de Blas <xaviblas gmail com>
Date: Tue Sep 17 13:30:28 2019 +0200
DB: 1.70, 1,71 Table runEncoder and import existing files
src/Makefile.am | 1 +
src/constants.cs | 2 +
src/forceSensor.cs | 2 +-
src/gui/runEncoder.cs | 6 +-
src/runEncoder.cs | 118 ++++++++++++++++++++--
src/sqlite/main.cs | 23 ++++-
src/sqlite/runEncoder.cs | 247 +++++++++++++++++++++++++++++++++++++++++++++++
7 files changed, 388 insertions(+), 11 deletions(-)
---
diff --git a/src/Makefile.am b/src/Makefile.am
index 5e1ea0c3..4c3aa91a 100644
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -122,6 +122,7 @@ SOURCES = \
sqlite/stat.cs\
sqlite/pulse.cs\
sqlite/pulseType.cs\
+ sqlite/runEncoder.cs\
sqlite/reactionTime.cs\
sqlite/multiChronopic.cs\
sqlite/event.cs\
diff --git a/src/constants.cs b/src/constants.cs
index 8b4812f5..8a0dbef1 100644
--- a/src/constants.cs
+++ b/src/constants.cs
@@ -160,6 +160,8 @@ public class Constants
public const string ForceSensorTable = "forceSensor";
public const string ForceSensorExerciseTable = "forceSensorExercise";
public const string ForceRFDTable = "forceRFD";
+ public const string RunEncoderTable = "runEncoder";
+ //public const string RunEncoderExerciseTable = "runEncoderExercise";
// Dummy variables that exists for translating purposes
// pragma warning is to avoid warnings of "defined and not used" for these variables.
diff --git a/src/forceSensor.cs b/src/forceSensor.cs
index e5dc043c..e71f3070 100644
--- a/src/forceSensor.cs
+++ b/src/forceSensor.cs
@@ -19,7 +19,7 @@
*/
using System;
-using System.IO; //for detect OS
+using System.IO; //for detect OS //TextWriter
using System.Collections.Generic; //List<T>
using Mono.Unix;
diff --git a/src/gui/runEncoder.cs b/src/gui/runEncoder.cs
index fabcb859..a137c911 100644
--- a/src/gui/runEncoder.cs
+++ b/src/gui/runEncoder.cs
@@ -46,7 +46,7 @@ public partial class ChronoJumpWindow
int race_analyzer_temperature;
int race_analyzer_graph_width;
int race_analyzer_graph_height;
- RunEncoderGraph.Devices race_analyzer_device;
+ RunEncoder.Devices race_analyzer_device;
Thread runEncoderCaptureThread;
@@ -215,9 +215,9 @@ public partial class ChronoJumpWindow
race_analyzer_graph_height = Convert.ToInt32(race_analyzer_spinbutton_graph_height.Value);
if(race_analyzer_radio_device_manual.Active)
- race_analyzer_device = RunEncoderGraph.Devices.MANUAL;
+ race_analyzer_device = RunEncoder.Devices.MANUAL;
else
- race_analyzer_device = RunEncoderGraph.Devices.RESISTED;
+ race_analyzer_device = RunEncoder.Devices.RESISTED;
}
//TODO: do all this with an "other" thread like in force sensor to allow connecting messages to be
displayed
diff --git a/src/runEncoder.cs b/src/runEncoder.cs
index 2e2150d6..869573c5 100644
--- a/src/runEncoder.cs
+++ b/src/runEncoder.cs
@@ -15,24 +15,130 @@
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
- * Copyright (C) 2018 Xavier de Blas <xaviblas gmail com>
+ * Copyright (C) 2018-2019 Xavier de Blas <xaviblas gmail com>
*/
using System;
-using System.IO; //for detect OS
+using System.IO; //for detect OS //TextWriter
using System.Collections.Generic; //List<T>
-public class RunEncoderGraph
+public class RunEncoder
{
- public enum Devices { MANUAL, RESISTED }
+ public enum Devices { MANUAL, RESISTED } //RESISTED will have two columns on the CSV (encoder,
forecSensor)
+ public static string DevicesStringMANUAL = "Manual race analyzer";
+ public static string DevicesStringRESISTED = "Resisted race analyzer";
+
+ private int uniqueID;
+ private int personID;
+ private int sessionID;
+ private int exerciseID; //until runEncoderExercise table is not created, all will be 0
+ private int angle;
+ private Devices device;
+ private int distance;
+ private int temperature;
+ private string filename;
+ private string url; //relative
+ private string dateTime;
+ private string comments;
+ private string videoURL;
+
+ //private string exerciseName;
+
+ /* constructors */
+
+ //have a uniqueID -1 contructor, useful when set is deleted
+ public RunEncoder()
+ {
+ uniqueID = -1;
+ }
+
+ //constructor
+ public RunEncoder(int uniqueID, int personID, int sessionID, int exerciseID, Devices device,
+ int distance, int temperature, string filename, string url,
+ string dateTime, string comments, string videoURL
+ //, string exerciseName
+ )
+ {
+ this.uniqueID = uniqueID;
+ this.personID = personID;
+ this.sessionID = sessionID;
+ this.exerciseID = exerciseID;
+ this.device = device;
+ this.distance = distance;
+ this.temperature = temperature;
+ this.filename = filename;
+ this.url = url;
+ this.dateTime = dateTime;
+ this.comments = comments;
+ this.videoURL = videoURL;
+
+ //this.exerciseName = exerciseName;
+ }
+
+ /* methods */
+
+ public int InsertSQL(bool dbconOpened)
+ {
+ return SqliteRunEncoder.Insert(dbconOpened, toSQLInsertString());
+ }
+ private string toSQLInsertString()
+ {
+ string uniqueIDStr = "NULL";
+ if(uniqueID != -1)
+ uniqueIDStr = uniqueID.ToString();
+
+ return
+ "(" + uniqueIDStr + ", " + personID + ", " + sessionID + ", " + exerciseID + ", \"" +
device.ToString() + "\", " +
+ distance + ", " + temperature + ", \"" + filename + "\", \"" + url + "\", \"" +
dateTime + "\", \"" +
+ comments + "\", \"" + videoURL + "\")";
+ }
+
+ public void UpdateSQL(bool dbconOpened)
+ {
+ SqliteRunEncoder.Update(dbconOpened, toSQLUpdateString());
+ }
+ private string toSQLUpdateString()
+ {
+ return
+ " uniqueID = " + uniqueID +
+ ", personID = " + personID +
+ ", sessionID = " + sessionID +
+ ", exerciseID = " + exerciseID +
+ ", device = \"" + device.ToString() +
+ "\", distance = " + distance +
+ ", temperature = " + temperature +
+ ", filename = \"" + filename +
+ "\", url = \"" + url +
+ "\", dateTime = \"" + dateTime +
+ "\", comments = \"" + comments +
+ "\", videoURL = \"" + Util.MakeURLrelative(videoURL) +
+ "\" WHERE uniqueID = " + uniqueID;
+ }
+
+ public string FullURL
+ {
+ get { return Util.GetRaceAnalyzerSessionDir(sessionID) + Path.DirectorySeparatorChar +
filename; }
+ }
+ public int UniqueID
+ {
+ get { return uniqueID; }
+ }
+ public int ExerciseID
+ {
+ get { return exerciseID; }
+ }
+}
+
+public class RunEncoderGraph
+{
private int testLength;
private double mass;
private double personHeight;
private double tempC;
- private Devices device;
+ private RunEncoder.Devices device;
- public RunEncoderGraph(int testLength, double mass, double personHeight, double tempC, Devices device)
+ public RunEncoderGraph(int testLength, double mass, double personHeight, double tempC,
RunEncoder.Devices device)
{
this.testLength = testLength;
this.mass = mass;
diff --git a/src/sqlite/main.cs b/src/sqlite/main.cs
index fc7fe332..a5c1cb2b 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.69";
+ static string lastChronojumpDatabaseVersion = "1.71";
public Sqlite() {
}
@@ -2440,6 +2440,22 @@ class Sqlite
currentVersion = updateVersion("1.69");
}
+ if(currentVersion == "1.69")
+ {
+ LogB.SQL("Created table: RunEncoder");
+
+ SqliteRunEncoder.createTable();
+
+ currentVersion = updateVersion("1.70");
+ }
+ if(currentVersion == "1.70")
+ {
+ LogB.SQL("Imported run encoder text files into SQL");
+
+ SqliteRunEncoder.import_from_1_70_to_1_71();
+
+ currentVersion = updateVersion("1.71");
+ }
// --- add more updates here
@@ -2619,6 +2635,9 @@ class Sqlite
SqliteForceSensorRFD.createTable();
SqliteForceSensorRFD.InsertDefaultValues(true);
+ //runEncoder
+ SqliteRunEncoder.createTable();
+
creationRate ++;
SqlitePreferences.createTable();
SqlitePreferences.initializeTable(lastChronojumpDatabaseVersion, creatingBlankDatabase);
@@ -2629,6 +2648,8 @@ class Sqlite
SqliteJson.createTableUploadExhibitionTestTemp ();
//changes [from - to - desc]
+ //1.70 - 1.71 Converted DB to 1.71 Imported run encoder text files into SQL
+ //1.69 - 1.70 Converted DB to 1.70 Created table: RunEncoder
//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
diff --git a/src/sqlite/runEncoder.cs b/src/sqlite/runEncoder.cs
new file mode 100644
index 00000000..85aba469
--- /dev/null
+++ b/src/sqlite/runEncoder.cs
@@ -0,0 +1,247 @@
+/*
+ * This file is part of ChronoJump
+ *
+ * ChronoJump is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * ChronoJump is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+ *
+ * Copyright (C) 2019 Xavier de Blas <xaviblas gmail com>
+ */
+
+using System;
+//using System.Data;
+using System.Collections;
+using System.IO; //DirectoryInfo
+using Mono.Data.Sqlite;
+using System.Text.RegularExpressions; //Regex
+
+class SqliteRunEncoder : Sqlite
+{
+ private static string table = Constants.RunEncoderTable;
+
+ public SqliteRunEncoder() {
+ }
+
+ ~SqliteRunEncoder() {}
+
+ /*
+ * create and initialize tables
+ */
+
+ protected internal static void createTable()
+ {
+ dbcmd.CommandText =
+ "CREATE TABLE " + table + " ( " +
+ "uniqueID INTEGER PRIMARY KEY, " +
+ "personID INT, " +
+ "sessionID INT, " +
+ "exerciseID INT, " + //right now all will be exercise 0, until we have a clear idea
of what exercises could be done and how can affect measurements
+ "device TEXT, " +
+ "distance INT, " +
+ "temperature INT, " +
+ "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 int Insert (bool dbconOpened, string insertString)
+ {
+ openIfNeeded(dbconOpened);
+
+ dbcmd.CommandText = "INSERT INTO " + table +
+ " (uniqueID, personID, sessionID, exerciseID, device, distance, temperature,
filename, url, dateTime, comments, videoURL)" +
+ " VALUES " + insertString;
+ LogB.SQL(dbcmd.CommandText.ToString());
+ dbcmd.ExecuteNonQuery(); //TODO uncomment this again
+
+ string myString = @"select last_insert_rowid()";
+ dbcmd.CommandText = myString;
+ int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since
`ExecuteScalar` returns an object.
+
+ closeIfNeeded(dbconOpened);
+
+ return myLast;
+ }
+
+ public static void Update (bool dbconOpened, string updateString)
+ {
+ openIfNeeded(dbconOpened);
+
+ dbcmd.CommandText = "UPDATE " + table + " SET " + updateString;
+
+ LogB.SQL(dbcmd.CommandText.ToString());
+ dbcmd.ExecuteNonQuery();
+
+ closeIfNeeded(dbconOpened);
+ }
+
+ /* right now unused
+ public static void DeleteSQLAndFile (bool dbconOpened, int uniqueID)
+ {
+ RunEncoder fs = (RunEncoder) Select (dbconOpened, uniqueID, -1, -1)[0];
+ DeleteSQLAndFile (dbconOpened, fs);
+ }
+ */
+ public static void DeleteSQLAndFile (bool dbconOpened, RunEncoder fs)
+ {
+ openIfNeeded(dbconOpened);
+
+ dbcmd.CommandText = "DELETE FROM " + table + " WHERE uniqueID = " + fs.UniqueID;
+
+ LogB.SQL(dbcmd.CommandText.ToString());
+ dbcmd.ExecuteNonQuery();
+
+ closeIfNeeded(dbconOpened);
+
+ //delete the file
+ Util.FileDelete(fs.FullURL);
+ }
+
+ public static ArrayList Select (bool dbconOpened, int uniqueID, int personID, int sessionID)
+ {
+ openIfNeeded(dbconOpened);
+
+ /*
+ * future: when we have RunEncoderExerciseTable
+ string selectStr = "SELECT " + table + ".*, " + Constants.RunEncoderExerciseTable + ".Name
FROM " + table + ", " + Constants.RunEncoderExerciseTable;
+ string whereStr = " WHERE " + table + ".exerciseID = " + Constants.RunEncoderExerciseTable +
".UniqueID ";
+ */
+ string selectStr = "SELECT * FROM " + table;
+ string connector = " WHERE ";
+
+ string uniqueIDStr = "";
+ if(uniqueID != -1)
+ {
+ uniqueIDStr = connector + table + ".uniqueID = " + uniqueID;
+ connector = " AND ";
+ }
+
+ string personIDStr = "";
+ if(personID != -1)
+ {
+ personIDStr = connector + table + ".personID = " + personID;
+ connector = " AND ";
+ }
+
+ string sessionIDStr = "";
+ if(sessionID != -1)
+ {
+ sessionIDStr = connector + table + ".sessionID = " + sessionID;
+ connector = " AND ";
+ }
+
+ //dbcmd.CommandText = selectStr + whereStr + uniqueIDStr + personIDStr + sessionIDStr + "
Order BY " + table + ".uniqueID";
+ dbcmd.CommandText = selectStr + uniqueIDStr + personIDStr + sessionIDStr + " Order BY " +
table + ".uniqueID";
+
+ LogB.SQL(dbcmd.CommandText.ToString());
+ dbcmd.ExecuteNonQuery();
+
+ SqliteDataReader reader;
+ reader = dbcmd.ExecuteReader();
+
+ ArrayList array = new ArrayList(1);
+ RunEncoder fs;
+
+ while(reader.Read()) {
+ fs = new RunEncoder (
+ Convert.ToInt32(reader[0].ToString()), //uniqueID
+ Convert.ToInt32(reader[1].ToString()), //personID
+ Convert.ToInt32(reader[2].ToString()), //sessionID
+ Convert.ToInt32(reader[3].ToString()), //exerciseID
+ (RunEncoder.Devices) Enum.Parse(
+ typeof(RunEncoder.Devices), reader[4].ToString()),
//captureOption
+ Convert.ToInt32(reader[5].ToString()), //distance
+ Convert.ToInt32(reader[6].ToString()), //temperature
+ reader[7].ToString(), //filename
+ Util.MakeURLabsolute(fixOSpath(reader[8].ToString())), //url
+ reader[9].ToString(), //datetime
+ reader[10].ToString(), //comments
+ reader[11].ToString() //videoURL
+ /*
+ reader[11].ToString(), //videoURL
+ reader[12].ToString() //exerciseName
+ */
+ );
+ array.Add(fs);
+ }
+
+ reader.Close();
+ closeIfNeeded(dbconOpened);
+
+ return array;
+ }
+
+ protected internal static void import_from_1_70_to_1_71() //database is opened
+ {
+ LogB.PrintAllThreads = true; //TODO: remove this
+ LogB.Information("at import_from_1_70_to_1_71()");
+ //LogB.Information("Sqlite isOpened: " + Sqlite.IsOpened.ToString());
+
+ string raceAnalyzerDir = Util.GetRaceAnalyzerDir();
+ DirectoryInfo [] sessions = new DirectoryInfo(raceAnalyzerDir).GetDirectories();
+ foreach (DirectoryInfo session in sessions) //session.Name will be the UniqueID
+ {
+ FileInfo[] files = session.GetFiles();
+ foreach (FileInfo file in files)
+ {
+ //in dir there are .csv and .png, take only the .csv
+ if(Util.GetExtension(file.Name) != ".csv")
+ continue;
+
+ string fileWithoutExtension =
Util.RemoveExtension(Util.GetLastPartOfPath(file.Name));
+ RaceEncoderLoadTryToAssignPerson relt =
+ new RaceEncoderLoadTryToAssignPerson(true, fileWithoutExtension,
Convert.ToInt32(session.Name));
+
+ Person p = relt.GetPerson();
+ if(p.UniqueID == -1)
+ continue;
+
+ if(! Util.IsNumber(session.Name, false))
+ continue;
+
+ 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;
+
+ //filename will be this
+ string myFilename = p.UniqueID + "_" + p.Name + "_" + parsedDate + ".csv";
+ //try to move the file
+ try{
+ File.Move(file.FullName,
Util.GetRaceAnalyzerSessionDir(Convert.ToInt32(session.Name)) + Path.DirectorySeparatorChar + myFilename);
+ } catch {
+ //if cannot, then use old filename
+ myFilename = file.FullName;
+ }
+
+ int exerciseID = 0; //initial import with all exercises as 0 (because
exercises are not yet defined)
+ int distance = 99; //mark to know at import that this have to be changed
+ int temperature = 25;
+ RunEncoder runEncoder = new RunEncoder(-1, p.UniqueID,
Convert.ToInt32(session.Name), exerciseID,
+ RunEncoder.Devices.MANUAL, distance, temperature,
+ myFilename,
+
Util.MakeURLrelative(Util.GetRaceAnalyzerSessionDir(Convert.ToInt32(session.Name))),
+ parsedDate, "", "");
+ runEncoder.InsertSQL(true);
+ }
+ }
+
+ LogB.Information("end of import_from_1_70_to_1_71()");
+ LogB.PrintAllThreads = false; //TODO: remove this
+ }
+
+}
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
