From 95dc3d90b3eb11080fdcd5edae37cbd35c4ffb57 Mon Sep 17 00:00:00 2001 From: whiteshark0 Date: Fri, 23 Mar 2007 17:40:06 +0000 Subject: Some Changes on Database System and Header Reader Fixed Bug: Scores with 0 are added to DB Made a Class instead of many Functions Fixed a Bug in UFiles, not reading Gap from Header correctly git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@20 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 163 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 163 insertions(+) create mode 100644 Game/Code/Classes/UDataBase.pas (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas new file mode 100644 index 00000000..1ebc18db --- /dev/null +++ b/Game/Code/Classes/UDataBase.pas @@ -0,0 +1,163 @@ +unit UDataBase; + +interface + +uses USongs, SQLiteTable3; + +//-------------------- +//DataBaseSystem - Class including all DB Methods +//-------------------- +type + TDataBaseSystem = class + private + ScoreDB: TSqliteDatabase; + sFilename: string; + public + + + Destructor Free; + + Procedure Init(const Filename: string); + procedure ReadScore(var Song: TSong); + procedure AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); + procedure WriteScore(var Song: TSong); + end; + +var + DataBase: TDataBaseSystem; + +implementation + +uses IniFiles, SysUtils; + +//-------------------- +//Create - Opens Database and Create Tables if not Exist +//-------------------- + +Procedure TDataBaseSystem.Init(const Filename: string); +begin + //Open Database + ScoreDB := TSqliteDatabase.Create(Filename); + sFilename := Filename; + + try + //Look for Tables => When not exist Create them + if not ScoreDB.TableExists('US_Scores') then + ScoreDB.execsql('CREATE TABLE `US_Scores` (`SongID` INT( 11 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );'); + + if not ScoreDB.TableExists('US_Songs') then + ScoreDB.execsql('CREATE TABLE `US_Songs` (`ID` INTEGER PRIMARY KEY, `Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );'); + //Not possible because of String Limitation to 255 Chars //Need to rewrite Wrapper + {if not ScoreDB.TableExists('US_SongCache') then + ScoreDB.ExecSQL('CREATE TABLE `US_SongCache` (`Path` VARCHAR( 255 ) NOT NULL , `Filename` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `Artist` VARCHAR( 255 ) NOT NULL , `Folder` VARCHAR( 255 ) NOT NULL , `Genre` VARCHAR( 255 ) NOT NULL , `Edition` VARCHAR( 255 ) NOT NULL , `Language` VARCHAR( 255 ) NOT NULL , `Creator` VARCHAR( 255 ) NOT NULL , `Cover` VARCHAR( 255 ) NOT NULL , `Background` VARCHAR( 255 ) NOT NULL , `Video` VARCHAR( 255 ) NOT NULL , `VideoGap` FLOAT NOT NULL , `Gap` FLOAT NOT NULL , `Start` FLOAT NOT NULL , `Finish` INT( 11 ) NOT NULL , `BPM` INT( 5 ) NOT NULL , `Relative` BOOLEAN NOT NULL , `NotesGap` INT( 11 ) NOT NULL);');} + + + finally + //ScoreDB.Free; + end; + +end; + +//-------------------- +//Free - Frees Database +//-------------------- +Destructor TDataBaseSystem.Free; +begin + ScoreDB.Free; +end; + +//-------------------- +//ReadScore - Read Scores into SongArray +//-------------------- +procedure TDataBaseSystem.ReadScore(var Song: TSong); +var + TableData: TSqliteTable; + Dif: Byte; +begin + //ScoreDB := TSqliteDatabase.Create(sFilename); + try + try + //Search Song in DB + TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score` FROM `us_scores` WHERE `SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '" LIMIT 1) ORDER BY `Score` DESC LIMIT 15'); + //Empty Old Scores + SetLength (Song.Score[0], 0); + SetLength (Song.Score[1], 0); + SetLength (Song.Score[2], 0); + + while not TableData.Eof do//Go through all Entrys + begin//Add one Entry to Array + Dif := StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Difficulty'])); + if (Dif>=0) AND (Dif<=2) then + begin + SetLength(Song.Score[Dif], Length(Song.Score[Dif]) + 1); + + Song.Score[Dif, high(Song.Score[Dif])].Name := TableData.FieldAsString(TableData.FieldIndex['Player']); + Song.Score[Dif, high(Song.Score[Dif])].Score:= StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score'])); + end; + TableData.Next; + end; + + except //Im Fehlerfall + for Dif := 0 to 2 do + begin + SetLength(Song.Score[Dif], 1); + Song.Score[Dif, 1].Name := 'Error Reading ScoreDB'; + end; + end; + finally + //ScoreDb.Free; + end; +end; + +//-------------------- +//AddScore - Add one new Score to DB +//-------------------- +procedure TDataBaseSystem.AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); +var +ID: Integer; +TableData: TSqliteTable; +begin + //ScoreDB := TSqliteDatabase.Create(sFilename); + try + //Prevent 0 Scores from being added + if (Score > 0) then + begin + + ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); + if ID = 0 then //Song doesn't exist -> Create + begin + ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed` ) VALUES (NULL , "' + Song.Artist + '", "' + Song.Title + '", "0");'); + ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); + if ID = 0 then //Could not Create Table + exit; + end; + //Create new Entry + ScoreDB.ExecSQL('INSERT INTO `US_Scores` ( `SongID` , `Difficulty` , `Player` , `Score` ) VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' + Name + '", "' + InttoStr(Score) + '");'); + + //Delete Last Position when there are more than 5 Entrys + if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then + begin + TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` ASC LIMIT 1'); + ScoreDB.ExecSQL('DELETE FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" AND `Player` = "' + TableData.FieldAsString(TableData.FieldIndex['Player']) + '" AND `Score` = "' + TableData.FieldAsString(TableData.FieldIndex['Score']) + '"'); + end; + + end; + finally + //ScoreDB.Free; + end; +end; + +//-------------------- +//WriteScore - Not needed with new System; But used for Increment Played Count +//-------------------- +procedure TDataBaseSystem.WriteScore(var Song: TSong); +begin + try + //Increase TimesPlayed + ScoreDB.ExecSQL ('UPDATE `us_songs` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";'); + except + + end; +end; + +end. -- cgit v1.2.3 From 6cdcfb402ce738dfc77b008fcb98fd1cda691eb5 Mon Sep 17 00:00:00 2001 From: whiteshark0 Date: Thu, 19 Apr 2007 18:53:22 +0000 Subject: Added Statistic Screens to C0de and to Theme Moved some Translated Strings from UScreenPartyOptions to UTheme to use it with the Statistic Screens, too. Fixed use of /n Tag istead of the correct \n git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@118 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 131 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 131 insertions(+) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index 1ebc18db..0e5a4a3f 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -8,6 +8,26 @@ uses USongs, SQLiteTable3; //DataBaseSystem - Class including all DB Methods //-------------------- type + TStatResult = record + Case Typ: Byte of + 0: (Singer: ShortString; + Score: Word; + Difficulty: Byte; + SongArtist: ShortString; + SongTitle: ShortString); + + 1: (Player: ShortString; + AverageScore: Word); + + 2: (Artist: ShortString; + Title: ShortString; + TimesSung: Word); + + 3: (ArtistName: ShortString; + TimesSungtot: Word); + end; + AStatResult = Array of TStatResult; + TDataBaseSystem = class private ScoreDB: TSqliteDatabase; @@ -15,12 +35,17 @@ type public + property Filename: String read sFilename; + Destructor Free; Procedure Init(const Filename: string); procedure ReadScore(var Song: TSong); procedure AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); procedure WriteScore(var Song: TSong); + + Function GetStats(var Stats: AStatResult; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean; + Function GetTotalEntrys(const Typ: Byte): Cardinal; end; var @@ -160,4 +185,110 @@ 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; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean; +var + Query: String; + TableData: TSqliteTable; +begin + Result := False; + + if (Length(Stats) < Count) then + Exit; + + {Todo: + Add Prevention that only Players with more than 5 Scores are Selected at Typ 2} + + //Create Query + Case Typ of + 0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` FROM `US_Scores` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`'; + 1: Query := 'SELECT `Player` , (Sum(`Score`) / COUNT(`Score`)) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; + 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `US_Songs` ORDER BY `TimesPlayed`'; + 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `US_Songs` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)'; + end; + + //Add Order Direction + If Reversed then + Query := Query + ' ASC' + else + Query := Query + ' DESC'; + + //Add Limit + Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';'; + + //Execute Query + //try + TableData := ScoreDB.GetTable(Query); + {except + exit; + end;} + + //if Result empty -> Exit + if (TableData.RowCount < 1) then + exit; + + //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 := 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 := TableData.Fields[3]; + Stats[TableData.Row].SongTitle := TableData.Fields[4]; + end; + + 1:begin + Stats[TableData.Row].Player := TableData.Fields[0]; + Stats[TableData.Row].AverageScore := TableData.FieldAsInteger(1); + end; + + 2:begin + Stats[TableData.Row].Artist := TableData.Fields[0]; + Stats[TableData.Row].Title := TableData.Fields[1]; + Stats[TableData.Row].TimesSung := StrtoIntDef(TableData.Fields[2], 0); + end; + + 3:begin + Stats[TableData.Row].ArtistName := TableData.Fields[0]; + Stats[TableData.Row].TimesSungtot := StrtoIntDef(TableData.Fields[1], 0); + end; + + end; + + TableData.Next; + end; + + Result := True; +end; + +//-------------------- +//GetTotalEntrys - Get Total Num of entrys for a Stats Query +//-------------------- +Function TDataBaseSystem.GetTotalEntrys(const Typ: Byte): Cardinal; +var Query: String; +begin + //Create Query + Case Typ of + 0: Query := 'SELECT COUNT(`SongID`) FROM `US_Scores`;'; + 1: Query := 'SELECT COUNT(DISTINCT `Player`) FROM `US_Scores`;'; + 2: Query := 'SELECT COUNT(`ID`) FROM `US_Songs`;'; + 3: Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `US_Songs`;'; + end; + + Result := ScoreDB.GetTableValue(Query); +end; + end. -- cgit v1.2.3 From 5d60d0893e283cd287ffc588774cbf8d995e19f3 Mon Sep 17 00:00:00 2001 From: whiteshark0 Date: Tue, 12 Jun 2007 16:55:47 +0000 Subject: Fixed a Bug in Database System thx to Pr3D@ToR git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@260 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index 0e5a4a3f..b8b41bc1 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -209,7 +209,7 @@ begin //Create Query Case Typ of 0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` FROM `US_Scores` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`'; - 1: Query := 'SELECT `Player` , (Sum(`Score`) / COUNT(`Score`)) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; + 1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `US_Songs` ORDER BY `TimesPlayed`'; 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `US_Songs` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)'; end; @@ -252,7 +252,7 @@ begin 1:begin Stats[TableData.Row].Player := TableData.Fields[0]; - Stats[TableData.Row].AverageScore := TableData.FieldAsInteger(1); + Stats[TableData.Row].AverageScore := StrtoIntDef(TableData.Fields[1], 0); end; 2:begin -- cgit v1.2.3 From 433a1b7339e2bf96f3b0bb4c98b8c799c6540027 Mon Sep 17 00:00:00 2001 From: jaybinks Date: Tue, 18 Sep 2007 13:19:20 +0000 Subject: changes in order to compile in lazarus... minor tidy ups and removal of big old comment blocks.. git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@394 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index b8b41bc1..009d0d63 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -2,6 +2,11 @@ unit UDataBase; interface +{$IFDEF FPC} + {$MODE DELPHI} +{$ENDIF} + + uses USongs, SQLiteTable3; //-------------------- -- cgit v1.2.3 From db82b7e30a1b58b56fdb4bfc6089b47200ca1da1 Mon Sep 17 00:00:00 2001 From: jaybinks Date: Thu, 20 Sep 2007 06:36:58 +0000 Subject: Ultrastar-DX now compiles in linux (using lazarus) Bass etc is commented out.. but it compiles, and im working through the runtime errors. git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@408 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 597 ++++++++++++++++++++-------------------- 1 file changed, 298 insertions(+), 299 deletions(-) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index 009d0d63..deee85c0 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -1,299 +1,298 @@ -unit UDataBase; - -interface - -{$IFDEF FPC} - {$MODE DELPHI} -{$ENDIF} - - -uses USongs, SQLiteTable3; - -//-------------------- -//DataBaseSystem - Class including all DB Methods -//-------------------- -type - TStatResult = record - Case Typ: Byte of - 0: (Singer: ShortString; - Score: Word; - Difficulty: Byte; - SongArtist: ShortString; - SongTitle: ShortString); - - 1: (Player: ShortString; - AverageScore: Word); - - 2: (Artist: ShortString; - Title: ShortString; - TimesSung: Word); - - 3: (ArtistName: ShortString; - TimesSungtot: Word); - end; - AStatResult = Array of TStatResult; - - TDataBaseSystem = class - private - ScoreDB: TSqliteDatabase; - sFilename: string; - public - - - property Filename: String read sFilename; - - Destructor Free; - - Procedure Init(const Filename: string); - procedure ReadScore(var Song: TSong); - procedure AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); - procedure WriteScore(var Song: TSong); - - Function GetStats(var Stats: AStatResult; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean; - Function GetTotalEntrys(const Typ: Byte): Cardinal; - end; - -var - DataBase: TDataBaseSystem; - -implementation - -uses IniFiles, SysUtils; - -//-------------------- -//Create - Opens Database and Create Tables if not Exist -//-------------------- - -Procedure TDataBaseSystem.Init(const Filename: string); -begin - //Open Database - ScoreDB := TSqliteDatabase.Create(Filename); - sFilename := Filename; - - try - //Look for Tables => When not exist Create them - if not ScoreDB.TableExists('US_Scores') then - ScoreDB.execsql('CREATE TABLE `US_Scores` (`SongID` INT( 11 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );'); - - if not ScoreDB.TableExists('US_Songs') then - ScoreDB.execsql('CREATE TABLE `US_Songs` (`ID` INTEGER PRIMARY KEY, `Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );'); - //Not possible because of String Limitation to 255 Chars //Need to rewrite Wrapper - {if not ScoreDB.TableExists('US_SongCache') then - ScoreDB.ExecSQL('CREATE TABLE `US_SongCache` (`Path` VARCHAR( 255 ) NOT NULL , `Filename` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `Artist` VARCHAR( 255 ) NOT NULL , `Folder` VARCHAR( 255 ) NOT NULL , `Genre` VARCHAR( 255 ) NOT NULL , `Edition` VARCHAR( 255 ) NOT NULL , `Language` VARCHAR( 255 ) NOT NULL , `Creator` VARCHAR( 255 ) NOT NULL , `Cover` VARCHAR( 255 ) NOT NULL , `Background` VARCHAR( 255 ) NOT NULL , `Video` VARCHAR( 255 ) NOT NULL , `VideoGap` FLOAT NOT NULL , `Gap` FLOAT NOT NULL , `Start` FLOAT NOT NULL , `Finish` INT( 11 ) NOT NULL , `BPM` INT( 5 ) NOT NULL , `Relative` BOOLEAN NOT NULL , `NotesGap` INT( 11 ) NOT NULL);');} - - - finally - //ScoreDB.Free; - end; - -end; - -//-------------------- -//Free - Frees Database -//-------------------- -Destructor TDataBaseSystem.Free; -begin - ScoreDB.Free; -end; - -//-------------------- -//ReadScore - Read Scores into SongArray -//-------------------- -procedure TDataBaseSystem.ReadScore(var Song: TSong); -var - TableData: TSqliteTable; - Dif: Byte; -begin - //ScoreDB := TSqliteDatabase.Create(sFilename); - try - try - //Search Song in DB - TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score` FROM `us_scores` WHERE `SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '" LIMIT 1) ORDER BY `Score` DESC LIMIT 15'); - //Empty Old Scores - SetLength (Song.Score[0], 0); - SetLength (Song.Score[1], 0); - SetLength (Song.Score[2], 0); - - while not TableData.Eof do//Go through all Entrys - begin//Add one Entry to Array - Dif := StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Difficulty'])); - if (Dif>=0) AND (Dif<=2) then - begin - SetLength(Song.Score[Dif], Length(Song.Score[Dif]) + 1); - - Song.Score[Dif, high(Song.Score[Dif])].Name := TableData.FieldAsString(TableData.FieldIndex['Player']); - Song.Score[Dif, high(Song.Score[Dif])].Score:= StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score'])); - end; - TableData.Next; - end; - - except //Im Fehlerfall - for Dif := 0 to 2 do - begin - SetLength(Song.Score[Dif], 1); - Song.Score[Dif, 1].Name := 'Error Reading ScoreDB'; - end; - end; - finally - //ScoreDb.Free; - end; -end; - -//-------------------- -//AddScore - Add one new Score to DB -//-------------------- -procedure TDataBaseSystem.AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); -var -ID: Integer; -TableData: TSqliteTable; -begin - //ScoreDB := TSqliteDatabase.Create(sFilename); - try - //Prevent 0 Scores from being added - if (Score > 0) then - begin - - ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); - if ID = 0 then //Song doesn't exist -> Create - begin - ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed` ) VALUES (NULL , "' + Song.Artist + '", "' + Song.Title + '", "0");'); - ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); - if ID = 0 then //Could not Create Table - exit; - end; - //Create new Entry - ScoreDB.ExecSQL('INSERT INTO `US_Scores` ( `SongID` , `Difficulty` , `Player` , `Score` ) VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' + Name + '", "' + InttoStr(Score) + '");'); - - //Delete Last Position when there are more than 5 Entrys - if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then - begin - TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` ASC LIMIT 1'); - ScoreDB.ExecSQL('DELETE FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" AND `Player` = "' + TableData.FieldAsString(TableData.FieldIndex['Player']) + '" AND `Score` = "' + TableData.FieldAsString(TableData.FieldIndex['Score']) + '"'); - end; - - end; - finally - //ScoreDB.Free; - end; -end; - -//-------------------- -//WriteScore - Not needed with new System; But used for Increment Played Count -//-------------------- -procedure TDataBaseSystem.WriteScore(var Song: TSong); -begin - try - //Increase TimesPlayed - ScoreDB.ExecSQL ('UPDATE `us_songs` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";'); - except - - 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; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean; -var - Query: String; - TableData: TSqliteTable; -begin - Result := False; - - if (Length(Stats) < Count) then - Exit; - - {Todo: - Add Prevention that only Players with more than 5 Scores are Selected at Typ 2} - - //Create Query - Case Typ of - 0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` FROM `US_Scores` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`'; - 1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; - 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `US_Songs` ORDER BY `TimesPlayed`'; - 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `US_Songs` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)'; - end; - - //Add Order Direction - If Reversed then - Query := Query + ' ASC' - else - Query := Query + ' DESC'; - - //Add Limit - Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';'; - - //Execute Query - //try - TableData := ScoreDB.GetTable(Query); - {except - exit; - end;} - - //if Result empty -> Exit - if (TableData.RowCount < 1) then - exit; - - //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 := 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 := TableData.Fields[3]; - Stats[TableData.Row].SongTitle := TableData.Fields[4]; - end; - - 1:begin - Stats[TableData.Row].Player := TableData.Fields[0]; - Stats[TableData.Row].AverageScore := StrtoIntDef(TableData.Fields[1], 0); - end; - - 2:begin - Stats[TableData.Row].Artist := TableData.Fields[0]; - Stats[TableData.Row].Title := TableData.Fields[1]; - Stats[TableData.Row].TimesSung := StrtoIntDef(TableData.Fields[2], 0); - end; - - 3:begin - Stats[TableData.Row].ArtistName := TableData.Fields[0]; - Stats[TableData.Row].TimesSungtot := StrtoIntDef(TableData.Fields[1], 0); - end; - - end; - - TableData.Next; - end; - - Result := True; -end; - -//-------------------- -//GetTotalEntrys - Get Total Num of entrys for a Stats Query -//-------------------- -Function TDataBaseSystem.GetTotalEntrys(const Typ: Byte): Cardinal; -var Query: String; -begin - //Create Query - Case Typ of - 0: Query := 'SELECT COUNT(`SongID`) FROM `US_Scores`;'; - 1: Query := 'SELECT COUNT(DISTINCT `Player`) FROM `US_Scores`;'; - 2: Query := 'SELECT COUNT(`ID`) FROM `US_Songs`;'; - 3: Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `US_Songs`;'; - end; - - Result := ScoreDB.GetTableValue(Query); -end; - -end. +unit UDataBase; + +interface + +{$IFDEF FPC} + {$MODE DELPHI} +{$ENDIF} + + +uses USongs, SQLiteTable3; + +//-------------------- +//DataBaseSystem - Class including all DB Methods +//-------------------- +type + TStatResult = record + Case Typ: Byte of + 0: (Singer: ShortString; + Score: Word; + Difficulty: Byte; + SongArtist: ShortString; + SongTitle: ShortString); + + 1: (Player: ShortString; + AverageScore: Word); + + 2: (Artist: ShortString; + Title: ShortString; + TimesSung: Word); + + 3: (ArtistName: ShortString; + TimesSungtot: Word); + end; + AStatResult = Array of TStatResult; + + TDataBaseSystem = class + private + ScoreDB: TSqliteDatabase; + sFilename: string; + public + + + property Filename: String read sFilename; + + Destructor Free; + + Procedure Init(const Filename: string); + procedure ReadScore(var Song: TSong); + procedure AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); + procedure WriteScore(var Song: TSong); + + Function GetStats(var Stats: AStatResult; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean; + Function GetTotalEntrys(const Typ: Byte): Cardinal; + end; + +var + DataBase: TDataBaseSystem; + +implementation + +uses IniFiles, SysUtils; + +//-------------------- +//Create - Opens Database and Create Tables if not Exist +//-------------------- + +Procedure TDataBaseSystem.Init(const Filename: string); +begin + //Open Database + ScoreDB := TSqliteDatabase.Create(Filename); + sFilename := Filename; + + try + //Look for Tables => When not exist Create them + if not ScoreDB.TableExists('US_Scores') then + ScoreDB.execsql('CREATE TABLE `US_Scores` (`SongID` INT( 11 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );'); + + if not ScoreDB.TableExists('US_Songs') then + ScoreDB.execsql('CREATE TABLE `US_Songs` (`ID` INTEGER PRIMARY KEY, `Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );'); + //Not possible because of String Limitation to 255 Chars //Need to rewrite Wrapper + {if not ScoreDB.TableExists('US_SongCache') then + ScoreDB.ExecSQL('CREATE TABLE `US_SongCache` (`Path` VARCHAR( 255 ) NOT NULL , `Filename` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `Artist` VARCHAR( 255 ) NOT NULL , `Folder` VARCHAR( 255 ) NOT NULL , `Genre` VARCHAR( 255 ) NOT NULL , `Edition` VARCHAR( 255 ) NOT NULL , `Language` VARCHAR( 255 ) NOT NULL , `Creator` VARCHAR( 255 ) NOT NULL , `Cover` VARCHAR( 255 ) NOT NULL , `Background` VARCHAR( 255 ) NOT NULL , `Video` VARCHAR( 255 ) NOT NULL , `VideoGap` FLOAT NOT NULL , `Gap` FLOAT NOT NULL , `Start` FLOAT NOT NULL , `Finish` INT( 11 ) NOT NULL , `BPM` INT( 5 ) NOT NULL , `Relative` BOOLEAN NOT NULL , `NotesGap` INT( 11 ) NOT NULL);');} + + + finally + //ScoreDB.Free; + end; + +end; + +//-------------------- +//Free - Frees Database +//-------------------- +Destructor TDataBaseSystem.Free; +begin + ScoreDB.Free; +end; + +//-------------------- +//ReadScore - Read Scores into SongArray +//-------------------- +procedure TDataBaseSystem.ReadScore(var Song: TSong); +var + TableData: TSqliteTable; + Dif: Byte; +begin + //ScoreDB := TSqliteDatabase.Create(sFilename); + try + try + //Search Song in DB + TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score` FROM `us_scores` WHERE `SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '" LIMIT 1) ORDER BY `Score` DESC LIMIT 15'); + //Empty Old Scores + SetLength (Song.Score[0], 0); + SetLength (Song.Score[1], 0); + SetLength (Song.Score[2], 0); + + while not TableData.Eof do//Go through all Entrys + begin//Add one Entry to Array + Dif := StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Difficulty'])); + if (Dif>=0) AND (Dif<=2) then + begin + SetLength(Song.Score[Dif], Length(Song.Score[Dif]) + 1); + + Song.Score[Dif, high(Song.Score[Dif])].Name := TableData.FieldAsString(TableData.FieldIndex['Player']); + Song.Score[Dif, high(Song.Score[Dif])].Score:= StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score'])); + end; + TableData.Next; + end; + + except //Im Fehlerfall + for Dif := 0 to 2 do + begin + SetLength(Song.Score[Dif], 1); + Song.Score[Dif, 1].Name := 'Error Reading ScoreDB'; + end; + end; + finally + //ScoreDb.Free; + end; +end; + +//-------------------- +//AddScore - Add one new Score to DB +//-------------------- +procedure TDataBaseSystem.AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); +var +ID: Integer; +TableData: TSqliteTable; +begin + //ScoreDB := TSqliteDatabase.Create(sFilename); + try + //Prevent 0 Scores from being added + if (Score > 0) then + begin + + ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); + if ID = 0 then //Song doesn't exist -> Create + begin + ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed` ) VALUES (NULL , "' + Song.Artist + '", "' + Song.Title + '", "0");'); + ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); + if ID = 0 then //Could not Create Table + exit; + end; + //Create new Entry + ScoreDB.ExecSQL('INSERT INTO `US_Scores` ( `SongID` , `Difficulty` , `Player` , `Score` ) VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' + Name + '", "' + InttoStr(Score) + '");'); + + //Delete Last Position when there are more than 5 Entrys + if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then + begin + TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` ASC LIMIT 1'); + ScoreDB.ExecSQL('DELETE FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" AND `Player` = "' + TableData.FieldAsString(TableData.FieldIndex['Player']) + '" AND `Score` = "' + TableData.FieldAsString(TableData.FieldIndex['Score']) + '"'); + end; + + end; + finally + //ScoreDB.Free; + end; +end; + +//-------------------- +//WriteScore - Not needed with new System; But used for Increment Played Count +//-------------------- +procedure TDataBaseSystem.WriteScore(var Song: TSong); +begin + try + //Increase TimesPlayed + ScoreDB.ExecSQL ('UPDATE `us_songs` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";'); + except + + 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; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean; +var + Query: String; + TableData: TSqliteTable; +begin + Result := False; + + if (Length(Stats) < Count) then + Exit; + + {Todo: Add Prevention that only Players with more than 5 Scores are Selected at Typ 2} + + //Create Query + Case Typ of + 0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` FROM `US_Scores` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`'; + 1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; + 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `US_Songs` ORDER BY `TimesPlayed`'; + 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `US_Songs` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)'; + end; + + //Add Order Direction + If Reversed then + Query := Query + ' ASC' + else + Query := Query + ' DESC'; + + //Add Limit + Query := Query + ' LIMIT ' + InttoStr(Count * Page) + ', ' + InttoStr(Count) + ';'; + + //Execute Query + //try + TableData := ScoreDB.GetTable(Query); + {except + exit; + end;} + + //if Result empty -> Exit + if (TableData.RowCount < 1) then + exit; + + //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 := 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 := TableData.Fields[3]; + Stats[TableData.Row].SongTitle := TableData.Fields[4]; + end; + + 1:begin + Stats[TableData.Row].Player := TableData.Fields[0]; + Stats[TableData.Row].AverageScore := StrtoIntDef(TableData.Fields[1], 0); + end; + + 2:begin + Stats[TableData.Row].Artist := TableData.Fields[0]; + Stats[TableData.Row].Title := TableData.Fields[1]; + Stats[TableData.Row].TimesSung := StrtoIntDef(TableData.Fields[2], 0); + end; + + 3:begin + Stats[TableData.Row].ArtistName := TableData.Fields[0]; + Stats[TableData.Row].TimesSungtot := StrtoIntDef(TableData.Fields[1], 0); + end; + + end; + + TableData.Next; + end; + + Result := True; +end; + +//-------------------- +//GetTotalEntrys - Get Total Num of entrys for a Stats Query +//-------------------- +Function TDataBaseSystem.GetTotalEntrys(const Typ: Byte): Cardinal; +var Query: String; +begin + //Create Query + Case Typ of + 0: Query := 'SELECT COUNT(`SongID`) FROM `US_Scores`;'; + 1: Query := 'SELECT COUNT(DISTINCT `Player`) FROM `US_Scores`;'; + 2: Query := 'SELECT COUNT(`ID`) FROM `US_Songs`;'; + 3: Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `US_Songs`;'; + end; + + Result := ScoreDB.GetTableValue(Query); +end; + +end. -- cgit v1.2.3 From 4d693075a4b5078a9e08430d003e2bdb918b68c2 Mon Sep 17 00:00:00 2001 From: jaybinks Date: Mon, 8 Oct 2007 02:02:07 +0000 Subject: trying to fix linux runtime errors with database ( Stats screen ) and Song Selection screen ( now back to kina working ) Testing Fisheye Links (please ignore): jira#USDX-1 sf#1790165 build#USDX-DELPHI-26 git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@469 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 175 +++++++++++++++++++++++++++------------- 1 file changed, 119 insertions(+), 56 deletions(-) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index deee85c0..bacb0d98 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -7,7 +7,8 @@ interface {$ENDIF} -uses USongs, SQLiteTable3; +uses USongs, + SQLiteTable3; //-------------------- //DataBaseSystem - Class including all DB Methods @@ -37,6 +38,7 @@ type private ScoreDB: TSqliteDatabase; sFilename: string; + public @@ -60,29 +62,44 @@ implementation uses IniFiles, SysUtils; +const + cUS_Scores = 'us_scores'; + cUS_Songs = 'us_songs'; + //-------------------- //Create - Opens Database and Create Tables if not Exist //-------------------- Procedure TDataBaseSystem.Init(const Filename: string); begin + writeln( 'TDataBaseSystem.Init' ); + //Open Database - ScoreDB := TSqliteDatabase.Create(Filename); + ScoreDB := TSqliteDatabase.Create( Filename ); sFilename := Filename; try - //Look for Tables => When not exist Create them - if not ScoreDB.TableExists('US_Scores') then - ScoreDB.execsql('CREATE TABLE `US_Scores` (`SongID` INT( 11 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );'); + //Look for Tables => When not exist Create them + if not ScoreDB.TableExists( cUS_Scores ) then + begin + ScoreDB.execsql('CREATE TABLE `'+cUS_Scores+'` (`SongID` INT( 11 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );'); + writeln( 'TDataBaseSystem.Init - CREATED US_Scores' ); + end; - if not ScoreDB.TableExists('US_Songs') then - ScoreDB.execsql('CREATE TABLE `US_Songs` (`ID` INTEGER PRIMARY KEY, `Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );'); - //Not possible because of String Limitation to 255 Chars //Need to rewrite Wrapper - {if not ScoreDB.TableExists('US_SongCache') then - ScoreDB.ExecSQL('CREATE TABLE `US_SongCache` (`Path` VARCHAR( 255 ) NOT NULL , `Filename` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `Artist` VARCHAR( 255 ) NOT NULL , `Folder` VARCHAR( 255 ) NOT NULL , `Genre` VARCHAR( 255 ) NOT NULL , `Edition` VARCHAR( 255 ) NOT NULL , `Language` VARCHAR( 255 ) NOT NULL , `Creator` VARCHAR( 255 ) NOT NULL , `Cover` VARCHAR( 255 ) NOT NULL , `Background` VARCHAR( 255 ) NOT NULL , `Video` VARCHAR( 255 ) NOT NULL , `VideoGap` FLOAT NOT NULL , `Gap` FLOAT NOT NULL , `Start` FLOAT NOT NULL , `Finish` INT( 11 ) NOT NULL , `BPM` INT( 5 ) NOT NULL , `Relative` BOOLEAN NOT NULL , `NotesGap` INT( 11 ) NOT NULL);');} + if not ScoreDB.TableExists( cUS_Songs ) then + begin + ScoreDB.execsql('CREATE TABLE `'+cUS_Songs+'` (`ID` INTEGER PRIMARY KEY, `Artist` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `TimesPlayed` int(5) NOT NULL );'); + writeln( 'TDataBaseSystem.Init - CREATED US_Songs' ); + end; + + //Not possible because of String Limitation to 255 Chars //Need to rewrite Wrapper + {if not ScoreDB.TableExists('US_SongCache') then + ScoreDB.ExecSQL('CREATE TABLE `US_SongCache` (`Path` VARCHAR( 255 ) NOT NULL , `Filename` VARCHAR( 255 ) NOT NULL , `Title` VARCHAR( 255 ) NOT NULL , `Artist` VARCHAR( 255 ) NOT NULL , `Folder` VARCHAR( 255 ) NOT NULL , `Genre` VARCHAR( 255 ) NOT NULL , `Edition` VARCHAR( 255 ) NOT NULL , `Language` VARCHAR( 255 ) NOT NULL , `Creator` VARCHAR( 255 ) NOT NULL , `Cover` VARCHAR( 255 ) NOT NULL , `Background` VARCHAR( 255 ) NOT NULL , `Video` VARCHAR( 255 ) NOT NULL , `VideoGap` FLOAT NOT NULL , `Gap` FLOAT NOT NULL , `Start` FLOAT NOT NULL , `Finish` INT( 11 ) NOT NULL , `BPM` INT( 5 ) NOT NULL , `Relative` BOOLEAN NOT NULL , `NotesGap` INT( 11 ) NOT NULL);');} finally + writeln( cUS_Songs +' Exist : ' + inttostr( integer(ScoreDB.TableExists( cUS_Songs )) ) ); + writeln( cUS_Scores +' Exist : ' + inttostr( integer(ScoreDB.TableExists( cUS_Scores )) ) ); //ScoreDB.Free; end; @@ -93,7 +110,9 @@ end; //-------------------- Destructor TDataBaseSystem.Free; begin - ScoreDB.Free; + writeln( 'TDataBaseSystem.Free' ); + + freeandnil( ScoreDB ); end; //-------------------- @@ -104,37 +123,44 @@ var TableData: TSqliteTable; Dif: Byte; begin + if not assigned( ScoreDB ) then + exit; + + //ScoreDB := TSqliteDatabase.Create(sFilename); try - try - //Search Song in DB - TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score` FROM `us_scores` WHERE `SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '" LIMIT 1) ORDER BY `Score` DESC LIMIT 15'); - //Empty Old Scores - SetLength (Song.Score[0], 0); - SetLength (Song.Score[1], 0); - SetLength (Song.Score[2], 0); - - while not TableData.Eof do//Go through all Entrys - begin//Add one Entry to Array - Dif := StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Difficulty'])); - if (Dif>=0) AND (Dif<=2) then - begin - SetLength(Song.Score[Dif], Length(Song.Score[Dif]) + 1); - - Song.Score[Dif, high(Song.Score[Dif])].Name := TableData.FieldAsString(TableData.FieldIndex['Player']); - Song.Score[Dif, high(Song.Score[Dif])].Score:= StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score'])); + try + //Search Song in DB + TableData := ScoreDB.GetTable('SELECT `Difficulty`, `Player`, `Score` FROM `'+cUS_Scores+'` WHERE `SongID` = (SELECT `ID` FROM `us_songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '" LIMIT 1) ORDER BY `Score` DESC LIMIT 15'); + + //Empty Old Scores + SetLength (Song.Score[0], 0); + SetLength (Song.Score[1], 0); + SetLength (Song.Score[2], 0); + + while not TableData.Eof do//Go through all Entrys + begin//Add one Entry to Array + Dif := StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Difficulty'])); + if (Dif>=0) AND (Dif<=2) then + begin + SetLength(Song.Score[Dif], Length(Song.Score[Dif]) + 1); + + Song.Score[Dif, high(Song.Score[Dif])].Name := TableData.FieldAsString(TableData.FieldIndex['Player']); + Song.Score[Dif, high(Song.Score[Dif])].Score := StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score'])); + end; + TableData.Next; + + end; // While not TableData.EOF + + except //Im Fehlerfall + for Dif := 0 to 2 do + begin + SetLength(Song.Score[Dif], 1); + Song.Score[Dif, 1].Name := 'Error Reading ScoreDB'; + end; end; - TableData.Next; - end; - - except //Im Fehlerfall - for Dif := 0 to 2 do - begin - SetLength(Song.Score[Dif], 1); - Song.Score[Dif, 1].Name := 'Error Reading ScoreDB'; - end; - end; - finally + + finally // Try Finally //ScoreDb.Free; end; end; @@ -147,27 +173,30 @@ var ID: Integer; TableData: TSqliteTable; begin + if not assigned( ScoreDB ) then + exit; + //ScoreDB := TSqliteDatabase.Create(sFilename); try //Prevent 0 Scores from being added if (Score > 0) then begin - ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); + ID := ScoreDB.GetTableValue('SELECT `ID` FROM `'+cUS_Songs+'` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); if ID = 0 then //Song doesn't exist -> Create begin - ScoreDB.ExecSQL ('INSERT INTO `US_Songs` ( `ID` , `Artist` , `Title` , `TimesPlayed` ) VALUES (NULL , "' + Song.Artist + '", "' + Song.Title + '", "0");'); + ScoreDB.ExecSQL ('INSERT INTO `'+cUS_Songs+'` ( `ID` , `Artist` , `Title` , `TimesPlayed` ) VALUES (NULL , "' + Song.Artist + '", "' + Song.Title + '", "0");'); ID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"'); if ID = 0 then //Could not Create Table exit; end; //Create new Entry - ScoreDB.ExecSQL('INSERT INTO `US_Scores` ( `SongID` , `Difficulty` , `Player` , `Score` ) VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' + Name + '", "' + InttoStr(Score) + '");'); + ScoreDB.ExecSQL('INSERT INTO `'+cUS_Scores+'` ( `SongID` , `Difficulty` , `Player` , `Score` ) VALUES ("' + InttoStr(ID) + '", "' + InttoStr(Level) + '", "' + Name + '", "' + InttoStr(Score) + '");'); //Delete Last Position when there are more than 5 Entrys - if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `US_Scores` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then + if ScoreDB.GetTableValue('SELECT COUNT(`SongID`) FROM `'+cUS_Scores+'` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then begin - TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` ASC LIMIT 1'); + TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `'+cUS_Scores+'` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" ORDER BY `Score` ASC LIMIT 1'); ScoreDB.ExecSQL('DELETE FROM `US_Scores` WHERE SongID = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'" AND `Player` = "' + TableData.FieldAsString(TableData.FieldIndex['Player']) + '" AND `Score` = "' + TableData.FieldAsString(TableData.FieldIndex['Score']) + '"'); end; @@ -182,9 +211,12 @@ end; //-------------------- procedure TDataBaseSystem.WriteScore(var Song: TSong); begin + if not assigned( ScoreDB ) then + exit; + try //Increase TimesPlayed - ScoreDB.ExecSQL ('UPDATE `us_songs` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";'); + ScoreDB.ExecSQL ('UPDATE `'+cUS_Songs+'` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";'); except end; @@ -205,6 +237,9 @@ var begin Result := False; + if not assigned( ScoreDB ) then + exit; + if (Length(Stats) < Count) then Exit; @@ -212,10 +247,10 @@ begin //Create Query Case Typ of - 0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` FROM `US_Scores` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`'; - 1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)) FROM `US_Scores` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; - 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `US_Songs` ORDER BY `TimesPlayed`'; - 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `US_Songs` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)'; + 0: Query := 'SELECT `Player` , `Difficulty` , `Score` , `Artist` , `Title` FROM `'+cUS_Scores+'` INNER JOIN `US_Songs` ON (`SongID` = `ID`) ORDER BY `Score`'; + 1: Query := 'SELECT `Player` , ROUND (Sum(`Score`) / COUNT(`Score`)) FROM `'+cUS_Scores+'` GROUP BY `Player` ORDER BY (Sum(`Score`) / COUNT(`Score`))'; + 2: Query := 'SELECT `Artist` , `Title` , `TimesPlayed` FROM `'+cUS_Scores+'` ORDER BY `TimesPlayed`'; + 3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `'+cUS_Scores+'` GROUP BY `Artist` ORDER BY Sum(`TimesPlayed`)'; end; //Add Order Direction @@ -284,15 +319,43 @@ end; Function TDataBaseSystem.GetTotalEntrys(const Typ: Byte): Cardinal; var Query: String; begin - //Create Query - Case Typ of - 0: Query := 'SELECT COUNT(`SongID`) FROM `US_Scores`;'; - 1: Query := 'SELECT COUNT(DISTINCT `Player`) FROM `US_Scores`;'; - 2: Query := 'SELECT COUNT(`ID`) FROM `US_Songs`;'; - 3: Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `US_Songs`;'; + if not assigned( ScoreDB ) then + exit; + try + //Create Query + Case Typ of + 0: begin + Query := 'SELECT COUNT(`SongID`) FROM `'+cUS_Scores+'`;'; + if not ScoreDB.TableExists( cUS_Scores ) then + exit; + end; + 1: begin + Query := 'SELECT COUNT(DISTINCT `Player`) FROM `'+cUS_Scores+'`;'; + if not ScoreDB.TableExists( cUS_Scores ) then + exit; + end; + 2: begin + Query := 'SELECT COUNT(`ID`) FROM `'+cUS_Scores+'`;'; + if not ScoreDB.TableExists( cUS_Songs ) then + exit; + end; + 3: begin + Query := 'SELECT COUNT(DISTINCT `Artist`) FROM `'+cUS_Songs+'`;'; + if not ScoreDB.TableExists( cUS_Songs ) then + exit; + end; + end; + + Result := ScoreDB.GetTableValue(Query); + except + // TODO : JB_Linux - Why do we get these exceptions on linux !! + on E:ESQLiteException DO // used to handle : Could not retrieve data "SELECT COUNT(`ID`) FROM `US_Songs`;" : SQL logic error or missing database + // however, we should pre-empt this error... and make sure the database DOES exist. + begin + result := 0; + end; end; - Result := ScoreDB.GetTableValue(Query); end; end. -- cgit v1.2.3 From 391d30716d48dc709f6444b19c008e82311623b9 Mon Sep 17 00:00:00 2001 From: eddie-0815 Date: Thu, 1 Nov 2007 19:34:40 +0000 Subject: Mac OS X version compiles and links. I hope I didn't break too many files on windows/linux. Added switches.inc to all files. Changed many IFDEFs. For Windows-only code please use MSWINDOWS instead of WIN32 now. WIN32 is also used by the Mac port. git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@546 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 5 +---- 1 file changed, 1 insertion(+), 4 deletions(-) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index bacb0d98..0cafc9fd 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -2,10 +2,7 @@ unit UDataBase; interface -{$IFDEF FPC} - {$MODE DELPHI} -{$ENDIF} - +{$I switches.inc} uses USongs, SQLiteTable3; -- cgit v1.2.3 From 99955c78f63d1cb0d8bec666bc33953590a74c8a Mon Sep 17 00:00:00 2001 From: jaybinks Date: Thu, 1 Nov 2007 23:22:01 +0000 Subject: fixed failed builds build:USDX-LAZLIN-75 build:USDX-LAZLIN-76 for some reason we can not use {$MODE Delphi} in an included file. ( Probably because of the way the compier scopes this switch to each pas file ) ive had to revert this part of eddies changes. git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@548 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index 0cafc9fd..e99cb891 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -2,6 +2,10 @@ unit UDataBase; interface +{$IFDEF FPC} + {$MODE Delphi} +{$ENDIF} + {$I switches.inc} uses USongs, -- cgit v1.2.3 From e74bd57c12f470257111c1c0530fb38f0fd34414 Mon Sep 17 00:00:00 2001 From: jaybinks Date: Sat, 12 Jan 2008 12:31:43 +0000 Subject: bunch of smaller changes... some changes to song loading... Record global changed to singleton object started implementing mic volume display in Settings-Record hope this dosnt break anything.. :P git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@789 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 1 + 1 file changed, 1 insertion(+) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index e99cb891..b5636d52 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -9,6 +9,7 @@ interface {$I switches.inc} uses USongs, + USong, SQLiteTable3; //-------------------- -- cgit v1.2.3