diff options
author | whiteshark0 <whiteshark0@b956fd51-792f-4845-bead-9b4dfca2ff2c> | 2009-12-11 17:34:54 +0000 |
---|---|---|
committer | whiteshark0 <whiteshark0@b956fd51-792f-4845-bead-9b4dfca2ff2c> | 2009-12-11 17:34:54 +0000 |
commit | 1ab628e8ad6c85c8f1b562f10480253ee3e622b7 (patch) | |
tree | d21621f68850ecd7762137e1c4387fa15731a811 /Lua/src/base/UDataBase.pas | |
parent | 6ec275387c320d3d9a8f5b6fe185687643565b8c (diff) | |
download | usdx-1ab628e8ad6c85c8f1b562f10480253ee3e622b7.tar.gz usdx-1ab628e8ad6c85c8f1b562f10480253ee3e622b7.tar.xz usdx-1ab628e8ad6c85c8f1b562f10480253ee3e622b7.zip |
merged trunk into lua branch
plugin loading is disabled atm because of a bug reading the files (lua may be the reason).
Reading the files in usdx and passing the contents to lua may solve this
git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/branches/experimental@2019 b956fd51-792f-4845-bead-9b4dfca2ff2c
Diffstat (limited to '')
-rw-r--r-- | Lua/src/base/UDataBase.pas | 352 |
1 files changed, 204 insertions, 148 deletions
diff --git a/Lua/src/base/UDataBase.pas b/Lua/src/base/UDataBase.pas index 0f9d88a7..85b4b8e8 100644 --- a/Lua/src/base/UDataBase.pas +++ b/Lua/src/base/UDataBase.pas @@ -34,20 +34,21 @@ interface {$I switches.inc} uses - USongs, - USong, Classes, - SQLiteTable3; + SQLiteTable3, + UPath, + USong, + USongs; //-------------------- -//DataBaseSystem - Class including all DB Methods +//DataBaseSystem - Class including all DB methods //-------------------- type TStatType = ( - stBestScores, // Best Scores - stBestSingers, // Best Singers - stMostSungSong, // Most sung Songs - stMostPopBand // Most popular Band + stBestScores, // Best scores + stBestSingers, // Best singers + stMostSungSong, // Most sung songs + stMostPopBand // Most popular band ); // abstract super-class for statistic results @@ -58,54 +59,56 @@ type TStatResultBestScores = class(TStatResult) public - Singer: WideString; - Score: Word; - Difficulty: Byte; - SongArtist: WideString; - SongTitle: WideString; + Singer: UTF8String; + Score: word; + Difficulty: byte; + SongArtist: UTF8String; + SongTitle: UTF8String; + Date: UTF8String; end; TStatResultBestSingers = class(TStatResult) public - Player: WideString; - AverageScore: Word; + Player: UTF8String; + AverageScore: word; end; TStatResultMostSungSong = class(TStatResult) public - Artist: WideString; - Title: WideString; - TimesSung: Word; + Artist: UTF8String; + Title: UTF8String; + TimesSung: word; end; TStatResultMostPopBand = class(TStatResult) public - ArtistName: WideString; - TimesSungTot: Word; + ArtistName: UTF8String; + TimesSungTot: word; end; - + TDataBaseSystem = class private - ScoreDB: TSQLiteDatabase; - fFilename: string; + ScoreDB: TSQLiteDatabase; + fFilename: IPath; function GetVersion(): integer; procedure SetVersion(Version: integer); public - property Filename: string read fFilename; + property Filename: IPath read fFilename; destructor Destroy; override; - procedure Init(const Filename: string); + procedure Init(const Filename: IPath); procedure ReadScore(Song: TSong); - procedure AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer); + 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; + function GetStats(Typ: TStatType; Count: byte; Page: cardinal; Reversed: boolean): TList; procedure FreeStats(StatList: TList); - function GetTotalEntrys(Typ: TStatType): Cardinal; + function GetTotalEntrys(Typ: TStatType): cardinal; function GetStatReset: TDateTime; + function FormatDate(time_stamp: integer): UTF8String; end; var @@ -114,53 +117,72 @@ var implementation uses - ULog, DateUtils, + ULanguage, StrUtils, - SysUtils; - + 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'; + cUS_Statistics_Info = 'us_statistics_info'; (** - * Opens Database and Create Tables if not Exist + * Open database and create tables if they do not exist *) -procedure TDataBaseSystem.Init(const Filename: string); +procedure TDataBaseSystem.Init(const Filename: IPath); var - Version: integer; + Version: integer; + finalizeConversion: boolean; begin if Assigned(ScoreDB) then Exit; - Log.LogStatus('Initializing database: "'+Filename+'"', 'TDataBaseSystem.Init'); + Log.LogStatus('Initializing database: "' + Filename.ToNative + '"', 'TDataBaseSystem.Init'); try - - // Open Database - ScoreDB := TSQLiteDatabase.Create(Filename); + + // open database + ScoreDB := TSQLiteDatabase.Create(Filename.ToUTF8); fFilename := Filename; - // Close and delete outdated file Version := GetVersion(); - if ((Version <> 0) and (Version <> cDBVersion)) then + + // 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 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; + 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, @@ -169,30 +191,49 @@ begin // 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+'] (' + + 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' + + '[Score] INTEGER NOT NULL, ' + + '[Date] INTEGER NULL' + ');'); - ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS ['+cUS_Songs+'] (' + + 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' + + '[TimesPlayed] INTEGER NOT NULL, ' + + '[Rating] INTEGER NULL' + ');'); - if not ScoreDB.TableExists(cUS_Statistics_Info) then + // convert data from 1.01 to 1.1 + // part #2 - accomplishment + if finalizeConversion 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())])); + 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 @@ -216,33 +257,55 @@ begin 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; + 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] FROM ['+cUS_Scores+'] ' + + 'SELECT [Difficulty], [Player], [Score], [Date] FROM [' + cUS_Scores + '] ' + 'WHERE [SongID] = (' + - 'SELECT [ID] FROM ['+cUS_Songs+'] ' + + 'SELECT [ID] FROM [' + cUS_Songs + '] ' + 'WHERE [Artist] = ? AND [Title] = ? ' + 'LIMIT 1) ' + - 'ORDER BY [Score] DESC LIMIT 15', - [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]); + 'ORDER BY [Score] DESC;', //no LIMIT! see filter below! + [Song.Artist, Song.Title]); // Empty Old Scores - SetLength(Song.Score[0], 0); - SetLength(Song.Score[1], 0); - SetLength(Song.Score[2], 0); + 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 @@ -252,12 +315,31 @@ begin if ((Difficulty >= 0) and (Difficulty <= 2)) and (Length(Song.Score[Difficulty]) < 5) then begin - SetLength(Song.Score[Difficulty], Length(Song.Score[Difficulty]) + 1); + //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; - Song.Score[Difficulty, High(Song.Score[Difficulty])].Name := - UTF8Decode(TableData.FieldByName['Player']); - Song.Score[Difficulty, High(Song.Score[Difficulty])].Score := + 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; @@ -277,70 +359,43 @@ end; (** * Adds one new score to DB *) -procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; const Name: WideString; Score: integer); +procedure TDataBaseSystem.AddScore(Song: TSong; Level: integer; const Name: UTF8String; Score: integer); var - ID: Integer; + ID: integer; TableData: TSQLiteTable; begin if not Assigned(ScoreDB) then Exit; - // Prevent 0 Scores from being added - if (Score <= 0) 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+'] ' + + 'SELECT [ID] FROM [' + cUS_Songs + '] ' + 'WHERE [Artist] = ? AND [Title] = ?', - [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]); + [Song.Artist, Song.Title]); if (ID = 0) then begin // Create song if it does not exist ScoreDB.ExecSQL( - 'INSERT INTO ['+cUS_Songs+'] ' + + 'INSERT INTO [' + cUS_Songs + '] ' + '([ID], [Artist], [Title], [TimesPlayed]) VALUES ' + '(NULL, ?, ?, 0);', - [UTF8Encode(Song.Artist), UTF8Encode(Song.Title)]); + [Song.Artist, 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; + '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'); @@ -350,21 +405,21 @@ begin end; (** - * Not needed with new System. - * Used for increment played count + * 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+'] ' + + 'UPDATE [' + cUS_Songs + '] ' + 'SET [TimesPlayed] = [TimesPlayed] + 1 ' + 'WHERE [Title] = ? AND [Artist] = ?;', - [UTF8Encode(Song.Title), UTF8Encode(Song.Artist)]); + [Song.Title, Song.Artist]); except on E: Exception do Log.LogError(E.Message, 'TDataBaseSystem.WriteScore'); end; @@ -376,11 +431,11 @@ end; * 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; +function TDataBaseSystem.GetStats(Typ: TStatType; Count: byte; Page: cardinal; Reversed: boolean): TList; var - Query: String; + Query: string; TableData: TSQLiteUniTable; - Stat: TStatResult; + Stat: TStatResult; begin Result := nil; @@ -392,19 +447,19 @@ begin // 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]'; + 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+'] ' + + 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+'] ' + + Query := 'SELECT [Artist], [Title], [TimesPlayed] FROM [' + cUS_Songs + '] ' + 'ORDER BY [TimesPlayed]'; end; stMostPopBand: begin - Query := 'SELECT [Artist], SUM([TimesPlayed]) FROM ['+cUS_Songs+'] ' + + Query := 'SELECT [Artist], SUM([TimesPlayed]) FROM [' + cUS_Songs + '] ' + 'GROUP BY [Artist] ORDER BY SUM([TimesPlayed])'; end; end; @@ -437,18 +492,19 @@ begin Stat := TStatResultBestScores.Create; with TStatResultBestScores(Stat) do begin - Singer := UTF8Decode(TableData.Fields[0]); + Singer := TableData.Fields[0]; Difficulty := TableData.FieldAsInteger(1); Score := TableData.FieldAsInteger(2); - SongArtist := UTF8Decode(TableData.Fields[3]); - SongTitle := UTF8Decode(TableData.Fields[4]); + 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 := UTF8Decode(TableData.Fields[0]); + Player := TableData.Fields[0]; AverageScore := TableData.FieldAsInteger(1); end; end; @@ -456,8 +512,8 @@ begin Stat := TStatResultMostSungSong.Create; with TStatResultMostSungSong(Stat) do begin - Artist := UTF8Decode(TableData.Fields[0]); - Title := UTF8Decode(TableData.Fields[1]); + Artist := TableData.Fields[0]; + Title := TableData.Fields[1]; TimesSung := TableData.FieldAsInteger(2); end; end; @@ -465,7 +521,7 @@ begin Stat := TStatResultMostPopBand.Create; with TStatResultMostPopBand(Stat) do begin - ArtistName := UTF8Decode(TableData.Fields[0]); + ArtistName := TableData.Fields[0]; TimesSungTot := TableData.FieldAsInteger(1); end; end @@ -484,21 +540,21 @@ end; procedure TDataBaseSystem.FreeStats(StatList: TList); var - I: integer; + Index: integer; begin if (StatList = nil) then Exit; - for I := 0 to StatList.Count-1 do - TStatResult(StatList[I]).Free; + 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; +function TDataBaseSystem.GetTotalEntrys(Typ: TStatType): cardinal; var - Query: String; + Query: string; begin Result := 0; @@ -509,13 +565,13 @@ begin // Create query case Typ of stBestScores: - Query := 'SELECT COUNT([SongID]) FROM ['+cUS_Scores+'];'; + Query := 'SELECT COUNT([SongID]) FROM [' + cUS_Scores + '];'; stBestSingers: - Query := 'SELECT COUNT(DISTINCT [Player]) FROM ['+cUS_Scores+'];'; + Query := 'SELECT COUNT(DISTINCT [Player]) FROM [' + cUS_Scores + '];'; stMostSungSong: - Query := 'SELECT COUNT([ID]) FROM ['+cUS_Songs+'];'; + Query := 'SELECT COUNT([ID]) FROM [' + cUS_Songs + '];'; stMostPopBand: - Query := 'SELECT COUNT(DISTINCT [Artist]) FROM ['+cUS_Songs+'];'; + Query := 'SELECT COUNT(DISTINCT [Artist]) FROM [' + cUS_Songs + '];'; end; Result := ScoreDB.GetTableValue(Query); @@ -538,7 +594,7 @@ begin Exit; try - Query := 'SELECT [ResetTime] FROM ['+cUS_Statistics_Info+'];'; + Query := 'SELECT [ResetTime] FROM [' + cUS_Statistics_Info + '];'; Result := UnixToDateTime(ScoreDB.GetTableValue(Query)); except on E: Exception do Log.LogError(E.Message, 'TDataBaseSystem.GetStatReset'); |