aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authortobigun <tobigun@b956fd51-792f-4845-bead-9b4dfca2ff2c>2008-08-07 15:02:08 +0000
committertobigun <tobigun@b956fd51-792f-4845-bead-9b4dfca2ff2c>2008-08-07 15:02:08 +0000
commit1438b790ed7385482bad70468b8efb6b2113570d (patch)
tree2b9f6f828cc9e3edc440bf1edfa0cf66366d56d3
parent51995630561a819b80bbf08039bd5d39ce0de564 (diff)
downloadusdx-1438b790ed7385482bad70468b8efb6b2113570d.tar.gz
usdx-1438b790ed7385482bad70468b8efb6b2113570d.tar.xz
usdx-1438b790ed7385482bad70468b8efb6b2113570d.zip
- strings are bound now to avoid failures with unquoted strings
- better error/exception handling - Strings (PlayerName, Artist, Title) are stored as UTF8 - TSQLiteTable objects returned by GetTable() are freed now. Memory was not released before causing memory leaks. - replaced `` with []. Both are not ANSI SQL compatible, `` is used in MySQL, [] in MSSQL. In contrast to `` SQLite states that [] is supported. "" is not used as it is interpreted as a string if an identifier with this name does not exist. This will make debugging mor difficult as SQLite does not return an error code if an identifier was misspelled. - cleanup git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@1226 b956fd51-792f-4845-bead-9b4dfca2ff2c
-rw-r--r--Game/Code/Classes/UDataBase.pas435
1 files changed, 243 insertions, 192 deletions
diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas
index 20b44c75..4dbb0334 100644
--- a/Game/Code/Classes/UDataBase.pas
+++ b/Game/Code/Classes/UDataBase.pas
@@ -17,45 +17,60 @@ uses USongs,
//--------------------
type
TStatResult = record
- Case Typ: Byte of
- 0: (Singer: ShortString;
- Score: Word;
- Difficulty: Byte;
- SongArtist: ShortString;
- SongTitle: ShortString);
-
- 1: (Player: ShortString;
+ case Typ: Byte of
+ 0: (Singer: ShortString;
+ Score: Word;
+ Difficulty: Byte;
+ SongArtist: ShortString;
+ SongTitle: ShortString);
+
+ 1: (Player: ShortString;
AverageScore: Word);
- 2: (Artist: ShortString;
- Title: ShortString;
- TimesSung: Word);
+ 2: (Artist: ShortString;
+ Title: ShortString;
+ TimesSung: Word);
3: (ArtistName: ShortString;
- TimesSungtot: Word);
+ TimesSungTot: Word);
end;
- AStatResult = Array of TStatResult;
-
+ AStatResult = array of TStatResult;
+
+(*
+ 0: (Singer: WideString;
+ Score: Word;
+ Difficulty: Byte;
+ SongArtist: WideString;
+ SongTitle: WideString);
+
+ 1: (Player: WideString;
+ AverageScore: Word);
+
+ 2: (Artist: WideString;
+ Title: WideString;
+ TimesSung: Word);
+
+ 3: (ArtistName: WideString;
+ TimesSungTot: Word);
+*)
+
TDataBaseSystem = class
private
ScoreDB: TSqliteDatabase;
- sFilename: string;
-
+ fFilename: string;
public
+ property Filename: string read fFilename;
+ destructor Destroy; override;
- property Filename: String read sFilename;
-
- Destructor Destroy; override;
-
- Procedure Init(const Filename: string);
- procedure ReadScore(Song: TSong);
- procedure AddScore(Song: TSong; Level: integer; Name: string; Score: integer);
- procedure WriteScore(Song: TSong);
+ procedure Init(const Filename: string);
+ procedure ReadScore(Song: TSong);
+ procedure AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer);
+ procedure WriteScore(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 GetStatReset: TDateTime;
+ function GetStats(var Stats: AStatResult; Typ, Count: Byte; Page: Cardinal; Reversed: Boolean): Boolean;
+ function GetTotalEntrys(Typ: Byte): Cardinal;
+ function GetStatReset: TDateTime;
end;
var
@@ -64,7 +79,6 @@ var
implementation
uses
- IniFiles,
ULog,
StrUtils,
SysUtils;
@@ -78,46 +92,49 @@ const
//Create - Opens Database and Create Tables if not Exist
//--------------------
-Procedure TDataBaseSystem.Init(const Filename: string);
+procedure TDataBaseSystem.Init(const Filename: string);
begin
+ if Assigned(ScoreDB) then
+ Exit;
+
Log.LogStatus('Initializing database: "'+Filename+'"', 'TDataBaseSystem.Init');
-
- //Open Database
- ScoreDB := TSqliteDatabase.Create( Filename );
- sFilename := Filename;
try
- //Look for Tables => When not exist Create them
- if not ScoreDB.TableExists( cUS_Scores ) then
- begin
- ScoreDB.execsql('CREATE TABLE `'+cUS_Scores+'` (`SongID` INT( 11 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );');
- debugWriteln( 'TDataBaseSystem.Init - CREATED US_Scores' );
- end;
-
- if not ScoreDB.TableExists( cUS_Songs ) then
+
+ //Open Database
+ ScoreDB := TSQLiteDatabase.Create(Filename);
+ fFilename := Filename;
+
+ 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' +
+ ');');
+
+ 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' +
+ ');');
+
+ if not ScoreDB.TableExists(cUS_Statistics_Info) then
begin
- ScoreDB.execsql('CREATE TABLE `'+cUS_Songs+'` (`ID` INTEGER PRIMARY KEY, `Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );');
- debugWriteln( 'TDataBaseSystem.Init - CREATED US_Songs' );
+ ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS ['+cUS_Statistics_Info+'] (' +
+ '[ResetTime] TEXT' +
+ ');');
+ ScoreDB.ExecSQL('INSERT INTO ['+cUS_Statistics_Info+'] ' +
+ '([ResetTime]) VALUES ' +
+ '('''+DateTimeToStr(now)+''');');
end;
- if not ScoreDB.TableExists( cUS_Statistics_Info ) then
+ except
+ on E: Exception do
begin
- ScoreDB.execsql('CREATE TABLE `'+cUS_Statistics_Info+'` (`ResetTime` VARCHAR(17) );');
- ScoreDB.execsql('INSERT INTO `'+cUS_Statistics_Info+'` (`ResetTime`) VALUES ("'+datetimetostr(now)+'");');
- debugWriteln( 'TDataBaseSystem.Init - CREATED US_Statistics_Info' );
+ Log.LogError(E.Message, 'TDataBaseSystem.Init');
+ FreeAndNil(ScoreDB);
end;
-
- //Not possible because of String Limitation to 255 Chars //Need to rewrite Wrapper
- {if not ScoreDB.TableExists('US_SongCache') then
- ScoreDB.ExecSQL('CREATE TABLE `US_SongCache` (`Path` VARCHAR( 255 ) NOT NULL , `Filename` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `Artist` VARCHAR( 255 ) NOT NULL , `Folder` VARCHAR( 255 ) NOT NULL , `Genre` VARCHAR( 255 ) NOT NULL , `Edition` VARCHAR( 255 ) NOT NULL , `Language` VARCHAR( 255 ) NOT NULL , `Creator` VARCHAR( 255 ) NOT NULL , `Cover` VARCHAR( 255 ) NOT NULL , `Background` VARCHAR( 255 ) NOT NULL , `Video` VARCHAR( 255 ) NOT NULL , `VideoGap` FLOAT NOT NULL , `Gap` FLOAT NOT NULL , `Start` FLOAT NOT NULL , `Finish` INT( 11 ) NOT NULL , `BPM` INT( 5 ) NOT NULL , `Relative` BOOLEAN NOT NULL , `NotesGap` INT( 11 ) NOT NULL);');}
-
-
- finally
- Log.LogInfo( cUS_Songs +' exists: ' + IfThen(ScoreDB.TableExists(cUS_Songs), 'true', 'false'),
- 'TDataBaseSystem.Init');
- Log.LogInfo( cUS_Scores +' exists: ' + IfThen(ScoreDB.TableExists(cUS_Scores), 'true', 'false'),
- 'TDataBaseSystem.Init');
- //ScoreDB.Free;
end;
end;
@@ -125,10 +142,10 @@ end;
//--------------------
//Destroy - Frees Database
//--------------------
-Destructor TDataBaseSystem.Destroy;
+destructor TDataBaseSystem.Destroy;
begin
Log.LogInfo('TDataBaseSystem.Free', 'TDataBaseSystem.Destroy');
- freeandnil( ScoreDB );
+ ScoreDB.Free;
inherited;
end;
@@ -140,100 +157,126 @@ var
TableData: TSqliteTable;
Difficulty: Integer;
begin
- if not assigned( ScoreDB ) then
- exit;
+ if not Assigned(ScoreDB) then
+ Exit;
+ TableData := nil;
- //ScoreDB := TSqliteDatabase.Create(sFilename);
try
- try
- //Search Song in DB
- TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score` FROM `'+cUS_Scores+'` WHERE `SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '" LIMIT 1) ORDER BY `Score` DESC LIMIT 15');
-
- //Empty Old Scores
- SetLength (Song.Score[0], 0);
- SetLength (Song.Score[1], 0);
- SetLength (Song.Score[2], 0);
-
- while (not TableData.Eof) do //Go through all Entrys
- begin //Add one Entry to Array
- Difficulty := StrToIntDef(TableData.FieldAsString(TableData.FieldIndex['Difficulty']), -1);
- if ((Difficulty >= 0) AND (Difficulty <= 2)) AND (Length(Song.Score[Difficulty]) < 5) then
+ // Search Song in DB
+ TableData := ScoreDB.GetTable(
+ 'SELECT [Difficulty], [Player], [Score] FROM ['+cUS_Scores+'] ' +
+ 'WHERE [SongID] = (' +
+ 'SELECT [ID] FROM ['+cUS_Songs+'] ' +
+ 'WHERE [Artist] = ? AND [Title] = ? ' +
+ 'LIMIT 1) ' +
+ 'ORDER BY [Score] DESC LIMIT 15',
+ [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]);
+
+ // Empty Old Scores
+ SetLength(Song.Score[0], 0);
+ SetLength(Song.Score[1], 0);
+ SetLength(Song.Score[2], 0);
+
+ // Go through all Entrys
+ while (not TableData.EOF) do
+ begin
+ // Add one Entry to Array
+ Difficulty := StrToIntDef(TableData.FieldAsString(TableData.FieldIndex['Difficulty']), -1);
+ if ((Difficulty >= 0) and (Difficulty <= 2)) and
+ (Length(Song.Score[Difficulty]) < 5) 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 :=
+ Song.Score[Difficulty, High(Song.Score[Difficulty])].Name :=
+ UTF8Decode(TableData.FieldAsString(TableData.FieldIndex['Player']));
+ Song.Score[Difficulty, High(Song.Score[Difficulty])].Score :=
StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score']));
end;
- TableData.Next;
- end; // While not TableData.EOF
+ 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;
+ except
+ for Difficulty := 0 to 2 do
+ begin
+ SetLength(Song.Score[Difficulty], 1);
+ Song.Score[Difficulty, 1].Name := 'Error Reading ScoreDB';
end;
-
- finally
- //ScoreDb.Free;
end;
+
+ TableData.Free;
end;
//--------------------
//AddScore - Add one new Score to DB
//--------------------
-procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; Name: string; Score: integer);
+procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer);
var
-ID: Integer;
-TableData: TSqliteTable;
+ ID: Integer;
+ TableData: TSQLiteTable;
begin
- if not assigned( ScoreDB ) then
- exit;
+ if not Assigned(ScoreDB) then
+ Exit;
- //ScoreDB := TSqliteDatabase.Create(sFilename);
- try
//Prevent 0 Scores from being added
- if (Score > 0) then
- begin
+ if (Score <= 0) then
+ Exit;
+
+ TableData := nil;
+
+ try
- ID := ScoreDB.GetTableValue('SELECT `ID` FROM `'+cUS_Songs+'` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"');
+ ID := ScoreDB.GetTableValue(
+ 'SELECT [ID] FROM ['+cUS_Songs+'] ' +
+ 'WHERE [Artist] = ? AND [Title] = ?',
+ [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]);
if ID = 0 then //Song doesn't exist -> Create
begin
- ScoreDB.ExecSQL ('INSERT INTO `'+cUS_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 + '"');
+ ScoreDB.ExecSQL (
+ 'INSERT INTO ['+cUS_Songs+'] ' +
+ '([ID], [Artist], [Title], [TimesPlayed]) VALUES ' +
+ '(NULL, ?, ?, 0);',
+ [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]);
+ ID := ScoreDB.GetTableValue(
+ 'SELECT [ID] FROM ['+cUS_Songs+'] ' +
+ 'WHERE [Artist] = ? AND [Title] = ?',
+ [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]);
if ID = 0 then //Could not Create Table
- exit;
+ Exit;
end;
//Create new Entry
- ScoreDB.ExecSQL('INSERT INTO `'+cUS_Scores+'` ( `SongID` , `Difficulty` , `Player` , `Score` ) VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' + Name + '", "' + InttoStr(Score) + '");');
-
- {//Old Deletion
- if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `'+cUS_Scores+'` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then
- begin
- TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `'+cUS_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;}
-
- //Delete Last Position when there are more than 5 Entrys - Fixes Crash when there are > 5 ScoreEntrys
- TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `'+cUS_Scores+'` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` DESC LIMIT -1 OFFSET 5');
-
- While not TableData.Eof do
+ ScoreDB.ExecSQL(
+ 'INSERT INTO ['+cUS_Scores+'] ' +
+ '([SongID] ,[Difficulty], [Player], [Score]) VALUES ' +
+ '(?, ?, ?, ?);',
+ [ID, Level, UTF8Encode(Name), Score]);
+
+ //Delete Last Position when there are more than 5 Entrys - Fixes Crash when there are > 5 ScoreEntrys
+ TableData := ScoreDB.GetTable(
+ 'SELECT [Player], [Score] FROM ['+cUS_Scores+'] ' +
+ 'WHERE [SongID] = ' + InttoStr(ID) + ' AND ' +
+ '[Difficulty] = ' + InttoStr(Level) +' ' +
+ 'ORDER BY [Score] DESC LIMIT -1 OFFSET 5');
+
+ while not TableData.EOF do
begin
- 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']) + '"');
+ ScoreDB.ExecSQL(
+ 'DELETE FROM ['+cUS_Scores+'] ' +
+ 'WHERE [SongID] = ' + InttoStr(ID) + ' AND ' +
+ '[Difficulty] = ' + InttoStr(Level) +' AND ' +
+ '[Player] = ? AND [Score] = ?',
+ [TableData.FieldAsString(TableData.FieldIndex['Player']),
+ TableData.FieldAsString(TableData.FieldIndex['Score'])]);
TableData.Next;
end;
-
- end;
- finally
- //ScoreDB.Free;
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.AddScore');
end;
+
+ TableData.Free;
end;
//--------------------
@@ -241,14 +284,18 @@ end;
//--------------------
procedure TDataBaseSystem.WriteScore(Song: TSong);
begin
- if not assigned( ScoreDB ) then
- exit;
+ if not Assigned(ScoreDB) then
+ Exit;
try
//Increase TimesPlayed
- ScoreDB.ExecSQL ('UPDATE `'+cUS_Songs+'` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";');
- except
-
+ ScoreDB.ExecSQL(
+ 'UPDATE ['+cUS_Songs+'] ' +
+ 'SET [TimesPlayed] = [TimesPlayed] + 1 ' +
+ 'WHERE [Title] = ? AND [Artist] = ?;',
+ [UTF8Encode(Song.Title), UTF8Encode(Song.Artist)]);
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.WriteScore');
end;
end;
@@ -260,15 +307,15 @@ end;
//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;
+function TDataBaseSystem.GetStats(var Stats: AStatResult; Typ, Count: Byte; Page: Cardinal; Reversed: Boolean): Boolean;
var
Query: String;
TableData: TSqliteTable;
begin
Result := False;
- if not assigned( ScoreDB ) then
- exit;
+ if not Assigned(ScoreDB) then
+ Exit;
if (Length(Stats) < Count) then
Exit;
@@ -276,18 +323,27 @@ begin
{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` FROM `'+cUS_Scores+'` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`';
- 1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)) FROM `'+cUS_Scores+'` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))';
- 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `'+cUS_Songs+'` ORDER BY `TimesPlayed`';
- 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `'+cUS_Songs+'` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)';
+ case Typ of
+ 0: begin
+ Query := 'SELECT [Player], [Difficulty], [Score], [Artist], [Title] FROM ['+cUS_Scores+'] ' +
+ 'INNER JOIN ['+cUS_Songs+'] ON ([SongID] = [ID]) ORDER BY [Score]';
+ end;
+ 1: begin
+ Query := 'SELECT [Player], ROUND (Sum([Score]) / COUNT([Score])) FROM ['+cUS_Scores+'] ' +
+ 'GROUP BY [Player] ORDER BY (Sum([Score]) / COUNT([Score]))';
+ end;
+ 2: begin
+ Query := 'SELECT [Artist], [Title], [TimesPlayed] FROM ['+cUS_Songs+'] ' +
+ 'ORDER BY [TimesPlayed]';
+ end;
+ 3: begin
+ Query := 'SELECT [Artist], Sum([TimesPlayed]) FROM ['+cUS_Songs+'] ' +
+ 'GROUP BY [Artist] ORDER BY Sum([TimesPlayed])';
+ end;
end;
//Add Order Direction
- If Reversed then
- Query := Query + ' ASC'
- else
- Query := Query + ' DESC';
+ Query := Query + IfThen(Reversed, ' ASC', ' DESC');
//Add Limit
Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';';
@@ -296,44 +352,47 @@ begin
try
TableData := ScoreDB.GetTable(Query);
except
- exit; // this has a try except, because ( on linux at least ) it seems that doing a GetTable, that returns nothing
- // causes an exception. and in the case of a new Database file, with no scores stored yet... this seems to except here.
+ on E: Exception do
+ begin
+ Log.LogError(E.Message, 'TDataBaseSystem.GetStats');
+ Exit;
+ end;
end;
//if Result empty -> Exit
if (TableData.RowCount < 1) then
- exit;
+ Exit;
//Copy Result to Stats Array
- while not TableData.Eof do
+ while not TableData.EOF do
begin
Stats[TableData.Row].Typ := Typ;
- Case Typ of
+ case Typ of
0:begin
- Stats[TableData.Row].Singer := TableData.Fields[0];
+ Stats[TableData.Row].Singer := UTF8Decode(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];
+ Stats[TableData.Row].SongArtist := UTF8Decode(TableData.Fields[3]);
+ Stats[TableData.Row].SongTitle := UTF8Decode(TableData.Fields[4]);
end;
1:begin
- Stats[TableData.Row].Player := TableData.Fields[0];
+ Stats[TableData.Row].Player := UTF8Decode(TableData.Fields[0]);
Stats[TableData.Row].AverageScore := StrtoIntDef(TableData.Fields[1], 0);
end;
2:begin
- Stats[TableData.Row].Artist := TableData.Fields[0];
- Stats[TableData.Row].Title := TableData.Fields[1];
+ Stats[TableData.Row].Artist := UTF8Decode(TableData.Fields[0]);
+ Stats[TableData.Row].Title := UTF8Decode(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);
+ Stats[TableData.Row].ArtistName := UTF8Decode(TableData.Fields[0]);
+ Stats[TableData.Row].TimesSungTot := StrtoIntDef(TableData.Fields[1], 0);
end;
end;
@@ -341,52 +400,34 @@ begin
TableData.Next;
end;
+ TableData.Free;
Result := True;
end;
//--------------------
//GetTotalEntrys - Get Total Num of entrys for a Stats Query
//--------------------
-Function TDataBaseSystem.GetTotalEntrys(const Typ: Byte): Cardinal;
-var Query: String;
+function TDataBaseSystem.GetTotalEntrys(Typ: Byte): Cardinal;
+var
+ Query: String;
begin
Result := 0;
- if not assigned( ScoreDB ) then
- exit;
+ if not Assigned(ScoreDB) then
+ Exit;
try
//Create Query
- Case Typ of
- 0: begin
- Query := 'SELECT COUNT(`SongID`) FROM `'+cUS_Scores+'`;';
- if not ScoreDB.TableExists( cUS_Scores ) then
- exit;
- end;
- 1: begin
- Query := 'SELECT COUNT(DISTINCT `Player`) FROM `'+cUS_Scores+'`;';
- if not ScoreDB.TableExists( cUS_Scores ) then
- exit;
- end;
- 2: begin
- Query := 'SELECT COUNT(`ID`) FROM `'+cUS_Songs+'`;';
- if not ScoreDB.TableExists( cUS_Songs ) then
- exit;
- end;
- 3: begin
- Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `'+cUS_Songs+'`;';
- if not ScoreDB.TableExists( cUS_Songs ) then
- exit;
- end;
+ case Typ of
+ 0: Query := 'SELECT COUNT([SongID]) FROM ['+cUS_Scores+'];';
+ 1: Query := 'SELECT COUNT(DISTINCT [Player]) FROM ['+cUS_Scores+'];';
+ 2: Query := 'SELECT COUNT([ID]) FROM ['+cUS_Songs+'];';
+ 3: Query := 'SELECT COUNT(DISTINCT [Artist]) FROM ['+cUS_Songs+'];';
end;
-
+
Result := ScoreDB.GetTableValue(Query);
- except
- // TODO : JB_Linux - Why do we get these exceptions on linux !! -> should be solved!
- on E:ESQLiteException DO
- begin
- exit;
- end;
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.GetTotalEntrys');
end;
end;
@@ -394,17 +435,27 @@ end;
//--------------------
//GetStatReset - Get reset date of statistic data
//--------------------
-Function TDataBaseSystem.GetStatReset: TDateTime;
+function TDataBaseSystem.GetStatReset: TDateTime;
var
- Query: String;
- TableData: TSqliteTable;
+ Query: string;
+ TableData: TSQLiteTable;
begin
- if not assigned( ScoreDB ) then
- exit;
+ Result := 0;
+
+ if not Assigned(ScoreDB) then
+ Exit;
+
+ TableData := nil;
+
+ try
+ Query := 'SELECT [ResetTime] FROM ['+cUS_Statistics_Info+'];';
+ TableData := ScoreDB.GetTable(Query);
+ Result := StrToDateTime(TableData.Fields[0]);
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.GetStatReset');
+ end;
- Query := 'SELECT `ResetTime` FROM `'+cUS_Statistics_Info+'`;';
- TableData := ScoreDB.GetTable(Query);
- result:=StrToDateTime(TableData.Fields[0]);
+ TableData.Free;
end;
end.