[Documentation] - Use an embedded database with UDK

[BlackHornet80]

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:

• SQL Tutorials
• SQLite SQL Systax

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.

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.

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.

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).

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

[UDKDatabase.DB_DatabaseMgr]
mDatabaseDriver=SQLDrv_SQLite
mUserCodeRelPathUDKGame=..\..\..\UDKGame\
mDataRootPath=Content\Data\
mDefaultContentDBs=database.s3db
mDefaultGameplayDB=gamedb.s3db

DB_DatabaseMgr.uc

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

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

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

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

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:

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

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:

• SQLite Administrator its very easy to use and available in many languages.

Other useful links:

• SQL Tutorials SQL Tutorial - Learn SQL
• SQLite SQL Systax SQL As Understood By SQLite

[Ayalaskin]

Awesome, favorited.

[Prezadent]

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

[BlackHornet80]

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

[Chopkeeper]

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? :-)

[BlackHornet80]

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.

[danimal']

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

[musilowski]

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

[BlackHornet80]

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

[n321]

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.

[Chopkeeper]

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.

[n321]

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

[seenooh]

So, your code supports SQLite only?

[Chopkeeper]

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

[seenooh]

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

[BlackHornet80]

In another thread I explained why I choose SQLite to be used. It is a very, very lightweight non-zero-configuration database. You don't have to install other libraries or programs, and you don't need another SQL Server running on your computer.

Sure it isn't a perfect database if you compare it with Oracle, MySQL or Postgres, but it is very easy to use. For my project (a Singleplayer Game) it is perfect.

I prepare all the tables and contents i need: during runtime, i dont alter any table. I create a new database in memory, and load a "template" into this database. The "template" is my database structure, with all tables and columns.

During runtime I only insert, update, delete and query rows. For saving a game, simply dump the "gameplay" db to disk. Loading: load from disk into memory and then load the map (startup of map initalizes all the actors saved to database, maybe spawn them and set their properties).

Note:
If you wanna connect with MySQL or anything else, you are free to write another SQLDriver which wraps all the library functions from your choosen library (MySQL, Postgres, whatever). But in that case I would like to get the changes too, so it can be added to the project as well (open source, you know?).

Btw: Chopkeeper, thanks for all the explanations. I couldn't write it better.

[Chopkeeper]

Meh... I generally attempt to translate technical talk into what passes for English on our side of the pond. I tell ya, teaching rednecks that a query is not a guy with "purdy hair" is actually not that easy... but it offers tremendous insight into individualized vs. group learning methods and how to tailor oneself to appease a demographic.

Also, I apologize if my rants have been disruptive to your thread. Solutions to simplify data transfer and storage just make me a touch excited. *as he places the lotion in the basket and starts lowering it into the hole...* j/k

Brightside, I got a day job now. So no more raging about portioned Ramen... once the first paycheck comes in. lol, lol, sob sob sob... %#@#ing stock market...

[This communication has been terminated by the DoHS. Remember, our little income monkeys, we care... unless we don't.]

Ok, that's my second shot at them I hope they operate on a 3 strikes policy. Or else I'm gonna get a new hat like on V for Vendetta. X-D

Seriously though, I wish you all the best.

[TOP-Proto]

this is very nice - maybe epic can utilise something like this to get rid of datastores next!!!

[kristiandelay]

For anyone new to db programing the example table above could be optimized for faster lookup and cut down on storing names more then once, so name changes or data changes are automatic.

Instead of
Table Login;
+----------- +
| Login | ... |
+----------- +
| Player1 | .. |
+------------+
| Player2 | .. |
+------------+
| Player3 | .. |
+------------+

Make the table
Table Login;
+---------------+
| ID | Login | ... |
+---------------+
| 1 | Player1 | ... |
+----------------+
| 2 | Player2 | ... |
+----------------+
| 3 | Player3 | ... |
+----------------+

Example of friend table
+-------------+
| id | friend_id |
+-------------+
| 1 | 2 |
+-------------+
| 2 | 1 |
+-------------+
| 3 | 2 |
+-------------+
| 2 | 3 |
+-------------+

Now you can see that you have stored less space instead of storing strings everywhere u are storing IDs. So when u change player1 to lets say player5 the friend list will automatically know about the update.

This is a great starting point for anyone looking into udk for rapid prototyping.

[TOP-Proto]

it would be essential reading to read up on database design before writing a database.

you should at least be using atomic data and be AT LEAST following normalisation rules for 2NF - (i would use 3NF tho!)

these rules are set to stop duplication of data and to keep your database optimised and efficient. If you dont follow these rules what you end up with is a glorified excell document posing as a database.

[mictlan27]

When i try to run DB_generatenames function in console i get: command not recognized
Any clue???

[mictlan27]

I've managed to deal with that but now when i start the udk.exe it crashes!
What could cause that?
The error is:

System.AccessViolationException error in UDK
An AccessViolationException is thrown when there is an attempt to read from or write to protected memory.

What does that mean???

[Baphomeh]

If i followed BlackHornet80's suggestion to create a new SQLDriver to enable MySQL (for example) for use in a multiplayer game, would it be hard to add the necessary TCPLink bits to enable the individual "clients" to connect to the MyQSQL db server?

Exactly.. rough overview, what would I need to do to adapt this system to a remote server?

[steve.k]

Firstly, thanks for posting this BlackHornet80, I appreciate you sharing it.

Secondly, has anyone had any issues implementing this with their game? In testing, as per the tutorial, my game will hang when it gets to SQL_createDatabase() in the createGameplayDatabase() function or if I comment out that function then it will hang when calling SQL_selectDatabase(0) in the DB_generateNames function. It must have problems with the dll except that it calls SQL_initSQLDriver(mDatabaseDriver) no worries. I'm not sure how to debug this any further. Does anyone have any ideas to help me with this?

[GreaseMonk]

For those who crash when exiting PIE after installing this math-er piece (hah!), go to DB_DatabaseMgr.uc and find:
"var private GameInfo mGameInfo; "
and
"mGameInfo = aGameInfo;"

comment them out.

what happens is:
" if Object A references Object B, and Object B references Object A, when the GC wants to delete Object A it can't because it is referenced by Object B. "

More about that problem:
http://forums.epicgames.com/threads/...eference-error

[UDKfisher]

Thanks for share!Very nice!

[Ordo]

It compiles just fine, but when I try to run it in PIE or launch it, I get this:
"[0012.29] Log: LoadMap: PF-testlevel1?Name=Player?Team=255?GoalScore=0?TimeLi mit=0?Game=ProjectFerrumGame.ProjectFerrumGameInfo
[0015.86] Log: Game class is 'ProjectFerrumGameInfo'
[0015.96] Log: Primary PhysX scene will be in software.
[0015.96] Log: Creating Primary PhysX Scene.
[0015.97] Log: Bringing World pf-testlevel1.TheWorld up for play (0) at 2013.01.14-18.12.30
[0040.47] Log: === Critical error: ===
Fatal error!

Address = 0x52d498b5 (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UserCode\UDKPr ojectDLL.DLL]
Address = 0x2b5b4bf (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x13efea3 (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x13f4991 (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x77e0e394 (filename not found) [in C:\Windows\SysWOW64\ntdll.dll]
Address = 0x77e0e394 (filename not found) [in C:\Windows\SysWOW64\ntdll.dll]
Address = 0x77e1a326 (filename not found) [in C:\Windows\SysWOW64\ntdll.dll]
Address = 0x77e0e1b2 (filename not found) [in C:\Windows\SysWOW64\ntdll.dll]
Address = 0x77e1a82b (filename not found) [in C:\Windows\SysWOW64\ntdll.dll]
Address = 0x77e0e023 (filename not found) [in C:\Windows\SysWOW64\ntdll.dll]
Address = 0x13f5dfc (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x13ed864 (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x1461c0a (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x1461c22 (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]
Address = 0x1461c22 (filename not found) [in C:\UDK\ProjectFerrum\Binaries\Win32\UDK.exe]"

EDIT: I had to comment out the following line in CUDKSQLDriverSQLite::execute() to get it to work:

case SQLITE_NULL:
lQRIter->second.addValue();

Leaving this here in case someone runs into the same issue. Compiled with Visual Studio C++ Express 2010.