aboutsummaryrefslogtreecommitdiffstats
path: root/Game/Code/Classes/UDataBase.pas
diff options
context:
space:
mode:
Diffstat (limited to 'Game/Code/Classes/UDataBase.pas')
-rw-r--r--Game/Code/Classes/UDataBase.pas175
1 files changed, 119 insertions, 56 deletions
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.
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-06 07:23:01 +0000