[chronojump] no more orphaned persons when a person is deleted from a session, if it doesn't exist in other sessi

[Xavier de Blas <xaviblas src gnome org>]

commit 6fdbfd5d3ceac81cb137e44a086d27fd9ac2e8d2
Author: Xavier de Blas <xaviblas gmail com>
Date:   Fri Dec 25 00:04:16 2009 +0100

    no more orphaned persons
    when a person is deleted from a session, if it doesn't exist in other sessions, now is completely deleted from db
    db: 0.73 (delete orphaned persons (they are in no sessions))

 chronojump_server/bin/chronojumpServer.dll |  Bin 274944 -> 275968 bytes
 src/sqlite/main.cs                         |   37 ++++++++++++++++++++++++++-
 src/sqlite/person.cs                       |   13 ++++++++-
 src/sqlite/personSession.cs                |   32 +++++++++++++++++++++++-
 4 files changed, 77 insertions(+), 5 deletions(-)
---
diff --git a/chronojump_server/bin/chronojumpServer.dll b/chronojump_server/bin/chronojumpServer.dll
index 3c4d29f..afd986b 100755
Binary files a/chronojump_server/bin/chronojumpServer.dll and b/chronojump_server/bin/chronojumpServer.dll differ
diff --git a/src/sqlite/main.cs b/src/sqlite/main.cs
index 269c052..6e369cc 100644
--- a/src/sqlite/main.cs
+++ b/src/sqlite/main.cs
@@ -72,7 +72,7 @@ class Sqlite
 	 * Important, change this if there's any update to database
 	 * Important2: if database version get numbers higher than 1, check if the comparisons with currentVersion works ok
 	 */
-	static string lastChronojumpDatabaseVersion = "0.72";
+	static string lastChronojumpDatabaseVersion = "0.73";
 
 	public Sqlite() {
 	}
@@ -96,7 +96,7 @@ class Sqlite
 		Console.ReadLine();		
 		*/
 
-Log.WriteLine("home is: " + home);
+		Log.WriteLine("home is: " + home);
 
 		bool defaultDBLocation = true;
 
@@ -948,6 +948,17 @@ Log.WriteLine("home is: " + home);
 				dbcon.Close();
 				currentVersion = "0.72";
 			}
+			if(currentVersion == "0.72") {
+				dbcon.Open();
+				
+				deleteOrphanedPersons();
+
+				SqlitePreferences.Update ("databaseVersion", "0.73", true); 
+				
+				Log.WriteLine("Converted DB to 0.73 (deleted orphaned persons (in person table but not in personSessionWeight table)"); 
+				dbcon.Close();
+				currentVersion = "0.73";
+			}
 
 
 		}
@@ -1073,6 +1084,7 @@ Log.WriteLine("home is: " + home);
 		SqliteCountry.initialize();
 		
 		//changes [from - to - desc]
+		//0.72 - 0.73 Converted DB to 0.73 (deleted orphaned persons (in person table but not in personSessionWeight table))
 		//0.71 - 0.72 dates to YYYY-MM-DD
 		//0.70 - 0.71 created personNotUploadTable on client
 		//0.69 - 0.70 added showPower to preferences
@@ -1271,6 +1283,27 @@ Log.WriteLine("home is: " + home);
 		conversionRate ++;
 	}
 
+	//to convert to sqlite 0.73
+	protected internal static void deleteOrphanedPersons()
+	{
+		dbcmd.CommandText = "SELECT uniqueID FROM " + Constants.PersonTable;
+		Log.WriteLine(dbcmd.CommandText.ToString());
+		dbcmd.ExecuteNonQuery();
+		
+		SqliteDataReader reader;
+		reader = dbcmd.ExecuteReader();
+		ArrayList myArray = new ArrayList(1);
+
+		while(reader.Read())
+			myArray.Add (Convert.ToInt32(reader[0]));
+		reader.Close();
+
+		foreach(int personID in myArray) {
+			//if person is not in other sessions, delete it from DB
+			if(! SqlitePersonSession.PersonExistsInPSW(personID))
+				SqlitePerson.Delete(personID);
+		}
+	}
 
 	protected internal static void convertTables(Sqlite sqliteObject, string tableName, int columnsBefore, ArrayList columnsToAdd, bool putDescriptionInMiddle) 
 	{
diff --git a/src/sqlite/person.cs b/src/sqlite/person.cs
index 7fd1887..b87b8e1 100644
--- a/src/sqlite/person.cs
+++ b/src/sqlite/person.cs
@@ -473,11 +473,20 @@ finishForeach:
 		dbcon.Close();
 	}
 
-	
-	public static void Delete()
+
+	/* 
+	   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 +
+			" WHERE uniqueID == " + uniqueID.ToString();
+		Log.WriteLine(dbcmd.CommandText.ToString());
+		dbcmd.ExecuteNonQuery();
 	}
 
+
 	/* 
 	 * don't do more like this, use Sqlite.convertTables()
 	 */
diff --git a/src/sqlite/personSession.cs b/src/sqlite/personSession.cs
index a975e78..3454b6f 100644
--- a/src/sqlite/personSession.cs
+++ b/src/sqlite/personSession.cs
@@ -70,6 +70,7 @@ class SqlitePersonSession : Sqlite
 		if(reader.Read()) {
 			myReturn = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[0].ToString()));
 		}
+		reader.Close();
 		dbcon.Close();
 		return myReturn;
 	}
@@ -94,6 +95,7 @@ class SqlitePersonSession : Sqlite
 		if(reader.Read()) {
 			myReturn = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[0].ToString()));
 		}
+		reader.Close();
 		dbcon.Close();
 		return myReturn;
 	}
@@ -131,6 +133,7 @@ class SqlitePersonSession : Sqlite
 		}
 		//Log.WriteLine("exists = {0}", exists.ToString());
 
+		reader.Close();
 		dbcon.Close();
 		return exists;
 	}
@@ -152,6 +155,7 @@ class SqlitePersonSession : Sqlite
 		while(reader.Read()) 
 			exists = true;
 
+		reader.Close();
 		dbcon.Close();
 		return exists;
 	}
@@ -194,6 +198,7 @@ class SqlitePersonSession : Sqlite
 			Convert.ToInt32(values[9]), Convert.ToInt32(values[10]), Convert.ToInt32(values[11])
 			); 
 		
+		reader.Close();
 		dbcon.Close();
 		return myPerson;
 	}
@@ -312,7 +317,11 @@ class SqlitePersonSession : Sqlite
 		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;
@@ -352,6 +361,27 @@ class SqlitePersonSession : Sqlite
 		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)
 	 * now weight of a person can change every session