From 6db1465f89985e8675db3fdac1aa7d662dda3722 Mon Sep 17 00:00:00 2001 From: s_alexander Date: Sat, 19 Apr 2008 15:23:35 +0000 Subject: tried to make songloading working with the old loader and keep all changes that were made since starting to work on a new one *hope that worked* you may delete your existing cover.cache (the cover cache is still NOT working) git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@1024 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 398 +++++++++------------------------------- 1 file changed, 86 insertions(+), 312 deletions(-) (limited to 'Game/Code/Classes/UDataBase.pas') diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index ee0ca94b..dcf32315 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -35,106 +35,28 @@ type TimesSungtot: Word); end; AStatResult = Array of TStatResult; - - PDBSongInfo = ^TDBSongInfo; - TDBSongInfo = record - FileName: String; - FolderID: Integer; - FullPath: String; - LastChanged: Integer; //TimeStamp - - //Required Information - Artist: widestring; - Title: widestring; - - Mp3: widestring; - - //Some Files - Cover: widestring; //Path to Cover - CoverID: Integer; //ID of Cover in Covers Cache - Video: widestring; - VideoGAP: real; - - - //Additional Information - Start: real; // in seconds - - //Sorting - Genre: widestring; - Edition: widestring; - Language: widestring; - Year: widestring; - Creator: widestring; - TimesPlayed:Word; - 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(const Song: Integer; const Difficulty: Byte): 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; - Function GetSongData(ID: Integer; const Info: PDBSongInfo): Boolean; - Function SetSongData(ID: Integer; const Info: PDBSongInfo): Boolean; - Function AddSong(ID: Integer; const Info: PDBSongInfo): Boolean; - 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; + 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; -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; @@ -148,9 +70,6 @@ uses 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 @@ -161,79 +80,26 @@ begin debugWriteln( 'TDataBaseSystem.Init ('+Filename+') @ '+ floattostr( now() ) ); //Open Database - ScoreDB := TSqliteDatabase.Create( Filename ); - sFilename := Filename; - UpdateFromVer1 := False; + ScoreDB := TSqliteDatabase.Create( Filename ); + sFilename := Filename; 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'', ''' + cDB_Version + ''');'); - - 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 );'); - + 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 );'); 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 + '`'); - + 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 );'); debugWriteln( '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 @@ -257,102 +123,109 @@ end; //-------------------- //ReadScore - Read Scores into SongArray //-------------------- -Function TDataBaseSystem.ReadScore(const Song: Integer; const Difficulty: Byte): AScore; +procedure TDataBaseSystem.ReadScore(var Song: TSong); var TableData: TSqliteTable; - I: Integer; + Difficulty: Integer; begin - if (assigned( ScoreDB )) AND (Difficulty < 2) then - begin + if not assigned( ScoreDB ) then + exit; + - //ScoreDB := TSqliteDatabase.Create(sFilename); + //ScoreDB := TSqliteDatabase.Create(sFilename); + try try - try - //Search Song in DB - TableData := ScoreDB.GetTable('SELECT `Player`, `Score` FROM `'+cUS_Scores+'` WHERE (`SongID` = ''' + InttoStr(Song) + ''') AND (`Difficulty` = ''' + InttoStr(Difficulty) + ''') ORDER BY `Score` DESC LIMIT 5'); - - - I := 0; - while not TableData.Eof do //Go through all Entrys - begin //Add one Entry to Array - Result[I].Name := - TableData.FieldAsString(TableData.FieldIndex['Player']); - Result[I].Score := - StrtoInt(TableData.FieldAsString(TableData.FieldIndex['Score'])); - - TableData.Next; - Inc(I); - end; // While not TableData.EOF - - If (I < 5) then - Result[1].Score := 0; //Place ending Zero - - except - Result[0].Name := 'Error Reading ScoreDB'; - Result[0].Score := -1; - Result[1].Score := 0; + //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 - finally - //ScoreDb.Free; + 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); +procedure TDataBaseSystem.AddScore(var Song: TSong; Level: integer; Name: string; Score: integer); var ID: Integer; TableData: TSqliteTable; begin - if assigned( ScoreDB ) then + if not assigned( ScoreDB ) then + exit; + + //ScoreDB := TSqliteDatabase.Create(sFilename); + try + //Prevent 0 Scores from being added + if (Score > 0) then begin - //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(Song) + '", "' + 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; + 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) + '");'); - end; - finally - //ScoreDB.Free; + //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); +procedure TDataBaseSystem.WriteScore(var Song: TSong); begin if not assigned( ScoreDB ) then - begin - try - //Increase TimesPlayed - ScoreDB.ExecSQL ('UPDATE `'+cUS_Songs+'` SET `TimesPlayed` = `TimesPlayed` + ''1'' WHERE `ID` = ''' + InttoStr(Song) + ''';'); - except + exit; + + try + //Increase TimesPlayed + ScoreDB.ExecSQL ('UPDATE `'+cUS_Songs+'` SET `TimesPlayed` = `TimesPlayed` + "1" WHERE `Title` = "' + Song.Title + '" AND `Artist` = "' + Song.Artist + '";'); + except - end; end; end; @@ -496,103 +369,4 @@ begin end; -Function TDataBaseSystem.GetIDbyPath(const Path: String): Integer; -begin - -end; - -Function TDataBaseSystem.GetIDbyFileName(const Filename: String; FolderID: Integer): Integer; -begin - try - Result := ScoreDB.GetTableValue('SELECT `ID` FROM `' + cUS_Songs + '` WHERE (`Filename` = ''' + Filename + ''') AND (`FolderID` = ''' + InttoStr(FolderID) + ''')'); - except - Result := 0; - end; -end; - -Function TDataBaseSystem.GetSongData(ID: Integer; const Info: PDBSongInfo): Boolean; -var - TableData: TSqliteTable; -begin - Result := True; - try - TableData := ScoreDB.GetTable('SELECT Filename`, `FolderID`, `FullPath`, `LastChanged`, `Artist`, `Title`, `Mp3`, `Cover`, `CoverID`, `Video`, `VideoGap`, `Start`, `Genre`, `Edition`, `Language`, `Year`, `Creator`, `TimesPlayed` FROM `' + cUS_Songs + '` WHERE `ID` = ''' + InttoStr(ID) + ''' '); - If (TableData.RowCount > 0) then //All Fieldnames are listed to ensure Field order - begin // - Info^.FileName := TableData.Fields[0]; - Info^.FolderID := StrToIntDef(TableData.Fields[1], -1); - Info^.FullPath := TableData.Fields[2]; - Info^.LastChanged := StrToIntDef(TableData.Fields[3], 0); - Info^.Artist := TableData.Fields[4]; - Info^.Title := TableData.Fields[5]; - Info^.Mp3 := TableData.Fields[6]; - Info^.Cover := TableData.Fields[7]; - Info^.CoverID := StrToIntDef(TableData.Fields[8], -1); - Info^.Video := TableData.Fields[9]; - Info^.VideoGAP := StrToFloatDef(TableData.Fields[10], 0); - Info^.Start := StrToFloatDef(TableData.Fields[11], 0); - Info^.Genre := TableData.Fields[12]; - Info^.Edition := TableData.Fields[13]; - Info^.Language := TableData.Fields[14]; - Info^.Year := TableData.Fields[15]; - Info^.Creator := TableData.Fields[16]; - Info^.TimesPlayed := StrToIntDef(TableData.Fields[17], 0); - end; - except - Result := False; - end; -end; - -Function TDataBaseSystem.AddSong(ID: Integer; const Info: PDBSongInfo): Boolean; -var - OldID: Integer; -begin - Result := True; - try - ScoreDB.ExecSQL('INSERT INTO `us_songs` ( `ID` , `Filename` , `FolderID` , `FullPath` , `LastChanged` , `Artist` , `Title` , `Mp3` , `Cover` , `CoverID` , `Video` , `VideoGap` , `Start` , `Genre` , `Edition` , `Language` , `Year` , `Creator` , `TimesPlayed` ) ' + - 'VALUES ( '''', '''+ Info^.FileName +''', '''+ InttoStr(Info^.FolderID) +''', '''+ Info^.FullPath +''', '''+ InttoStr(Info^.LastChanged) +''', '''+ Info^.Artist +''', '''+ Info^.Title +''', '''+ Info^.Mp3 +''', '''+ Info^.Cover +''', '''+ InttoStr(Info^.CoverID) +''', '''+ Info^.Video + ''', '''+ FloattoStr(Info^.VideoGAP) + ''', '''+ FloattoStr(Info^.Start) +''', '''+ Info^.Genre +''', '''+ Info^.Edition +''', '''+ Info^.Language +''', '''+ Info^.Year +''', '''+ Info^.Creator +''', ''0'');'); - - //Version 1.0 to 2.0 Update - If UpdateFromVer1 then - begin //Search for Song w/ same Artist and Title in Old DB - OldID := ScoreDB.GetTableValue('SELECT `ID` FROM `US_Songs` WHERE `Artist` = "' + Info^.Artist + '" AND `Title` = "' + Info^.Title + '"'); - end; - except - Result := False; - end; -end; - -Function TDataBaseSystem.SetSongData(ID: Integer; const Info: PDBSongInfo): Boolean; -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. -- cgit v1.2.3