- Home /
How can I store the individual values of EulerAngles on a Database then re-apply
I am creating an MMORPG and I've got a mysql database which is all designed to copy the design on Unity.. at least almost entirely...
I have a Transform table with foreign keys to entries on a Vector3 table which stores X, Y, and Z float values. This much all works. I have stored procedures for updating the location and rotation of the player, as well as for pulling the values.
the issue I'm having is in which values to store IN the database for Rotation.. I have spent the last 5+ hours looking online for help... I'm aware that Euler can be represented in many ways and mean the same thing and understand a gimbal lock so please don't clutter a response with that stuff. Very simply put, I've tried storing the x,y,z from Transform.localRotation and from Transform.localEulerAngles (it needs to be local, as it is the camera on the player, child'ed to a child of the player (perhaps this is part of the issue? - using global I've also tried and it also fails.)
here's the code for storing and for getting the rotation (I have a custom Command Method to clean up the number of code lines for my simpler database procedure calls.)
private void Update()
{
if (Input.GetKeyDown(KeyCode.U))
{
print("Updating Rotation on Server to : " + character.transform.GetChild(3).GetChild(0).eulerAngles + "\n");
PlayerData.Command("UpdateEulerRotation", new MySqlParameter[] {
new MySqlParameter("inUsername", PlayerData.GetUsername()),
new MySqlParameter("inPassword", PlayerData.GetPassword()),
new MySqlParameter("inCharacterName", PhotonNetwork.player.NickName),
new MySqlParameter("inRotX", character.transform.GetChild(3).GetChild(0).localEulerAngles.x),
new MySqlParameter("inRotY", character.transform.GetChild(3).GetChild(0).localEulerAngles.y),
new MySqlParameter("inRotZ", character.transform.GetChild(3).GetChild(0).localEulerAngles.z)
});
}
if (Input.GetKeyDown(KeyCode.S))
{
character.GetComponent<FirstPersonController>().enabled = false;
PlayerData.Command("GetCharacterEulerRotation", new MySqlParameter[]
{
new MySqlParameter("inUsername", PlayerData.GetUsername()),
new MySqlParameter("inPassword", PlayerData.GetPassword()),
new MySqlParameter("inCharacterName", PhotonNetwork.player.NickName)
}, true);
character.transform.GetChild(3).GetChild(0).localEulerAngles. = new Vector3(
(float)PlayerData.ResponseTable.Rows[0]["Vector3_X"],
(float)PlayerData.ResponseTable.Rows[0]["Vector3_Y"],
(float)PlayerData.ResponseTable.Rows[0]["Vector3_Z"]
);
character.GetComponent<FirstPersonController>().enabled = true;
}
}
To shorten any concerns, as I stated previously, the "talking to the database" part is working fine. I get back the X, Y, and Z float values individually, but whenever I apply them, the rotation is WAY off.
I will resort to creating an entirely new table to represent quaternions if I must, but I would really prefer to keep it condensed into one vector3 table.
Shouldn't it be
(float)PlayerData.ResponseTable.Rows[0]["inRotX"],
(float)PlayerData.ResponseTable.Rows[0]["inRotY"],
(float)PlayerData.ResponseTable.Rows[0]["inRotZ"]
Ins$$anonymous$$d of
(float)PlayerData.ResponseTable.Rows[0]["Vector3_X"],
(float)PlayerData.ResponseTable.Rows[0]["Vector3_Y"],
(float)PlayerData.ResponseTable.Rows[0]["Vector3_Z"]
No, I'm not sure how familiar you are with databases in particular, but assu$$anonymous$$g you are familiar enough,
the "inRot[XYZ]" is an input parameter for the stored procedure which inserts the x, y and z values into my vector3 table (on the database) by referencing the UserAccountIndex>UsersCharacterIndex>CharactersTransformIndex>TransformsEulerRotationIndex
the "Vector3_[XYZ]" is the index of a C#'s System.Data (namespace) DataTable which is a copy of the values returned from a separate stored procedure which gets the Vector3 EulerRotation by referencing the UserAccountIndex>UsersCharacterIndex>CharactersTransformIndex>TransformsEulerRotationIndex>EulerRotationsVector3Index>Vector3_[XYZ]
so Vector3_[XYZ] is actually just the column name on the locally stored table, whereas "inRot[XYZ] is the name of the parameter on a stored procedure... it's just the syntax necessary for passing data to a mysql database using Connector/NET
Just remember to hash password before you save it! :)
When you said hashing hopefully you had this in $$anonymous$$d. Anything less than hashing and salting is a bad idea. Also it would be a good idea to generate a session key after the user logged on and don't send the username and password with every request.
Also for extra security you may want to hash the username and password twice, once before you send it off on the client side (with a fix salt) and once again (with user specific random salt) on the server. You can get in real trouble nowadays if you don't handle private date with care.
Answer by SkaredCreations · Oct 08, 2018 at 01:03 PM
First of all you're using wayy too much "character.transform.GetChild(3).GetChild(0)" which will hit the performances since you're basically "searching" for the correct child in every line, you should really cache this Transform into a local or class member variable instead of "searching" for the component at every line.
Then you could store the values as string (like VARCHAR(45) in MySql):
Transform characterTfm;
Vector3 rotationVec;
void Update()
{
if (characterTfm == null)
{
characterTfm = character.transform.GetChild(3).GetChild(0);
}
if (Input.GetKeyDown(KeyCode.U))
{
// ...
rotationVec = characterTfm.localEulerAngles;
PlayerData.Command("UpdateEulerRotation", new MySqlParameter[] {
new MySqlParameter("inUsername", PlayerData.GetUsername()),
new MySqlParameter("inPassword", PlayerData.GetPassword()),
new MySqlParameter("inCharacterName", PhotonNetwork.player.NickName),
new MySqlParameter("inRotX", rotationVec.x.ToString()),
new MySqlParameter("inRotY", rotationVec.y.ToString()),
new MySqlParameter("inRotZ", rotationVec.z.ToString())
});
// ...
}
if (Input.GetKeyDown(KeyCode.S))
{
// ...
var row = PlayerData.ResponseTable.Rows[0];
characterTfm.localEulerAngles. = new Vector3(
float.Parse(row["Vector3_X"].ToString()),
float.Parse(row["Vector3_Y"].ToString()),
float.Parse(row["Vector3_Z"].ToString())
);
// ...
}
}
Anyway it mostly depends on how the rotation is handled by your input controller, that is yiou should know if you need to get/set the local rotation of this child transform or the global rotation of your root player object.
Also remember that it's not a good idea to let the clients access directly to the MySql database in a MMORPG, instead only your server should access and manipulate it.
Sorry about such a long response, I only get mondays and tuesdays to code, I'll respond with my results on tuesday most likely.
Yes, I'm aware that I shouldn't use getchild(x) in production code, this is for development only, just habit of $$anonymous$$e. I always go back and store it in the start later,
I'll definitely look and see how the first person character controller is applying rotation for some pointers, but I believe it is taking values from the mouse, whereas I need raw previous values and to be applied exactly as previously stored in whatever format is necessary using whatever equation...
There's nothing wrong with allowing the clients access to the database, because I am using stored procedures and they only have execute permissions... so it's all safe in the regard, pertaining to the fact you intend that the client should not be able to send values to the server, that is what front-end normalization and limitation is for. And of course there will be hard limits which the server will also control.
I see a couple things in your example I am not familiar with that I will look into and try out and like I said, hopefully respond back on Tuesday, and accept your answer if it helps or state any further troubles.
Thank you for your quick response.
In regard of your statement
There's nothing wrong with allowing the clients access to the database
Generally yes it's possible to set permissions to restrict the interactions. However there are so many other issues which are difficult to fight or even detect.
First and foremost
that is what front-end normalization and limitation is for
No! Front end normalization and limitation is only to improve user feedback and to prevent wrong input. The front end is not part of any security mechanism or validation process in a client-server setup. A client that has direct access to your database can perform any action (they are allowed to) with any data they want. Input data validation can only be done on the server side. $$anonymous$$aybe you do sanity checks in your stored procedure, however your statement about the front is just wrong.
Next is any sort of attack (like DoS attack) on your server will be difficult to detect. Send / query rate limitations are quite difficult to implement this way.
Again keep in $$anonymous$$d that a client can execute any of your stored procedures you gave him permissions for at any time with any parameters they want. Depending on the game state certain things may only be available in certain situations. That means the general game state has to be managed on the server as well and checked in each and every of your stored procedures. While this would be possible it doesn't seem very feasible.
Finally the $$anonymous$$ysql connection protocol has quite a bit of overhead. Especially in a $$anonymous$$$$anonymous$$O scenario you want to keep the data throughput as low as possible.
Actually on topic: Have you actually debugged your situation? Have you tried printing out the values you send to the database and those you get back? However when i said "those you get back" i don't mean doing
character.transform.GetChild(3).GetChild(0).localEulerAngles = ........
Debug.Log("rotation: " + character.transform.GetChild(3).GetChild(0).localEulerAngles);
This is NOT what you get back from the database. Assigning localEulerAngles will convert the angles into a quaternion. Reading them back will convert the quaternion back to eulerangles.
$$anonymous$$eep in $$anonymous$$d that the same localEulerAngles can still result in a different rotation if the parent is rotated different.
I've accepted because you showed the correct format for accomplishing what I needed, and gave some very good tips for anyone else who is, for example, unaware that getchild should be in the start(). Also your point of debugging EXACTLY what I sent to the database and logging EXACTLY what I pull from it led me to realize it is something entirely unrelated to what I thought... something about how I'm processing the command to call my stored procedure is not updating my locally stored table. I realized that what I have in my local table is just the location values not my rotation values. In other words, I am (not surprisingly) setting and getting both position AND rotation... for what ever reason (i'll find it shortly) the rotation is not being pulled and when I try applying the locally stored rows (Vector3_X, Vector3_Y, and Vector3_Z) what is stored is the values from the position call.)
Answer by DDeathlonger · Oct 18, 2018 at 07:01 AM
For my particular situation, it actually had to do with how I was doing my error handling... I have a helper class with a helper method for MySql called Command which puts the MySql request into a try block and returns a string, either returning the MySql Exception if one occurred, or returning a formatted string for debugging, of the response table. (in case I want to see it, but don't want to store it.)
This is my old method, I've updated it since this post. This method is a wrong approach.
internal static string Command(string command, MySqlParameter[] parameters, bool storeResponseTable = false)
{
if (!string.IsNullOrEmpty(command))
{
try
{
MySqlCommand cmd = new MySqlCommand(command, Connection) { CommandType = CommandType.StoredProcedure };
if (parameters.Length > 0)
{
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
}
cmd.ExecuteNonQuery();
using (MySqlDataReader responseReader = cmd.ExecuteReader())
{
if (responseReader.HasRows)
{
if (storeResponseTable)
{
ResponseTable = new DataTable("Stored Response Table");
ResponseTable.Load(responseReader);
responseReader.Close();
return FormatDataTableToString(ResponseTable);
}
else
{
using (DataTable responseData = new DataTable())
{
responseData.Load(responseReader);
responseReader.Close();
return FormatDataTableToString(responseData);
}
}
}
else
return null;
}
}
catch (MySqlException e)
{
return "MYSQL ERROR: " + e.Message;
}
}
Yeah, anyhow, when I logged out the return of this Command helper method I found my issue.. .
"MYSQL ERROR: Unknown column 'Transform_EulerRotation' in 'field list'" It is a MySql Stored Proc issue... since it 'error'ed out', I never got back the new results of the rotation values that were stored, and thus was applying my last response table (the position) to the rotation of the player.
Thank you to @Bunny83 for the response and kindness in his assistance. I've accepted his answer as I feel anyone in a similar situation will find greater help seeing his answer than my niche issue. :P :)
Stay Classy Y'all.
Well, this is generally a bad implementation ^^. First of all returning errors just as data without handling that error in the code that processes the response is pointless. You should have actually raised an error in your stored procedure and catch it on the client side.
Using a static (probably public) field to hold the response table of a static method is the worst thing i've seen so far ^^. Such an approach could be used when you have a connection class instance and use instance methods and instance variable. A function should take parameters and return the result. If you want to communicate errors back you can return just the error code and return the result in a ref or out parameter.
For calls / requests that do not return any values at all you want to use "ExecuteNonQuery" for anything else you can use "ExecuteReader". Currently you always execute your stored procedure twice. Once with "ExecuteNonQuery" and once with "ExecuteReader".
Anyways as i already said using direct user connections to a database is not recommended for any kind of game. Unlike in business applications, game users generally have the tendencies to manipulate the game, cheat or just to disturb the game (often out of frustration ^^). It's almost impossible to sequre a mysql server against all sorts of attacks (DoS, SlowLoris). You won't find much about this scenario because no body ever opens a database server directly to the internet ^^. Hopefully all your other DB users are not allowed to be accessed from remote but only from localhost. If not people can just brute force your password. They don't care if it takes 2 or 3 years ^^. Really i highly recommend you overthink this approach.
Thank you for your feedback, it definitely has given some valuable 3rd party insight into my design choices...
Yes, I realized that, and made it so it both raises an error as well as returning the stringified error message. That was more of a noob-y mistake.
I don't use a public field, I actually use static internal, as to protect it from anyone else's scripts, of course they only have to decompile the game to view the ip and login creds, which is nothing that is technically secure anyways...
I'll definitely adjust the difference between non-query and query, as I hadn't even thought of that but surely will fix $$anonymous$$or lag spikes I was dealing with! haha.
In terms of a direct connection, I am truthfully unsure of any good alternative.. I've seen PHP, and even started with that, but implementing that into a game is rather overly complicated, and also much more vulnerable to SQL-Injection. I'm glad to switch to another option, but I cannot find anything online showing any alternative to PHP.
Also I have been looking at how to prevent SDOS Attacks: https://stackoverflow.com/questions/19496097/methods-to-prevent-ddos-based-mysql-queries-submitted-through-url-string
Between this, and using an SSL Certificate included in the application release I think I can help to mitigate the chances of such an attack; At least for the initial release... I'm a business man first, I just happen to love program$$anonymous$$g, but after release, I will look to hire a more well trained professional most likely.
This being said, if you have any resources which may help me to prevent such, or avoid this without using PHP, I would really appreciate it. :)
Your answer
Follow this Question
Related Questions
Object makes random jumps in rotation 1 Answer
Rotation losing precision 0 Answers
Trouble Spawning Bullets with proper rotation 0 Answers
Whats the Difference Between Rotation EulerAngles and Quaternions? 2 Answers
Camera rotation behaving strangely 2 Answers