SQL implementation

#1 There are resources to connect to MySQL, PostgreSQL and SQLite. Maybe even more. Search!

#2 If you're looking for quick and simple, without a lot of heavy "rpg-style" traffic I would recommend using the SQLite resource. SQLite is still very fast and requires no server to setup and configure, the database sits in what appears to be a "flat" file.

If you're looking for a database framework with some more "meat" to it, I highly recommend Tony Richards' Event Driven Database code. It's multi-threaded and event-driven (as the name implies) which means that your server can still respond to game-related requests while waiting for database responses. Very cool.

#3 Forgive me, I searched for SQL and databases and nothing came up. Thanks very much for both answers, highly appreciated :)

#4 If you are preparing a persistent multiplayer world, you will find MySql or PostgreSQL handy. Because of licensing, you better use MySQL if you do not share the server code, and PostgreSQL if you do.

Although Mark's post is an event driven MySQL resource, here is an alternate that's a bit simpler implementation - I'd probably use Mark's link now though, but it used to work for me:

MySQL integration for TGE/TGEA by Henri Garle

PosgreSQL:

Pooled PostgreSQL database by Danni

However, if you are creating a single player game, you probably want to use SQLite to store data.

#5 Thanks again for the useful answer.
Also, what do you mean because of licensing? I read that PostgreSQL is free to use for all people?

#6 Tal, you're welcome.

I meant that if you want to redistribute the MySQL server, then you need some other, non-free license. I know that PostgreSQL has no such limitation. It all depends on what game you are making:

- For a single player game, use SQLite
- For multiplayer RTS and FPS games, where players can host games, use PostgreSQL or SQLite
- For multiplayer games, where the central database is protected and not redistributed (such as World of Warcraft) you could use MySQL or PostgreSQL

Since you are thinking about logging users in, you could choose between MySQL and PostgreSQL. If you have more experience with one over the other, choose that. If you are not experienced with either one, or know both of them pretty well, your best bet is to use the Event Driven Mysql Database implementation to which Mark linked to.

#7 Be wary though the Event Driven database resource is not one for the feint hearted, it doesn't implement condition variables so the thread created consumes a fair amount of CPU usage. Also, adding any additional functionality will require some good understanding of multithreaded development in C++ (not the easiest of topics).

#8 Or you could just use httpobject and do the login via php. It works well and doesn't require much code change as SQLite or other resources.

#9 Yes, but if he does it through httpobject, the server still needs to be able to access the data that are to be protected by the login system. If you mean that he could access these data through a http tunnel, that'd be possible, true, but that'd still require development both server and client side - even if in php.

Besides, odds are that it'd be XML based, so it would require a larger bandwidth between the game server and the database server. Also, the tunnel would have to solve a few issues to be secure, so the implementation itself would be more likely to have a security hole.

#10 Konrad, I don't want to start a flame war as he was just asking questions on how to access a SQL database. I was just giving him an option.

I have a working login system that is PHP based so I know it is possible. The game client sends a command to the game server and then the game server talks to a private(meaning accessible only on the inside IP) HTTP server and then sends the results back to the client.

It looks similar to what you see in figure 3 on this page.

#11 @Eric: I'm sorry if I sounded like someone flaming, it wasn't my intention at all! This thread has been really cool, and I am sure it is going to help many developers who are looking for an optimal way to plan the integration of a database into a game - depending on the genre.

Ken Finney's Advanced Game Programming All In One has a chapter on Online Game Services using PHP. That too uses a HTTPObject, and shows how to fetch news for and authenticate the player. This implementation uses HTTPObject to authenticate the user on the server side. Now, this is very useful in a situation where your game's server code is distributed along with your game, so any player can host a server, and you have a centralized registration system for all the players - something like Battlefield 2142 and such.

I was not previously thinking of this possibility, so forgive me for pointing out the cons right away without thinking much about the pros. It is possible, that Tal is creating such a game, and in that case, your solution is the one he should take.

However, if he wants to make an MMO, he'll want the game servers to directly access the login / db server, and not through a httpobject, because there's simply no reason to do otherwise, there are no real bonuses to using httpobject on the server in such a scenario.

#12 Database access is one of the slowest tasks that a game server performs especially if your data volumes are large such as retrieveing a hundred or so rows of character inventory. What happens then when you have a few hundred or indeed thousands of players all trying to login, pick up items, save games, inventory, etc your game server can be spending a fair amount of its time reading and writing to the database in a single game cycle?

Not sure what you class as the game servers, for me they refer to the actual server managing the game world, physics,AI, etc rather than say a character server, master server, etc.

You can implement a multi-threaded design such as Tony Richards Event Driven Database but this adds a huge layer of complexity and multithreaded applications are ones that can introduce lots of bugs, engine crashes, etc.

Another option and one used in most MMO designs is to have a seperate server handling either your database requests or your character server (for example Minions of Mirth and the MMOKIT on here are configured that way) to take that load off of the actual game server leaving it to focus on core gameplay.

What really would need deciding is the method used to communicate between game server, client and the database/character server - the HTTP protocol is one choice it's easy to use but it's slow, which may make it a great choice for a small indie game with a few hundred or thousand users with fairly low volumes of data but it's not best suited for larger volumes where something like UDP/TCP would be a much better choice.

No one solution is the right one, they're all valid solutions - it's just finding the right one for your project.

#13 Thank you once again for all your answers, I am intrigued by the amount of solutions there are for logins and databases in games.

As for what I am using this for, it is a Third Person shooter that will allow each user to customize their character equipment, accessories and clothing. I was thinking the way of doing this will require a database for the users and information of the user will be stored on that database in a different table. This would be an easy way to do this, but from what I am understanding from Andy it is also a slow way. Except I must remind myself that character information, inventory and other things are updated only outside of game time and while in the lobby.

I am thinking at the moment that the way to do this is to have one server handling the database and the other for the actual game server, which Andy has pointed out has been used before.

Again, thank you for all your answers. :) Any more opinions or useful information will be accepted kindly.

Tal

#14

Quote:Another option and one used in most MMO designs is to have a seperate server handling either your database requests or your character server (for example Minions of Mirth and the MMOKIT on here are configured that way) to take that load off of the actual game server leaving it to focus on core gameplay.

I would like to point out that the Event Driven database code also connects to a database via ODBC. This is significant because the code itself is agnostic to the kind of database used (MySQL, MSSQL, SQLite, Oracle, PostreSQL), and having the DB server on a separate machine makes no difference to the code since the connection is handled via the ODBC source.

#15 While ODBC's pros are awesome, it was not really meant to work in a high traffic environment. If you are expecting several thousands of players simultaneously sending queries to the game / site, I wonder if this would be the best choice. I probably would not want something in the middle that was planned to be a fit-for-all for accessing data - but I could as well be wrong.

Everything of course depends on the ODBC driver that you are using. The driver needs to implement the features of the database of choice. Unfortunately, these drivers rarely evolve as fast as some of the dbs. I'm not sure you could use stored procedures or even subselects with the latest ODBC drivers for MySQL. Yet, these features make MySQL truly available for optimizing the database usage of a high traffic MMO.

I vote for a named pipe or tcp direct connection - depending on whether the db is on localhost or not. Just by thinking of having something in the middle it makes more sense to me. I can't say for sure that it's faster, but to me, it seems that way.

It would be interesting to run a few tests and see how they compare against each other under stress.

#16 Very true, ODBC does add another "layer." I can almost guarantee that it's not as fast as the "native" SDK for any particular database that is being used, but it does give that resource a lot of flexibility out of the box.

The important point I wanted to make was in relation to Andy Rollins' post that seemed to indicate that moving the DB to a separate server as an alternative to multi-threaded. Indeed, having a separate DB server is something that I would consider essential for writing a database-driven game such as a mutli-player RPG. However, having the DB on a separate machine still doesn't solve the problem of what the game server is doing while it is waiting for DB requests to be resolved. "Standard" DB implementation logic (like the SQLite resource) makes a database request, and then sits and waits for the request to be completed before proceeding. The Event Driven Database code allows the server to process other game-related requests while waiting for a response from the DB server.

I also agree that "off-loading" certain tasks from the main game server is something that is a really good idea. Character Creation is a classic example of something that doesn't need to be "in sync" with the regular game server and can (and probably should) be given as a task for another server.

#17 @Mark: I have not yet implemented the event driven database that you've linked to, but you are definitely right about how it's a waste of time to wait for the db with any queries.

However, I'm worried about the extra CPU resources it requires for the separate threads. Also, it's discomforting to think of how it would slow the game server down when the load is extreme.

So I was thinking about trying a simple mysql implementation such as the one by Henri Garle, and passing queries in the built in Event Manager. For some reason I'm thinking that this would be a bit lighter on the resources, but I'll have to try and see.

Edit: Though it seems I'd still have to fork threads, that Event Manager does not seem to work like that.

#18 I just dived a bit more into how thread safe TGEA is. I've reached a door where I'm not ready to step through yet. There is something really big and ugly behind that door.

Quote:
"It's Multi-Threading safeish" - Ben Garney

That's comforting. Even if I understood everything about the hassles of Multi Threading, the best I could do would be an unstable pile of code?

#19 Mark - Actually the Event driven database resource uses mysql++ and not ODBC so you don't get those advantages, however, if you're toying with using a multithreaded app your C++ should be a good enough level that altering the resource to use ODBC or another engine specific API shouldn't really be much of a challenge for you.

I quite agree with Mark the actual database engine needs to be on it's own box, what I'd meant was the server accessing the database rather than the database itself - you've put it better than I did, with the what the game server is doing whilst waiting for a response from the database.

That's where I believe you have 2 solutions:
- Multithreaded i.e. the Event Driven database resource.
- Seperate DB server, i.e. Game server talks via TCP to DB server and DB server talks to the database.

I've been down both roads on projects and the multithreaded option worked really well and we added a lot of functionality to that resource it did have it's issues:

- We had memory leaks and found functions in TGE that were not thread safe causing the engine to crash at random spots, as anyone that has ever tried debugging a multithreaded application will know it's a minefield and you can lose a hell of a lot of development time. We had to wrap a few of torques functions to make them thread-safe.

- Scalability - with a threaded App you are limited to what a single box can run, now ok you can buy huge beasts of machines with lots of CPUs but they cost big bucks. Plus, if you start on a small machine then each time you upgrade that machine the game world has to be brought down to migrate to a new world. If you seperate all your elements I can just crank up another version of whatever server is getting hammered and keep it all running (assuming your design had this in mind).

- Resilience - if everything is in one game engine (threaded) then a problem with one part may require the entire lot to be brought down. If you seperate each elements you can run multiple say character servers or database access servers if one goes down theres always the fallback of using the others.

- Simplicity - If you're seperating things out you can use whatever language you like to code each element, personally I'd start with a scripting language like Python or Lua it's a million times easier to script than develop a multithreaded C++ app.

I benchmarked Twisted Python versus multithreaded database server reading/write millions of rows - the multithreaded app did perform better but not significantly more that I'd consider using it over a scripted solution where I can rapidly prototype and not worry about the other headaches.

The threaded app used a lot more CPU (it's mentioned in the resource that currently it doesn't use condition variables so the database thread is always active checking for events and as it runs in it's own thread it cycles around a hell of a lot of times), you really need to add condition variables which is easier on Linux/mac than it is on windows.

The scripted solution used more network bandwidth - however assuming all your machines are hosted on the same LAN it's local traffic across what should be 100mb or gigabit lans rather than much slower WAN access.

I'm a great advocate of there never being one way to do things and overall both are viable solutions that I've used and got working well in game but personal preference and simplicity steers me towards seperating the data acess out.

#20 @Andy: So does that mean that you'd do something like stored procedures, except that complex sql tasks would be stored in script procedures rather than a glob of sql?

That sounds interesting, because it looks a lot more manageable than either code or sql stored procedures. But something tells me that it's a waste of optimization that the database server can do for you when you use stored procedures. Maybe the two could be combined - ie. wrapping calls to sql stored procedures in script - so you get to enjoy the pros of stored procedures, and you can tune your sql without having to recompile. Sweet.

Thanks for the idea!