main.cs 93.7 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
/*
 * 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
 *
18
 * Copyright (C) 2004-2015   Xavier de Blas <xaviblas@gmail.com> 
19 20 21 22
 */

using System;
using System.Data;
23
using System.IO; //"File" things. TextWriter
24
using System.Collections; //ArrayList
25
using Mono.Data.Sqlite;
26
using System.Diagnostics; 	//for launching other process
27

Xavier de Blas's avatar
Xavier de Blas committed
28 29
using Mono.Unix;

30 31 32 33 34

class Sqlite
{
	protected static SqliteConnection dbcon;
	protected static SqliteCommand dbcmd;
35 36 37 38 39

	//since we use installJammer (chronojump 0.7)	
	//database was on c:\.chronojump\ or in ~/.chronojump
	//now it's on installed dir, eg linux: ~/Chronojump/database
	public static string home = Util.GetDatabaseDir();
40
	public static string sqlFile = home + Path.DirectorySeparatorChar + "chronojump.db";
Xavier de Blas's avatar
Xavier de Blas committed
41 42 43
	
	public static string temp = Util.GetDatabaseTempDir();
	public static string sqlFileTemp = temp + Path.DirectorySeparatorChar + "chronojump.db";
44 45 46 47

	//before installJammer
	public static string homeOld = Util.GetOldDatabaseDir();
	public static string sqlFileOld = homeOld + Path.DirectorySeparatorChar + "chronojump.db";
Xavier de Blas's avatar
Xavier de Blas committed
48
	
49 50 51
	//http://www.mono-project.com/SQLite

	static string connectionString = "version = 3; Data source = " + sqlFile;
Xavier de Blas's avatar
Xavier de Blas committed
52 53 54 55
	static string connectionStringTemp = "version = 3; Data source = " + sqlFileTemp;

	//test to try to open db in a dir with accents (latin)
	//static string connectionString = "globalization requestEncoding=\"iso-8859-1\"; responseEncoding=\"iso-8859-1\"; fileEncoding=\"iso-8859-1\"; culture=\"es-ES\";version = 3; Data source = " + sqlFile;
56 57
	
	//create blank database
58
	static bool creatingBlankDatabase = false;
59

Xavier de Blas's avatar
Xavier de Blas committed
60
	
61

62 63 64 65 66 67
	//for db creation
	static int creationRate;
	static int creationTotal;

	//for db conversion
	static string currentVersion = "0";
68
	
69 70
	static int conversionRate;
	static int conversionRateTotal;
71 72
	protected static int conversionSubRate;
	protected static int conversionSubRateTotal;
73

74 75 76
	/*
	 * Important, change this if there's any update to database
	 */
77
	static string lastChronojumpDatabaseVersion = "1.24";
78 79 80 81 82 83 84

	public Sqlite() {
	}

	protected virtual void createTable(string tableName) {
	}
	
85 86 87 88
	//used by personSessionWeight
	protected virtual void createTable() {
	}
	
89
	~Sqlite() {}
90

91 92 93 94
	//these two methods are used by methods who want to leave the connection opened
	//because lots of similar transactions have to be done
	public static void Open()
	{
95
		try {
Xavier de Blas's avatar
Xavier de Blas committed
96
			LogB.SQLon();
97 98
			dbcon.Open();
		} catch {
99
			LogB.SQL("-- catched --");
100

Xavier de Blas's avatar
Xavier de Blas committed
101
			LogB.SQLoff();
102 103
			dbcon.Close();
			
Xavier de Blas's avatar
Xavier de Blas committed
104
			LogB.SQLon();
105 106
			dbcon.Open();
			
107
			LogB.SQL("-- end of catched --");
108
		}
109 110 111
	}
	public static void Close()
	{
Xavier de Blas's avatar
Xavier de Blas committed
112
		LogB.SQLoff();
113 114
		dbcon.Close();
	}
115

Xavier de Blas's avatar
Xavier de Blas committed
116
	public static bool Connect()
117
	{
Xavier de Blas's avatar
Xavier de Blas committed
118 119 120 121 122 123
		/*
	       splashMessage = "pre";
		needUpdateSplashMessage = true;
		Console.ReadLine();		
		*/

124
		LogB.SQL("home is: " + home);
125

Xavier de Blas's avatar
Xavier de Blas committed
126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
		bool defaultDBLocation = true;

		dbcon = new SqliteConnection();

		/*
		 * the Open() helps to know it threre are problems with path and sqlite
		 * passing utf-8 or looking for invalid chars is not enough
		 * but, as Open creates a file (if it doesn't exist)
		 * we prefer to create a test file (test.db) instead of chronojump.db
		 */
		string sqlFileTest = home + Path.DirectorySeparatorChar + "test.db";
		string sqlFileTestTemp = temp + Path.DirectorySeparatorChar + "test.db";
		string connectionStringTest = "version = 3; Data source = " + sqlFileTest;
		string connectionStringTestTemp = "version = 3; Data source = " + sqlFileTestTemp;


		dbcon.ConnectionString = connectionStringTest;
		dbcmd = dbcon.CreateCommand();

		try {
			dbcon.Open();
		} catch {
			dbcon.Close();
			dbcon.ConnectionString = connectionStringTestTemp;
			dbcmd = dbcon.CreateCommand();
			dbcon.Open();
			defaultDBLocation = false;
		}
		dbcon.Close();
		
		
		if(defaultDBLocation) {
			dbcon.ConnectionString = connectionString;
			if (File.Exists(sqlFileTest)){
				File.Delete(sqlFileTest);
			}
		} else {
			dbcon.ConnectionString = connectionStringTemp;
			if (File.Exists(sqlFileTestTemp)){
				File.Delete(sqlFileTestTemp);
			}
		}
		dbcmd = dbcon.CreateCommand();
Xavier de Blas's avatar
Xavier de Blas committed
169

Xavier de Blas's avatar
Xavier de Blas committed
170
		/*
171
		LogB.SQL(string.Format("press3"));
Xavier de Blas's avatar
Xavier de Blas committed
172 173 174 175 176 177
	       	splashMessage = "post1";
		needUpdateSplashMessage = true;
		Console.ReadLine();		
		*/

		/*
Xavier de Blas's avatar
Xavier de Blas committed
178
		try{
179
			LogB.SQL(string.Format("Trying database in ... " + connectionString));
Xavier de Blas's avatar
Xavier de Blas committed
180

181
		//dbcon = new SqliteConnection();
Xavier de Blas's avatar
Xavier de Blas committed
182 183
			*/
		/*
Xavier de Blas's avatar
Xavier de Blas committed
184
			dbcon.ConnectionString = connectionString;
Xavier de Blas's avatar
Xavier de Blas committed
185
			//dbcon.ConnectionString = connectionStringTemp;
Xavier de Blas's avatar
Xavier de Blas committed
186 187
			dbcmd = dbcon.CreateCommand();
		} catch {
Xavier de Blas's avatar
Xavier de Blas committed
188
			try {
189
				LogB.SQL(string.Format("Trying database in ... " + connectionStringTemp));
Xavier de Blas's avatar
Xavier de Blas committed
190

191
		//dbcon = new SqliteConnection();
Xavier de Blas's avatar
Xavier de Blas committed
192 193 194
				dbcon.ConnectionString = connectionStringTemp;
				dbcmd = dbcon.CreateCommand();
			} catch { 
195
				LogB.SQL("Problems, exiting...\n");
Xavier de Blas's avatar
Xavier de Blas committed
196 197 198 199 200 201
				System.Console.Out.Close();
				Log.End();
				Log.Delete();
				Environment.Exit(1);
			}

Xavier de Blas's avatar
Xavier de Blas committed
202
		}
Xavier de Blas's avatar
Xavier de Blas committed
203 204 205 206 207

		*/
			
		return defaultDBLocation;
		
208 209
	}

210 211 212
	//only create blank DB
	public static void ConnectBlank()
	{
Xavier de Blas's avatar
Xavier de Blas committed
213 214 215
		string sqlFileBlank = "chronojump_blank.db"; //copied on /chronojump-x.y/data installjammer will copy it to database
		string connectionStringBlank = "version = 3; Data source = " + sqlFileBlank;

216 217
		//delete blank file if exists
		if (File.Exists(sqlFileBlank)) {
218
			LogB.SQL("File blank exists, deleting...");
219 220 221 222 223 224
			File.Delete(sqlFileBlank);
		}

		dbcon = new SqliteConnection();
		dbcon.ConnectionString = connectionStringBlank;
		dbcmd = dbcon.CreateCommand();
225 226 227 228 229 230 231 232

		/*
		 * blankDB is created on Linux (create_release.sh) but
		 * it will work on windows.
		 * this bool allows to put COM? as default port
		 */

		creatingBlankDatabase = true; 
233
	}
234
	
235
	public static void CreateDir()
236
	{
237
		LogB.SQL(connectionString);
238

239
		string applicationDataDir = UtilAll.GetApplicationDataDir();
240 241

		if(!Directory.Exists(applicationDataDir)) {
242
			LogB.SQL("creating dir 1...");
243 244
			Directory.CreateDirectory (applicationDataDir);
		}
Xavier de Blas's avatar
Xavier de Blas committed
245 246
		
		if(!Directory.Exists(home)) {
247
			LogB.SQL("creating dir 2...");
Xavier de Blas's avatar
Xavier de Blas committed
248 249
			Directory.CreateDirectory (home);
		}
250
		LogB.SQL("Dirs created.");
251 252 253 254
	}

	public static void CreateFile()
	{
255 256
		LogB.SQL("creating file...");
		LogB.SQL(connectionString);
257
		
258 259 260
		//	if(!Directory.Exists(home)) {
		//		Directory.CreateDirectory (home);
		//	}
Xavier de Blas's avatar
Xavier de Blas committed
261

262 263 264 265 266 267 268 269 270 271
		//try {	
		dbcon.Open();
		/*
		   } catch {
		   dbcon.Close();
		   dbcon.ConnectionString = connectionStringTemp;
		   dbcmd = dbcon.CreateCommand();
		   dbcon.Open();
		   }
		   */
272 273
		dbcon.Close();
	}
274
	
275

Xavier de Blas's avatar
Xavier de Blas committed
276
	public static bool CheckTables(bool defaultDBLocation)
277
	{
Xavier de Blas's avatar
Xavier de Blas committed
278 279 280 281 282 283 284 285 286
		if(defaultDBLocation) {
			if (File.Exists(sqlFile)){
				return true;
			}
		} else {
			if (File.Exists(sqlFile)){
				//backup the database
				Util.BackupDirCreateIfNeeded();
				Util.BackupDatabase();
287
				LogB.SQL ("made a database backup"); //not compressed yet, it seems System.IO.Compression.DeflateStream and
Xavier de Blas's avatar
Xavier de Blas committed
288
				//System.IO.Compression.GZipStream are not in mono
Xavier de Blas's avatar
Xavier de Blas committed
289

Xavier de Blas's avatar
Xavier de Blas committed
290 291 292 293 294 295
				File.Move(Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump.db",
					Util.GetDatabaseTempDir() + Path.DirectorySeparatorChar + "chronojump.db");
				return true;
			}
		}
		return false;
296
	}
297

298

299 300
	public static bool IsSqlite3() {
		if(sqlite3SelectWorks()){
301
			LogB.SQL("SQLITE3");
302
			Sqlite.Close();
303 304 305
			return true;
		}
		else if(sqlite2SelectWorks()) {
306
			LogB.SQL("SQLITE2");
307
			Sqlite.Close();
308 309 310 311 312 313 314
			//write sqlFile path on data/databasePath.txt
			//TODO
			//

			return false;
		}
		else {
315
			LogB.SQL("ERROR in sqlite detection");
316
			Sqlite.Close();
317 318 319 320 321 322 323
			return false;
		}
	}
	private static bool sqlite3SelectWorks() {
		try {
			SqlitePreferences.Select("chronopicPort");
		} catch {
Xavier de Blas's avatar
Xavier de Blas committed
324 325
			/*
			try {
326
				Sqlite.Close();
Xavier de Blas's avatar
Xavier de Blas committed
327 328 329 330 331 332
				if(File.Exists(Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump.db"))
					File.Move(Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump.db",
							Util.GetDatabaseTempDir() + Path.DirectorySeparatorChar + "chronojump.db");

				dbcon.ConnectionString = connectionStringTemp;
				dbcmd = dbcon.CreateCommand();
333
				Sqlite.Open();
Xavier de Blas's avatar
Xavier de Blas committed
334 335
				SqlitePreferences.Select("chronopicPort");
			} catch {
336
				Sqlite.Close();
Xavier de Blas's avatar
Xavier de Blas committed
337 338 339 340 341 342 343
				if(File.Exists(Util.GetDatabaseTempDir() + Path.DirectorySeparatorChar + "chronojump.db"))
					File.Move(Util.GetDatabaseTempDir() + Path.DirectorySeparatorChar + "chronojump.db",
							Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump.db");

			*/
				return false;
			//}
344 345 346 347 348 349 350 351 352
		}
		return true;
	}
	private static bool sqlite2SelectWorks() {
		/*
		 *it says:
		 Unhandled Exception: System.NotSupportedException: Only Sqlite Version 3 is supported at this time
		   at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000]
		 *
353
		Sqlite.Close();
354 355
		connectionString = "version=2; URI=file:" + sqlFile;
		dbcon.ConnectionString = connectionString;
356
		Sqlite.Open();
357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373
		try {
			SqlitePreferences.Select("chronopicPort");
		} catch {
			return false;
		}
		*/
		return true;
	}


	public static bool ConvertFromSqlite2To3() {
		/*
		 * 1 write the sqlite2 dumped data to an archive
		 * 2 copy db
		 * 3 create sqlite3 file from archive
		 */

374 375
		string sqlite2File = Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump-sqlite2.81.db";
		string sqliteDB = Util.GetDatabaseDir() + Path.DirectorySeparatorChar + "chronojump.db";
376

377
		File.Copy(sqliteDB, sqlite2File, true);
378 379 380

		string myPath = "";
		string sqliteStr = "";
381
		//string sqlite3Str = "";
382 383
		string extension = "";
		try {
384
			if(UtilAll.IsWindows()) {
385 386 387
				myPath = Constants.UtilProgramsWindows;
				extension = Constants.ExtensionProgramsWindows;
				sqliteStr = "sqlite.exe";
388
				//sqlite3Str = "sqlite3.exe";
389 390 391 392 393
			}
			else {
				myPath = Constants.UtilProgramsLinux;
				extension = Constants.ExtensionProgramsLinux;
				sqliteStr = "sqlite-2.8.17.bin";
394
				//sqlite3Str = "sqlite3-3.5.0.bin";
395 396 397
			}

			if(File.Exists(myPath + Path.DirectorySeparatorChar + sqliteStr)) 
398
				LogB.SQL("exists1");
399
			if(File.Exists(sqlite2File)) 
400
				LogB.SQL("exists2");
401

402
			/*
403
			LogB.SQL("{0}-{1}", myPath + Path.DirectorySeparatorChar + sqliteStr , sqlite2File + " .dump");
404 405 406 407 408 409 410
			ProcessStartInfo ps = new ProcessStartInfo(myPath + Path.DirectorySeparatorChar + sqliteStr , sqlite2File + " .dump");

			ps.UseShellExecute = false;
			//ps.UseShellExecute = true;
			ps.RedirectStandardOutput = true;
			string output = "";
			using(Process p = Process.Start(ps)) {
411
				//TODO: this doesn't work on windows (it gets hanged)
412 413 414 415
				p.WaitForExit();
				output = p.StandardOutput.ReadToEnd();
			}
*/
416 417 418
			
			//write the path to chronojumpdb in a txt file (for convert_database.bat and .sh)
			TextWriter writer = File.CreateText(myPath + Path.DirectorySeparatorChar + "db_path.txt");
Xavier de Blas's avatar
Xavier de Blas committed
419 420
			string scriptsAreTwoDirsAhead = ".." + Path.DirectorySeparatorChar + ".." + Path.DirectorySeparatorChar;
			writer.WriteLine(scriptsAreTwoDirsAhead + Util.GetDatabaseDir());
421 422
			((IDisposable)writer).Dispose();
			
423
			LogB.SQL("Path written");
424 425 426 427

			Process p2 = Process.Start(myPath + Path.DirectorySeparatorChar + "convert_database." + extension);
			p2.WaitForExit();

428
			LogB.SQL("sqlite3 db created");
429 430 431
				
			File.Copy(myPath + Path.DirectorySeparatorChar + "tmp.db", sqliteDB, true ); //overwrite
		} catch {
432
			LogB.Error("PROBLEMS");
433 434 435
			return false;
		}

436
		LogB.SQL("done");
437 438 439 440
		return true;

	}

441 442
	//for splashWin text
	public static string PrintConversionText() {
443
		double toReach = Convert.ToDouble(Util.ChangeDecimalSeparator(lastChronojumpDatabaseVersion));
444 445 446 447 448
		return currentVersion + "/" + toReach.ToString() + " " +
			conversionRate.ToString() + "/" + conversionRateTotal.ToString() + " " +
			conversionSubRate.ToString() + "/" + conversionSubRateTotal.ToString() + " ";
	}

449 450
	//for splashWin progressbars
	public static double PrintCreation() {
451
		return Util.DivideSafeFraction(creationRate, creationTotal);
452 453
	}
	public static double PrintConversionVersion() {
454 455 456 457
		return Util.DivideSafeFraction(
				Convert.ToDouble(Util.ChangeDecimalSeparator(currentVersion)), 
				Convert.ToDouble(Util.ChangeDecimalSeparator(lastChronojumpDatabaseVersion))
				);
458 459
	}
	public static double PrintConversionRate() {
460
		return Util.DivideSafeFraction(conversionRate, conversionRateTotal);
461 462
	}
	public static double PrintConversionSubRate() {
463
		return Util.DivideSafeFraction(conversionSubRate, conversionSubRateTotal);
464 465
	}

466
	public static bool ConvertToLastChronojumpDBVersion() {
467
		LogB.SQL("SelectChronojumpProfile ()");
468

469 470 471
		//if(checkIfIsSqlite2())
		//	convertSqlite2To3();

472
		addChronopicPortNameIfNotExists();
473

474
		currentVersion = SqlitePreferences.Select("databaseVersion");
475

476 477
		//LogB.SQL("lastDB: {0}", Convert.ToDouble(lastChronojumpDatabaseVersion));
		//LogB.SQL("currentVersion: {0}", Convert.ToDouble(currentVersion));
478

Xavier de Blas's avatar
0.8.9.4  
Xavier de Blas committed
479
		bool returnSoftwareIsNew = true; //-1 if software is too old for database (moved db to other computer)
480 481 482
		if(
				Convert.ToDouble(Util.ChangeDecimalSeparator(lastChronojumpDatabaseVersion)) == 
				Convert.ToDouble(Util.ChangeDecimalSeparator(currentVersion)))
483
			LogB.SQL("Database is already latest version");
484 485 486
		else if(
				Convert.ToDouble(Util.ChangeDecimalSeparator(lastChronojumpDatabaseVersion)) < 
				Convert.ToDouble(Util.ChangeDecimalSeparator(currentVersion))) {
487
			LogB.SQL("User database newer than program, need to update software");
488 489
			returnSoftwareIsNew = false;
		} else {
490 491
			LogB.Warning("Old database, need to convert");
			LogB.Warning("db version: " + currentVersion);
492

493
			bool needToConvertPersonToSport = false;
Xavier de Blas's avatar
Xavier de Blas committed
494
			bool jumpFallAsDouble = false;
495
	 		bool runAndRunIntervalInitialSpeedAdded = false;
Xavier de Blas's avatar
Xavier de Blas committed
496
			bool addedRSA = false;
497 498 499 500 501

			SqliteJumpRj sqliteJumpRjObject = new SqliteJumpRj();
			SqliteRunInterval sqliteRunIntervalObject = new SqliteRunInterval();
			SqliteReactionTime sqliteReactionTimeObject = new SqliteReactionTime();
			SqlitePulse sqlitePulseObject = new SqlitePulse();
502
			SqliteMultiChronopic sqliteMultiChronopicObject = new SqliteMultiChronopic();
503
			SqlitePersonSessionOld sqlitePersonSessionOldObject = new SqlitePersonSessionOld();
504 505

			if(currentVersion == "0.41") {
506
				Sqlite.Open();
507

508 509
				//SqlitePulse.createTable(Constants.PulseTable);
				sqlitePulseObject.createTable(Constants.PulseTable);
510 511 512
				SqlitePulseType.createTablePulseType();
				SqlitePulseType.initializeTablePulseType();

513
				SqlitePreferences.Update ("databaseVersion", "0.42", true); 
514
				LogB.SQL("Converted DB to 0.42 (added pulse and pulseType tables)");
515

516
				Sqlite.Close();
517
				currentVersion = "0.42";
518 519
			}

520
			if(currentVersion == "0.42") {
521
				Sqlite.Open();
522 523
				SqlitePulseType.Insert ("Free:-1:-1:free PulseStep mode", true); 
				SqlitePreferences.Insert ("language", "es-ES"); 
524
				SqlitePreferences.Update ("databaseVersion", "0.43", true); 
525
				LogB.SQL("Converted DB to 0.43 (added 'free' pulseType & language peference)");
526
				Sqlite.Close();
527
				currentVersion = "0.43";
528 529
			}

530
			if(currentVersion == "0.43") {
531
				Sqlite.Open();
532 533
				SqlitePreferences.Insert ("showQIndex", "False"); 
				SqlitePreferences.Insert ("showDjIndex", "False"); 
534
				SqlitePreferences.Update ("databaseVersion", "0.44", true); 
535
				LogB.SQL("Converted DB to 0.44 (added showQIndex, showDjIndex)");
536
				Sqlite.Close();
537
				currentVersion = "0.44";
538 539
			}

540
			if(currentVersion == "0.44") {
541
				Sqlite.Open();
542
				SqlitePreferences.Insert ("allowFinishRjAfterTime", "True"); 
543
				SqlitePreferences.Update ("databaseVersion", "0.45", true); 
544
				LogB.SQL("Converted DB to 0.45 (added allowFinishRjAfterTime)");
545
				Sqlite.Close();
546
				currentVersion = "0.45";
547 548
			}

549
			if(currentVersion == "0.45") {
550
				Sqlite.Open();
551
				SqliteJumpType.JumpTypeInsert ("Free:1:0:Free jump", true); 
552
				SqlitePreferences.Update ("databaseVersion", "0.46", true); 
553
				LogB.SQL("Added Free jump type");
554
				Sqlite.Close();
555
				currentVersion = "0.46";
556 557
			}

558
			if(currentVersion == "0.46") {
559
				Sqlite.Open();
560

561 562
				//SqliteReactionTime.createTable(Constants.ReactionTimeTable);
				sqliteReactionTimeObject.createTable(Constants.ReactionTimeTable);
563

564
				SqlitePreferences.Update ("databaseVersion", "0.47", true); 
565
				LogB.SQL("Added reaction time table");
566
				Sqlite.Close();
567
				currentVersion = "0.47";
568 569
			}

570
			if(currentVersion == "0.47") {
571
				Sqlite.Open();
572

573 574 575 576
				//SqliteJumpRj.createTable(Constants.TempJumpRjTable);
				sqliteJumpRjObject.createTable(Constants.TempJumpRjTable);
				//SqliteRun.intervalCreateTable(Constants.TempRunIntervalTable);
				sqliteRunIntervalObject.createTable(Constants.TempRunIntervalTable);
577

578
				SqlitePreferences.Update ("databaseVersion", "0.48", true); 
579
				LogB.SQL("created tempJumpReactive and tempRunInterval tables");
580
				Sqlite.Close();
581
				currentVersion = "0.48";
582 583
			}

584
			if(currentVersion == "0.48") {
585
				Sqlite.Open();
586 587 588

				SqliteJumpType.JumpTypeInsert ("Rocket:1:0:Rocket jump", true); 

589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604
				string [] iniRunTypes = {
					"Agility-20Yard:18.28:20Yard Agility test",
					"Agility-505:10:505 Agility test",
					"Agility-Illinois:60:Illinois Agility test",
					"Agility-Shuttle-Run:40:Shuttle Run Agility test",
					"Agility-ZigZag:17.6:ZigZag Agility test"
				};
				foreach(string myString in iniRunTypes) {
					string [] s = myString.Split(new char[] {':'});
					RunType type = new RunType();
					type.Name = s[0];
					type.Distance = Convert.ToDouble(s[1]);
					type.Description = s[2];
					SqliteRunType.Insert(type, Constants.RunTypeTable, true);
				}
	
605 606 607 608

				SqliteEvent.createGraphLinkTable();
				SqliteRunType.AddGraphLinksRunSimpleAgility();	

609
				SqlitePreferences.Update ("databaseVersion", "0.49", true); 
610
				LogB.SQL("Added graphLinkTable, added Rocket jump and 5 agility tests: (20Yard, 505, Illinois, Shuttle-Run & ZigZag. Added graphs pof the 5 agility tests)");
611

612
				Sqlite.Close();
613
				currentVersion = "0.49";
614 615
			}

616
			if(currentVersion == "0.49") {
617
				Sqlite.Open();
618 619 620 621 622 623
				SqliteJumpType.Update ("SJ+", "SJl"); 
				SqliteJumpType.Update ("CMJ+", "CJl"); 
				SqliteJumpType.Update ("ABK+", "ABKl"); 
				SqliteJump.ChangeWeightToL();
				SqliteJumpType.AddGraphLinks();	
				SqliteJumpType.AddGraphLinksRj();	
624
				SqlitePreferences.Update ("databaseVersion", "0.50", true); 
625
				LogB.SQL("changed SJ+ to SJl, same for CMJ+ and ABK+, added jump and jumpRj graph links");
626
				Sqlite.Close();
627
				currentVersion = "0.50";
628 629
			}

630
			if(currentVersion == "0.50") {
631
				Sqlite.Open();
632
				SqliteRunType.AddGraphLinksRunSimple();	
633
				SqliteRunIntervalType.AddGraphLinksRunInterval();	
634
				SqlitePreferences.Update ("databaseVersion", "0.51", true); 
635
				LogB.SQL("added graphLinks for run simple and interval");
636
				Sqlite.Close();
637
				currentVersion = "0.51";
638 639
			}

640
			if(currentVersion == "0.51") {
641
				Sqlite.Open();
642
				SqliteJumpType.Update ("CJl", "CMJl"); 
643 644
				SqliteEvent.GraphLinkInsert (Constants.JumpTable, "CMJl", "jump_cmj_l.png", true);
				SqliteEvent.GraphLinkInsert (Constants.JumpTable, "ABKl", "jump_abk_l.png", true);
645
				SqlitePreferences.Update ("databaseVersion", "0.52", true); 
646
				LogB.SQL("added graphLinks for cmj_l and abk_l, fixed CMJl name");
647
				Sqlite.Close();
648
				currentVersion = "0.52";
649
			}
650
			
651
			if(currentVersion == "0.52") {
652
				Sqlite.Open();
653
				sqlitePersonSessionOldObject.createTable (); 
654
				Sqlite.Close();
655 656
				
				//this needs the dbCon closed
657
				SqlitePersonSessionOld.moveOldTableToNewTable (); 
658
				
659
				Sqlite.Open();
660
				SqlitePreferences.Update ("databaseVersion", "0.53", true); 
661
				Sqlite.Close();
662
				
663
				LogB.SQL("created weightSession table. Moved person weight data to weightSession table for each session that has performed");
664
				currentVersion = "0.53";
665
			}
666
			
667
			if(currentVersion == "0.53") {
668
				Sqlite.Open();
669 670 671 672 673 674

				SqliteSport.createTable();
				SqliteSport.initialize();
				SqliteSpeciallity.createTable();
				SqliteSpeciallity.initialize();

675
				//SqlitePersonOld.convertTableToSportRelated (); 
676
				needToConvertPersonToSport = true;
677 678
				
				SqlitePreferences.Update ("databaseVersion", "0.54", true); 
679
				Sqlite.Close();
680
				
681
				LogB.SQL("Created sport tables. Added sport data, speciallity and level of practice to person table");
682
				currentVersion = "0.54";
683
			}
684
			if(currentVersion == "0.54") {
685
				Sqlite.Open();
686 687 688 689

				SqliteSpeciallity.InsertUndefined(true);

				SqlitePreferences.Update ("databaseVersion", "0.55", true); 
690
				Sqlite.Close();
691
				
692
				LogB.SQL("Added undefined to speciallity table");
693
				currentVersion = "0.55";
694
			}
695
			if(currentVersion == "0.55") {
696
				Sqlite.Open();
Xavier de Blas's avatar
Xavier de Blas committed
697

Xavier de Blas's avatar
Xavier de Blas committed
698
				SqliteSessionOld.convertTableAddingSportStuff();
Xavier de Blas's avatar
Xavier de Blas committed
699 700

				SqlitePreferences.Update ("databaseVersion", "0.56", true); 
701
				Sqlite.Close();
Xavier de Blas's avatar
Xavier de Blas committed
702
				
703
				LogB.SQL("Added session default sport stuff into session table");
704 705 706
				currentVersion = "0.56";
			}
			if(currentVersion == "0.56") {
707
				Sqlite.Open();
708

709
				//jump and jumpRj
710 711 712
				ArrayList arrayAngleAndSimulated = new ArrayList(1);
				arrayAngleAndSimulated.Add("-1"); //angle
				arrayAngleAndSimulated.Add("-1"); //simulated
713
				
714 715 716 717 718
				//run and runInterval
				ArrayList arraySimulatedAndInitialSpeed = new ArrayList(1);
				arraySimulatedAndInitialSpeed.Add("-1"); //simulated
				arraySimulatedAndInitialSpeed.Add("0"); //initial speed
				
719 720 721 722
				//others
				ArrayList arraySimulated = new ArrayList(1);
				arraySimulated.Add("-1"); //simulated

723

724
				conversionRateTotal = 9;
725
				conversionRate = 1;
726
				convertTables(new SqliteJump(), Constants.JumpTable, 9, arrayAngleAndSimulated, false);
727
				conversionRate ++;
728
				convertTables(new SqliteJumpRj(), Constants.JumpRjTable, 16, arrayAngleAndSimulated, false);
729
				conversionRate ++;
730
				convertTables(new SqliteRun(), Constants.RunTable, 7, arraySimulated, false);
731
				conversionRate ++;
732
				convertTables(new SqliteRunInterval(), Constants.RunIntervalTable, 11, arraySimulated, false);
733 734
				runAndRunIntervalInitialSpeedAdded = true;
				
735
				conversionRate ++;
736
				convertTables(new SqliteReactionTime(), Constants.ReactionTimeTable, 6, arraySimulated, false);
737
				conversionRate ++;
738
				convertTables(new SqlitePulse(), Constants.PulseTable, 8, arraySimulated, false);
739

740

741 742 743 744 745 746 747
				//reacreate temp tables for have also the simulated column
				conversionRate ++;
				Sqlite.dropTable(Constants.TempJumpRjTable);
				sqliteJumpRjObject.createTable(Constants.TempJumpRjTable);
				Sqlite.dropTable(Constants.TempRunIntervalTable);
				sqliteRunIntervalObject.createTable(Constants.TempRunIntervalTable);

748 749 750
				conversionRate ++;
				SqliteCountry.createTable();
				SqliteCountry.initialize();
751 752 753 754 755 756 757 758 759 760 761 762 763 764 765
				
				conversionRate ++;
				int columnsBefore = 10;
				bool putDescriptionInMiddle = false;
				ArrayList arrayPersonRaceCountryServerID = new ArrayList(1);
				if(needToConvertPersonToSport) {
					columnsBefore = 7;
					arrayPersonRaceCountryServerID.Add(Constants.SportUndefinedID.ToString());
					arrayPersonRaceCountryServerID.Add(Constants.SpeciallityUndefinedID.ToString());
					arrayPersonRaceCountryServerID.Add(Constants.LevelUndefinedID.ToString());
					putDescriptionInMiddle = true;
				}
				arrayPersonRaceCountryServerID.Add(Constants.RaceUndefinedID.ToString());
				arrayPersonRaceCountryServerID.Add(Constants.CountryUndefinedID.ToString());
				arrayPersonRaceCountryServerID.Add(Constants.ServerUndefinedID.ToString());
766
				convertTables(new SqlitePersonOld(), Constants.PersonOldTable, columnsBefore, arrayPersonRaceCountryServerID, putDescriptionInMiddle);
767

768
				SqlitePreferences.Update ("databaseVersion", "0.57", true); 
769
				Sqlite.Close();
770
				
771
				LogB.SQL("Added simulated column to each event table on client. Added to person: race, country, serverUniqueID. Convert to sport related done here if needed. Added also run and runInterval initial speed");
772
				currentVersion = "0.57";
Xavier de Blas's avatar
Xavier de Blas committed
773
			}
774
			if(currentVersion == "0.57") {
775
				Sqlite.Open();
776 777 778 779 780 781 782 783 784 785 786
		
				//check if "republic" is in country table
				if(SqliteCountry.TableHasOldRepublicStuff()){
					conversionRateTotal = 4;
					conversionRate = 1;
					Sqlite.dropTable(Constants.CountryTable);
					conversionRate ++;
					SqliteCountry.createTable();
					conversionRate ++;
					SqliteCountry.initialize();
					conversionRate ++;
787
					LogB.SQL("Countries without kingdom or republic (except when needed)");
788 789
				}
				
790
				SqlitePreferences.Update ("databaseVersion", "0.58", true); 
791
				Sqlite.Close();
792 793 794 795
				
				currentVersion = "0.58";
			}

796
			if(currentVersion == "0.58") {
797
				Sqlite.Open();
798 799 800 801 802
				conversionRateTotal = 2;
				conversionRate = 1;
				SqlitePreferences.Insert ("showAngle", "False"); 
				alterTableColumn(new SqliteJump(), Constants.JumpTable, 11);

Xavier de Blas's avatar
Xavier de Blas committed
803 804 805
				//jump fall is also converted to double (don't need to do at conversion to 0.76)
				jumpFallAsDouble = true;

806
				SqlitePreferences.Update ("databaseVersion", "0.59", true); 
807
				LogB.SQL("Converted DB to 0.59 (added 'showAngle' to preferences, changed angle on jump to double)"); 
808
				conversionRate = 2;
809
				Sqlite.Close();
810 811 812
				currentVersion = "0.59";
			}

813
			if(currentVersion == "0.59") {
814
				Sqlite.Open();
815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831
				conversionRateTotal = 4;

				conversionRate = 1;
				SqlitePreferences.Insert ("volumeOn", "True"); 
				SqlitePreferences.Insert ("evaluatorServerID", "-1");

				conversionRate = 2;
			
				int columnsBefore = 8;
				ArrayList arrayServerID = new ArrayList(1);
				arrayServerID.Add(Constants.ServerUndefinedID.ToString());
				convertTables(new SqliteSession(), Constants.SessionTable, columnsBefore, arrayServerID, false);
				
				conversionRate = 3;
				SqliteEvent.SimulatedConvertToNegative();

				SqlitePreferences.Update ("databaseVersion", "0.60", true); 
832
				LogB.SQL("Converted DB to 0.60 (added volumeOn and evaluatorServerID to preferences. session has now serverUniqueID. Simulated now are -1, because 0 is real and positive is serverUniqueID)"); 
833 834
				
				conversionRate = 4;
835
				Sqlite.Close();
836 837 838
				currentVersion = "0.60";
			}

839
			if(currentVersion == "0.60") {
840
				Sqlite.Open();
841
				conversionRateTotal = 3;
842 843
				conversionRate = 1;

844 845 846
				ArrayList arrayDS = new ArrayList(1);
				arrayDS.Add("-1"); //distancesString
				convertTables(new SqliteRunIntervalType(), Constants.RunIntervalTypeTable, 7, arrayDS, false);
847 848
				
				conversionRate = 2;
849 850 851 852 853 854 855 856 857 858 859 860 861

				//SqliteRunType.RunIntervalTypeInsert ("MTGUG:-1:true:3:false:Modified time Getup and Go test:1-7-19", true);
				RunType type = new RunType();
				type.Name = "MTGUG";
				type.Distance = -1;
				type.TracksLimited = true;
				type.FixedValue = 3;
				type.Unlimited = false;
				type.Description = "Modified time Getup and Go test";
				type.DistancesString = "1-7-19";
				SqliteRunIntervalType.Insert(type, Constants.RunIntervalTypeTable, true);
				
				SqlitePreferences.Update ("databaseVersion", "0.61", true); 
862
				LogB.SQL("Converted DB to 0.61 added RunIntervalType distancesString (now we van have interval tests with different distances of tracks). Added MTGUG");
863 864
				
				conversionRate = 3;
865
				Sqlite.Close();
866
				currentVersion = "0.61";
867
			}
868
			if(currentVersion == "0.61") {
869
				Sqlite.Open();
870 871
				SqliteJumpType.JumpRjTypeInsert ("RJ(hexagon):1:0:1:18:Reactive Jump on a hexagon until three full revolutions are done", true);
				SqlitePreferences.Update ("databaseVersion", "0.62", true); 
872
				LogB.SQL("Converted DB to 0.62 added hexagon");
873
				Sqlite.Close();
874 875
				currentVersion = "0.62";
			}
876
			if(currentVersion == "0.62") {
877
				Sqlite.Open();
878 879
				SqlitePreferences.Insert ("versionAvailable", "");
				SqlitePreferences.Update ("databaseVersion", "0.63", true); 
880