From 1db7c89df8670ef1129e172fdc8b5534f4b792d6 Mon Sep 17 00:00:00 2001 From: tobigun Date: Thu, 7 Aug 2008 18:09:21 +0000 Subject: - 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 --- Game/Code/Classes/UDataBase.pas | 258 ++++++++++++++++++++++------------------ 1 file changed, 139 insertions(+), 119 deletions(-) (limited to 'Game/Code/Classes') 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'); -- cgit v1.2.3