[Documentation] - Use an embedded database with UDK

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

[YousefBuHazza]

Amazing work
(thumbs up)