converted initialation and update of table at start

1 files changed, 117 insertions, 89 deletions

diff --git a/src/base/database.cpp b/src/base/database.cpp
index ae097be4..7fc2c825 100644
--- a/src/base/database.cpp
+++ b/src/base/database.cpp
@@ -132,102 +132,130 @@ namespace usdx
 		LOG4CXX_DEBUG(log, "Initializing Database: " << filename);
 
 		instance = new StatDatabase(filename);
-		// TODO
-		//   // open database
-		//   ScoreDB   := TSQLiteDatabase.Create(Filename.ToUTF8);
-		//   fFilename := Filename;
-
-		//   Version := GetVersion();
-
-		//   // add Table cUS_Statistics_Info
-		//   // needed in the conversion from 1.01 to 1.1
-		//   if not ScoreDB.TableExists(cUS_Statistics_Info) then
-		//   begin
-		//     Log.LogInfo('Outdated song database found - missing table"' + cUS_Statistics_Info + '"', 'TDataBaseSystem.Init');
-		//     ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS [' + cUS_Statistics_Info + '] (' +
-		//                     '[ResetTime] INTEGER' +
-		//                     ');');
-		//     // insert creation timestamp
-		//     ScoreDB.ExecSQL(Format('INSERT INTO [' + cUS_Statistics_Info + '] ' +
-		//                            '([ResetTime]) VALUES(%d);',
-		//                            [DateTimeToUnix(Now())]));
-		//   end;
 
-		//   // convert data from 1.01 to 1.1
-		//   // part #1 - prearrangement
-		//   finalizeConversion := false;
-		//   if (Version = 0) AND ScoreDB.TableExists('US_Scores') then
-		//   begin
-		//     // rename old tables - to be able to insert new table structures
-		//     ScoreDB.ExecSQL('ALTER TABLE US_Scores RENAME TO us_scores_101;');
-		//     ScoreDB.ExecSQL('ALTER TABLE US_Songs RENAME TO us_songs_101;');
-		//     finalizeConversion := true; // means: conversion has to be done!
-		//   end;
+		try {
+			if (! instance->sqlite_table_exists(usdx_statistics_info)) {
+				// add table usdx_statistics_info, needed in the conversion from 1.01 to 1.1
+				LOG4CXX_INFO(log, "Outdated song database found - missing table'" << usdx_statistics_info << "'");
 
-		//   // Set version number after creation
-		//   if (Version = 0) then
-		//     SetVersion(cDBVersion);
-
-		//   // SQLite does not handle VARCHAR(n) or INT(n) as expected.
-		//   // Texts do not have a restricted length, no matter which type is used,
-		//   // so use the native TEXT type. INT(n) is always INTEGER.
-		//   // In addition, SQLiteTable3 will fail if other types than the native SQLite
-		//   // types are used (especially FieldAsInteger). Also take care to write the
-		//   // types in upper-case letters although SQLite does not care about this -
-		//   // SQLiteTable3 is very sensitive in this regard.
-		//   ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS [' + cUS_Scores + '] (' +
-		//                     '[SongID] INTEGER NOT NULL, ' +
-		//                     '[Difficulty] INTEGER NOT NULL, ' +
-		//                     '[Player] TEXT NOT NULL, ' +
-		//                     '[Score] INTEGER NOT NULL, ' +
-		//                     '[Date] INTEGER NULL' +
-		//                   ');');
-
-		//   ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS [' + cUS_Songs + '] (' +
-		//                     '[ID] INTEGER PRIMARY KEY, ' +
-		//                     '[Artist] TEXT NOT NULL, ' +
-		//                     '[Title] TEXT NOT NULL, ' +
-		//                     '[TimesPlayed] INTEGER NOT NULL, ' +
-		//                     '[Rating] INTEGER NULL' +
-		//                   ');');
-
-		//   // convert data from 1.01 to 1.1
-		//   // part #2 - accomplishment
-		//   if finalizeConversion then
-		//   begin
-		//     Log.LogInfo('Outdated song database  found - begin conversion from V1.01 to V1.1', 'TDataBaseSystem.Init');
-		//     // insert old values into new db-schemes (/tables)
-		//     ScoreDB.ExecSQL('INSERT INTO ' + cUS_Scores + ' SELECT  SongID, Difficulty, Player, Score FROM us_scores_101;');
-		//     ScoreDB.ExecSQL('INSERT INTO ' + cUS_Songs + ' SELECT  ID, Artist, Title, TimesPlayed, ''NULL'' FROM us_songs_101;');
-		//     //now drop old tables
-		//     ScoreDB.ExecSQL('DROP TABLE us_scores_101;');
-		//     ScoreDB.ExecSQL('DROP TABLE us_songs_101;');
-		//   end;
+				std::string sqlStatement;
 
-		//   // add column rating to cUS_Songs
-		//   // just for users of nightly builds and developers!
-		//   if not ScoreDB.ContainsColumn(cUS_Songs, 'Rating') then
-		//   begin
-		//     Log.LogInfo('Outdated song database found - adding column rating to "' + cUS_Songs + '"', 'TDataBaseSystem.Init');
-		//     ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Songs + ' ADD COLUMN [Rating] INTEGER NULL');
-		//   end;
+				sqlStatement = "CREATE TABLE IF NOT EXISTS [";
+				sqlStatement += usdx_statistics_info;
+				sqlStatement += "] ([ResetTime] Integer);";
 
+				instance->sqlite_exec(sqlStatement);
 
-		//   //add column date to cUS-Scores
-		//   if not ScoreDB.ContainsColumn(cUS_Scores, 'Date') then
-		//   begin
-		//     Log.LogInfo('adding column date to "' + cUS_Scores + '"', 'TDataBaseSystem.Init');
-		//     ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Scores + ' ADD COLUMN [Date] INTEGER NULL');
-		//   end;
+				// insert creation timestamp
+				sqlStatement = "INSERT INTO [";
+				sqlStatement += usdx_statistics_info;
+				sqlStatement += "] ([ResetTime]) VALUES (?1);";
 
-		// except
-		//   on E: Exception do
-		//   begin
-		//     Log.LogError(E.Message, 'TDataBaseSystem.Init');
-		//     FreeAndNil(ScoreDB);
-		//   end;
-		// end;
+				sqlite3_stmt *sqliteStatement = instance->sqlite_prepare(sqlStatement);
+				sqlite3_bind_int(sqliteStatement, 1, time(NULL));
+				sqlite3_step(sqliteStatement);
+				sqlite3_finalize(sqliteStatement);
+			}
+
+			int version = instance->get_version();
+
+			bool finalizeConversion = false;
+			if (version == 0 && instance->sqlite_table_exists("US_Scores")) {
+				// convert data from 1.01 to 1.1
+				// part #1 - prearrangement
+				// rename old tables - to be able to insert new table structures
+				instance->sqlite_exec("ALTER TABLE US_Scores RENAME TO us_scores_101;");
+				instance->sqlite_exec("ALTER TABLE US_Songs RENAME TO us_songs_101;");
+			}
 
+			if (version == 0) {
+				// Set version number after creation
+				instance->set_version(db_version);
+			}
+
+			// SQLite does not handle VARCHAR(n) or INT(n) as expected.
+			// Texts do not have a restricted length, no matter which type is used,
+			// so use the native TEXT type. INT(n) is always INTEGER.
+			// In addition, SQLiteTable3 will fail if other types than the native SQLite
+			// types are used (especially FieldAsInteger). Also take care to write the
+			// types in upper-case letters although SQLite does not care about this -
+			// SQLiteTable3 is very sensitive in this regard.
+			std::string sqlStatement;
+
+			sqlStatement = "CREATE TABLE IF NOT EXISTS [";
+			sqlStatement += usdx_scores;
+			sqlStatement += "] (";
+			sqlStatement += "[SongID] INTEGER NOT NULL, ";
+			sqlStatement += "[Difficulty] INTEGER NOT NULL, ";
+			sqlStatement += "[Player] TEXT NOT NULL, ";
+			sqlStatement += "[Score] INTEGER NOT NULL, ";
+			sqlStatement += "[Date] INTEGER NULL";
+			sqlStatement += ");";
+
+			instance->sqlite_exec(sqlStatement);
+
+			sqlStatement = "CREATE TABLE IF NOT EXISTS [";
+			sqlStatement += usdx_songs;
+			sqlStatement += "] (";
+			sqlStatement += "[ID] INTEGER PRIMARY KEY, ";
+			sqlStatement += "[Artist] TEXT NOT NULL, ";
+			sqlStatement += "[Title] TEXT NOT NULL, ";
+			sqlStatement += "[TimesPlayed] INTEGER NOT NULL, ";
+			sqlStatement += "[Rating] INTEGER NULL";
+			sqlStatement += ");";
+
+			instance->sqlite_exec(sqlStatement);
+
+			if (finalizeConversion) {
+				// convert data from 1.01 to 1.1
+				// part #2 - accomplishment
+				LOG4CXX_INFO(log, "Outdated song database  found - begin conversion from V1.01 to V1.1");
+
+				// insert old values into new db-schemes (/tables)
+			 	sqlStatement = "INSERT INTO [";
+				sqlStatement += usdx_scores;
+				sqlStatement += "] SELECT  [SongID], [Difficulty], [Player], [Score] FROM [us_scores_101];";
+				instance->sqlite_exec(sqlStatement);
+
+				sqlStatement = "INSERT INTO [";
+				sqlStatement += usdx_songs;
+				sqlStatement += "]  SELECT  [ID], [Artist], [Title], [TimesPlayed], NULL FROM [us_songs_101];";
+				instance->sqlite_exec(sqlStatement);
+
+				// now drop old tables
+				instance->sqlite_exec("DROP TABLE us_scores_101;");
+				instance->sqlite_exec("DROP TABLE us_songs_101;");
+			}
+
+			// add column rating to cUS_Songs
+			// just for users of nightly builds and developers!
+			if (! instance->sqlite_table_contains_column(usdx_songs, "Rating")) {
+				LOG4CXX_INFO(log, "Outdated song database found - adding column rating to '" <<  usdx_songs << "'");
+
+			 	sqlStatement = "ALTER TABLE [";
+				sqlStatement += usdx_songs;
+				sqlStatement += "] ADD COLUMN [Rating] INTEGER NULL;";
+				instance->sqlite_exec(sqlStatement);
+			}
+
+			//add column date to cUS-Scores
+			if (! instance->sqlite_table_contains_column(usdx_scores, "Date")) {
+				LOG4CXX_INFO(log, "Outdated score database found - adding column date to '" <<  usdx_scores << "'");
+
+			 	sqlStatement = "ALTER TABLE [";
+				sqlStatement += usdx_scores;
+				sqlStatement += "] ADD COLUMN [Date] INTEGER NULL;";
+				instance->sqlite_exec(sqlStatement);
+			}
+		}
+		catch (...) {
+			// TODO:
+			// catch (Execption e) {
+			// LOG4CXX_ERROR(log, e.message);
+
+		 	delete instance;
+			// throw e;
+		}
 	}
 
 	int StatDatabase::get_version(void)