From 3f7fb70c3a31c88c3308b6a6fc88303ef5a36430 Mon Sep 17 00:00:00 2001 From: whiteshark0 Date: Sun, 30 Mar 2008 19:37:37 +0000 Subject: New Database Structure Init ready git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@984 b956fd51-792f-4845-bead-9b4dfca2ff2c --- Game/Code/Classes/UDataBase.pas | 81 +++++++++++++++++++++++++++++++++++------ Game/Code/Classes/USong.pas | 13 ++++--- 2 files changed, 77 insertions(+), 17 deletions(-) diff --git a/Game/Code/Classes/UDataBase.pas b/Game/Code/Classes/UDataBase.pas index 059443ac..9215edab 100644 --- a/Game/Code/Classes/UDataBase.pas +++ b/Game/Code/Classes/UDataBase.pas @@ -41,6 +41,7 @@ type FolderID: String; FullPath: String; LastChanged: Integer; //TimeStamp + TimesPlayed: Integer; //Required Information Title: widestring; @@ -49,7 +50,6 @@ type Mp3: widestring; Text: widestring; - Creator: widestring; Resolution: integer; BPM: array of TBPM; @@ -78,6 +78,8 @@ type Genre: widestring; Edition: widestring; Language: widestring; + Year: widestring; + Creator: widestring; end; TSongListInfo = record //SongInfo used by Songscreen @@ -102,6 +104,7 @@ type private ScoreDB: TSqliteDatabase; sFilename: string; + UpdateFromVer1: Boolean; public @@ -111,7 +114,7 @@ type Destructor Free; Procedure Init(const Filename: string); - procedure ReadScore(Song: Integer); + Function ReadScore(Song: Integer): AScore; procedure AddScore(Song: Integer; Level: integer; Name: string; Score: integer); procedure WriteScore(Song: Integer); @@ -158,6 +161,9 @@ 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 @@ -168,26 +174,79 @@ begin debugWriteln( 'TDataBaseSystem.Init ('+Filename+') @ '+ floattostr( now() ) ); //Open Database - ScoreDB := TSqliteDatabase.Create( Filename ); - sFilename := Filename; + 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( 11 ) 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( 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 - 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 );'); + // 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; - - //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 @@ -211,7 +270,7 @@ end; //-------------------- //ReadScore - Read Scores into SongArray //-------------------- -procedure TDataBaseSystem.ReadScore(Song: Integer); +Function TDataBaseSystem.ReadScore(Song: Integer): AScore; var TableData: TSqliteTable; Difficulty: Integer; diff --git a/Game/Code/Classes/USong.pas b/Game/Code/Classes/USong.pas index 27c79a71..d6021811 100644 --- a/Game/Code/Classes/USong.pas +++ b/Game/Code/Classes/USong.pas @@ -42,12 +42,6 @@ type StartBeat: real; end; - TScore = record - Name: widestring; - Score: integer; - Length: string; - end; - {TSong = class FileLineNo : integer; //Line which is readed at Last, for error reporting @@ -113,7 +107,14 @@ type procedure clear(); end; } + TScore = record + Name: widestring; + Score: integer; + Length: string; + end; + AScore = Array[0..4] of TScore; + {******************* New TSong Class. Containing some Methods for DB communication -- cgit v1.2.3