Populating a World/Zone with MySQL data?
by Rodney (OldRod) Burns · in Torque 3D Professional · 10/15/2009 (4:11 pm) · 13 replies
I'm working on a non-massive online RPG (NMORPG? :)) and I'm trying to brainstorm a way to control the world creation.
I have a mySQL server set up, linked into Torque3D, to allow players to create and maintain accounts, and log in to the game world. I am also setting up tables to maintain inventory, bank space, etc.
What I'm planning is to have a separate mySQL table for the spawn information of each zone (mission file) when the server is first brought up. I'm wondering if it would be possible for T3D to create a mission file "on the fly" with data pulled from that mySQL table.
For instance, I have a base mission file that sets up the terrain and other permanent architecture that is always present in the zone. Then at start-up time, all the mob's information, or other temporary/player-created architecture is inserted into this mission file according to data in the mySQL table. At server shutdown, this same information is re-saved to the table, so that on next startup they can be put back to the same state they were in at shutdown time.
If I can implement this properly, this should allow the creation of a player housing system, where players can build structures that exist physically in the game world (as opposed to an instanced system), and allows for this information to be dynamically maintained between server maintenance/updates.
Has anyone attempted this with Torque? Is there some "gotcha" that I'm not thinking of? How would I go about starting on such a system?
I have a mySQL server set up, linked into Torque3D, to allow players to create and maintain accounts, and log in to the game world. I am also setting up tables to maintain inventory, bank space, etc.
What I'm planning is to have a separate mySQL table for the spawn information of each zone (mission file) when the server is first brought up. I'm wondering if it would be possible for T3D to create a mission file "on the fly" with data pulled from that mySQL table.
For instance, I have a base mission file that sets up the terrain and other permanent architecture that is always present in the zone. Then at start-up time, all the mob's information, or other temporary/player-created architecture is inserted into this mission file according to data in the mySQL table. At server shutdown, this same information is re-saved to the table, so that on next startup they can be put back to the same state they were in at shutdown time.
If I can implement this properly, this should allow the creation of a player housing system, where players can build structures that exist physically in the game world (as opposed to an instanced system), and allows for this information to be dynamically maintained between server maintenance/updates.
Has anyone attempted this with Torque? Is there some "gotcha" that I'm not thinking of? How would I go about starting on such a system?
#2
10/15/2009 (7:11 pm)
Thanks Ted!! That's exactly the tip I was looking for :) 
#3
10/15/2009 (7:11 pm)
The "mission" file is just a script that creates a bunch of objects on the server-side. You can, of course, ditch the static-script (or use one with only barebones objects) and create objects dynamically during the server-creation phase. 
#4
Thanks in advance,
= Ed =
03/02/2010 (3:15 pm)
@Ted, @Rodney: How are you guys hooking into MySql from Torque? I'm very interested in this for per-user authentication - which it looks like you've already got working Ted!Thanks in advance,
= Ed =
#5
www.torquepowered.com/community/resources/view/13528
03/02/2010 (4:55 pm)
@Ed, they are most likely using this resource updated for T3d www.torquepowered.com/community/resources/view/13528
#7
03/02/2010 (8:34 pm)
I actually use the ODBC resource (it's a carry-over from the previous versions of the engine, but it drops right in), though any of the several that are out work fine as far as I can see from the threads attached to them.
#8
A better approach would be having a server-side application that provides the game with a sanitized interface for the queries.
03/03/2010 (12:42 pm)
Wouldn't having the game perform remote database queries directly be considered unsafe? If someone got the login and password off the game they'd have a wide control over the database.A better approach would be having a server-side application that provides the game with a sanitized interface for the queries.
#9
Quite. Of course, right now I'm in a testing phase, so that's not yet a concern. When it is (a few months, I think), there is a three part IBM whitepaper about building MMO backend infrastructure using HTTPObjects and database calls separated for security purposes.
Also, you don't do any calls from the client, except to tell the server "hey, I want to do this", and then the server makes a request, so I actually don't have to redo any of that when the infrastructure change occurs.
03/03/2010 (12:51 pm)
Quote:Wouldn't having the game perform remote database queries directly be considered unsafe?
Quite. Of course, right now I'm in a testing phase, so that's not yet a concern. When it is (a few months, I think), there is a three part IBM whitepaper about building MMO backend infrastructure using HTTPObjects and database calls separated for security purposes.
Also, you don't do any calls from the client, except to tell the server "hey, I want to do this", and then the server makes a request, so I actually don't have to redo any of that when the infrastructure change occurs.
#10
Anytime the player actually wants to take action that changes something, just filter it through the Torque server, which itself logs into a mySQL account with additional priledges.
Maybe I'm missing something here.. it's been quite a while since I've used mySQL, but I was certain you could, say, give a specific account access to only the SELECT command and only to certain DB's/tables.
03/03/2010 (1:06 pm)
This does introduce extra lag in some setups, but couldn't you just put the clients on an account which has only read access, and only to specific databases?Anytime the player actually wants to take action that changes something, just filter it through the Torque server, which itself logs into a mySQL account with additional priledges.
Maybe I'm missing something here.. it's been quite a while since I've used mySQL, but I was certain you could, say, give a specific account access to only the SELECT command and only to certain DB's/tables.
#11
You don't want to do that for a few reasons. If the client has the ability to do queries- you have to assume that your dso's will be decompiled. Once that happens, they have passwords and logins for the client account (if it's a special one), and then from there they can look at the functions you use to query the data, and then reverse engineer your database. It sort of happens anyway as the players datamine for those "strategy" sites, but that's a much slower process than this. They can probably also figure out how to do SQL injection attacks too, using those queries. Putting anything on the client that can lead to within seven miles of it is asking for it.
Even with the servers, you'd want to have them query a web server that itself gets the information from the database server, and does not run the game server. This way, if the game servers get hacked, it limits damage. As for lag, you're probably not going to experience it on the side of the servers, but rather between the client and the server, due to the internet, crappy hardware, etc. I also read an article that talked about how with Anarchy Online, the lag was from loading textures, and those who complained about the lag being so bad mostly had lower-end graphics cards.
That actually goes to world-building, and how you use "blocking" to control what's in view. For the cities in Epic Frontiers, we're using ways of getting to them that doesn't flood the viewport until you're basically inside the city (and you need it anyway), and even then doesn't (usually) open into a grand view of the whole place. When that happens, the server is going to fire off a lot of traffic to you, and if a lot of people do it at once... lag.
I'm probably going to post a lot of lessons I'm learning and setting down for my other designers for world-building, since there's a lot of little things that make it easier and more efficient. No idea when that's going to be, since I'm in crunch mode for GDC :)
03/03/2010 (1:28 pm)
Quote:but couldn't you just put the clients on an account which has only read access, and only to specific databases?
You don't want to do that for a few reasons. If the client has the ability to do queries- you have to assume that your dso's will be decompiled. Once that happens, they have passwords and logins for the client account (if it's a special one), and then from there they can look at the functions you use to query the data, and then reverse engineer your database. It sort of happens anyway as the players datamine for those "strategy" sites, but that's a much slower process than this. They can probably also figure out how to do SQL injection attacks too, using those queries. Putting anything on the client that can lead to within seven miles of it is asking for it.
Even with the servers, you'd want to have them query a web server that itself gets the information from the database server, and does not run the game server. This way, if the game servers get hacked, it limits damage. As for lag, you're probably not going to experience it on the side of the servers, but rather between the client and the server, due to the internet, crappy hardware, etc. I also read an article that talked about how with Anarchy Online, the lag was from loading textures, and those who complained about the lag being so bad mostly had lower-end graphics cards.
That actually goes to world-building, and how you use "blocking" to control what's in view. For the cities in Epic Frontiers, we're using ways of getting to them that doesn't flood the viewport until you're basically inside the city (and you need it anyway), and even then doesn't (usually) open into a grand view of the whole place. When that happens, the server is going to fire off a lot of traffic to you, and if a lot of people do it at once... lag.
I'm probably going to post a lot of lessons I'm learning and setting down for my other designers for world-building, since there's a lot of little things that make it easier and more efficient. No idea when that's going to be, since I'm in crunch mode for GDC :)
#12
I also gather that a lot of people are using this for true MMO systems, where data beyond simply savable character info will be run through the database, so players might be able to use those queries to cheat by intercepting the locations of players and other potentially sensitive status information you want to regulate.
In other words, no one listen to me. Filter your database access. Make the client talk to the torque server directly and still put another step between that and the DB.
As far as lag, I was referring to the extra hops put in place by having, for example, a client inventory update go from DBServer->Webserver->TorqueServer->Client. I guess in any real world setting these servers are likely all on the same local network, so it wasn't even worth mentioning.
03/03/2010 (2:24 pm)
Ah, yeah, good points... I'm underestimating how dedicated to breaking things people are again. I suppose the real issue here is in trusting the security of mySQL servers themselves -- while in theory players being able to log directly into your database with read-only access would be harmless, in reality it gives them way more information than anyone would like to send out about how the data is organized, not to mention opening this core server up to direct assault by providing its IP to every player.I also gather that a lot of people are using this for true MMO systems, where data beyond simply savable character info will be run through the database, so players might be able to use those queries to cheat by intercepting the locations of players and other potentially sensitive status information you want to regulate.
In other words, no one listen to me. Filter your database access. Make the client talk to the torque server directly and still put another step between that and the DB.
As far as lag, I was referring to the extra hops put in place by having, for example, a client inventory update go from DBServer->Webserver->TorqueServer->Client. I guess in any real world setting these servers are likely all on the same local network, so it wasn't even worth mentioning.
#13
Well, if you're running a serious MMO, then you've got a serious backplane. Blizzard most likely has more fiber in their NOC than a shipping container full of Shredded Wheat. A small MMO (a few boxes), you can get away with gigabit ethernet between them (or teamed adapters), but it just depends on how chatty your servers are.
03/04/2010 (3:23 pm)
Quote:I guess in any real world setting these servers are likely all on the same local network, so it wasn't even worth mentioning.
Well, if you're running a serious MMO, then you've got a serious backplane. Blizzard most likely has more fiber in their NOC than a shipping container full of Shredded Wheat. A small MMO (a few boxes), you can get away with gigabit ethernet between them (or teamed adapters), but it just depends on how chatty your servers are.
Torque 3D Owner Ted Southard
In scripts/server/game.cs, at the end of the startGame() function, I've placed a populateMission() function that loads data from a table. The table has the type of asset as well as extended data (can be a string, for the cases where I was loading foliage replicators), and that type is used in a switch statement to handle each loading as it's meant to be (NPCs have special spawn functions, and here they load just like they would if generated somewhere else in the game).
It works well, and while I can probably optimize the process, I find it to be almost as fast as loading the mission otherwise, except you've got a more dynamic zone for it. Hope that helps.