unit UDataBase;
interface
{$IFDEF FPC}
{$MODE Delphi}
{$ENDIF}
{$I switches.inc}
uses USongs,
USong,
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;
TDBSongInfo = record
FileName: String;
FolderID: String;
FullPath: String;
LastChanged: Integer; //TimeStamp
TimesPlayed: Integer;
//Required Information
Title: widestring;
Artist: widestring;
Mp3: widestring;
Text: widestring;
Resolution: integer;
BPM: array of TBPM;
GAP: real; // in miliseconds
Base : array[0..1] of integer;
Rel : array[0..1] of integer;
Mult : integer;
MultBPM : integer;
//Some Files
Cover: widestring; //Path to Cover
CoverID: Integer; //ID of Cover in Covers Cache
Background: widestring;
Video: widestring;
VideoGAP: real;
//Additional Information
NotesGAP: integer;
Start: real; // in seconds
Finish: integer; // in miliseconds
Relative: boolean;
//Sorting
Genre: widestring;
Edition: widestring;
Language: widestring;
Year: widestring;
Creator: widestring;
end;
TSongListInfo = record //SongInfo used by Songscreen
ID: Integer;
Title: widestring;
Artist: widestring;
Mp3: widestring;
Video: widestring;
CoverID: Integer; //Cover ID in CoversCache
end;
ASongList = Array of TSongListInfo;
TSongListFilter = record
Field: Byte; //FieldID //See constants below
isSearch: Boolean; //Search Mask(true) or exact Match(False)
Filter: String;
end;
ASongListFilter = Array of TSongListFilter;
TDataBaseSystem = class
private
ScoreDB: TSqliteDatabase;
sFilename: string;
UpdateFromVer1: Boolean;
public
property Filename: String read sFilename;
Destructor Free;
Procedure Init(const Filename: string);
Function ReadScore(Song: Integer): AScore;
procedure AddScore(Song: Integer; Level: integer; Name: string; Score: integer);
procedure WriteScore(Song: Integer);
Function GetIDbyPath(const Path: String): Integer;
Function GetIDbyFileName(const Filename: String; FolderID: Integer): Integer;
Procedure GetSongData(ID: Integer; var Info: TDBSongInfo);
Procedure SetSongData(ID: Integer; var Info: TDBSongInfo);
Function GetLastChangedbyID(const Song: Integer): Integer;
Function GetLastChangedbyFileName(const Filename: String; FolderID: Integer): Integer;
Function GetFolderIDbyPath(Path: String): Integer;
Function GetFolderIDbyName(const Name: String; ParentFolder: Integer): Integer;
Function GetSongList(var List: ASongList; const Filter: ASongListFilter): Integer;
Function GetStats(var Stats: AStatResult; const Typ, Count: Byte; const Page: Cardinal; const Reversed: Boolean): Boolean;
Function GetTotalEntrys(const Typ: Byte): Cardinal;
end;
const
//Filter FieldIDs
SLF_Edition = 0;
SLF_Genre = 1;
SLF_Language = 2;
SLF_Folder = 3;
SLF_Title = 4;
SLF_Artist = 5;
SLF_Year = 6;
SLF_Creator = 7;
SLF_Video = 8; //Songs w/ Video are returned
SLF_NoVideo = 9; //Songs w/o Video are returned
Max_FilterFieldID = SLF_NoVideo;
var
DataBase: TDataBaseSystem;
implementation
uses
IniFiles,
ULog,
SysUtils;
const
cUS_Scores = 'us_scores';
cUS_Songs = 'us_songs';
cUS_Info = 'us_info';
cUS_Directories = 'us_directories';
cDB_Version = '2.0.0';
//--------------------
//Create - Opens Database and Create Tables if not Exist
//--------------------
Procedure TDataBaseSystem.Init(const Filename: string);
begin
debugWriteln( 'TDataBaseSystem.Init ('+Filename+') @ '+ floattostr( now() ) );
//Open Database
ScoreDB := TSqliteDatabase.Create( Filename );
sFilename := Filename;
UpdateFromVer1 := False;
try
//Check for Database Version
if not ScoreDB.TableExists( cUS_Info ) then
begin
If (ScoreDB.TableExists( cUS_Scores ) And ScoreDB.TableExists( cUS_Songs )) And (Not ScoreDB.TableExists( cUS_Directories )) then
begin //Update from a Ver. 1.0 - 1.0.1a Database
UpdateFromVer1 := True; //This table should be Updated from before Database ver 2.0
ScoreDb.ExecSQL('ALTER TABLE `' + cUS_Songs + '` RENAME TO ''' + cUS_Songs + '_old'''); //Rename old Song Table, to achieve old SongIDs
debugWriteln( 'TDataBaseSystem.Init - Switched to "Update from DB Version 1" Mode' );
end;
//Create Info Table
ScoreDB.ExecSQL('CREATE TABLE `' + cUS_Info + '` ( `Ident` varchar(32) PRIMARY KEY, `Value` varchar(64) NOT NULL default '''');');
//Write Database Version to Table
ScoreDB.ExecSQL('INSERT INTO `' + cUS_Info + '` VALUES (''version'', ''2.0.0'');');
debugWriteln( 'TDataBaseSystem.Init - CREATED US_Info' );
end;
//Look for Tables => When not exist Create them
if not ScoreDB.TableExists( cUS_Directories ) then
begin
ScoreDB.execsql('CREATE TABLE `' + cUS_Directories + '` ( `ID` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `Name` varchar(255) NOT NULL default '''', `FullPath` text NOT NULL, `LastChange` int(10) NOT NULL default ''0'');');
//Add Root Directory
ScoreDB.ExecSQL('INSERT INTO `us_directories` VALUES (1, ''root'', '''', ''0'');');
debugWriteln( 'TDataBaseSystem.Init - CREATED US_Directories' );
end;
if not ScoreDB.TableExists( cUS_Scores ) then
begin
ScoreDB.execsql('CREATE TABLE `'+cUS_Scores+'` (`SongID` INT( 10 ) NOT NULL , `Difficulty` INT( 1 ) NOT NULL , `Player` VARCHAR( 150 ) NOT NULL , `Score` INT( 5 ) NOT NULL );');
debugWriteln( 'TDataBaseSystem.Init - CREATED US_Scores' );
end;
if not ScoreDB.TableExists( cUS_Songs ) then
begin
// Old 1.0 Style: 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 );');
//New: Dataabse 2.0 Style
ScoreDb.ExecSQL('CREATE TABLE `' + cUS_Songs + '` ( ' +
'`ID` integer PRIMARY KEY AUTOINCREMENT, ' +
'`Filename` varchar(255) NOT NULL default '''', ' +
'`FolderID` int(10) NOT NULL default ''0'', ' +
'`FullPath` text NOT NULL, ' +
'`LastChanged` int(10) NOT NULL default ''0'', ' +
'`Artist` varchar(255) NOT NULL default '''', ' +
'`Title` varchar(255) NOT NULL default '''', ' +
'`Mp3` text NOT NULL, ' +
'`Cover` text NOT NULL, ' +
'`CoverID` int(10) NOT NULL default ''0'', ' +
'`Video` text NOT NULL, ' +
'`VideoGap` float NOT NULL default ''0'', ' +
'`Start` float NOT NULL default ''0'', ' +
'`Genre` varchar(255) NOT NULL default '''', ' +
'`Edition` varchar(255) NOT NULL default '''', ' +
'`Language` varchar(255) NOT NULL default '''', ' +
'`Year` varchar(255) NOT NULL default '''', ' +
'`Creator` varchar(255) NOT NULL default '''', ' +
'`TimesPlayed` int(5) NOT NULL default ''0'');');
//Delete Score Table to avoid wrong IDS
ScoreDb.ExecSQL('DELETE FROM `' + cUS_Scores + '`');
debugWriteln( 'TDataBaseSystem.Init - CREATED US_Songs' );
end;
finally
debugWriteln( cUS_Songs +' Exist : ' + inttostr( integer(ScoreDB.TableExists( cUS_Songs )) ) );
debugWriteln( cUS_Scores +' Exist : ' + inttostr( integer(ScoreDB.TableExists( cUS_Scores )) ) );
//ScoreDB.Free;
end;
end;
//--------------------
//Free - Frees Database
//--------------------
Destructor TDataBaseSystem.Free;
begin
debugWriteln( 'TDataBaseSystem.Free' );
freeandnil( ScoreDB );
end;
//--------------------
//ReadScore - Read Scores into SongArray
//--------------------
Function TDataBaseSystem.ReadScore(Song: Integer): AScore;
var
TableData: TSqliteTable;
Difficulty: Integer;
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 `'+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
Difficulty := StrToIntDef(TableData.FieldAsString(TableData.FieldIndex['Difficulty']), -1);
if (Difficulty >= 0) AND (Difficulty <= 2) then
begin
SetLength(Song.Score[Difficulty], Length(Song.Score[Difficulty]) + 1);
Song.Score[Difficulty, high(Song.Score[Difficulty])].Name :=
TableData.FieldAsString(TableData.FieldIndex['Player']);
Song.Score[Difficulty, high(Song.Score[Difficulty])].Score :=
StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score']));
end;
TableData.Next;
end; // While not TableData.EOF
except
for Difficulty := 0 to 2 do
begin
SetLength(Song.Score[Difficulty], 1);
Song.Score[Difficulty, 1].Name := 'Error Reading ScoreDB';
end;
end;
finally
//ScoreDb.Free;
end; }
end;
//--------------------
//AddScore - Add one new Score to DB
//--------------------
procedure TDataBaseSystem.AddScore(Song: Integer; Level: integer; Name: string; Score: integer);
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 `'+cUS_Songs+'` WHERE `Artist` = "' + Song.Artist + '" AND `Title` = "' + Song.Title + '"');
if ID = 0 then //Song doesn't exist -> Create
begin
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 `'+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 `'+cUS_Scores+'` WHERE `SongID` = "' + InttoStr(ID) + '" AND `Difficulty` = "' + InttoStr(Level) +'"') > 5 then
begin
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;
end;
finally
//ScoreDB.Free;
end; }
end;
//--------------------
//WriteScore - Not needed with new System; But used for Increment Played Count
//--------------------
procedure TDataBaseSystem.WriteScore(Song: Integer);
begin
{if not assigned( ScoreDB ) then
exit;
try
//Increase TimesPlayed
ScoreDB.ExecSQL ('UPDATE `'+cUS_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 not assigned( ScoreDB ) then
exit;
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 `'+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_Songs+'` ORDER BY `TimesPlayed`';
3: Query := 'SELECT `Artist` , Sum(`TimesPlayed`) FROM `'+cUS_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; // this has a try except, because ( on linux at least ) it seems that doing a GetTable, that returns nothing
// causes an exception. and in the case of a new Database file, with no scores stored yet... this seems to except here.
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
Result := 0;
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_Songs+'`;';
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
exit;
end;
end;
end;
Function TDataBaseSystem.GetIDbyPath(const Path: String): Integer;
begin
end;
Function TDataBaseSystem.GetIDbyFileName(const Filename: String; FolderID: Integer): Integer;
begin
end;
Procedure TDataBaseSystem.GetSongData(ID: Integer; var Info: TDBSongInfo);
begin
end;
Procedure TDataBaseSystem.SetSongData(ID: Integer; var Info: TDBSongInfo);
begin
end;
Function TDataBaseSystem.GetLastChangedbyID(const Song: Integer): Integer;
begin
end;
Function TDataBaseSystem.GetLastChangedbyFileName(const Filename: String; FolderID: Integer): Integer;
begin
end;
Function TDataBaseSystem.GetFolderIDbyPath(Path: String): Integer;
begin
end;
Function TDataBaseSystem.GetFolderIDbyName(const Name: String; ParentFolder: Integer): Integer;
begin
end;
Function TDataBaseSystem.GetSongList(var List: ASongList; const Filter: ASongListFilter): Integer;
begin
end;
end.