1 files changed, 0 insertions, 614 deletions
diff --git a/src/base/UDataBase.pas b/src/base/UDataBase.pas
deleted file mode 100644
index 85b4b8e8..00000000
--- a/src/base/UDataBase.pas
+++ /dev/null
@@ -1,614 +0,0 @@
-{* UltraStar Deluxe - Karaoke Game
- *
- * UltraStar Deluxe is the legal property of its developers, whose names
- * are too numerous to list here. Please refer to the COPYRIGHT
- * file distributed with this source distribution.
- *
- * This program is free software; you can redistribute it and/or
- * modify it under the terms of the GNU General Public License
- * as published by the Free Software Foundation; either version 2
- * of the License, or (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public License
- * along with this program; see the file COPYING. If not, write to
- * the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
- * Boston, MA 02110-1301, USA.
- *
- * $URL$
- * $Id$
- *}
-
-unit UDataBase;
-
-interface
-
-{$IFDEF FPC}
-  {$MODE Delphi}
-{$ENDIF}
-
-{$I switches.inc}
-
-uses
-  Classes,
-  SQLiteTable3,
-  UPath,
-  USong,
-  USongs;
-
-//--------------------
-//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:       UTF8String;
-      Score:        word;
-      Difficulty:   byte;
-      SongArtist:   UTF8String;
-      SongTitle:    UTF8String;
-      Date:         UTF8String;
-  end;
-
-  TStatResultBestSingers = class(TStatResult)
-    public
-      Player:       UTF8String;
-      AverageScore: word;
-  end;
-
-  TStatResultMostSungSong = class(TStatResult)
-    public
-      Artist:       UTF8String;
-      Title:        UTF8String;
-      TimesSung:    word;
-  end;
-
-  TStatResultMostPopBand = class(TStatResult)
-    public
-      ArtistName:   UTF8String;
-      TimesSungTot: word;
-  end;
-
-
-  TDataBaseSystem = class
-    private
-      ScoreDB:      TSQLiteDatabase;
-      fFilename: IPath;
-
-      function GetVersion(): integer;
-      procedure SetVersion(Version: integer);
-    public
-      property Filename: IPath read fFilename;
-
-      destructor Destroy; override;
-
-      procedure Init(const Filename: IPath);
-      procedure ReadScore(Song: TSong);
-      procedure AddScore(Song: TSong; Level: integer; const Name: UTF8String; 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;
-      function FormatDate(time_stamp: integer): UTF8String;
-  end;
-
-var
-  DataBase: TDataBaseSystem;
-
-implementation
-
-uses
-  DateUtils,
-  ULanguage,
-  StrUtils,
-  SysUtils,
-  ULog;
-
-{
- cDBVersion - history
- 0 = USDX 1.01 or no Database
- 01 = USDX 1.1
-}
-const
-  cDBVersion = 01; // 0.1
-  cUS_Scores = 'us_scores';
-  cUS_Songs  = 'us_songs';
-  cUS_Statistics_Info = 'us_statistics_info';
-
-(**
- * Open database and create tables if they do not exist
- *)
-procedure TDataBaseSystem.Init(const Filename: IPath);
-var
-  Version:            integer;
-  finalizeConversion: boolean;
-begin
-  if Assigned(ScoreDB) then
-    Exit;
-
-  Log.LogStatus('Initializing database: "' + Filename.ToNative + '"', 'TDataBaseSystem.Init');
-
-  try
-
-    // open database
-    ScoreDB   := TSQLiteDatabase.Create(Filename.ToUTF8);
-    fFilename := Filename;
-
-    Version := GetVersion();
-
-    // add Table cUS_Statistics_Info
-    // needed in the conversion from 1.01 to 1.1
-    if not ScoreDB.TableExists(cUS_Statistics_Info) then
-    begin
-      Log.LogInfo('Outdated song database found - missing table"' + cUS_Statistics_Info + '"', 'TDataBaseSystem.Init');
-      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;
-
-    // convert data from 1.01 to 1.1
-    // part #1 - prearrangement
-    finalizeConversion := false;
-    if (Version = 0) AND ScoreDB.TableExists('US_Scores') then
-    begin
-      // rename old tables - to be able to insert new table structures
-      ScoreDB.ExecSQL('ALTER TABLE US_Scores RENAME TO us_scores_101;');
-      ScoreDB.ExecSQL('ALTER TABLE US_Songs RENAME TO us_songs_101;');
-      finalizeConversion := true; // means: conversion has to be done!
-    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, ' +
-                      '[Date] INTEGER 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, ' +
-                      '[Rating] INTEGER NULL' +
-                    ');');
-
-    // convert data from 1.01 to 1.1
-    // part #2 - accomplishment
-    if finalizeConversion then
-    begin
-      Log.LogInfo('Outdated song database  found - begin conversion from V1.01 to V1.1', 'TDataBaseSystem.Init');
-      // insert old values into new db-schemes (/tables)
-      ScoreDB.ExecSQL('INSERT INTO ' + cUS_Scores + ' SELECT  SongID, Difficulty, Player, Score FROM us_scores_101;');
-      ScoreDB.ExecSQL('INSERT INTO ' + cUS_Songs + ' SELECT  ID, Artist, Title, TimesPlayed, ''NULL'' FROM us_songs_101;');
-      //now drop old tables
-      ScoreDB.ExecSQL('DROP TABLE us_scores_101;');
-      ScoreDB.ExecSQL('DROP TABLE us_songs_101;');
-    end;
-
-    // add column rating to cUS_Songs
-    // just for users of nightly builds and developers!
-    if not ScoreDB.ContainsColumn(cUS_Songs, 'Rating') then
-    begin
-      Log.LogInfo('Outdated song database found - adding column rating to "' + cUS_Songs + '"', 'TDataBaseSystem.Init');
-      ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Songs + ' ADD COLUMN [Rating] INTEGER NULL');
-    end;
-
-
-    //add column date to cUS-Scores
-    if not ScoreDB.ContainsColumn(cUS_Scores, 'Date') then
-    begin
-      Log.LogInfo('adding column date to "' + cUS_Scores + '"', 'TDataBaseSystem.Init');
-      ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Scores + ' ADD COLUMN [Date] INTEGER NULL');
-    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;
-
-(**
- * Format a UNIX-Timestamp into DATE (If 0 then '')
- *)
-function TDataBaseSystem.FormatDate(time_stamp: integer): UTF8String;
-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);
-  end;
-end;
-
-
-(**
- * Read Scores into SongArray
- *)
-procedure TDataBaseSystem.ReadScore(Song: TSong);
-var
-  TableData:  TSQLiteUniTable;
-  Difficulty: integer;
-  I: integer;
-  PlayerListed: boolean;
-begin
-  if not Assigned(ScoreDB) then
-    Exit;
-
-  TableData := nil;
-  try
-    // Search Song in DB
-    TableData := ScoreDB.GetUniTable(
-      'SELECT [Difficulty], [Player], [Score], [Date] FROM [' + cUS_Scores + '] ' +
-      'WHERE [SongID] = (' +
-        'SELECT [ID] FROM [' + cUS_Songs + '] ' +
-        'WHERE [Artist] = ? AND [Title] = ? ' +
-        'LIMIT 1) ' +
-      'ORDER BY [Score] DESC;', //no LIMIT! see filter below!
-      [Song.Artist, Song.Title]);
-
-    // 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 := TableData.FieldAsInteger(TableData.FieldIndex['Difficulty']);
-      if ((Difficulty >= 0) and (Difficulty <= 2)) and
-         (Length(Song.Score[Difficulty]) < 5) 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.FieldByName['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.FieldByName['Player'];
-          Song.Score[Difficulty, High(Song.Score[Difficulty])].Score :=
-            TableData.FieldAsInteger(TableData.FieldIndex['Score']);
-          Song.Score[Difficulty, High(Song.Score[Difficulty])].Date :=
-            FormatDate(TableData.FieldAsInteger(TableData.FieldIndex['Date']));
-        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;
-
-  TableData.Free;
-end;
-
-(**
- * Adds one new score to DB
- *)
-procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; const Name: UTF8String; Score: integer);
-var
-  ID:        integer;
-  TableData: TSQLiteTable;
-begin
-  if not Assigned(ScoreDB) then
-    Exit;
-
-  // Prevent 0 Scores from being added EDIT: ==> UScreenTop5.pas!
-  //if (Score <= 0) then
-  //  Exit;
-
-  TableData := nil;
-
-  try
-
-    ID := ScoreDB.GetTableValue(
-        'SELECT [ID] FROM [' + cUS_Songs + '] ' +
-        'WHERE [Artist] = ? AND [Title] = ?',
-        [Song.Artist, 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);',
-          [Song.Artist, Song.Title]);
-      // Get song-ID
-      ID := ScoreDB.GetLastInsertRowID();
-    end;
-    // Create new entry
-    ScoreDB.ExecSQL(
-      'INSERT INTO [' + cUS_Scores + '] ' +
-      '([SongID] ,[Difficulty], [Player], [Score], [Date]) VALUES ' +
-      '(?, ?, ?, ?, ?);',
-      [ID, Level, Name, Score, DateTimeToUnix(Now())]);
-
-  except on E: Exception do
-    Log.LogError(E.Message, 'TDataBaseSystem.AddScore');
-  end;
-
-  TableData.Free;
-end;
-
-(**
- * Not needed with new system.
- * Used to 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] = ?;',
-        [Song.Title, 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], [Date] 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 := TableData.Fields[0];
-          Difficulty := TableData.FieldAsInteger(1);
-          Score := TableData.FieldAsInteger(2);
-          SongArtist := TableData.Fields[3];
-          SongTitle := TableData.Fields[4];
-          Date := FormatDate(TableData.FieldAsInteger(5));
-        end;
-      end;
-      stBestSingers: begin
-        Stat := TStatResultBestSingers.Create;
-        with TStatResultBestSingers(Stat) do
-        begin
-          Player := TableData.Fields[0];
-          AverageScore := TableData.FieldAsInteger(1);
-        end;
-      end;
-      stMostSungSong: begin
-        Stat := TStatResultMostSungSong.Create;
-        with TStatResultMostSungSong(Stat) do
-        begin
-          Artist := TableData.Fields[0];
-          Title  := TableData.Fields[1];
-          TimesSung  := TableData.FieldAsInteger(2);
-        end;
-      end;
-      stMostPopBand: begin
-        Stat := TStatResultMostPopBand.Create;
-        with TStatResultMostPopBand(Stat) do
-        begin
-          ArtistName := 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
-  Index: integer;
-begin
-  if (StatList = nil) then
-    Exit;
-  for Index := 0 to StatList.Count-1 do
-    TStatResult(StatList[Index]).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;
-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.