aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorAlexander Sulfrian <alexander@sulfrian.net>2010-01-10 21:48:29 +0100
committerAlexander Sulfrian <alexander@sulfrian.net>2013-01-05 17:17:44 +0100
commit05b99652431e3747d0de8c3d4e7b601ad19ecadb (patch)
treed487b1d14e37554bec47a96a03bab574c73a4acd
parent98bdc2079442b79780db04bb8d05a2c329577e10 (diff)
downloadusdx-05b99652431e3747d0de8c3d4e7b601ad19ecadb.tar.gz
usdx-05b99652431e3747d0de8c3d4e7b601ad19ecadb.tar.xz
usdx-05b99652431e3747d0de8c3d4e7b601ad19ecadb.zip
converted initialation and update of table at start
-rw-r--r--src/base/database.cpp206
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)