some modifications on the DataBaseLoading

- now ALL databases in table "us_songs" gets the column "Rating"
- nicer Structure and some useful comments

-> should work now with all DB-loading-combinations!

git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@1851 b956fd51-792f-4845-bead-9b4dfca2ff2c

2 files changed, 49 insertions, 9 deletions

diff --git a/src/base/UDataBase.pas b/src/base/UDataBase.pas
index ae9f2532..227db653 100644
--- a/src/base/UDataBase.pas
+++ b/src/base/UDataBase.pas
@@ -119,6 +119,11 @@ uses
   SysUtils,
   ULog;
 
+{
+ cDBVersion - history
+ 0 = USDX 1.01 or no Database
+ 01 = USDX 1.1
+}
 const
   cDBVersion = 01; // 0.1
   cUS_Scores = 'us_scores';
@@ -146,16 +151,11 @@ begin
 
     Version := GetVersion();
 
-    finalizeConvertion := false;
+    //Adds Table cUS_Statistics_Info
+    //Happens from Convertion 1.01 -> 1.1
     if not ScoreDB.TableExists(cUS_Statistics_Info) then
     begin
-      Log.LogInfo('Outdated song-database file found', 'TDataBaseSystem.Init');
-      Version := 0;
-      //following just works with convertion of usdx1.01 to usdx1.1!
-      //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;');
-
+      Log.LogInfo('Outdated song-database file found - Missing Table"'+cUS_Statistics_Info+'"', 'TDataBaseSystem.Init');
       ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS ['+cUS_Statistics_Info+'] (' +
                       '[ResetTime] INTEGER' +
                       ');');
@@ -163,6 +163,16 @@ begin
       ScoreDB.ExecSQL(Format('INSERT INTO ['+cUS_Statistics_Info+'] ' +
                              '([ResetTime]) VALUES(%d);',
                              [DateTimeToUnix(Now())]));
+    end;
+
+    //Converts data of 1.01 -> 1.1
+    //Part #1 - prearrangement
+    finalizeConvertion := 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;');
       finalizeConvertion := true; //means: convertion has to be done!
     end;
 
@@ -177,7 +187,6 @@ begin
     // 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, ' +
@@ -193,8 +202,11 @@ begin
                       '[Rating] INTEGER NULL' + 
                     ');');
 
+    //Converts data of 1.01 -> 1.1
+    //Part #2 - accomplishment
     if finalizeConvertion then
     begin
+      Log.LogInfo('Outdated song-database file found - Began Converting from V1.01 to V1.1', 'TDataBaseSystem.Init');
       //insert old values in 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;');
@@ -203,6 +215,13 @@ begin
       ScoreDB.ExecSQL('DROP TABLE us_songs_101;');
     end;
 
+    //Adds Column Rating to cUS_Songs
+    //Just for the users of Nightly-Builds and all Developers!
+    if not ScoreDB.ContainsColumn(cUS_Songs, 'Rating') then
+    begin
+      Log.LogInfo('Outdated song-database file found - Adding Column Rating to "'+cUS_Songs+'"', 'TDataBaseSystem.Init');
+      ScoreDB.ExecSQL('ALTER TABLE '+cUS_Songs+' ADD COLUMN Rating INTEGER NULL');
+    end;
 
   except
     on E: Exception do
diff --git a/src/lib/SQLite/SQLiteTable3.pas b/src/lib/SQLite/SQLiteTable3.pas
index 7df76363..3aed54a4 100644
--- a/src/lib/SQLite/SQLiteTable3.pas
+++ b/src/lib/SQLite/SQLiteTable3.pas
@@ -139,6 +139,7 @@ type
     procedure Commit;
     procedure Rollback;
     function TableExists(TableName: string): boolean;
+    function ContainsColumn(Table: String; Column: String) : boolean;
     function GetLastInsertRowID: int64;
     function GetLastChangedRows: int64;
     procedure SetTimeout(Value: integer);
@@ -759,6 +760,26 @@ begin
   end;
 end;
 
+function TSQLiteDatabase.ContainsColumn(Table: String; Column: String) : boolean;
+var
+  sql: string;
+  ds: TSqliteTable;
+  i : integer;
+begin
+  sql := 'PRAGMA TABLE_INFO('+Table+');';
+  ds := self.GetTable(sql);
+  try
+    Result := false;
+    while (ds.Next() and not Result and not ds.EOF)  do
+    begin
+      if ds.FieldAsString(1) = Column then
+        Result := true;
+    end;
+  finally
+    ds.Free;
+  end;
+end;
+
 procedure TSQLiteDatabase.SetTimeout(Value: integer);
 begin
   SQLite3_BusyTimeout(self.fDB, Value);