Challenge MOD r7 alpha based on Ultrastar Deluxe v1.0.1a

for changes read Changelog.txt in folder Game

git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/branches/1.0.1 Challenge MOD@2107 b956fd51-792f-4845-bead-9b4dfca2ff2c

1 files changed, 500 insertions, 0 deletions

diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas
new file mode 100644
index 00000000..580f7144
--- /dev/null
+++ b/Game/Code/Classes/UDataBase.pas
@@ -0,0 +1,500 @@
+unit UDataBase;

+

+interface

+

+uses USongs, SQLiteTable3;

+

+//--------------------

+//DataBaseSystem - Class including all DB Methods

+//--------------------

+type

+  THandicapResult = record

+    P1m: real;

+    P2m: real;

+  end;

+

+  TStatResult = record

+    Case Typ: Byte of

+      0: (Singer:     ShortString;    //best scores

+          Score:      Word;

+          Difficulty: Byte;

+          SongArtist: ShortString;

+          SongTitle:  ShortString;

+          Date:       ShortString);

+

+      1: (Player:     ShortString;    //best singers

+          AverageScore: Word;

+          SungTimes:    Word);

+

+      2: (Artist: ShortString;        //most sung song

+          Title:  ShortString;

+          TimesSung:  Word);

+

+      3: (ArtistName:   ShortString;  //most sung band

+          TimesSungtot: Word);

+  end;

+  AStatResult = Array of TStatResult;

+  

+  TDataBaseSystem = class

+    private

+      ScoreDB: TSqliteDatabase;

+      sFilename: string;

+    public

+

+

+    property Filename: String read sFilename;

+    

+    Destructor Free;

+

+    Procedure Init(const Filename: string);

+    procedure ReadScore(var Song: TSong);

+    procedure AddScore(var Song: TSong; Level: integer; Name: string; Score: integer; TimeStamp: integer);

+    procedure WriteScore(var Song: TSong);

+

+    Function  GetStats(var Stats: AStatResult; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean;

+    Function  GetTotalEntrys(const Typ: Byte): Cardinal;

+    function  FormatDate(time_stamp: integer): String;

+    function  GetHandicap(P1: string; P2: string): THandicapResult; //for Handicap-Mode

+    function  GetAspect(Artist, Title: string; def: integer): integer;

+    procedure SetAspect(Artist, Title: string; aspect: integer);

+  end;

+

+var

+  DataBase: TDataBaseSystem;

+

+implementation

+

+uses IniFiles, SysUtils, DateUtils, ULanguage;

+

+function  TDataBaseSystem.GetHandicap(P1: string; P2: string): THandicapResult;

+const

+  min = 3;

+var

+  P1m, P2m, temp: real;

+  P1c, P2c: integer;

+begin

+  if not Assigned(ScoreDB) then

+    Exit;

+

+  //init

+  Result.P1m := 1;

+  Result.P2m := 1;

+  P1m := 0;

+  P2m := 0;

+  P1c := 0;

+  P2c := 0;

+

+  try

+    P1c := ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` '+

+      'WHERE `Player` = "' + P1 + '";');

+    P2c := ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` '+

+      'WHERE `Player` = "' + P2 + '";');

+

+    if (P1c>min) and (P2c>min) then

+    begin

+      P1m := ScoreDB.GetTableFloat('SELECT AVG(`Score`) FROM `US_Scores` '+

+        'WHERE `Score` IN ('+

+        'SELECT Score FROM `US_Scores` '+

+        'WHERE `Player` = "' + P1 + '" ORDER BY `rowid` DESC LIMIT 10);');

+

+      P2m := ScoreDB.GetTableFloat('SELECT AVG(`Score`) FROM `US_Scores` '+

+        'WHERE `Score` IN ('+

+        'SELECT Score FROM `US_Scores` '+

+        'WHERE `Player` = "' + P2 + '" ORDER BY `rowid` DESC LIMIT 10);');

+    end;

+  except

+    P1m := 0;

+    P2m := 0;

+  end;

+

+  if (P1m>0) and (P2m>0) then

+  begin

+    if (P1m>P2m) then

+    begin

+      temp := (P2m/P1m) + (P2m/P1m)*0.2;

+      if temp<=1 then

+        Result.P1m := temp

+      else

+        Result.P1m := 1;

+    end else

+    begin

+      temp := (P1m/P2m) + (P1m/P2m)*0.2;

+      if temp<=1 then

+        Result.P2m := temp

+      else

+        Result.P2m := 1;

+    end;

+  end;

+end;

+

+function TDataBaseSystem.GetAspect(Artist, Title: string; def: integer): integer;

+var

+  ID: Integer;

+

+begin

+  try

+    ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' +

+      Artist + '" AND `Title` = "' + Title + '"');

+

+    if ID = 0 then //Song doesn't exist -> Create

+    begin

+      ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed`, `Aspect` ) '+

+              'VALUES (NULL , "' + Artist + '", "' + Title + '", "0", "'+ IntToStr(def) +'");');

+      Result := def;

+    end else

+    begin

+      Result := ScoreDB.GetTableValue('SELECT `Aspect` FROM `US_Songs` WHERE `ID` = "' +

+        IntToStr(ID) + '"');

+    end;

+  except

+    Result := def;

+  end;

+end;

+

+procedure TDataBaseSystem.SetAspect(Artist, Title: string; aspect: integer);

+var

+  ID: Integer;

+

+begin

+  try

+    ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' +

+      Artist + '" AND `Title` = "' + Title + '"');

+

+    if ID = 0 then //Song doesn't exist -> Create

+    begin

+      ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed`, `Aspect` ) '+

+              'VALUES (NULL , "' + Artist + '", "' + Title + '", "0", "'+ IntToStr(Aspect) +'");');

+    end else

+    begin

+

+      //Create new Entry

+      ScoreDB.ExecSQL ('UPDATE `Us_Songs` SET `Aspect` = "' + IntToStr(aspect) + '" WHERE `ID` = "' +

+        IntToStr(ID) + '"');

+    end;

+  except

+

+  end;

+end;

+

+(**

+ * Format a UNIX-Timestamp into DATE (If 0 then '')

+ *)

+function TDataBaseSystem.FormatDate(time_stamp: integer): String;

+var

+  Year, Month, Day: word;

+begin

+  Result:='';

+  try

+    if time_stamp<>0 then

+    begin

+      DecodeDate(UnixToDateTime(time_stamp), Year, Month, Day);

+      Result := Format(Language.Translate('STAT_FORMAT_DATE'), [Day, Month, Year]);

+    end;

+  except

+    on E: EConvertError do

+    //Log.LogError('Error Parsing FormatString "STAT_FORMAT_DATE": ' + E.Message); TODO

+  end;

+end;

+

+

+

+//--------------------

+//Create - Opens Database and Create Tables if not Exist

+//--------------------

+

+Procedure TDataBaseSystem.Init(const Filename: string);

+begin

+  //Open Database

+  ScoreDB := TSqliteDatabase.Create(Filename);

+  sFilename := Filename;

+

+  try

+    //Look for Tables => When not exist Create them

+    if not ScoreDB.TableExists('US_Scores') then

+      ScoreDB.execsql('CREATE TABLE `US_Scores` (`SongID` INT( 11 ) NOT NULL , '+

+        '`Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , '+

+        '`Score` INT( 5 ) NOT NULL , `Date` INT NULL)');

+

+    if not ScoreDB.TableExists('US_Songs') then

+      ScoreDB.execsql('CREATE TABLE `US_Songs` (`ID` INTEGER PRIMARY KEY, '+

+        '`Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );');

+

+    //add column date to cUS-Scores

+    if not ScoreDB.ContainsColumn('US_Scores', 'Date') then

+    begin

+      //Log.LogInfo('adding column date to "' + cUS_Scores + '"', 'TDataBaseSystem.Init'); TODO

+      ScoreDB.ExecSQL('ALTER TABLE ' + '`US_Scores`' + ' ADD COLUMN `Date` INT NULL');

+    end;

+

+    //add column aspect to cUS-Songs

+    if not ScoreDB.ContainsColumn('US_Songs', 'Aspect') then

+    begin

+      //0=acoStretch; 1=acoCrop; 2=acoLetterBox

+      ScoreDB.ExecSQL('ALTER TABLE ' + '`US_Songs`' + ' ADD COLUMN `Aspect` INT NULL');

+    end;

+  finally

+  //ScoreDB.Free;

+  end;

+

+end;

+

+//--------------------

+//Free - Frees Database

+//--------------------

+Destructor TDataBaseSystem.Free;

+begin

+  ScoreDB.Free;

+end;

+

+//--------------------

+//ReadScore - Read Scores into SongArray

+//--------------------

+procedure TDataBaseSystem.ReadScore(var Song: TSong);

+var

+  TableData: TSqliteTable;

+  Difficulty: Byte;

+  I: integer;

+  PlayerListed: boolean;

+  DateStr: string;

+

+begin

+  if not Assigned(ScoreDB) then

+    Exit;

+

+  try

+    //Search Song in DB

+    TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score`, `Date` '+

+      'FROM `us_scores` WHERE '+

+      '`SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' +

+      Song.Artist + '" AND `Title` = "' + Song.Title +

+      '" LIMIT 1) ORDER BY `Score` DESC;');

+    //Empty Old Scores

+    SetLength (Song.Score[0], 0); //easy

+    SetLength (Song.Score[1], 0); //medium

+    SetLength (Song.Score[2], 0); //hard

+

+   // Go through all Entrys

+    while (not TableData.EOF) do

+    begin

+      // Add one Entry to Array

+      Difficulty := StrToInt(TableData.FieldAsString(TableData.FieldIndex['Difficulty']));

+      if ((Difficulty >= 0) and (Difficulty <= 2)) and

+         (Length(Song.Score[Difficulty]) < 8) then

+      begin

+        //filter player

+        PlayerListed:=false;

+        if (Length(Song.Score[Difficulty])>0) then

+        begin

+          for I := 0 to Length(Song.Score[Difficulty]) - 1 do

+          begin

+            if (Song.Score[Difficulty, I].Name = TableData.FieldAsString(TableData.FieldIndex['Player'])) then

+            begin

+              PlayerListed:=true;

+              break;

+            end;

+          end;

+        end;

+

+        if not PlayerListed then

+        begin

+          SetLength(Song.Score[Difficulty], Length(Song.Score[Difficulty]) + 1);

+

+          Song.Score[Difficulty, High(Song.Score[Difficulty])].Name  :=

+            TableData.FieldAsString(TableData.FieldIndex['Player']);

+          Song.Score[Difficulty, High(Song.Score[Difficulty])].Score :=

+            StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score']));

+          DateStr := TableData.FieldAsString(TableData.FieldIndex['Date']);

+          if DateStr<>'' then

+            Song.Score[Difficulty, High(Song.Score[Difficulty])].Date :=

+              FormatDate(StrToInt(DateStr))

+          else

+            Song.Score[Difficulty, High(Song.Score[Difficulty])].Date := '??.??.20??';

+        end;

+      end;

+

+      TableData.Next;

+    end; // while

+

+  except

+    for Difficulty := 0 to 2 do

+    begin

+      SetLength(Song.Score[Difficulty], 1);

+      Song.Score[Difficulty, 1].Name := 'Error Reading ScoreDB';

+    end;

+  end;

+

+end;

+

+//--------------------

+//AddScore - Add one new Score to DB

+//--------------------

+procedure TDataBaseSystem.AddScore(var Song: TSong; Level: integer; Name: string; Score: integer; TimeStamp: integer);

+var

+ID: Integer;

+TableData: TSqliteTable;

+begin

+  //ScoreDB := TSqliteDatabase.Create(sFilename);

+  try

+  //Prevent 0 Scores from being added

+  if (Score > 0) then

+  begin

+    try //todo : wrapper shouldn't throw exceptions at all - this fixed a wine bug, thanks linnex! (11.11.07)

+      ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"');

+        if ID = 0 then //Song doesn't exist -> Create

+          begin

+            ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed` ) '+

+              'VALUES (NULL , "' + Song.Artist + '", "' + Song.Title + '", "0");');

+            ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"');

+

+            if ID = 0 then //Could not Create Table

+              exit;

+          end;

+

+          //Create new Entry

+          ScoreDB.ExecSQL('INSERT INTO `US_Scores` ( `SongID` , `Difficulty` , `Player` , `Score` , `Date` ) '+

+            'VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' +

+            Name + '", "' + InttoStr(Score) + '","' + InttoStr(TimeStamp) + '");');

+

+          {

+          //Delete Last Position when there are more than 5 Entrys

+          if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then

+            begin

+              TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` ASC LIMIT 1');

+              ScoreDB.ExecSQL('DELETE FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" AND `Player` = "' + TableData.FieldAsString(TableData.FieldIndex['Player']) + '" AND `Score` = "' + TableData.FieldAsString(TableData.FieldIndex['Score']) + '"');

+            end; }

+    except

+    // katze!

+    end;

+  end;

+  finally

+  //ScoreDB.Free;

+  end;

+end;

+

+//--------------------

+//WriteScore - Not needed with new System; But used for Increment Played Count

+//--------------------

+procedure TDataBaseSystem.WriteScore(var Song: TSong);

+begin

+  try

+    //Increase TimesPlayed

+    ScoreDB.ExecSQL ('UPDATE `us_songs` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";');

+  except

+

+  end;

+end;

+

+//--------------------

+//GetStats - Write some Stats to Array, Returns True if Chossen Page has Entrys

+//Case Typ of

+//0 - Best Scores

+//1 - Best Singers

+//2 - Most sung Songs

+//3 - Most popular Band

+//--------------------

+Function TDataBaseSystem.GetStats(var Stats: AStatResult; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean;

+var

+  Query: String;

+  TableData: TSqliteTable;

+  DateStr: String;

+begin

+  Result := False;

+

+  if (Length(Stats) < Count) then

+    Exit;

+

+  {Todo:

+    Add Prevention that only Players with more than 5 Scores are Selected at Typ 2}

+

+  //Create Query

+  Case Typ of

+    0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` , `Date` FROM `US_Scores` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`';

+    1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)), COUNT(`rowid`) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))';

+    2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `US_Songs` ORDER BY `TimesPlayed`';

+    3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `US_Songs` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)';

+  end;

+

+  //Add Order Direction

+  If Reversed then

+    Query := Query + ' ASC'

+  else

+    Query := Query + ' DESC';

+

+  //Add Limit

+  Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';';

+

+  //Execute Query

+  //try

+    TableData := ScoreDB.GetTable(Query);

+  {except

+    exit;

+  end;}

+

+  //if Result empty -> Exit

+  if (TableData.RowCount < 1) then

+    exit;

+

+  //Copy Result to Stats Array

+  while not TableData.Eof do

+  begin

+    Stats[TableData.Row].Typ := Typ;

+

+    Case Typ of

+      0:begin

+          Stats[TableData.Row].Singer := TableData.Fields[0];

+

+          Stats[TableData.Row].Difficulty := StrtoIntDef(TableData.Fields[1], 0);

+

+          Stats[TableData.Row].Score := StrtoIntDef(TableData.Fields[2], 0){TableData.FieldAsInteger(2)};

+          Stats[TableData.Row].SongArtist := TableData.Fields[3];

+          Stats[TableData.Row].SongTitle := TableData.Fields[4];

+          DateStr := TableData.Fields[5];

+          if DateStr<>'' then

+            Stats[TableData.Row].Date := FormatDate(StrToInt(DateStr))

+          else

+            Stats[TableData.Row].Date := '??.??.20??';

+        end;

+

+        1:begin

+          Stats[TableData.Row].Player := TableData.Fields[0];

+          Stats[TableData.Row].AverageScore := StrtoIntDef(TableData.Fields[1], 0);

+          Stats[TableData.Row].SungTimes := StrtoIntDef(TableData.Fields[2], 0);

+        end;

+

+        2:begin

+          Stats[TableData.Row].Artist := TableData.Fields[0];

+          Stats[TableData.Row].Title  := TableData.Fields[1];

+          Stats[TableData.Row].TimesSung  := StrtoIntDef(TableData.Fields[2], 0);

+        end;

+

+        3:begin

+          Stats[TableData.Row].ArtistName := TableData.Fields[0];

+          Stats[TableData.Row].TimesSungtot := StrtoIntDef(TableData.Fields[1], 0);

+        end;

+

+    end;

+

+    TableData.Next;

+  end;

+

+  Result := True;

+end;

+

+//--------------------

+//GetTotalEntrys - Get Total Num of entrys for a Stats Query

+//--------------------

+Function  TDataBaseSystem.GetTotalEntrys(const Typ: Byte): Cardinal;

+var Query: String;

+begin

+  //Create Query

+  Case Typ of

+    0: Query := 'SELECT COUNT(`SongID`) FROM `US_Scores`;';

+    1: Query := 'SELECT COUNT(DISTINCT `Player`) FROM `US_Scores`;';

+    2: Query := 'SELECT COUNT(`ID`) FROM `US_Songs`;';

+    3: Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `US_Songs`;';

+  end;

+

+  Result := ScoreDB.GetTableValue(Query);

+end;

+

+end.