[chronojump/77] Separating person and personSession old code
- From: Xavier de Blas <xaviblas src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [chronojump/77] Separating person and personSession old code
- Date: Wed, 24 Feb 2010 13:20:06 +0000 (UTC)
commit 288c5b15d8ef7cb43492cc03c251d113144c90c1
Author: Xavier de Blas <xaviblas gmail com>
Date: Wed Feb 24 21:19:26 2010 +0800
Separating person and personSession old code
src/constants.cs | 10 +-
src/oldCodeNeedToDBConvert/person.cs | 16 +-
src/oldCodeNeedToDBConvert/personSession.cs | 14 +-
src/oldCodeNeedToDBConvert/sqlite/person.cs | 501 +-------------------
src/oldCodeNeedToDBConvert/sqlite/personSession.cs | 335 +-------------
src/sqlite/main.cs | 46 +-
6 files changed, 64 insertions(+), 858 deletions(-)
---
diff --git a/src/constants.cs b/src/constants.cs
index 5b92624..aa833c3 100644
--- a/src/constants.cs
+++ b/src/constants.cs
@@ -62,10 +62,14 @@ public class Constants
//sqlite tables
//instead of typing the tableName directly (that can crash if it's bad written and it's not detected by compiler)
//use the following consts, and if it's misspelled, compiler will know
- public const string PersonTable = "person";
+ public const string PersonTable = "person77";
+ public const string PersonSessionTable = "personSession77";
+
+ public const string PersonOldTable = "person";
+ public const string PersonSessionOldTable = "personSession"; //old table, used before db 0.53
+ public const string PersonSessionOldWeightTable = "personSessionWeight"; //old table, used from db 0.53 to 0.76
+
public const string SessionTable = "session";
- public const string PersonSessionTable = "personSession";
- public const string PersonSessionWeightTable = "personSessionWeight";
public const string PersonNotUploadTable = "personSessionNotUpload";
public const string SportTable = "sport";
public const string SpeciallityTable = "speciallity";
diff --git a/src/oldCodeNeedToDBConvert/person.cs b/src/oldCodeNeedToDBConvert/person.cs
index 4de4631..3227056 100644
--- a/src/oldCodeNeedToDBConvert/person.cs
+++ b/src/oldCodeNeedToDBConvert/person.cs
@@ -23,7 +23,7 @@ using System.Data;
using System.Text; //StringBuilder
using Mono.Unix;
-public partial class Person {
+public partial class PersonOld {
private int uniqueID;
private string name;
@@ -41,11 +41,11 @@ public partial class Person {
private int sessionID;
- public Person() {
+ public PersonOld() {
}
//suitable when we load a person from the database for being the current Person
- public Person(int uniqueID, string name, string sex, DateTime dateBorn,
+ public PersonOld(int uniqueID, string name, string sex, DateTime dateBorn,
double height, double weight, int sportID, int speciallityID, int practice, string description,
int race, int countryID, int serverUniqueID
)
@@ -70,7 +70,7 @@ public partial class Person {
}
//typical constructor
- public Person(string name, string sex, DateTime dateBorn,
+ public PersonOld(string name, string sex, DateTime dateBorn,
double height, double weight, int sportID, int speciallityID, int practice, string description,
int race, int countryID, int serverUniqueID,
int sessionID)
@@ -95,7 +95,7 @@ public partial class Person {
//insert in the person table
//when insert as person we don't know uniqueID
uniqueID = -1;
- int insertedID = this.InsertAtDB(false, Constants.PersonTable);
+ int insertedID = this.InsertAtDB(false, Constants.PersonOldTable);
//we need uniqueID for personSession
uniqueID = insertedID;
@@ -103,11 +103,11 @@ public partial class Person {
Log.WriteLine(this.ToString());
//insert in the personSession table (fast way of knowing who was in each session)
- SqlitePersonSession.Insert (false, Constants.PersonSessionWeightTable, "-1", uniqueID, sessionID, weight);
+ SqlitePersonSession.Insert (false, Constants.PersonSessionOldWeightTable, "-1", uniqueID, sessionID, weight);
}
//used to select a person at Sqlite.convertTables
- public Person(string [] myString)
+ public PersonOld(string [] myString)
{
this.uniqueID = Convert.ToInt32(myString[0]);
this.name = myString[1];
@@ -226,7 +226,7 @@ public partial class Person {
}
- ~Person() {}
+ ~PersonOld() {}
}
diff --git a/src/oldCodeNeedToDBConvert/personSession.cs b/src/oldCodeNeedToDBConvert/personSession.cs
index 88bdbba..923f829 100644
--- a/src/oldCodeNeedToDBConvert/personSession.cs
+++ b/src/oldCodeNeedToDBConvert/personSession.cs
@@ -23,7 +23,7 @@ using System.Data;
using System.Text; //StringBuilder
using Mono.Unix;
-public partial class PersonSession {
+public partial class PersonSessionOld {
private int uniqueID;
private int personID;
@@ -31,10 +31,10 @@ public partial class PersonSession {
private double weight;
- public PersonSession() {
+ public PersonSessionOld() {
}
- public PersonSession(int uniqueID, int personID, int sessionID, double weight)
+ public PersonSessionOld(int uniqueID, int personID, int sessionID, double weight)
{
this.uniqueID = uniqueID;
this.personID = personID;
@@ -43,7 +43,7 @@ public partial class PersonSession {
}
//typical constructor
- public PersonSession(int personID, int sessionID, double weight)
+ public PersonSessionOld(int personID, int sessionID, double weight)
{
this.personID = personID;
this.sessionID = sessionID;
@@ -53,7 +53,7 @@ public partial class PersonSession {
//insert in the personSession table
//when insert as personSession we don't know uniqueID
uniqueID = -1;
- int insertedID = this.InsertAtDB(false, Constants.PersonSessionWeightTable);
+ int insertedID = this.InsertAtDB(false, Constants.PersonSessionOldWeightTable);
//we need uniqueID for personSession
uniqueID = insertedID;
@@ -62,7 +62,7 @@ public partial class PersonSession {
}
//used to select a personSession at Sqlite.convertTables
- public PersonSession(string [] myString)
+ public PersonSessionOld(string [] myString)
{
this.uniqueID = Convert.ToInt32(myString[0]);
this.personID = Convert.ToInt32(myString[1]);
@@ -84,7 +84,7 @@ public partial class PersonSession {
//return "[uniqueID: " + uniqueID + "]" + name + ", " + ", " + sex + ", " + dateBorn.ToShortDateString() + ", " + description;
}
- ~PersonSession() {}
+ ~PersonSessionOld() {}
}
diff --git a/src/oldCodeNeedToDBConvert/sqlite/person.cs b/src/oldCodeNeedToDBConvert/sqlite/person.cs
index 1f05e92..7ecca71 100644
--- a/src/oldCodeNeedToDBConvert/sqlite/person.cs
+++ b/src/oldCodeNeedToDBConvert/sqlite/person.cs
@@ -25,14 +25,14 @@ using System.Collections; //ArrayList
using Mono.Data.Sqlite;
-class SqlitePerson : Sqlite
+class SqlitePersonOld : Sqlite
{
- public SqlitePerson() {
+ public SqlitePersonOld() {
}
- ~SqlitePerson() {}
+ ~SqlitePersonOld() {}
- //can be "Constants.PersonTable" or "Constants.ConvertTempTable"
+ //can be "Constants.PersonOldTable" or "Constants.ConvertTempTable"
//temp is used to modify table between different database versions if needed
//protected new internal static void createTable(string tableName)
protected override void createTable(string tableName)
@@ -55,7 +55,7 @@ class SqlitePerson : Sqlite
dbcmd.ExecuteNonQuery();
}
- //can be "Constants.PersonTable" or "Constants.ConvertTempTable"
+ //can be "Constants.PersonOldTable" or "Constants.ConvertTempTable"
//temp is used to modify table between different database versions if needed
public static int Insert(bool dbconOpened, string tableName, string uniqueID, string name, string sex, DateTime dateBorn, double height, double weight, int sportID, int speciallityID, int practice, string description, int race, int countryID, int serverUniqueID)
{
@@ -82,506 +82,17 @@ class SqlitePerson : Sqlite
return myReturn;
}
-
- public static string SelectJumperName(int uniqueID)
- {
- dbcon.Open();
-
- dbcmd.CommandText = "SELECT name FROM " + Constants.PersonTable + " WHERE uniqueID == " + uniqueID;
-
- Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- string myReturn = "";
- if(reader.Read()) {
- myReturn = reader[0].ToString();
- }
- dbcon.Close();
- return myReturn;
- }
-
- //currently only used on server
- public static ArrayList SelectAllPersons()
- {
- dbcon.Open();
- dbcmd.CommandText = "SELECT * FROM person";
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- ArrayList myArray = new ArrayList(1);
-
- while(reader.Read())
- myArray.Add ("(" + reader[0].ToString() + ") " + reader[1].ToString());
-
- reader.Close();
- dbcon.Close();
-
- return myArray;
- }
-
- public static string[] SelectAllPersonsRecuperable(string sortedBy, int except, int inSession, string searchFilterName)
- {
- //sortedBy = name or uniqueID (= creation date)
-
-
- //1st select all the person.uniqueID of people who are in CurrentSession (or none if except == -1)
- //2n select all names in database (or in one session if inSession != -1)
- //3d filter all names (save all found in 2 that is not in 1)
- //
- //probably this can be made in only one time... future
- //
- //1
-
- dbcon.Open();
- dbcmd.CommandText = "SELECT person.uniqueID " +
- " FROM person, personSessionWeight " +
- " WHERE personSessionWeight.sessionID == " + except +
- " AND person.uniqueID == personSessionWeight.personID ";
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- ArrayList myArray = new ArrayList(2);
-
- int count = new int();
- count = 0;
-
- while(reader.Read()) {
- myArray.Add (reader[0].ToString());
- count ++;
- }
-
- reader.Close();
- dbcon.Close();
-
- //2
- //sort no case sensitive when we sort by name
- if(sortedBy == "name") {
- sortedBy = "lower(person.name)" ;
- } else {
- sortedBy = "person.uniqueID" ;
- }
-
- dbcon.Open();
- if(inSession == -1) {
- string nameLike = "";
- if(searchFilterName != "")
- nameLike = "LOWER(person.name) LIKE LOWER ('%" + searchFilterName + "%') AND ";
-
- dbcmd.CommandText =
- "SELECT person.*, personSessionWeight.weight, sport.Name, speciallity.Name " +
- " FROM person, personSessionWeight, sport, speciallity " +
- " WHERE " + nameLike + " person.UniqueID == personSessionWeight.personID " +
- " AND person.sportID == sport.UniqueID AND person.speciallityID == speciallity.UniqueID " +
- " GROUP BY person.uniqueID" +
- " ORDER BY " + sortedBy;
- } else {
- dbcmd.CommandText =
- "SELECT person.*, personSessionWeight.weight, sport.Name, speciallity.Name " +
- " FROM person, personSessionWeight, sport, speciallity " +
- " WHERE personSessionWeight.sessionID == " + inSession +
- " AND person.uniqueID == personSessionWeight.personID " +
- " AND person.sportID == sport.UniqueID AND person.speciallityID == speciallity.UniqueID " +
- " ORDER BY " + sortedBy;
- }
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- SqliteDataReader reader2;
- reader2 = dbcmd.ExecuteReader();
-
- ArrayList myArray2 = new ArrayList(2);
-
- int count2 = new int();
- count2 = 0;
- bool found;
-
- //3
- while(reader2.Read()) {
- found = false;
- foreach (string line in myArray) {
- if(line == reader2[0].ToString()) {
- found = true;
- goto finishForeach;
- }
- }
-
-finishForeach:
-
- if (!found) {
- myArray2.Add (reader2[0].ToString() + ":" + reader2[1].ToString() + ":" +
- reader2[2].ToString() + ":" + UtilDate.FromSql(reader2[3].ToString()).ToShortDateString() + ":" +
- reader2[4].ToString() + ":" + //height
- reader2[13].ToString() + ":" + //weight (from personSessionWeight)
- reader2[14].ToString() + ":" + //sportName
- reader2[15].ToString() + ":" + //speciallityName
- Util.FindLevelName(Convert.ToInt32(reader2[8])) + ":" + //levelName
- reader2[9].ToString() //description
- );
- //add race, countryID, serverUniqueID
- count2 ++;
- }
- }
-
- reader2.Close();
- dbcon.Close();
-
- string [] myPersons = new string[count2];
- count2 = 0;
- foreach (string line in myArray2) {
- myPersons [count2++] = line;
- }
-
- return myPersons;
- }
-
- public static ArrayList SelectAllPersonEvents(int personID)
- {
- SqliteDataReader reader;
- ArrayList arraySessions = new ArrayList(2);
- ArrayList arrayJumps = new ArrayList(2);
- ArrayList arrayJumpsRj = new ArrayList(2);
- ArrayList arrayRuns = new ArrayList(2);
- ArrayList arrayRunsInterval = new ArrayList(2);
- ArrayList arrayRTs = new ArrayList(2);
- ArrayList arrayPulses = new ArrayList(2);
- ArrayList arrayMCs = new ArrayList(2);
-
- dbcon.Open();
-
- //session where this person is loaded
- dbcmd.CommandText = "SELECT sessionID, session.Name, session.Place, session.Date " +
- " FROM personSessionWeight, session " +
- " WHERE personID = " + personID + " AND session.uniqueID == personSessionWeight.sessionID " +
- " ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arraySessions.Add ( reader[0].ToString() + ":" + reader[1].ToString() + ":" +
- reader[2].ToString() + ":" +
- UtilDate.FromSql(reader[3].ToString()).ToShortDateString()
- );
- }
- reader.Close();
-
-
- //jumps
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM jump WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayJumps.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
- //jumpsRj
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM jumpRj WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayJumpsRj.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
- //runs
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM run WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayRuns.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
- //runsInterval
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM runInterval WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayRunsInterval.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
- //reaction time
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM reactiontime WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayRTs.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
- //pulses
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM pulse WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayPulses.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
- //pulses
- dbcmd.CommandText = "SELECT sessionID, count(*) FROM multiChronopic WHERE personID = " + personID +
- " GROUP BY sessionID ORDER BY sessionID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- arrayMCs.Add ( reader[0].ToString() + ":" + reader[1].ToString() );
- }
- reader.Close();
-
-
- dbcon.Close();
-
-
- ArrayList arrayAll = new ArrayList(2);
- string tempJumps;
- string tempJumpsRj;
- string tempRuns;
- string tempRunsInterval;
- string tempRTs;
- string tempPulses;
- string tempMCs;
- bool found; //using found because a person can be loaded in a session
- //but whithout having done any event yet
-
- //foreach session where this jumper it's loaded, check which events has
- foreach (string mySession in arraySessions) {
- string [] myStrSession = mySession.Split(new char[] {':'});
- tempJumps = "";
- tempJumpsRj = "";
- tempRuns = "";
- tempRunsInterval = "";
- tempRTs = "";
- tempPulses = "";
- tempMCs = "";
- found = false;
-
- foreach (string myJumps in arrayJumps) {
- string [] myStr = myJumps.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempJumps = myStr[1];
- found = true;
- break;
- }
- }
-
- foreach (string myJumpsRj in arrayJumpsRj) {
- string [] myStr = myJumpsRj.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempJumpsRj = myStr[1];
- found = true;
- break;
- }
- }
-
- foreach (string myRuns in arrayRuns) {
- string [] myStr = myRuns.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempRuns = myStr[1];
- found = true;
- break;
- }
- }
-
- foreach (string myRunsInterval in arrayRunsInterval) {
- string [] myStr = myRunsInterval.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempRunsInterval = myStr[1];
- found = true;
- break;
- }
- }
-
- foreach (string myRTs in arrayRTs) {
- string [] myStr = myRTs.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempRTs = myStr[1];
- found = true;
- break;
- }
- }
-
- foreach (string myPulses in arrayPulses) {
- string [] myStr = myPulses.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempPulses = myStr[1];
- found = true;
- break;
- }
- }
-
- foreach (string myMCs in arrayMCs) {
- string [] myStr = myMCs.Split(new char[] {':'});
- if(myStrSession[0] == myStr[0]) {
- tempMCs = myStr[1];
- found = true;
- break;
- }
- }
-
-
-
- //if has events, write it's data
- if (found) {
- arrayAll.Add (myStrSession[1] + ":" + myStrSession[2] + ":" + //session name, place
- myStrSession[3] + ":" + tempJumps + ":" + //sessionDate, jumps
- tempJumpsRj + ":" + tempRuns + ":" + //jumpsRj, Runs
- tempRunsInterval + ":" + tempRTs + ":" + //runsInterval, Reaction times
- tempPulses + ":" + tempMCs); //pulses, MultiChronopic
- }
- }
-
- return arrayAll;
- }
-
- public static bool ExistsAndItsNotMe(int uniqueID, string personName)
- {
- dbcon.Open();
- dbcmd.CommandText = "SELECT uniqueID FROM " + Constants.PersonTable +
- " WHERE LOWER(person.name) == LOWER('" + personName + "')" +
- " AND uniqueID != " + uniqueID ;
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- bool exists = new bool();
- exists = false;
-
- if (reader.Read()) {
- exists = true;
- //Log.WriteLine("valor {0}", reader[0].ToString());
- }
- //Log.WriteLine("exists = {0}", exists.ToString());
-
- reader.Close();
- dbcon.Close();
- return exists;
- }
-
-
- public static void Update(Person myPerson)
- {
- dbcon.Open();
- dbcmd.CommandText = "UPDATE " + Constants.PersonTable +
- " SET name = '" + myPerson.Name +
- "', sex = '" + myPerson.Sex +
- "', dateborn = '" + UtilDate.ToSql(myPerson.DateBorn) +
- "', height = " + Util.ConvertToPoint(myPerson.Height) +
- ", weight = " + Util.ConvertToPoint(myPerson.Weight) +
- ", sportID = " + myPerson.SportID +
- ", speciallityID = " + myPerson.SpeciallityID +
- ", practice = " + myPerson.Practice +
- ", description = '" + myPerson.Description +
- "', race = " + myPerson.Race +
- ", countryID = " + myPerson.CountryID +
- ", serverUniqueID = " + myPerson.ServerUniqueID +
- " WHERE uniqueID == '" + myPerson.UniqueID + "'" ;
- Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
- dbcon.Close();
- }
-
-
/*
from SqlitePersonSessionWeight.DeletePersonFromSessionAndTests()
if person is not in other sessions, delete it from DB
*/
public static void Delete(int uniqueID)
{
- dbcmd.CommandText = "Delete FROM " + Constants.PersonTable +
+ dbcmd.CommandText = "Delete FROM " + Constants.PersonOldTable +
" WHERE uniqueID == " + uniqueID.ToString();
Log.WriteLine(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
}
-
- /*
- * don't do more like this, use Sqlite.convertTables()
- */
- //change DB from 0.53 to 0.54
- /*
- protected internal static void convertTableToSportRelated()
- {
- ArrayList myArray = new ArrayList(2);
-
- //1st create a temp table
- SqlitePerson sqlitePersonObject = new SqlitePerson();
- sqlitePersonObject.createTable(Constants.ConvertTempTable);
-
- //2nd copy all data from person table to temp table
- dbcmd.CommandText = "SELECT * " +
- "FROM " + Constants.PersonTable + " ORDER BY uniqueID";
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- Person myPerson = new Person(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), Convert.ToInt32(reader[4]), Convert.ToInt32(reader[5]),
- 1, //sport undefined
- -1, //speciallity undefined
- -1, //practice level undefined
- reader[6].ToString(), //desc
- Constants.RaceUndefinedID,
- Constants.CountryUndefinedID,
- Constants.ServerUndefinedID
- );
- myArray.Add(myPerson);
-
- }
- reader.Close();
-
- foreach (Person myPerson in myArray)
- Insert(true, Constants.ConvertTempTable,
- myPerson.Name, myPerson.Sex, myPerson.DateBorn,
- myPerson.Height, myPerson.Weight, myPerson.SportID, myPerson.SpeciallityID, myPerson.Practice, myPerson.Description,
- Constants.RaceUndefinedID,
- Constants.CountryUndefinedID,
- Constants.ServerUndefinedID
- );
-
- //3rd drop table persons
- Sqlite.dropTable(Constants.PersonTable);
-
- //4d create table persons (now with sport related stuff
- sqlitePersonObject.createTable(Constants.PersonTable);
-
- //5th insert data in persons (with sport related stuff)
- foreach (Person myPerson in myArray)
- Insert(true, Constants.PersonTable,
- myPerson.Name, myPerson.Sex, myPerson.DateBorn,
- myPerson.Height, myPerson.Weight, myPerson.SportID, myPerson.SpeciallityID, myPerson.Practice, myPerson.Description,
- Constants.RaceUndefinedID,
- Constants.CountryUndefinedID,
- Constants.ServerUndefinedID
- );
-
-
- //6th drop temp table
- Sqlite.dropTable(Constants.ConvertTempTable);
- }
- */
-
- /*
- private static void dropTable(string tableName) {
- dbcmd.CommandText = "DROP TABLE " + tableName;
- dbcmd.ExecuteNonQuery();
- }
- */
-
}
diff --git a/src/oldCodeNeedToDBConvert/sqlite/personSession.cs b/src/oldCodeNeedToDBConvert/sqlite/personSession.cs
index 44f1541..be926c5 100644
--- a/src/oldCodeNeedToDBConvert/sqlite/personSession.cs
+++ b/src/oldCodeNeedToDBConvert/sqlite/personSession.cs
@@ -26,12 +26,12 @@ using Mono.Data.Sqlite;
using Mono.Unix;
-class SqlitePersonSession : Sqlite
+class SqlitePersonSessionOld : Sqlite
{
- public SqlitePersonSession() {
+ public SqlitePersonSessionOld() {
}
- ~SqlitePersonSession() {}
+ ~SqlitePersonSessionOld() {}
protected override void createTable(string tableName)
{
@@ -62,319 +62,6 @@ class SqlitePersonSession : Sqlite
return myReturn;
}
- /* new in database 0.53 (weight can change in different sessions) */
- public static double SelectPersonWeight(int personID, int sessionID)
- {
- dbcon.Open();
-
- dbcmd.CommandText = "SELECT weight FROM " + Constants.PersonSessionWeightTable +
- " WHERE personID == " + personID +
- " AND sessionID == " + sessionID;
-
- //Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- double myReturn = 0;
- if(reader.Read()) {
- myReturn = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[0].ToString()));
- }
- reader.Close();
- dbcon.Close();
- return myReturn;
- }
-
- /* when a session is not know, then select last weight */
- /* new in database 0.53 (weight can change in different sessions) */
- public static double SelectPersonWeight(int personID)
- {
- dbcon.Open();
-
- dbcmd.CommandText = "SELECT weight, sessionID FROM " + Constants.PersonSessionWeightTable +
- " WHERE personID == " + personID +
- "ORDER BY sessionID DESC LIMIT 1";
-
- Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- double myReturn = 0;
- if(reader.Read()) {
- myReturn = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[0].ToString()));
- }
- reader.Close();
- dbcon.Close();
- return myReturn;
- }
-
- public static void UpdateWeight(int personID, int sessionID, double weight)
- {
- dbcon.Open();
- dbcmd.CommandText = "UPDATE " + Constants.PersonSessionWeightTable +
- " SET weight = " + Util.ConvertToPoint(weight) +
- " WHERE personID = " + personID +
- " AND sessionID = " + sessionID
- ;
- Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
- dbcon.Close();
- }
-
- public static bool PersonSelectExistsInSession(int myPersonID, int mySessionID)
- {
- dbcon.Open();
- dbcmd.CommandText = "SELECT * FROM " + Constants.PersonSessionWeightTable +
- " WHERE personID == " + myPersonID +
- " AND sessionID == " + mySessionID ;
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- bool exists = new bool();
- exists = false;
-
- while(reader.Read())
- exists = true;
-
- reader.Close();
- dbcon.Close();
- return exists;
- }
-
- public static Person PersonSelect(int uniqueID, int sessionID)
- {
- dbcon.Open();
- dbcmd.CommandText = "SELECT person.name, person.sex, person.dateborn, person.height, " +
- "personSessionWeight.weight, person.sportID, person.speciallityID, person.practice, person.description, " +
- "person.race, person.countryID, person.serverUniqueID " +
- " FROM person, personSessionWeight WHERE person.uniqueID == " + uniqueID +
- " AND personSessionWeight.sessionID == " + sessionID +
- " AND person.uniqueID == personSessionWeight.personID";
- Log.WriteLine(dbcmd.CommandText.ToString());
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- string [] values = new string[12];
-
- while(reader.Read()) {
- values[0] = reader[0].ToString();
- values[1] = reader[1].ToString();
- values[2] = reader[2].ToString();
- values[3] = reader[3].ToString();
- values[4] = reader[4].ToString();
- values[5] = reader[5].ToString();
- values[6] = reader[6].ToString();
- values[7] = reader[7].ToString();
- values[8] = reader[8].ToString();
- values[9] = reader[9].ToString();
- values[10] = reader[10].ToString();
- values[11] = reader[11].ToString();
- }
-
- Person myPerson = new Person(uniqueID, values[0],
- values[1], UtilDate.FromSql(values[2]),
- Convert.ToDouble(Util.ChangeDecimalSeparator(values[3])), //height
- Convert.ToDouble(Util.ChangeDecimalSeparator(values[4])), //weight
- Convert.ToInt32(values[5]), Convert.ToInt32(values[6]), Convert.ToInt32(values[7]),
- values[8], //desc
- Convert.ToInt32(values[9]), Convert.ToInt32(values[10]), Convert.ToInt32(values[11])
- );
-
- reader.Close();
- dbcon.Close();
- return myPerson;
- }
-
- //the difference between this select and others, is that this returns and ArrayList of Persons
- //this is better than return the strings that can produce bugs in the future
- //use this in the future:
- public static ArrayList SelectCurrentSessionPersons(int sessionID)
- {
- dbcon.Open();
- dbcmd.CommandText = "SELECT person.*, personSessionWeight.weight " +
- " FROM person, personSessionWeight " +
- " WHERE personSessionWeight.sessionID == " + sessionID +
- " AND person.uniqueID == personSessionWeight.personID " +
- " ORDER BY upper(person.name)";
- Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- ArrayList myArray = new ArrayList(1);
- while(reader.Read()) {
- Person person = new Person(
- Convert.ToInt32(reader[0].ToString()), //uniqueID
- reader[1].ToString(), //name
- reader[2].ToString(), //sex
- UtilDate.FromSql(reader[3].ToString()), //dateBorn
- Convert.ToDouble(Util.ChangeDecimalSeparator(reader[4].ToString())), //height
- Convert.ToDouble(Util.ChangeDecimalSeparator(reader[13].ToString())), //weight (personSessionWeight)
- Convert.ToInt32(reader[6].ToString()), //sportID
- Convert.ToInt32(reader[7].ToString()), //speciallityID
- Convert.ToInt32(reader[8].ToString()), //practice
- reader[9].ToString(), //description
- Convert.ToInt32(reader[10].ToString()), //race
- Convert.ToInt32(reader[11].ToString()), //countryID
- Convert.ToInt32(reader[12].ToString()) //serverUniqueID
- );
- myArray.Add (person);
- }
- reader.Close();
- dbcon.Close();
- return myArray;
- }
-
- public static string[] SelectCurrentSession(int sessionID, bool onlyIDAndName, bool reverse)
- {
- dbcon.Open();
- dbcmd.CommandText = "SELECT person.*, personSessionWeight.weight, sport.name, speciallity.name " +
- "FROM person, personSessionWeight, sport, speciallity " +
- " WHERE personSessionWeight.sessionID == " + sessionID +
- " AND person.uniqueID == personSessionWeight.personID " +
- " AND person.sportID == sport.uniqueID " +
- " AND person.speciallityID == speciallity.uniqueID " +
- " ORDER BY upper(person.name)";
- Log.WriteLine(dbcmd.CommandText.ToString());
- dbcmd.ExecuteNonQuery();
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- ArrayList myArray = new ArrayList(2);
-
- int count = new int();
- count = 0;
-
- while(reader.Read()) {
- if(onlyIDAndName)
- myArray.Add (reader[0].ToString() + ":" + reader[1].ToString() );
- else {
- string sportName = Catalog.GetString(reader[14].ToString());
-
- string speciallityName = ""; //to solve a gettext bug (probably because speciallity undefined name is "")
- if(reader[15].ToString() != "")
- speciallityName = Catalog.GetString(reader[15].ToString());
- string levelName = Catalog.GetString(Util.FindLevelName(Convert.ToInt32(reader[8])));
-
- myArray.Add (
- reader[0].ToString() + ":" + reader[1].ToString() + ":" + //id, name
- reader[2].ToString() + ":" + //sex
- UtilDate.FromSql(reader[3].ToString()).ToShortDateString() + ":" + //dateborn
- reader[4].ToString() + ":" + reader[13].ToString() + ":" + //height, weight (from personSessionWeight)
- sportName + ":" + speciallityName + ":" + levelName + ":" +
- reader[9].ToString() //desc
- );
- }
- count ++;
- }
-
- reader.Close();
- dbcon.Close();
-
- string [] myJumpers = new string[count];
-
- if(reverse) {
- //show the results in the combo_sujeto_actual in reversed order,
- //then when we create a new person, this is the active, and this is shown
- //correctly in the combo_sujeto_actual
- int count2 = count -1;
- foreach (string line in myArray) {
- myJumpers [count2--] = line;
- }
- } else {
- int count2 = 0;
- foreach (string line in myArray) {
- myJumpers [count2++] = line;
- }
- }
- return myJumpers;
- }
-
- public static void DeletePersonFromSessionAndTests(string sessionID, string personID)
- {
- dbcon.Open();
-
- //delete relations (existance) within persons and sessions in this session
- dbcmd.CommandText = "Delete FROM personSessionWeight WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
- dbcmd.ExecuteNonQuery();
-
- //if person is not in other sessions, delete it from DB
- if(! PersonExistsInPSW(Convert.ToInt32(personID)))
- SqlitePerson.Delete(Convert.ToInt32(personID));
-
- //delete normal jumps
- dbcmd.CommandText = "Delete FROM jump WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
-
- dbcmd.ExecuteNonQuery();
-
- //delete repetitive jumps
- dbcmd.CommandText = "Delete FROM jumpRj WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
- dbcmd.ExecuteNonQuery();
-
- //delete normal runs
- dbcmd.CommandText = "Delete FROM run WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
-
- dbcmd.ExecuteNonQuery();
-
- //delete intervallic runs
- dbcmd.CommandText = "Delete FROM runInterval WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
-
- dbcmd.ExecuteNonQuery();
-
- //delete reaction times
- dbcmd.CommandText = "Delete FROM reactionTime WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
-
- dbcmd.ExecuteNonQuery();
-
- //delete pulses
- dbcmd.CommandText = "Delete FROM pulse WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
-
- dbcmd.ExecuteNonQuery();
-
- //delete multiChronopic
- dbcmd.CommandText = "Delete FROM multiChronopic WHERE sessionID == " + sessionID +
- " AND personID == " + personID;
-
- dbcmd.ExecuteNonQuery();
-
-
- dbcon.Close();
- }
-
- public static bool PersonExistsInPSW(int personID)
- {
- dbcmd.CommandText = "SELECT * FROM " + Constants.PersonSessionWeightTable +
- " WHERE personID == " + personID;
- //Log.WriteLine(dbcmd.CommandText.ToString());
-
- SqliteDataReader reader;
- reader = dbcmd.ExecuteReader();
-
- bool exists = new bool();
- exists = false;
-
- if (reader.Read()) {
- exists = true;
- }
- //Log.WriteLine(string.Format("personID exists = {0}", exists.ToString()));
-
- reader.Close();
- return exists;
- }
/*
* conversion from database 0.52 to 0.53 (add weight into personSession)
@@ -382,12 +69,16 @@ class SqlitePersonSession : Sqlite
*/
protected internal static void moveOldTableToNewTable()
{
+ string tp = Constants.PersonOldTable;
+ string tps1 = Constants.PersonSessionOldTable;
+ string tps2 = Constants.PersonSessionOldWeightTable;
+
dbcon.Open();
- dbcmd.CommandText = "SELECT personSession.*, person.weight " +
- "FROM personSession, person " +
- "WHERE personSession.personID = person.UniqueID " +
- "ORDER BY sessionID, personID";
+ dbcmd.CommandText = "SELECT " + tps1 + ".*, " + tp + ".weight " +
+ " FROM " + tps1 + ", " + tp +
+ " WHERE " + tps1 + ".personID = " + tp + ".UniqueID " +
+ " ORDER BY sessionID, personID";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
@@ -398,7 +89,7 @@ class SqlitePersonSession : Sqlite
}
reader.Close();
- dropOldTable(Constants.PersonSessionTable);
+ dropOldTable(tps1);
dbcon.Close();
@@ -406,7 +97,7 @@ class SqlitePersonSession : Sqlite
string [] stringFull = line.Split(new char[] {':'});
Insert(
false,
- Constants.PersonSessionWeightTable,
+ tps2,
"-1",
Convert.ToInt32(stringFull[0]), //personID
Convert.ToInt32(stringFull[1]), //sessionID
diff --git a/src/sqlite/main.cs b/src/sqlite/main.cs
index d66d9f0..65513c6 100644
--- a/src/sqlite/main.cs
+++ b/src/sqlite/main.cs
@@ -470,7 +470,7 @@ class Sqlite
SqliteReactionTime sqliteReactionTimeObject = new SqliteReactionTime();
SqlitePulse sqlitePulseObject = new SqlitePulse();
SqliteMultiChronopic sqliteMultiChronopicObject = new SqliteMultiChronopic();
- SqlitePersonSession sqlitePersonSessionObject = new SqlitePersonSession();
+ SqlitePersonSessionOld sqlitePersonSessionOldObject = new SqlitePersonSessionOld();
if(currentVersion == "0.41") {
dbcon.Open();
@@ -620,11 +620,11 @@ class Sqlite
if(currentVersion == "0.52") {
dbcon.Open();
- sqlitePersonSessionObject.createTable ();
+ sqlitePersonSessionOldObject.createTable ();
dbcon.Close();
//this needs the dbCon closed
- SqlitePersonSession.moveOldTableToNewTable ();
+ SqlitePersonSessionOld.moveOldTableToNewTable ();
dbcon.Open();
SqlitePreferences.Update ("databaseVersion", "0.53", true);
@@ -642,7 +642,7 @@ class Sqlite
SqliteSpeciallity.createTable();
SqliteSpeciallity.initialize();
- //SqlitePerson.convertTableToSportRelated ();
+ //SqlitePersonOld.convertTableToSportRelated ();
needToConvertPersonToSport = true;
SqlitePreferences.Update ("databaseVersion", "0.54", true);
@@ -726,7 +726,7 @@ class Sqlite
arrayPersonRaceCountryServerID.Add(Constants.RaceUndefinedID.ToString());
arrayPersonRaceCountryServerID.Add(Constants.CountryUndefinedID.ToString());
arrayPersonRaceCountryServerID.Add(Constants.ServerUndefinedID.ToString());
- convertTables(new SqlitePerson(), Constants.PersonTable, columnsBefore, arrayPersonRaceCountryServerID, putDescriptionInMiddle);
+ convertTables(new SqlitePersonOld(), Constants.PersonOldTable, columnsBefore, arrayPersonRaceCountryServerID, putDescriptionInMiddle);
SqlitePreferences.Update ("databaseVersion", "0.57", true);
dbcon.Close();
@@ -980,10 +980,10 @@ class Sqlite
dbcon.Open();
- convertTables(new SqlitePerson(), Constants.PersonTable, 13, new ArrayList(), false);
+ convertTables(new SqlitePersonOld(), Constants.PersonOldTable, 13, new ArrayList(), false);
conversionRate++;
- convertTables(new SqlitePersonSession(), Constants.PersonSessionWeightTable, 4, new ArrayList(), false);
+ convertTables(new SqlitePersonSessionOld(), Constants.PersonSessionOldWeightTable, 4, new ArrayList(), false);
SqlitePreferences.Update ("databaseVersion", "0.75", true);
conversionRate++;
@@ -1349,7 +1349,7 @@ class Sqlite
//also used to convert to sqlite 0.73
protected internal static void deleteOrphanedPersons()
{
- dbcmd.CommandText = "SELECT uniqueID FROM " + Constants.PersonTable;
+ dbcmd.CommandText = "SELECT uniqueID FROM " + Constants.PersonOldTable;
Log.WriteLine(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
@@ -1363,8 +1363,8 @@ class Sqlite
foreach(int personID in myArray) {
//if person is not in other sessions, delete it from DB
- if(! SqlitePersonSession.PersonExistsInPSW(personID))
- SqlitePerson.Delete(personID);
+ if(! SqlitePersonSessionOld.PersonExistsInPSW(personID))
+ SqlitePersonOld.Delete(personID);
}
}
@@ -1463,8 +1463,8 @@ class Sqlite
myReaderStr[9] = desc;
}
- if(tableName == Constants.PersonTable) {
- Person myPerson = new Person(myReaderStr);
+ if(tableName == Constants.PersonOldTable) {
+ PersonOld myPerson = new PersonOld(myReaderStr);
myArray.Add(myPerson);
} else if(tableName == Constants.SessionTable) {
Session mySession = new Session(myReaderStr);
@@ -1472,8 +1472,8 @@ class Sqlite
} else if(tableName == Constants.RunIntervalTypeTable) {
RunType myType = new RunType(myReaderStr, true); //interval
myArray.Add(myType);
- } else if(tableName == Constants.PersonSessionWeightTable) {
- PersonSession myPS = new PersonSession(myReaderStr);
+ } else if(tableName == Constants.PersonSessionOldWeightTable) {
+ PersonSessionOld myPS = new PersonSessionOld(myReaderStr);
myArray.Add(myPS);
} else {
Event myEvent = new Event();
@@ -1506,9 +1506,9 @@ Console.WriteLine("1" + tableName);
conversionSubRateTotal = myArray.Count * 2;
- if(tableName == Constants.PersonTable) {
- foreach (Person myPerson in myArray) {
- myPerson.InsertAtDB(true, Constants.ConvertTempTable);
+ if(tableName == Constants.PersonOldTable) {
+ foreach (PersonOld myPerson in myArray) {
+ myPersonOld.InsertAtDB(true, Constants.ConvertTempTable);
conversionSubRate ++;
}
} else if(tableName == Constants.SessionTable) {
@@ -1521,8 +1521,8 @@ Console.WriteLine("1" + tableName);
type.InsertAtDB(true, Constants.ConvertTempTable, true); //last true is for interval
conversionSubRate ++;
}
- } else if(tableName == Constants.PersonSessionWeightTable) {
- foreach (PersonSession ps in myArray) {
+ } else if(tableName == Constants.PersonSessionOldWeightTable) {
+ foreach (PersonSessionOld ps in myArray) {
ps.InsertAtDB(true, Constants.ConvertTempTable);
conversionSubRate ++;
}
@@ -1545,8 +1545,8 @@ Console.WriteLine("3" + tableName);
Console.WriteLine("4" + tableName);
//5th insert data in desired table
- if(tableName == Constants.PersonTable) {
- foreach (Person myPerson in myArray) {
+ if(tableName == Constants.PersonOldTable) {
+ foreach (PersonOld myPerson in myArray) {
myPerson.InsertAtDB(true, tableName);
conversionSubRate ++;
}
@@ -1560,8 +1560,8 @@ Console.WriteLine("4" + tableName);
type.InsertAtDB(true, tableName, true); //last true is for interval
conversionSubRate ++;
}
- } else if(tableName == Constants.PersonSessionWeightTable) {
- foreach (PersonSession ps in myArray) {
+ } else if(tableName == Constants.PersonSessionOldWeightTable) {
+ foreach (PersonSessionOld ps in myArray) {
ps.InsertAtDB(true, tableName);
conversionSubRate ++;
}
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
