From 05b99652431e3747d0de8c3d4e7b601ad19ecadb Mon Sep 17 00:00:00 2001 From: Alexander Sulfrian Date: Sun, 10 Jan 2010 21:48:29 +0100 Subject: converted initialation and update of table at start --- src/base/database.cpp | 206 ++++++++++++++++++++++++++++---------------------- 1 file changed, 117 insertions(+), 89 deletions(-) (limited to 'src') 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) -- cgit v1.2.3