Announcement

Collapse
No announcement yet.

[Documentation] - Use an embedded database with UDK

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    [Documentation] - Use an embedded database with UDK

    Introduction
    This is my first documentation/article/tutorial ever, so excuse me if some parts are not 100% clear to understand. Let me know and I will edit the parts to make the text easier to understand.

    I will use my own SQL database wrapper, you can download HERE (v. 1.0.5.1)
    The files of this articles are attached to this post and can be directly downloaded HERE



    Background
    SQL databases are used for many applications today. Every game today uses some kind of a database for easy and fast access to stored values, attributes and texts. Think of a RTS game, unit names, building times, weapon strength...everything is stored in a database. Think of a RPG, quests, items, names...stored in a database.

    When having a look into the possibilities of UDK, i found nothing that could realise the things I just mentioned. For sure an .ini based database could be used, but it isn't very flexible compared to the possibilities with a database query.
    So how I could use UDK and a database at the same time. The first thing that comes to my mind was DLLBind, added to UDK in November '09. With DLLBind you are able to write C++ libraries used by your UnrealScript classes.
    That was the moment where my SQLProject was born.


    Note: This tutorial does not cover how to create a database, a table or any other SQL related funtion like building queries or the reason for using prepared statements. Use the following links to inform about these things:

    A quick tour into SQL would go beyond the scope of this tutorial.


    Step: 1 - Installation
    Installation of the SQLProject library is fairly easy. Just extract the folder UserCode from the zip file to your <UDK_Root>\Binaries\Win32 folder. The UDKProjectDLL.dll should be located in <UDK_Root>\Binaries\Win32\UserCode.

    <UDK_Root> is the installation directory of your current UDK Version (e.g. C:\Development\UDK-2010-04).


    Now you could use the library functions to work with a database. In the following chapter I will introduce a small project and will explain what the code do and what I thought when writing the sources.


    Step: 2 - Setting up the API class
    In the following steps I will use a package UDKDatabase where all our classes will be created. This is for demonstration only, you are free to directly implement the code into your existing project.


    The next thing we have to do: Create a class which will be our interface to the UDKProjectDLL.dll. I always use a special class for this, to have an clean interface using the DLLBind feature without any other functions.

    So we create a new class DB_DLLAPI.uc and add all exported functions of UDKProjectDLL.dll so we are able to use them in our scripts.

    Code:
    class DB_DLLAPI extends Object
      DLLBind(UDKProjectDLL);
    
    enum ESQLDriver
    {
      SQLDrv_None,
      SQLDrv_SQLite,
    };
    
    /**
     * SQL FUNCTIONS
     */
    dllimport final function SQL_initSQLDriver(int aSQLDriver);
    dllimport final function int SQL_createDatabase();
    dllimport final function SQL_closeDatabase(int aDbIdx);
    dllimport final function bool SQL_selectDatabase(int aDbIdx);
    dllimport final function bool SQL_loadDatabase(string aFilename);
    dllimport final function bool SQL_saveDatabase(string aFilename);
    dllimport final function bool SQL_queryDatabase(string aStatement);
    dllimport final function SQL_prepareStatement(string aStatement);
    dllimport final function bool SQL_bindValueInt(int aParamIndex, int aValue);
    dllimport final function bool SQL_bindNamedValueInt(string aParamName, int aValue);
    dllimport final function bool SQL_bindValueFloat(int aParamIndex, float aValue);
    dllimport final function bool SQL_bindNamedValueFloat(string aParamName, float aValue);
    dllimport final function bool SQL_bindValueString(int aParamIndex, string aValue);
    dllimport final function bool SQL_bindNamedValueString(string aParamName, string aValue);
    dllimport final function bool SQL_executeStatement();
    dllimport final function bool SQL_nextResult();
    dllimport final function SQL_getIntVal(string aParamName, out int aValue);
    dllimport final function SQL_getFloatVal(string aParamName, out float aValue);
    dllimport final function SQL_getStringVal(string aParamName, out string aValue);
    
    // DEPREACTED
    dllimport final function SQL_getValueInt(int aColumnIdx, out int aValue);
    dllimport final function SQL_getValueFloat(int aColumnIdx, out float aValue);
    dllimport final function SQL_getValueString(int aColumnIdx, out string aValue);
    
    /**
     * IO FUNCTIONS
     */
    dllimport final function bool IO_directoryExists(string aDirectoryPath);
    dllimport final function bool IO_createDirectory(string aDirectoryPath);
    dllimport final function bool IO_deleteDirectory(string aDirectoryPath, int aRecursive);
    dllimport final function bool IO_fileExists(string aFilePath);
    dllimport final function bool IO_deleteFile(string aFilePath);
    
    DefaultProperties
    {
    }
    Note the enumeration ESQLDriver which shows all available SQL drivers currently implemented in UDKProjectDLL.dll.

    We created our interface class and are now able to call the DLLs function. But wait: If we want to use this class, we need an instance of it. Thats leads to the next step, the real database connection.


    Step: 3 - The database connection
    In the last step we only set up the interface class defining the functions we are able to call, which are exported to UDKProjectDLL.dll.

    But how to use this functions now? We need an instance of this class when we run our game.

    So I set up a bunch of classes which will handle all the stuff.
    Code:
    class DB_DatabaseMgr extends Object
      dependson(DB_DLLAPI)
      config(Database);
    
    
    //=============================================================================
    // Variables
    //=============================================================================
    var private DB_DLLAPI mDLLAPI;
    var private GameInfo mGameInfo;         // variable added to DB_DatabaseMgr...BackReference to current GameInfo
    var config ESQLDriver mDatabaseDriver;  // Used SQL driver set in UDKDatabase.ini
    
    
    final function DB_DLLAPI getDLLAPI()
    {
      return mDLLAPI;
    }
    
    function initDatabase(GameInfo aGameInfo)
    {
      mGameInfo = aGameInfo; // set backreference to GameInfo
    
      mDLLAPI.SQL_initSQLDriver(mDatabaseDriver); // automatically create one empty DB
    }
    
    
    DefaultProperties
    {
      Begin Object Class=DB_DLLAPI Name=DllApiInstance
      End Object
      mDLLAPI=DllApiInstance
    }
    First of all the databasemanager which owns our object instance of the interface class DB_DLLAPI. I decided to call this class DatabaseMgr, because I want to use only this class to access, select and modify the databases. To make this clear i set the reference to my interface object private. Only the databasemanager is able to access the object directly. But to make it visible for other classes i add a function that returns the objects reference.

    Code:
    class DB_FileMgr extends Object
      dependson(DB_DLLAPI)
      config(Database);
    
    
    //=============================================================================
    // Variables
    //=============================================================================
    var private DB_DatabaseMgr mDatabaseMgr;
    var private DB_DLLAPI mDLLAPI;
    
    
    function initFilemanager(DB_DatabaseMgr aDatabaseMgr)
    {
      mDatabaseMgr = aDatabaseMgr;
      mDLLAPI = mDatabaseMgr.getDLLAPI();
    }
    
    
    DefaultProperties
    {
    }
    When I thought about saving and loading databases from hard drive to memory and back, I come to the conclusion I will have to use some function for writing and reading files. Like the databasemanager I want to use a single class for all these things. For convenience I save the reference to the interface object (DB_DLLAPI) within the filemanager too, instead of get the reference from the databasemanager everytime when we need it (we will see this later in our BaseGameInfo).

    Code:
    class DB_BaseGameInfo extends GameInfo
      config(Database);
    
    
    //=============================================================================
    // Variables
    //=============================================================================
    var private DB_DatabaseMgr mDatabase;
    var private DB_FileMgr mFilemanager;
    
    
    final function DB_DatabaseMgr getDBMgr()
    {
      return mDatabase;
    }
    
    final function DB_FileMgr getFileMgr()
    {
      return mFilemanager;
    }
    
    event InitGame( string Options, out string ErrorMessage )
    {
      Super.InitGame(Options, ErrorMessage);	
    	
      mDatabase.initDatabase(self);
      mFilemanager.initFilemanager(mDatabase);
    }
    
    
    DefaultProperties
    {
      Begin Object Class=DB_DatabaseMgr Name=DatabaseMgr
      End Object
      mDatabase=DatabaseMgr
    
      Begin Object Class=DB_FileMgr Name=FileMgr
      End Object
      mFilemanager=FileMgr
    }
    At last we add the databasemanager and filemanager to our GameInfo class and create the objects in the DefaultProperties. I choose the GameInfo class due to several reasons:
    • The GameInfo is created very early when we load a new map
    • The GameInfo is always valid during a game session
    • In a network game the GameInfo is only created on the server


    The last point is very important, because we can be sure local changes on a database by the user wont affect the gameplay, because if all the content for our game is read and stored by the server, cheating the game by modifying the local database is minimized.


    Step: 4 - Use .ini file for smart and flexible database content
    The next step is a small thing I decided for myself to automatically load content databases into memory, so I can use their data for future SQL queries.

    In the UDKDatabase.ini I add the following values and I defined the needed config variables in the specific class.

    UDKDatabase.ini
    Code:
    [UDKDatabase.DB_DatabaseMgr]
    mDatabaseDriver=SQLDrv_SQLite
    mUserCodeRelPathUDKGame=..\..\..\UDKGame\
    mDataRootPath=Content\Data\
    mDefaultContentDBs=database.s3db
    mDefaultGameplayDB=gamedb.s3db
    DB_DatabaseMgr.uc
    Code:
    var config string mUserCodeRelPathUDKGame;
    var config string mDataRootPath;
    
    var config array<string> mDefaultContentDBs;
    var config string mDefaultGameplayDB;
    The databases we will need for this are located in the referenced folders you will see when unzip the attached file to your hard drive.

    Note: mDefaultContentDBs is an array that will allow you to load more than one content database into memory (see Step: 5 - Loading databases).


    Step: 5 - Loading databases
    After setting up the .ini we can load the referenced databases into memory. I will show you my way, you are free to use your own loading routine, if you don't like mine. Just want to show you my approach.

    To load my mGameContentDBs defined in the .ini I created a function in my DB_DatabaseMgr.uc and call this function in initDatabase:

    DB_DatabaseMgr.uc
    Code:
    function initDatabase(GameInfo aGameInfo)
    {
      mGameInfo = aGameInfo;
    
      mDLLAPI.SQL_initSQLDriver(mDatabaseDriver); // automatically create one empty DB
    
      loadGameContentDatabases();
    }
    
    final function loadGameContentDatabases()
    {
      local int il;
      local int lNewDatabase;
      local string lFilePath;
    
      if(mDefaultContentDBs.Length > 0){
        lFilePath = mUserCodeRelPathUDKGame $ mDataRootPath;
    
        // load database into in-memory database 0 (automatically created after calling SQL_initSQLDriver!!)
        if(mDLLAPI.SQL_loadDatabase(lFilePath $ mDefaultContentDBs[0])){
          `log("<<< GameContentDatabase loaded: " $ lFilePath $ mDefaultContentDBs[0]);
        }
    
        // for all additional default content databases create a new database, select it and load database into memory
        for(il=1; il<mDefaultContentDBs.Length; ++il){
          lNewDatabase = mDLLAPI.SQL_createDatabase();
          if(lNewDatabase >= 0){
            mDLLAPI.SQL_selectDatabase(lNewDatabase);
            if(mDLLAPI.SQL_loadDatabase(lFilePath $ mDefaultContentDBs[il])){
              `log("<<< GameContentDatabase loaded: " $ lFilePath $ mDefaultContentDBs[il]);
            }
          }
        }
      }
    }
    Now all DefaultContentDBs defined in .ini will be loaded into in-memory databases. use SQL_selectDatabase to set a specific in-memory database as active for the next queries (see Appendix A for detailed informations to the SQL_ functions of DB_DLLAPI).

    After I load all my content databases into memory, I now will create my main gameplay database and store its identifier in my databasemanager, for easier access. The gameplay database is the most important, I wanna use it to store the character data for all pawns (the main character, the NPC, everything). Additionally the gameplay database will be saved to hard drive as a savegame and load back into memory when we load a previous stored savegame.

    So lets do some small modification to the class and add the following function to the databasemanager. Don't forget to call the new function in initDatabase() after loading the content databases.

    DB_DatabaseMgr.uc
    Code:
    var int mGameplayDatabaseIdx; // additional variable to store the current database index of our gameplay database
    
    function initDatabase(GameInfo aGameInfo)
    {
      mGameInfo = aGameInfo;
    
      mDLLAPI.SQL_initSQLDriver(mDatabaseDriver); // automatically create one empty DB
    
      loadGameContentDatabases();
      createGameplayDatabase();
    }
    
    function createGameplayDatabase()
    {
      local int lNewDatabase;
      local string lFilePath;
    
      lNewDatabase = mDLLAPI.SQL_createDatabase();
      if(lNewDatabase >= 0 && mDefaultGameplayDB != ""){
        lFilePath = mUserCodeRelPathUDKGame $ mDataRootPath;
        mDLLAPI.SQL_selectDatabase(lNewDatabase);
        if(mDLLAPI.SQL_loadDatabase(lFilePath $ mDefaultGameplayDB)){
          `log("<<< GameplayTemplate loaded: " $ lFilePath $ mDefaultGameplayDB);
        }
        mGameplayDatabaseIdx = lNewDatabase;
      }
    }
    As you can see I load mDefaultGameplayDB by default into the new created empty in-memory database. I use this, because my default gameplay database contains all tables i will need for my game, including an internal defines gameplayDB version, so I am able to verify the used gameplayDB version after an update for my game, where the database structure might be changed. Just to ensure the player can use an older savegame with a newer game version.


    The system for filling the gameplay database with data, keep the tables up to date is fairly simple if you know how to work with a SQL database. Just execute some queries for updating, inserting and deleting table content.

    Tutorials for this can be found here http://www.sql-tutorial.net/, for additional informations to the current SQL syntax available for SQLite check out http://www.sqlite.org/lang.html.


    Step: 6 - Keep the gameplay database alive even after a map change
    Now we have a working gameplay database, loaded our content and were able to update and query out databases with the given SQL functions. All of this is possible because the GameInfo is always alive, never being deleted. Keep in mind, the DB_BaseGameInfo owns the databasemanager object which owns the DLLAPI itself. If the BaseGameInfo will be deleted, our databasemanager and the DLLAPI will be deleted to and all our in-memory databases would be lost.
    This leads to the next step: Saving our gameplay database and keep it alive even if we do a map/gameinfo change.

    I created some functions which will help me with this. Maybe not the smartest way, but a working way.
    First I will add two functions to my DB_FileMgr.

    DB_FileMgr.uc
    Code:
    function loadTransition()
    {
      local string lFilePath;
    
      lFilePath = mDatabaseMgr.mUserCodeRelPathUDKGame $ mDatabaseMgr.mDataRootPath;
      if(mDLLAPI.IO_fileExists(lFilePath $ "transition.s3db")){
        mDLLAPI.SQL_selectDatabase(mDatabaseMgr.mGameplayDatabaseIdx);
        if(mDLLAPI.SQL_loadDatabase(lFilePath $ "transition.s3db")){
          `log("<<< GameDatabase restored from Transition: " $ lFilePath $ "transition.s3db");
        }
      }
    }
    
    function saveTransition()
    {
      local string lFilePath;
    
      lFilePath = mDatabaseMgr.mUserCodeRelPathUDKGame $ mDatabaseMgr.mDataRootPath;
    
      mDLLAPI.SQL_selectDatabase(mDatabaseMgr.mGameplayDatabaseIdx);
      if(mDLLAPI.SQL_saveDatabase(lFilePath $ "transition.s3db")){
        `log("<<< GameDatabase saved to Transition: " $ lFilePath $ "transition.s3db");
      }
    }
    The transition file is like a quick save of the current gameplay database. My plan is to save it, when leaving a map, and reload it, when entering the new map.

    So I modified my BaseGameInfo and added this functions:

    DB_BaseGameInfo.uc
    Code:
    function performServerTravel(string aURL)
    {
      if(Class == class'DB_BaseGameInfo'){
        `log("<<<< GameInfo.performServerTravel(): DO NOT SAVE TRANSITION MAP");
      }else{
        `log("<<<< GameInfo.performServerTravel(): SAVE TRANSITION MAP");
        mFilemanager.saveTransition();
      }
      WorldInfo.ServerTravel(aURL);
    }
    
    function performServerTravelDone(string aOptions)
    {
      if(Class == class'DB_BaseGameInfo'){
        `log("<<<< GameInfo.performServerTravel(): DO NOT LOAD TRANSITION MAP");
        return;
      }
    
      `log("<<<< GameInfo.performServerTravel(): LOAD TRANSITION MAP");
      mFilemanager.loadTransition();
    }
    and added performServerTravelDone to the InitGame:

    Code:
    event InitGame( string Options, out string ErrorMessage )
    {
      Super.InitGame(Options, ErrorMessage);	
    	
      mDatabase.initDatabase(self);
      mFilemanager.initFilemanager(mDatabase);
    	
      // do enter function
      performServerTravelDone(Options);
    }
    ATTENTION:

    The last thing that should be done is calling DB_BaseGameInfo.performServerTravel(string aURL) if you want to change the map instead of a simple ConsoleCommand("open"), so you ensure the transition database is stored to hard drive before leaving the current map. This is the only thing where you have to careful when writing your scripts.


    Step: 7 - Query the databases
    After everything is set up now, we can finally perform some queries on our databases. The following code uses the content database to generate random names (forename, surname, gender) from a database with a simple query, gather the informations from the result and log it onto the console. The code can be placed in any class you wanna have it, I put it into the PlayerController and declared it as exec just for the tutorial now.
    The only thing you have to keep in mind when you want to query the database: You need the GameInfo.

    DB_PlayerController.uc
    Code:
    class DB_PlayerController extends UDKPlayerController;
    
    exec function DB_generateNames(int aMaxNameCount)
    {
      local DB_DLLAPI lDLLAPI;
      local string lForename;
      local string lSurname;
      local string lGender;
    
      lDLLAPI = DB_BaseGameInfo(WorldInfo.Game).getDBMgr().getDLLAPI();
      lDLLAPI.SQL_selectDatabase(0); // content database 0!
      if(lDLLAPI.SQL_queryDatabase("SELECT DB1.FORENAME, DB2.SURNAME, DB1.GENDER FROM NAMES AS DB1, NAMES AS DB2 ORDER BY RANDOM() LIMIT "$aMaxNameCount$";")){
        while(lDLLAPI.SQL_nextResult()){
          lForename = class'DB_Defines'.static.initString(30); // prepare string for DLLBind usage
          lSurname = class'DB_Defines'.static.initString(30); // prepare string for DLLBind usage
          lGender = class'DB_Defines'.static.initString(1); // prepare string for DLLBind usage
    
          lDLLAPI.SQL_getStringVal("FORENAME", lForename);
          lDLLAPI.SQL_getStringVal("SURNAME", lSurname);
          lDLLAPI.SQL_getStringVal("GENDER", lGender);
          
          `log("Generated name: "$lForename$" "$lSurname$" ("$lGender$")");
        }
      }
    }
    
    
    DefaultProperties
    {
    }
    The static function initString in class DB_Defines is a simple function used to create a string with a certain length, so we can use it in the DLL to save out a string value to.

    Appendix: A - DLLAPI Interface functions
    function SQL_initSQLDriver(int aSQLDriver)
    • Initalize out SQL driver and create a first empty in-memory database.



    function int SQL_createDatabase()
    • Create a new in-memory database.
    • Return an index identifier used by SQL_selectDatabase to select the database as active database.



    function SQL_closeDatabase(int aDbIdx)
    • Close a in-memory datbase with given aDbIdx and remove it from memory



    function bool SQL_selectDatabase(int aDbIdx)
    • Set an in-memory database at given index as active database. All following functions work on the selected database!
    • Return true if database selection was successful.



    function bool SQL_loadDatabase(string aFilename)
    • Load a database from given filename into the selected in-memory database. ''Completely override previous content!''
    • Return true if loading was successful.



    function bool SQL_saveDatabase(string aFilename)
    • Saves the current selected in-memory database to given filename.
    • Return true if saving was successful.



    function bool SQL_queryDatabase(string aStatement)
    • Saves the current selected in-memory database to given filename.
    • Same like calling SQL_prepareStatement and SQL_executeStatement.
    • Return true if query execution was successful.



    function SQL_prepareStatement(string aStatement)
    • Prepare a statement for the selected database.
    • All statements for a database will be cached internally for reuse, if aStatement wasn't changed (case-insensitive comparison)



    function bool SQL_bindValueInt(int aParamIndex, int aValue)
    • Binds a integer value to an parameter of the previous prepared statement for the given aParamIndex.
    • Returns true if binding was successful.



    function bool SQL_bindNamedValueInt(string aParamName, int aValue)
    • Binds a integer value to an parameter of the previous prepared statement for the given aParamName.
    • Returns true if binding was successful.



    function bool SQL_bindValueFloat(int aParamIndex, float aValue)
    • Binds a float value to an parameter of the previous prepared statement for the given aParamIndex.
    • Returns true if binding was successful.



    function bool SQL_bindNamedValueFloat(string aParamName, float aValue)
    • Binds a float value to an parameter of the previous prepared statement for the given aParamName.
    • Returns true if binding was successful.



    function bool SQL_bindValueString(int aParamIndex, string aValue)
    • Binds a string value to an parameter of the previous prepared statement for the given aParamIndex.
    • Returns true if binding was successful.



    function bool SQL_bindNamedValueString(string aParamName, string aValue)
    • Binds a string value to an parameter of the previous prepared statement for the given aParamName.
    • Returns true if binding was successful.



    function bool SQL_executeStatement()
    • Executes the previous prepared statement.
    • Return true is execution was successful.



    function bool SQL_nextResult()
    • Set result pointer to the next result set.
    • Return true if result pointer is valid. Use while(SQL_nextResult()) to iterate through the query result.



    function SQL_getIntVal(string aParamName, out int aValue)
    • Get the integer value for the given aParamName from the current result set.
    • If aParamName is invalid aValue will be 0.



    function SQL_getFloatVal(string aParamName, out float aValue)
    • Get the float value for the given aParamName from the current result set.
    • If aParamName is invalid aValue will be 0.0.



    function SQL_getStringVal(string aParamName, out string aValue)
    • Get the string value for the given aParamName from the current result set.
    • If aParamName is invalid aValue will be an empty string.



    // DEPRECATED
    The following functions are still available but marked as deprecated and shouldn't be used any longer. Like the three getter functions they return the specific value for a given index within the result (means the column index of the result row):

    function SQL_getValueInt(int aColumnIdx, out int aValue)

    function SQL_getValueFloat(int aColumnIdx, out float aValue)

    function SQL_getValueString(int aColumnIdx, out string aValue)



    Appendix: B - SQL Administration tools & useful links
    When working with a database it is helpful to use external tools for database administration. Means creating tables, fill tables with data, and develop queries which will be used by the UnrealScripts at runtime. Compiling and running Unreal everytime you change the database to see the results is time consuming and not necessary.

    One of the tools I use for creating a database and administrate it:

    Other useful links:
    Attached Files

    #2
    Awesome, favorited.

    Comment


      #3
      This looks nice. What is the license? Open source? I would hate to start using something and not be able to update it.

      Comment


        #4
        Will release the sources as OpenSource too...Need to document them and create a small DoxyGen first, would be glad if after releasing the sources changes on the source code reaches me, to implement good additions to the library as well

        Comment


          #5
          Excellent work BlackHornet80, a well-employed database can save a programmer hours of needless specific class coding and can reduce file sizes immensely. For example, 57 usable items in a game whose item class is a single file and whose specific variables are held in a table referenced in that class would use 2 files, not 57 files. This leads to more efficient and robust programs, as well as modular deployment of content for present and future works making the leg work of rolling out an expansion pack or sequel substantially easier than reinventing the wheel and recoding characteristics of objects. (or even updating/fixing the current release) It also reduces the headaches of botched attempts to cut/paste old code and hack it to fit new naming conventions and variables by organizing all your variables into an easily editable file.
          Long story short, take notes kids. This is good design advice.

          P.S. ...Cyberpunk? RPG?! Why does Deutschland get all the cool stuff? :-)

          Comment


            #6
            For everybody who is interesed in the sources...i released my source under the NewBSD license, you can download the zip or access the sources via svn.

            Links for download/checkout are added to the first post of this thread (http://forums.epicgames.com/showthread.php?t=726137).

            There are NO functional changed from 1.0.5.1 to 2.0.0.0, I changed the version number due to license change.

            Comment


              #7
              Wowsa, thank you, this took some serious time and effort, much appreciated!

              Comment


                #8
                Awesome work there mister, will be useful in the future. Thanks!

                Comment


                  #9
                  New released version 2.0.1.0 available as package and on SVN. see http://forums.epicgames.com/showthread.php?t=726137

                  Comment


                    #10
                    would it be possible for you to post an example of a database that would work with the code above?

                    also could you speculate a bit futher on the uses of the in regards to say an inventory or quest system in an RPG genre project.

                    Im currently working to create a set of prototypes that I would like to release to the community when i am finished that could be used in an RPG. For example I am creating a set of prototypes that can either be used together to make an RPG style game or used separately. I am working on the following ideas: Dialogue System, XP system, Looting system, Inventory System, Quest/Journal System, And leveling system. I have finished The dialogue system but I am currently working on removing its reliance on Kismet, I have also completed the XP system tho all it does for now is math. Looting system I have finished as well as it was the easiest part but it needs to be implemented to the undeveloped inventory system.

                    So In regards to above could you speculate a bit more on how databses could be used to store information for Inventory and Quest type systems and then used with UI (or scaleform) to display results for the end user.

                    I am currently working on researching these things and have no current experience with SQL.

                    Comment


                      #11
                      In the original post near the bottom BlackHornet80 put some nice links to the software required and tutorials for their application. Good documentation is also a hallmark of a skilled professional. As far as speculating on db applications in your game, dialog should probably still be handled by something akin to kismet, (due to the nature of dialog being easily forked and occasionally circular a scene manager like kismet is probably still the best way to go.) But Loot, Inventory, Pawn Statistics (like XP and level) would all be great choices to database. The latter in particular as UDK's engine DOES NOT carry changes in game data or pawns across to new maps. As outlined in the original post a database can keep these changes and "migrate" them to the new level. Which is sweet for anyone that wants to I dunno, do any genre other than a box level DM/DOM/CTF type of game. Or reduce level sizes to accommodate a wider, lower hardware level client base and just link them with short load screens or a slower background load for nearby areas during gameplay targeted at speeds that will complete before the character can physically move to that area. (Hey, that's a good idea... I wonder how to implement that in udk...)

                      Other than that SQL is a link between a database and a program, you still have to make a database to store data in and a program to send it to and vice versa as it is a two-way pipe. I can't tell you how to build your game that would diminish your creativity, but an example table would be something like this with lots of entry rows below Timmy Jones. (yes, keyboard art sucks, but it saves me from making a whole new database)
                      __________________________________________________ _______
                      | Player_name | Player_Level | Player_Class | Player_Gold | status |
                      |____________|___________|____________|__________| _______|
                      |Timmy_Jones_|____55_____|___mage____ | ___34526_ | banned|
                      |____________|___________|____________|__________| _______|

                      You will use your game code (BlackHornet80's code rather) to reference external SQL to reference the field names (the top row) and then return those values (the boxes under the first row) to the GameInfo file, so when Timmy Jones picks up a bag of loot he gets a random pile of loot from the loot database that you set up.

                      In game application is kinda like this :
                      Timmy: "Sweet, I killed it! Lets see what the dreaded pig monster dropped!" *click corpse*

                      GameInfo: "Clicking on dead corpse requires a call to the external loot table. SQL! I need you to find loot for Timmy Jones off of the pig monster table in the Bariellon Forest directory."

                      SQL: "Okay dude, Hey pigmonstertable.db how much sweet lootz does Timmy Jones get?

                      Pigmonstertable: "Oink, 3 copper coins, 1 crusty pelt, 2 busted tusks.."

                      SQL: "Thanks pigmonstertable, hey Gameinfo add 3 copper coins, 1 crusty pelt, and 2 busted tusks to Timmy Jone's Inventory file.

                      GameInfo: got the package can you drop it in the PlayerInv table... Oh no hang on guys he's going to change maps! Saving Playerconfig for transfer WOAAAHAH!" Loading Playerconfig... phew it's all still there.

                      SQL:"Yeah, I'm good like that..."

                      Maybe I should do a youtube sock puppet show for this stuff. It could be quite fun. Sock puppets rock.

                      This is a very basic description of what really happens behind the scenes, super cereal though, read and follow along with the first post as best you can after reading up on SQL and database creation if this is something you wish to include in your game. You may want to devote one member of your dev team to doing databases if you do for they are rather robust tools if you design them well and take a bit of work to design well. Not to mention all the work of inputting field values, but if configured well they will still save you tons of time in the long run and will permit simple changes to be made, no more digging through your code to find the broken variable. Now if we could just prevent all typos...

                      This rant may be edited in the future to clarify if I described something wrong. Good luck and happy data crunching. (blasting creepy trance helps) www.w3schools.com has tons of free tutorials to learn tons of languages like JAVA, SQL, HTML, just to name a few. worth a peek if you are serious about wanting to code.

                      Comment


                        #12
                        Great Thanx! That was both informational and entertaining! Ill just have to play with it now to get it working on the uscript side. I primarilly interested in it being used to keep track of the players current inventory. Everything else I have an idea on how to get it working

                        Comment


                          #13
                          So, your code supports SQLite only?

                          Comment


                            #14
                            SQL in and of itself is a programming language, meant to query and update data between databases over networks. The code should work fine with alternative database software as long as it uses SQL and does not employ any of the special functions listed on the "SQL as understood by SQLite" link at the bottom of the initial post or any other SQL language that they omitted in SQLite's creation.

                            SQLite CAN'T DO the following: (This is for SQL-experienced folks who just want the meat of. . . "Can I pull it off with SQLite?" without reading the site.)
                            Right and Full Outer Joins, it only does Left Outer Joins.
                            ALTER TABLE Command only supports the Variants RENAME TABLE and ADD COLUMN.
                            FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
                            VIEWS are read only.
                            GRANT and REVOKE don't work. Access to files is reliant on the underlying OS.

                            So in a nutshell since you are making your databases with another program like Open Office or Microsoft Office and simply using the code above to query the database not modify it you should be in the clear for compatibility regardless of which database administration software you choose. Provided your preferred one doesn't have any unsupported features which would interfere with the code as written... but then again I would have to read the code itself and cross-reference it with supported language for both software.

                            But that sounds like work I got a degree in and am still not getting paid for. So we'll see if I make a hobby of it after I move back in with the folks for the [bleep]ing nth time since graduating college 2 years ago...

                            Apologies, I get cranky when I have to portion my meals to less than one serving size.
                            I sincerely love you all and hope I have helped cleared some stuff up for ya about SQL.

                            Inappropriate XXX's and Creepy Grandpa OOO's X-D

                            Chops

                            Comment


                              #15
                              LOL... thanx mate. I'm gonna try it out with MS SQL Server sometime next week.

                              Comment

                              Working...
                              X