aboutsummaryrefslogtreecommitdiffstats
path: root/src/classes/UDataBase.pas
diff options
context:
space:
mode:
Diffstat (limited to 'src/classes/UDataBase.pas')
-rw-r--r--src/classes/UDataBase.pas533
1 files changed, 533 insertions, 0 deletions
diff --git a/src/classes/UDataBase.pas b/src/classes/UDataBase.pas
new file mode 100644
index 00000000..cd315df3
--- /dev/null
+++ b/src/classes/UDataBase.pas
@@ -0,0 +1,533 @@
+unit UDataBase;
+
+interface
+
+{$IFDEF FPC}
+ {$MODE Delphi}
+{$ENDIF}
+
+{$I switches.inc}
+
+uses USongs,
+ USong,
+ Classes,
+ SQLiteTable3;
+
+//--------------------
+//DataBaseSystem - Class including all DB Methods
+//--------------------
+type
+ TStatType = (
+ stBestScores, // Best Scores
+ stBestSingers, // Best Singers
+ stMostSungSong, // Most sung Songs
+ stMostPopBand // Most popular Band
+ );
+
+ // abstract super-class for statistic results
+ TStatResult = class
+ public
+ Typ: TStatType;
+ end;
+
+ TStatResultBestScores = class(TStatResult)
+ public
+ Singer: WideString;
+ Score: Word;
+ Difficulty: Byte;
+ SongArtist: WideString;
+ SongTitle: WideString;
+ end;
+
+ TStatResultBestSingers = class(TStatResult)
+ public
+ Player: WideString;
+ AverageScore: Word;
+ end;
+
+ TStatResultMostSungSong = class(TStatResult)
+ public
+ Artist: WideString;
+ Title: WideString;
+ TimesSung: Word;
+ end;
+
+ TStatResultMostPopBand = class(TStatResult)
+ public
+ ArtistName: WideString;
+ TimesSungTot: Word;
+ end;
+
+
+ TDataBaseSystem = class
+ private
+ ScoreDB: TSQLiteDatabase;
+ fFilename: string;
+
+ function GetVersion(): integer;
+ procedure SetVersion(Version: integer);
+ public
+ property Filename: string read fFilename;
+
+ destructor Destroy; override;
+
+ 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(Typ: TStatType; Count: Byte; Page: Cardinal; Reversed: Boolean): TList;
+ procedure FreeStats(StatList: TList);
+ function GetTotalEntrys(Typ: TStatType): Cardinal;
+ function GetStatReset: TDateTime;
+ end;
+
+var
+ DataBase: TDataBaseSystem;
+
+implementation
+
+uses
+ ULog,
+ DateUtils,
+ StrUtils,
+ SysUtils;
+
+const
+ cDBVersion = 01; // 0.1
+ cUS_Scores = 'us_scores';
+ cUS_Songs = 'us_songs';
+ cUS_Statistics_Info = 'us_statistics_info';
+
+(**
+ * Opens Database and Create Tables if not Exist
+ *)
+procedure TDataBaseSystem.Init(const Filename: string);
+var
+ Version: integer;
+begin
+ if Assigned(ScoreDB) then
+ Exit;
+
+ Log.LogStatus('Initializing database: "'+Filename+'"', 'TDataBaseSystem.Init');
+
+ try
+
+ // Open Database
+ ScoreDB := TSQLiteDatabase.Create(Filename);
+ fFilename := Filename;
+
+ // Close and delete outdated file
+ Version := GetVersion();
+ if ((Version <> 0) and (Version <> cDBVersion)) then
+ begin
+ Log.LogInfo('Outdated cover-database file found', 'TDataBaseSystem.Init');
+ // Close and delete outdated file
+ ScoreDB.Free;
+ if (not DeleteFile(Filename)) then
+ raise Exception.Create('Could not delete ' + Filename);
+ // Reopen
+ ScoreDB := TSQLiteDatabase.Create(Filename);
+ Version := 0;
+ end;
+
+ // Set version number after creation
+ if (Version = 0) then
+ SetVersion(cDBVersion);
+
+
+ // 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, ' +
+ '[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 IF NOT EXISTS ['+cUS_Statistics_Info+'] (' +
+ '[ResetTime] INTEGER' +
+ ');');
+ // insert creation timestamp
+ ScoreDB.ExecSQL(Format('INSERT INTO ['+cUS_Statistics_Info+'] ' +
+ '([ResetTime]) VALUES(%d);',
+ [DateTimeToUnix(Now())]));
+ end;
+
+ except
+ on E: Exception do
+ begin
+ Log.LogError(E.Message, 'TDataBaseSystem.Init');
+ FreeAndNil(ScoreDB);
+ end;
+ end;
+
+end;
+
+(**
+ * Frees Database
+ *)
+destructor TDataBaseSystem.Destroy;
+begin
+ Log.LogInfo('TDataBaseSystem.Free', 'TDataBaseSystem.Destroy');
+ ScoreDB.Free;
+ inherited;
+end;
+
+(**
+ * Read Scores into SongArray
+ *)
+procedure TDataBaseSystem.ReadScore(Song: TSong);
+var
+ TableData: TSQLiteUniTable;
+ Difficulty: Integer;
+begin
+ if not Assigned(ScoreDB) then
+ Exit;
+
+ TableData := nil;
+
+ try
+ // Search Song in DB
+ TableData := ScoreDB.GetUniTable(
+ '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 := 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.FieldByName['Player']);
+ Song.Score[Difficulty, High(Song.Score[Difficulty])].Score :=
+ TableData.FieldAsInteger(TableData.FieldIndex['Score']);
+ 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;
+
+ TableData.Free;
+end;
+
+(**
+ * Adds one new score to DB
+ *)
+procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer);
+var
+ ID: Integer;
+ TableData: TSQLiteTable;
+begin
+ if not Assigned(ScoreDB) then
+ Exit;
+
+ // Prevent 0 Scores from being added
+ if (Score <= 0) then
+ Exit;
+
+ TableData := nil;
+
+ try
+
+ ID := ScoreDB.GetTableValue(
+ 'SELECT [ID] FROM ['+cUS_Songs+'] ' +
+ 'WHERE [Artist] = ? AND [Title] = ?',
+ [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]);
+ if (ID = 0) then
+ begin
+ // 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)]);
+ // Get song-ID
+ ID := ScoreDB.GetLastInsertRowID();
+ end;
+ // 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
+ // 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
+ 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.FieldByName['Score'],
+ [TableData.FieldByName['Player']]);
+
+ TableData.Next;
+ end;
+
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.AddScore');
+ end;
+
+ TableData.Free;
+end;
+
+(**
+ * 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
+ 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;
+
+(**
+ * Writes some stats to array.
+ * Returns nil if the database is not ready or a list with zero or more statistic
+ * entries.
+ * Free the result-list with FreeStats() after usage to avoid memory leaks.
+ *)
+function TDataBaseSystem.GetStats(Typ: TStatType; Count: Byte; Page: Cardinal; Reversed: Boolean): TList;
+var
+ Query: String;
+ TableData: TSQLiteUniTable;
+ Stat: TStatResult;
+begin
+ Result := nil;
+
+ if not Assigned(ScoreDB) then
+ Exit;
+
+ {Todo: Add Prevention that only players with more than 5 scores are selected at type 2}
+
+ // Create query
+ case Typ of
+ stBestScores: begin
+ Query := 'SELECT [Player], [Difficulty], [Score], [Artist], [Title] FROM ['+cUS_Scores+'] ' +
+ 'INNER JOIN ['+cUS_Songs+'] ON ([SongID] = [ID]) ORDER BY [Score]';
+ end;
+ stBestSingers: begin
+ Query := 'SELECT [Player], ROUND(AVG([Score])) FROM ['+cUS_Scores+'] ' +
+ 'GROUP BY [Player] ORDER BY AVG([Score])';
+ end;
+ stMostSungSong: begin
+ Query := 'SELECT [Artist], [Title], [TimesPlayed] FROM ['+cUS_Songs+'] ' +
+ 'ORDER BY [TimesPlayed]';
+ end;
+ stMostPopBand: begin
+ Query := 'SELECT [Artist], SUM([TimesPlayed]) FROM ['+cUS_Songs+'] ' +
+ 'GROUP BY [Artist] ORDER BY SUM([TimesPlayed])';
+ end;
+ end;
+
+ // Add order direction
+ Query := Query + IfThen(Reversed, ' ASC', ' DESC');
+
+ // Add limit
+ Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';';
+
+ // Execute query
+ try
+ TableData := ScoreDB.GetUniTable(Query);
+ except
+ on E: Exception do
+ begin
+ Log.LogError(E.Message, 'TDataBaseSystem.GetStats');
+ Exit;
+ end;
+ end;
+
+ Result := TList.Create;
+ Stat := nil;
+
+ // Copy result to stats array
+ while not TableData.EOF do
+ begin
+ case Typ of
+ stBestScores: begin
+ Stat := TStatResultBestScores.Create;
+ with TStatResultBestScores(Stat) do
+ begin
+ Singer := UTF8Decode(TableData.Fields[0]);
+ Difficulty := TableData.FieldAsInteger(1);
+ Score := TableData.FieldAsInteger(2);
+ SongArtist := UTF8Decode(TableData.Fields[3]);
+ SongTitle := UTF8Decode(TableData.Fields[4]);
+ end;
+ end;
+ stBestSingers: begin
+ Stat := TStatResultBestSingers.Create;
+ with TStatResultBestSingers(Stat) do
+ begin
+ Player := UTF8Decode(TableData.Fields[0]);
+ AverageScore := TableData.FieldAsInteger(1);
+ end;
+ end;
+ stMostSungSong: begin
+ Stat := TStatResultMostSungSong.Create;
+ with TStatResultMostSungSong(Stat) do
+ begin
+ Artist := UTF8Decode(TableData.Fields[0]);
+ Title := UTF8Decode(TableData.Fields[1]);
+ TimesSung := TableData.FieldAsInteger(2);
+ end;
+ end;
+ stMostPopBand: begin
+ Stat := TStatResultMostPopBand.Create;
+ with TStatResultMostPopBand(Stat) do
+ begin
+ ArtistName := UTF8Decode(TableData.Fields[0]);
+ TimesSungTot := TableData.FieldAsInteger(1);
+ end;
+ end
+ else
+ Log.LogCritical('Unknown stat-type', 'TDataBaseSystem.GetStats');
+ end;
+
+ Stat.Typ := Typ;
+ Result.Add(Stat);
+
+ TableData.Next;
+ end;
+
+ TableData.Free;
+end;
+
+procedure TDataBaseSystem.FreeStats(StatList: TList);
+var
+ I: integer;
+begin
+ if (StatList = nil) then
+ Exit;
+ for I := 0 to StatList.Count-1 do
+ TStatResult(StatList[I]).Free;
+ StatList.Free;
+end;
+
+(**
+ * Gets total number of entrys for a stats query
+ *)
+function TDataBaseSystem.GetTotalEntrys(Typ: TStatType): Cardinal;
+var
+ Query: String;
+begin
+ Result := 0;
+
+ if not Assigned(ScoreDB) then
+ Exit;
+
+ try
+ // Create query
+ case Typ of
+ 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);
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.GetTotalEntrys');
+ end;
+
+end;
+
+(**
+ * Gets reset date of statistic data
+ *)
+function TDataBaseSystem.GetStatReset: TDateTime;
+var
+ Query: string;
+ ResetTime: int64;
+begin
+ Result := 0;
+
+ if not Assigned(ScoreDB) then
+ Exit;
+
+ try
+ Query := 'SELECT [ResetTime] FROM ['+cUS_Statistics_Info+'];';
+ Result := UnixToDateTime(ScoreDB.GetTableValue(Query));
+ except on E: Exception do
+ Log.LogError(E.Message, 'TDataBaseSystem.GetStatReset');
+ end;
+end;
+
+function TDataBaseSystem.GetVersion(): integer;
+begin
+ Result := ScoreDB.GetTableValue('PRAGMA user_version');
+end;
+
+procedure TDataBaseSystem.SetVersion(Version: integer);
+begin
+ ScoreDB.ExecSQL(Format('PRAGMA user_version = %d', [Version]));
+end;
+
+end.