aboutsummaryrefslogtreecommitdiffstats
path: root/Game/Code/Classes
diff options
context:
space:
mode:
authortobigun <tobigun@b956fd51-792f-4845-bead-9b4dfca2ff2c>2008-08-07 18:09:21 +0000
committertobigun <tobigun@b956fd51-792f-4845-bead-9b4dfca2ff2c>2008-08-07 18:09:21 +0000
commit1db7c89df8670ef1129e172fdc8b5534f4b792d6 (patch)
tree7f0f0a8529287ba4c0e71e7648d07f6bdfd25b44 /Game/Code/Classes
parent507d210257a8e92246aae9d7bfa7c62f78d104ce (diff)
downloadusdx-1db7c89df8670ef1129e172fdc8b5534f4b792d6.tar.gz
usdx-1db7c89df8670ef1129e172fdc8b5534f4b792d6.tar.xz
usdx-1db7c89df8670ef1129e172fdc8b5534f4b792d6.zip
- Introduced TStatType.
- Replaced TSQLiteTable with the new TSQLiteUniTable. The ..UniTable-version does not retrieve data at once (this feature is not needed/wanted in most cases). The major advantage of this version is a better handling of datatypes. TSQLiteTable does not handle datatypes correctly (and even crashes) if the field-types are not one of the SQLite types (e.g. an INT(12) field will crash if it accessed by FieldAsInteger, the same applies to "integer" but the upper-case version "INTEGER" works). With TSQLiteUniTable those crashes should not occur. git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@1228 b956fd51-792f-4845-bead-9b4dfca2ff2c
Diffstat (limited to 'Game/Code/Classes')
-rw-r--r--Game/Code/Classes/UDataBase.pas258
1 files changed, 139 insertions, 119 deletions
diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas
index e00965c4..cfe36ca9 100644
--- a/Game/Code/Classes/UDataBase.pas
+++ b/Game/Code/Classes/UDataBase.pas
@@ -16,23 +16,35 @@ uses USongs,
//DataBaseSystem - Class including all DB Methods
//--------------------
type
- TStatResult = record
- case Typ: Byte of
- 0: (Singer: ShortString;
- Score: Word;
- Difficulty: Byte;
- SongArtist: ShortString;
- SongTitle: ShortString);
+ TStatType = (
+ stBestScores, // Best Scores
+ stBestSingers, // Best Singers
+ stMostSungSong, // Most sung Songs
+ stMostPopBand // Most popular Band
+ );
- 1: (Player: ShortString;
- AverageScore: Word);
-
- 2: (Artist: ShortString;
- Title: ShortString;
- TimesSung: Word);
-
- 3: (ArtistName: ShortString;
- TimesSungTot: Word);
+ TStatResult = record
+ case Typ: TStatType of
+ stBestScores: (
+ Singer: ShortString;
+ Score: Word;
+ Difficulty: Byte;
+ SongArtist: ShortString;
+ SongTitle: ShortString
+ );
+ stBestSingers: (
+ Player: ShortString;
+ AverageScore: Word
+ );
+ stMostSungSong: (
+ Artist: ShortString;
+ Title: ShortString;
+ TimesSung: Word
+ );
+ stMostPopBand: (
+ ArtistName: ShortString;
+ TimesSungTot: Word
+ );
end;
AStatResult = array of TStatResult;
@@ -68,8 +80,8 @@ type
procedure AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer);
procedure WriteScore(Song: TSong);
- function GetStats(var Stats: AStatResult; Typ, Count: Byte; Page: Cardinal; Reversed: Boolean): Boolean;
- function GetTotalEntrys(Typ: Byte): Cardinal;
+ function GetStats(var Stats: AStatResult; Typ: TStatType; Count: Byte; Page: Cardinal; Reversed: Boolean): Boolean;
+ function GetTotalEntrys(Typ: TStatType): Cardinal;
function GetStatReset: TDateTime;
end;
@@ -88,10 +100,9 @@ const
cUS_Songs = 'us_songs';
cUS_Statistics_Info = 'us_statistics_info';
-//--------------------
-//Create - Opens Database and Create Tables if not Exist
-//--------------------
-
+(**
+ * Opens Database and Create Tables if not Exist
+ *)
procedure TDataBaseSystem.Init(const Filename: string);
begin
if Assigned(ScoreDB) then
@@ -105,6 +116,14 @@ begin
ScoreDB := TSQLiteDatabase.Create(Filename);
fFilename := Filename;
+ // 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, ' +
@@ -139,9 +158,9 @@ begin
end;
-//--------------------
-//Destroy - Frees Database
-//--------------------
+(**
+ * Frees Database
+ *)
destructor TDataBaseSystem.Destroy;
begin
Log.LogInfo('TDataBaseSystem.Free', 'TDataBaseSystem.Destroy');
@@ -149,12 +168,12 @@ begin
inherited;
end;
-//--------------------
-//ReadScore - Read Scores into SongArray
-//--------------------
+(**
+ * Read Scores into SongArray
+ *)
procedure TDataBaseSystem.ReadScore(Song: TSong);
var
- TableData: TSqliteTable;
+ TableData: TSQLiteUniTable;
Difficulty: Integer;
begin
if not Assigned(ScoreDB) then
@@ -164,7 +183,7 @@ begin
try
// Search Song in DB
- TableData := ScoreDB.GetTable(
+ TableData := ScoreDB.GetUniTable(
'SELECT [Difficulty], [Player], [Score] FROM ['+cUS_Scores+'] ' +
'WHERE [SongID] = (' +
'SELECT [ID] FROM ['+cUS_Songs+'] ' +
@@ -182,18 +201,18 @@ begin
while (not TableData.EOF) do
begin
// Add one Entry to Array
- Difficulty := StrToIntDef(TableData.FieldAsString(TableData.FieldIndex['Difficulty']), -1);
+ Difficulty := TableData.FieldAsInteger(TableData.FieldIndex['Difficulty']);
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 :=
- UTF8Decode(TableData.FieldAsString(TableData.FieldIndex['Player']));
+ UTF8Decode(TableData.FieldByName['Player']);
Song.Score[Difficulty, High(Song.Score[Difficulty])].Score :=
- StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score']));
+ TableData.FieldAsInteger(TableData.FieldIndex['Score']);
end;
-
+
TableData.Next;
end; // while
@@ -208,9 +227,9 @@ begin
TableData.Free;
end;
-//--------------------
-//AddScore - Add one new Score to DB
-//--------------------
+(**
+ * Adds one new score to DB
+ *)
procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer);
var
ID: Integer;
@@ -219,7 +238,7 @@ begin
if not Assigned(ScoreDB) then
Exit;
- //Prevent 0 Scores from being added
+ // Prevent 0 Scores from being added
if (Score <= 0) then
Exit;
@@ -231,43 +250,47 @@ begin
'SELECT [ID] FROM ['+cUS_Songs+'] ' +
'WHERE [Artist] = ? AND [Title] = ?',
[UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]);
- if ID = 0 then //Song doesn't exist -> Create
+ if (ID = 0) then
begin
- ScoreDB.ExecSQL (
+ // Create song if it does not exist
+ 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;
+ // Get song-ID
+ ID := ScoreDB.GetLastInsertRowID();
end;
- //Create new Entry
+ // Create new entry
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
+ // Delete last position when there are more than 5 entrys.
+ // Fixes crash when there are > 5 ScoreEntrys
+ // Note: GetUniTable is not applicable here, as the results are used while
+ // table entries are deleted.
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
+ while (not TableData.EOF) do
begin
+ // Note: Score is an int-value, so in contrast to Player, we do not bind
+ // this value. Otherwise we had to convert the string to an int to avoid
+ // an automatic cast of this field to the TEXT type (although it might even
+ // work that way).
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'])]);
+ '[Player] = ? AND ' +
+ '[Score] = ' + TableData.FieldByName['Score'],
+ [TableData.FieldByName['Player']]);
TableData.Next;
end;
@@ -279,16 +302,17 @@ begin
TableData.Free;
end;
-//--------------------
-//WriteScore - Not needed with new System; But used for Increment Played Count
-//--------------------
+(**
+ * Not needed with new System.
+ * Used for increment played count
+ *)
procedure TDataBaseSystem.WriteScore(Song: TSong);
begin
if not Assigned(ScoreDB) then
Exit;
try
- //Increase TimesPlayed
+ // Increase TimesPlayed
ScoreDB.ExecSQL(
'UPDATE ['+cUS_Songs+'] ' +
'SET [TimesPlayed] = [TimesPlayed] + 1 ' +
@@ -299,18 +323,14 @@ begin
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; Typ, Count: Byte; Page: Cardinal; Reversed: Boolean): Boolean;
+(**
+ * Writes some stats to array.
+ * Returns true if choosen page has entrys
+ *)
+function TDataBaseSystem.GetStats(var Stats: AStatResult; Typ: TStatType; Count: Byte; Page: Cardinal; Reversed: Boolean): Boolean;
var
Query: String;
- TableData: TSqliteTable;
+ TableData: TSQLiteUniTable;
begin
Result := False;
@@ -320,37 +340,37 @@ begin
if (Length(Stats) < Count) then
Exit;
- {Todo: Add Prevention that only Players with more than 5 Scores are Selected at Typ 2}
+ {Todo: Add Prevention that only players with more than 5 scores are selected at type 2}
- //Create Query
+ // Create query
case Typ of
- 0: begin
+ stBestScores: begin
Query := 'SELECT [Player], [Difficulty], [Score], [Artist], [Title] FROM ['+cUS_Scores+'] ' +
'INNER JOIN ['+cUS_Songs+'] ON ([SongID] = [ID]) ORDER BY [Score]';
end;
- 1: begin
+ stBestSingers: begin
Query := 'SELECT [Player], ROUND(AVG([Score])) FROM ['+cUS_Scores+'] ' +
'GROUP BY [Player] ORDER BY AVG([Score])';
end;
- 2: begin
+ stMostSungSong: begin
Query := 'SELECT [Artist], [Title], [TimesPlayed] FROM ['+cUS_Songs+'] ' +
'ORDER BY [TimesPlayed]';
end;
- 3: begin
+ stMostPopBand: begin
Query := 'SELECT [Artist], SUM([TimesPlayed]) FROM ['+cUS_Songs+'] ' +
'GROUP BY [Artist] ORDER BY SUM([TimesPlayed])';
end;
end;
- //Add Order Direction
+ // Add order direction
Query := Query + IfThen(Reversed, ' ASC', ' DESC');
- //Add Limit
+ // Add limit
Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';';
- //Execute Query
+ // Execute query
try
- TableData := ScoreDB.GetTable(Query);
+ TableData := ScoreDB.GetUniTable(Query);
except
on E: Exception do
begin
@@ -359,42 +379,38 @@ begin
end;
end;
- //if result is empty -> Exit
- if (TableData.RowCount < 1) then
+ if (TableData.EOF) then
+ begin
+ TableData.Free;
Exit;
+ end;
- //Copy Result to Stats Array
+ // 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 := 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 := UTF8Decode(TableData.Fields[3]);
- Stats[TableData.Row].SongTitle := UTF8Decode(TableData.Fields[4]);
- end;
-
- 1:begin
- Stats[TableData.Row].Player := UTF8Decode(TableData.Fields[0]);
- Stats[TableData.Row].AverageScore := StrtoIntDef(TableData.Fields[1], 0);
- end;
-
- 2:begin
- 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 := UTF8Decode(TableData.Fields[0]);
- Stats[TableData.Row].TimesSungTot := StrtoIntDef(TableData.Fields[1], 0);
- end;
-
+ stBestScores: begin
+ Stats[TableData.Row].Singer := UTF8Decode(TableData.Fields[0]);
+ Stats[TableData.Row].Difficulty := TableData.FieldAsInteger(1);
+ Stats[TableData.Row].Score := TableData.FieldAsInteger(2);
+ Stats[TableData.Row].SongArtist := UTF8Decode(TableData.Fields[3]);
+ Stats[TableData.Row].SongTitle := UTF8Decode(TableData.Fields[4]);
+ end;
+ stBestSingers: begin
+ Stats[TableData.Row].Player := UTF8Decode(TableData.Fields[0]);
+ Stats[TableData.Row].AverageScore := TableData.FieldAsInteger(1);
+ end;
+ stMostSungSong: begin
+ Stats[TableData.Row].Artist := UTF8Decode(TableData.Fields[0]);
+ Stats[TableData.Row].Title := UTF8Decode(TableData.Fields[1]);
+ Stats[TableData.Row].TimesSung := TableData.FieldAsInteger(2);
+ end;
+ stMostPopBand: begin
+ Stats[TableData.Row].ArtistName := UTF8Decode(TableData.Fields[0]);
+ Stats[TableData.Row].TimesSungTot := TableData.FieldAsInteger(1);
+ end;
end;
TableData.Next;
@@ -404,10 +420,10 @@ begin
Result := True;
end;
-//--------------------
-//GetTotalEntrys - Get Total Num of entrys for a Stats Query
-//--------------------
-function TDataBaseSystem.GetTotalEntrys(Typ: Byte): Cardinal;
+(**
+ * Gets total number of entrys for a stats query
+ *)
+function TDataBaseSystem.GetTotalEntrys(Typ: TStatType): Cardinal;
var
Query: String;
begin
@@ -417,12 +433,16 @@ begin
Exit;
try
- //Create Query
+ // Create query
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+'];';
+ stBestScores:
+ Query := 'SELECT COUNT([SongID]) FROM ['+cUS_Scores+'];';
+ stBestSingers:
+ Query := 'SELECT COUNT(DISTINCT [Player]) FROM ['+cUS_Scores+'];';
+ stMostSungSong:
+ Query := 'SELECT COUNT([ID]) FROM ['+cUS_Songs+'];';
+ stMostPopBand:
+ Query := 'SELECT COUNT(DISTINCT [Artist]) FROM ['+cUS_Songs+'];';
end;
Result := ScoreDB.GetTableValue(Query);
@@ -432,13 +452,13 @@ begin
end;
-//--------------------
-//GetStatReset - Get reset date of statistic data
-//--------------------
+(**
+ * Gets reset date of statistic data
+ *)
function TDataBaseSystem.GetStatReset: TDateTime;
var
Query: string;
- TableData: TSQLiteTable;
+ TableData: TSQLiteUniTable;
begin
Result := 0;
@@ -449,7 +469,7 @@ begin
try
Query := 'SELECT [ResetTime] FROM ['+cUS_Statistics_Info+'];';
- TableData := ScoreDB.GetTable(Query);
+ TableData := ScoreDB.GetUniTable(Query);
Result := StrToDateTime(TableData.Fields[0]);
except on E: Exception do
Log.LogError(E.Message, 'TDataBaseSystem.GetStatReset');