Photon and MySQL

Briksins

Hello

I was digging the forum to find any guidance on how do i connect photon with MySQL
and all I did understand that Photon doesn't support MySQL integration, however there are a lot of threads where mentions SQL interaction, what proves that people are using it some how

so the question is how do i do for example simple login functionality trough DB with Unity and Photon?

if there are no specific tutorial, could u t least explain the logic how MySQL connected to the Photon?

Best Regards
Vadims Briksins

Tobias

You can use any tutorial about using MySQL that's out there. Photon is not doing anything to prevent you from using a DB, it just doesn't use one by default.

Maybe the question is not how to connect and use the DB but how do to it effectively so it doesn't stall Photon's performance?
If so, yes, we need a sample for that. We should be able to provide a few guidelines.

[Deleted User]

The Photon applications are in .NET and you can extend them in whatever way you want - including DB access. You might want to have a look at the LoadBalancing project - there is a "dummy" Authenticate operation, which does not do anything useful right now, but it is a starting point for you to build your own login system. Just change the implementation of the Authenticate operation to open a connection to your MySQL DB and verify the username, for example.

We've discussed that in a bit more detail here: viewtopic.php?f=5&t=1212

If you need info how to use MySQL from .NET - there should be plenty tutorials out there on the internet. It's not specific to Photon.

Briksins

Ohh right i see...
So to use MySQL in my Unity project i would have to add another *.dll to the project to get SQL libraries available in the code and use it from the client.
This is changing whole connection architecture, what I was thinking is that Server connected to the database and client get data from database trough server.


As it is most efficient and secure way.

However as far as i understand now you suggesting to me do this thing:


but it is a bad practice to allow clients to access DB directly

so what is solution?
1) add MySQl libraries to Unity and allow client connect directly to DB?
2) use Unity "www" with PHP to access DB?
3) or there is a way to extend Photon server functionality and implement additional methods for photon like "LoginUser(userLogin, userPass)" which i would be able to call from client side, however whole logic would have to be written by my self?

i do already understand that i will have to create whole this logic, it is fine and I can do it, but what is the most sufficient way? and as Tobias already said:

Tobias wrote:

how do to it effectively so it doesn't stall Photon's performance?

I would be very much appreciated for any tips and suggestions
Thank you very much

Briksins

Sorry for double post, quick update for those who will read this thread in the future

I was goggling a lot to find answers on my own questions while forum was quite and dig out more info, so i think it would be useful to have full history in one thread...

so here is what i find out:
1) I was right from the logical point of view that allow to client access DB directly is very bad idea, so we have to use proper pattern (see picture 1)
2) Using PHP with Photon together is also not good idea, as it is 2 different things, and would require to make 2 connections (first for Photon, another "www" to access php) im sure there would be plenty of downsides if you will dig out this info
3) PROPER SOLUTION:
Photon server allows you to build your own functionality on top of the server, that functionality could be anything you like and including connection to Database

I found good tutorial which is more related to the MMO setup and load balance. for my own task it is too much and overkill, however anyway i will have to go trough it to understand the way it works, but later ill probably simplify it.

Possible if ill get enough info and accomplish my task i will write some simplified tutorial or just share entire code here.

[Deleted User]

Thanks for sharing your thoughts!

You're conclusion to extend Photon is correct - we would recommend, that, too. Sorry if it did not become clear from our answers.

paala

But for registering and login how do you do it with Nhibernate?
You have to use php and mysql, no?

[Deleted User]

To implement a login system, you have bascially two (recommanded) options:
1.) Build a separate web service that accesses a database with your user data (with PHP / MySQL or any other framework & database of your choice) and make the clients connect to and authenticate at that service before they are allowed to access Photon
2.) customize Photon's .NET applications and integrate your database (probably with a framework like NHibernate) - in this case, your clients will only connect to Photon (no other service) and you can access your user database from within Photon's operations.

Neither of this is specific to Photon.

davidm

There is a way to connect via C# without the requirement of PHP/Mysql.

First, you will need the mysql dotnet connector (link-> http://dev.mysql.com/downloads/connector/net/)

Personally, I wrote a wrapper class for handling my database persistence.

For the sake of this discussion, I will use non-wrappered code and this does not implement any third party libraries or framework, simple objects only.

I would also like to add that this is a simple example, in anything you do, never trust the user and validate the data as much as possible.


[code2=csharp]//Connection info
server = "localhost";
database = "mydatabase";
uid = "username";
password = "password";
string connectionString;

//Build the connection string
connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

//Start the connection
connection = new MySqlConnection(connectionString);

//Open the connection
connection.Open();

//Set a new mysql command object
MySqlCommand cmd = new MySqlCommand();

//Set the command text, I would recommend using prepared statements for security reasons and ease of use
cmd.CommandText = "SELECT * FROM users WHERE id=@id LIMIT 1";

//Prepare the statement
cmd.Prepare();

//Add the parameters
cmd.Parameters.AddWithValue("@id", id);

//Start the reader
MySqlDataReader dataReader = cmd.ExecuteReader();

//Read the data
while (dataReader.Read())
{
//Insert processing logic
}

//close data reader
dataReader.Close();

//close the connection
connection.Close();[/code2]

Here's a sample of what your authenticate method will look like in your login request handler:

[code2=csharp]private bool Authenticate(string username, string password)
{
//Verify the username and password are not null or empty strings
if (!String.IsNullOrEmpty(username) && !String.IsNullOrEmpty(password))
{
//Hash the provided password to check against users stored password
string passwordHash = Encryption.GetHashedString(password+Account.salt, EncryptionModeEnum.SHA1, false);

//Fetch the user information
DataTable dt = Account.FindByUsername(username);

//Ensure that the datatable has data
if (dt != null && dt.Rows.Count > 0)
{
//Instantiate the account object
Account acct = new Account(dt.Rows[0].ItemArray);

//Check the passwords
if (acct.Password == passwordHash)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}[/code2]