Handle user accounts with MySql

Hello

    Now we have a solid way to store and request data the next step is going to be the creation of user accounts.

First we are going to prepare the database, to do so we will connect to the mysql server:

mysql -h localhost -u root -p

Create a database

crete database rpgdata;

Use the newly created database

use rpgdata;

Then create a table, that will store the user accounts

 CREATE TABLE accounts (     id INT AUTO_INCREMENT,     fullname VARCHAR(60),     email VARCHAR(120),     password VARCHAR(30), 

    lastlogin TIMESTAMP,

 PRIMARY KEY (id));

We want to login into the game with an existent account or to create a new one, after that step we will get into game character creation (the next step), to simplify things I'm gonna make a text-only client , and a simplified server too.

The logic will be:

1. Client connect to the server socket
2. Client displays two options:2a-create a new character, 2b-login with an existing character
3. If we choose to create a new character the client will show list of text fields we need to fill to create the account
4. If we choose to login with an existing account we will fill our username (email) and password. 

In the first case, it asks for:

• Full user name
• email(we will use it like our user account)
• password
• password (another time), to prevent mistakes when typing

        cout << "NEW ACCOUNT CREATION" << endl;
        cout << "Full name?: ";
        cin  >> fullname;
        cout << "e-mail?: ";
        cin  >> username;
        cout << "Password?: ";
        cin  >> password;
        cout << "Repeat Password?: ";
        cin  >> password2;

        if(password.compare(password2)==0)
            {
                done=true;
            }else{
                cout << "Passwords don't match" << endl;
            }

After filling all the data, it will pack it and sent it to the server

std::stringstream Msg;

Msg << fullname << "#"<< username << "#" << password << "#";

NET.SockSend(CREATE_NEW_ACCOUNT,Msg.str().c_str());

After we receive the message in the server we are going to unpack the data and get the email.

switch (command){
            case CREATE_NEW_ACCOUNT:
                cout << "New account request" << endl;
                data2.clear();
                split(data[3],'#',data2);//I split it into strings

                fullname = data2[0];
                username = data2[1];
                password = data2[2];

                cout << "Fullname " << fullname << endl;
                cout << "Username " << username << endl;
                cout << "Password " << password << endl;

 We will make a query to the MySql server with that email account

                aquery.clear();
                aquery = "select * from accounts where email = '";
                aquery+= username;
                aquery += "'";
                cout << aquery << endl;
                aSQL.executequery(aquery.c_str());//executing select query

If there is already an account with the same email we will reject the data and send a "duplicate account" message to the client, if we don't find it, then we save a new account to the server and send a "account created properly" message to the client

 if (aSQL.getrow())
                    {
                        //Username duplicate..
                        cout << "Duplicate username.." << endl;
                        aNET.Clients[atoi(data[0].c_str())]->SockSend(DUPLICATE_ACCOUNT,"");
                    }else{
                        //We have not found username, ok to insert..
                        cout << "We have not found username, ok to insert.." << endl;
                        aquery.clear();
                        aquery = "insert into accounts values (NULL,'";
                        aquery+= fullname;
                        aquery+="' , '";
                        aquery+= username;
                        aquery+="' , '";
                        aquery+= password;
                        aquery+="', NULL)";
                        cout << aquery << endl;
                        aSQL.executequery(aquery.c_str());//executing select query
                        cout << "New user inserted" << endl;
                        aNET.Clients[atoi(data[0].c_str())]->SockSend(NEW_ACCOUNT_ACCEPTED,"");
                        }
                    break;

After we create a new user, it will return to the main menu.

In the second case, it asks for:

• email
• password

After filling the data, it will pack and sent it to the server

    cout << "ACCOUNT LOGIN" << endl;
    cout << "e-mail?: ";
    cin >> username;
    cout << "Password?: ";
    cin >> password;
    std::stringstream Msg;
    Msg << username << "#" << password << "#";
    NET.SockSend(LOGIN_WITH_ACCOUNT,Msg.str().c_str());

After we receive the message in the server we are going to unpack the data and get the email.

            case LOGIN_WITH_ACCOUNT:
                cout << "Login request" << endl;
                data2.clear();
                split(data[3],'#',data2);//I split it into strings

                username = data2[0];
                password = data2[1];
                cout << "Username " << username << endl;
                cout << "Password " << password << endl;

With the email we are going to make a query to the server asking for accounts with that email.

                aquery.clear();
                aquery = "select * from accounts where email = '";
                aquery+= username;
                aquery += "'";
                cout << aquery << endl;
                aSQL.executequery(aquery.c_str());//executing select query

If we receive no accounts, obviously, there is no such account.

 if (aSQL.getrow())
                    {

        /... 

                    }else{
                    //We have not found username..
                    cout << "Username not found.." << endl;
                    aNET.Clients[atoi(data[0].c_str())]->SockSend(UNKNOW_USERNAME,"");
                    }

If we have found that account we check the password match the one the user has typed.

//Username found
                    cout << aSQL.row[0] << " " << aSQL.row[1] << " " << aSQL.row[2] << " " << aSQL.row[3] << " " << aSQL.row[4] << endl;
                    std::string pass;
                    pass = aSQL.row[3];
                    if (pass.compare(password)==0)
                        {
                        cout << "Username/password match, login ok" << endl;
                        aNET.Clients[atoi(data[0].c_str())]->SockSend(ACCOUNT_ACCEPTED,"");
                        aNET.Clients[atoi(data[0].c_str())]->Login  = true;
                        }else{
                        //wrong password
                        cout << "Password mismatch.. " << endl;
                        aNET.Clients[atoi(data[0].c_str())]->SockSend(BAD_PASSWORD,"");
                        }

If the password is right, it will send an ACCOUNT_ACCEPTED message.

In the client side, after sending data to creating a new account or login with an existing account we will wait to the server to send us a reply

switch(command)
            {
            case ACCOUNT_ACCEPTED:
            cout << "ACCOUNT_ACCEPTED" << endl;
            reply = true;
            logedin = true;
            break;

            case NEW_ACCOUNT_ACCEPTED:
            cout << "NEW_ACCOUNT_ACCEPTED" << endl;
            reply = true;
            break;

            case DUPLICATE_ACCOUNT :
            cout << "DUPLICATE_ACCOUNT " << endl;
            reply = true;
            break;

            case UNKNOW_USERNAME:
            cout << "UNKNOW_USERNAME" << endl;
            reply=true;
            break;

            case BAD_PASSWORD:
            cout << "BAD_PASSWORD" << endl;
            reply=true;
            break;
            }//switch

And basically that's all, We have now authenticated users, ready for the next step: Player creation process!

Notes:

1. Don't look for encryption: it is not yet implemented so the server / client are not secure, the reason for that is 1-Simplicity 2-Easier to understand incoming / outgoing packets and 3-It is very easy to add encryption later.
2. Messages are not encoded: no binary numbers, just plain text, the reason the same in point 1.1 and 1.2 : making things simpler to understand.

The full code can be downloaded at https://code.google.com/p/jbfprj/downloads/list or at the svn

See you!

A server socket class

Hello

I'm going to describe a new class, (last one before I start with the server)

We already have a class to store persistent data, now we need a way to communicate with the world.

I need a class to start a TCP socket server then accept incoming TCP connections , I need to be able to send data to any of the clients too, and finally enqueue the incoming data so I can process it when I need to do.

What the class is going to do is:

1. Open a server socket in a port X
2. Start a thread that will be listening in port X
3. When we receive connections it will save the client info (ip/port) to a vector so we can communicate with the client, every client starts a new thread to get the incoming data from each client (it is a blocking-socket server)
4. The data is stored into two different queues depending if the client has logged in  or not, I made that because I will apply different priority for the queues , the main (client updates) must be processed with maximum priority other way clients will be lagging.. in the other hand client login request can wait a pair of seconds without big problems.

To achieve that I use two classes, first one is cNetwork, it is the main server, second one os cClient, used to store information from every connected client, the header from both classes are stored in the same file:


<network.h>

#ifndef NETWORK_H
#define NETWORK_H

#define MAXLEN 1024
#include "SDL/SDL.h"
#include "SDL/SDL_net.h"
#include "SDL/SDL_thread.h"

#include <vector>
#include <queue>
#include <iostream>
#include <sstream>

using namespace std;

class cNetwork;

class cClient //the connected clients

{
public:
//functions
cClient();//creator
virtual ~cClient();//destructor
void StartThreads();//Start listener thread
static int listener( void *data );//Listener Thread
void Send(const char* Msg);//Send data to client

//variables
cNetwork *_server;//To comunicate with main server instance
TCPsocket socket;//client socket
SDL_Thread *ListenerThread;//The network client thread
int         threadReturnValueclient;
Uint16 UID;//Unique identifier
bool Connected;//socket connected?
bool Login; //loged in?

};

class cNetwork//main network server class
{
      friend class cClient;

    public:
        //functions
        cNetwork();
        virtual ~cNetwork();
        void Start(Uint16 MAXUsers, Uint16 Port);
        void CleanClient(unsigned int Client);//clean a client so we can use it
        static int Master( void *data );//Master listener function/thread
        void ClearClients();//Clear disconnected clients
        void Finish();//close all client connections and finish server

        //variables
        queue<std::string> InputQueue;//Here we store received data
        queue<std::string> LoginQueue;//Here we store login requests
        SDL_sem *ClientsLock; //Semaphore to prevent server from accesing a variable from different threads
        cNetwork *myserver;//server pointer
        vector<cClient*> Clients;//Here I store clients
        SDL_Thread *MasterThread;//The Master thread >> accepts new clients
        int         threadReturnMaster;//return value for master thread
        bool ShutDown;//closing the server?

    private:
        //private variables
        IPaddress ip;//Server ip address
        TCPsocket ServerSock; //Server socket
};

#endif // NETWORK_H

Then I have the client implementation:

<client.cpp>

#include "network.h"

cClient::cClient()
{
    //ctor
    ListenerThread = NULL;
    Connected = false;
}

cClient::~cClient()
{
    //dtor
    //Stop the thread
    SDL_KillThread( ListenerThread );
    //close the socket
    SDLNet_TCP_Close(socket);
}

void cClient::StartThreads()
{
    //Create and run the thread +
    ListenerThread = SDL_CreateThread( &listener, this );
}

int cClient::listener( void *data )
{ //While the program is not over
    cout << "Starting client listener Thread..." << endl;
    int result;
    char in_msg[MAXLEN];
    bool quit=false;

    while( quit == false )
        {
        memset(in_msg, 0, MAXLEN); // Clear in buffer
        if ( ((cClient*)data)->Connected)//If I'm connected...
            {
                result=SDLNet_TCP_Recv(((cClient*)data)->socket,in_msg,MAXLEN);//Check for incoming data
                    if(result<=0)
                        {
                            //NO DATA
                            // TCP Connection is broken. (because of error or closure)
                            SDLNet_TCP_Close(((cClient*)data)->socket);
                            cout << "Socket closed..." << endl;
                            ((cClient*)data)->Connected = false;
                            quit=true;
                        }

                stringstream idmsg;
                idmsg << ((cClient*)data)->UID << "#" << in_msg;// I will add the UID from the client to the incoming message
                try{
                    SDL_SemWait(((cClient*)data)->_server->ClientsLock);//lock smaphore to prevent client to freeze
                    cout << idmsg.str();//only for debuging

                    if  (((cClient*)data)-> Login==true)//Am I loged in?
                        {
                            ((cClient*)data)->_server->InputQueue.push(idmsg.str());  //I'm logged in
                        }else{
                            ((cClient*)data)->_server->LoginQueue.push(idmsg.str());  //New player requests
                        }
                    SDL_SemPost(((cClient*)data)->_server->ClientsLock);//unlock smaphore
                    }
                catch(exception& e)
                    {
                        cout << "QUEUE IN ERROR: " << e.what() << endl;
                    }
            }//if connected
        }//while
    cout << "Closing client listener thread..." <<endl;
    return 0;
}

void cClient::Send(const char* Msg)
{
    if (Connected==true)
        {
            int len;
            int result;
            len=strlen(Msg+1); // add one for the terminating NULL
            result=SDLNet_TCP_Send(socket,Msg,len);
            if(result<len) //If I can't send data probably I've been disconnected so....
                {
                    cout << "SDLNet_TCP_Send: " << endl << SDLNet_GetError();
                    Connected = false;
                }
        }else{
            cout << "Not connected!!" << endl;
            }
}

Notice the client stores the data in two different queues depending if the client has loged in or not, finally we have the main socket server class:

<network.cpp>

#include "network.h"

cNetwork::cNetwork()
{
    //ctor
if(SDLNet_Init()==-1) {//Start SDL_NET
cout << "SDLNet_TCP_INIT: \n " << SDLNet_GetError();
exit(2);
}

//Clean thread variables
MasterThread = NULL;
ClientsLock = NULL;
ClientsLock = SDL_CreateSemaphore( 1 );//semafor protector, previene que mas de un thread vuelque información a la vez a la cola
}

cNetwork::~cNetwork()
{
    //dtor
SDLNet_TCP_Close(ServerSock);//Close socket
SDLNet_Quit();//Close SDL_NET
}


void cNetwork::CleanClient(unsigned int Client)//clean a client so we can use it
{
//cout << "cleaning client: " << Client << endl;
Clients[Client]->UID =Client;
Clients[Client]->socket = NULL;
Clients[Client]->_server = myserver;
Clients[Client]->Connected = false;
Clients[Client]->Login = false;
}

void cNetwork::Start(Uint16 MAXUsers, Uint16 Port)
{

//1-initialize MAXUsers clients
//2-Start sock server

unsigned int x;
for (x=1;x<=MAXUsers;x++)
{
Clients.push_back(new cClient());//insert a new client into the clients vector
CleanClient(Clients.size() -1);
}

cout << "OPENING SERVER SOCKET... " << endl;
if(SDLNet_ResolveHost(&ip,NULL,Port)==-1) {
cout << "SDLNet_TCP_ResolveHost:" << endl  << SDLNet_GetError();
exit(1);
}

ServerSock=SDLNet_TCP_Open(&ip);
if(!ServerSock) {
cout << "SDLNet_TCP_open: " << endl << SDLNet_GetError();
exit(2);
}

ShutDown = false;
//Create and run the threads
MasterThread = SDL_CreateThread( &Master, this );

}



int cNetwork::Master( void *data )//Master listener function/thread
{
TCPsocket new_tcpsock; //Temporary socket to store incoming connections
new_tcpsock=NULL;

cout << "Waiting for incoming connections... " << endl;

bool doneMain=false;
while(!doneMain)//MAIN LOOP
    {
    if(!new_tcpsock)//We have a new client incoming
    {
        new_tcpsock=SDLNet_TCP_Accept(((cNetwork*)data)->ServerSock); // accept a connection coming in on server_tcpsock
        SDL_Delay(5);//No new clients, wait a little
    }else{
        cout << "New client incoming..." << endl;
        unsigned int x;
        for(x=0;x<((cNetwork*)data)->Clients.size();x++)
        {
        if (((cNetwork*)data)->Clients[x]->Connected==false)
            {
            ((cNetwork*)data)->CleanClient(x);
            ((cNetwork*)data)->Clients[x]->socket=new_tcpsock;//asign the socket
            ((cNetwork*)data)->Clients[x]->Connected = true;
            ((cNetwork*)data)->Clients[x]->Login = false;
            ((cNetwork*)data)->Clients[x]->StartThreads();//start client listener thread
            break;
            }
    }//for
        new_tcpsock=NULL;//release temporary socket var

        }//if new data

if (((cNetwork*)data)->ShutDown==true)doneMain =true;

}//while
cout << "Exiting Main thread..." << endl;
return 0;
}

void cNetwork::ClearClients()//Clear disconnected clients
{
unsigned int x;
bool done = false;
//SDL_SemWait(ClientsLock);
if (Clients.size()>0)
{
x=0;
while (!done)
{
 if (!Clients[x]->Connected)
            {
            Clients.erase(Clients.begin()+x);
           // cout << "Number of clients:" << Clients.size() << endl;
            if (x>0)x--;
            }//if !connected
x++;
if(x>=Clients.size())done=true;
}
}//clients size

}

void cNetwork::Finish()
{
    ShutDown =true;
    SDL_WaitThread(MasterThread,&threadReturnMaster);
    unsigned int x;
    for(x=0;x<Clients.size();x++)
        {
            Clients[x]->Connected=false;//force disconnection
        }//for

    while (Clients.size()>0)
        {
            ClearClients();
        }
}

There are many things here, but I will resume it to the maximum, to do that, below there is a simple example to use the class:

#include "network.h"//socket server class 

cNetwork NET;
NET.myserver = &NET;
NET.Start(100,55555); //start networking, up to 100 clients on port 55555

std::sting text;

SDL_SemWait( NET.ClientsLock );
text = NET.InputQueue.front();//take a message out from the queue
NET.InputQueue.pop();
SDL_SemPost( NET.ClientsLock ); 

NET.Clients[X]->Send("data");//will send "data" to client X

NET.Finish();//finish socket server 

• We include the class
• Then instantiate it
• Start the listener thread (at this moment it is fully functional)
• Then I extract a message from the input queue (let's suppose we have a connected client that send it..)
• I send a message to the server ("data")
• Finally, I close the server

Want to test the class? basically start an empty project, add the three files I have described above and then add the example, after you compile the project you will be able to connect just using telnet (telnet localhost 55555)

That's all for now, in the next post I will mix everything to make the client login a reality, I will make a text based client too and hope everything is clear that way.

A class to use MYSQL

<After installing and setting up the MySql server ,we need to access the MySql database from code::blocks>

To achieve the objective I'm going to make a class to connect and launch SQL queries to the MySql server , and of course a test project to use the class.

So the steps to get started are:

1. Open code::blocks
2. Create an empty project
3. Add a file and save it "main.cpp"
4. Add a new class with the wizard and name it "csql"

Now we edit the header for class csql (csql.h):

#ifndef CSQL_H
#define CSQL_H
#include <mysql.h>//Needed to use MySql
#include <string.h>
#include <sstream>

using namespace std;

class csql
{
    public:
        MYSQL_ROW row; //Here we store query results in rows
        csql(); //constructor
        virtual ~csql();//destructor
        bool connect(const char * serverip, const char * username, const char * password, const char * database);//connecto to a server , with username and password and select database
        void executequery(const char *query);//execute SQL query
        bool getrow();//we get a row from the results from a query

    private:
        MYSQL mysql;//mysql class
        MYSQL_RES *res ;//mysql results for queries
};

#endif // CSQL_H

Basically there are 5 functions: the creator and destructor, a function to connect to the mysql server, a function to execute queries and finally a function to get the result from SELECT queries.

Now the implementation (csql.cpp):

#include "csql.h"

using namespace std;

csql::csql()
{
    //ctor
        mysql_init(&mysql);//start mysql
}

csql::~csql()
{
    //dtor
        mysql_close(&mysql);//close mysql
}

//We use connect() to connect to the database needed
bool csql::connect( const char * serverip, const char * username, const char * password, const char * database)
{
    if (mysql_real_connect(&mysql
  ,serverip
  ,username
  ,password
  ,database,0,NULL,0))
    {

        return true;//everything ok
    }else{
        return false;//connection failed
    }
}

void csql::executequery(const char *query)//Execute SQL query
{
mysql_real_query(&mysql,query,strlen(query));
res = mysql_store_result(&mysql);
}

bool csql::getrow()//We extract a row from the result from an SQL query (or get false if empty)
{
    if ((row = mysql_fetch_row(res)))
    {
        return true;//we have extracted a row
    }else{
        return false; // no rows to extract
    }
}

It is quite simple to follow the code:

• csql() (constructor) just initializes the mysql class used to work with the DB
•  ~csql() (destructor) frees the class, closing any active connection
• connect() connect to the server, using 4 parameters: the server IP, the username, the password and the database to connect (it returns true if the it connects ok, false when there is a failure)
• executequery() used to launch SQL queries, just plain SQL commands like "select * from table"
• getrow() get a data row from the results of a select query, if there are rows it will return true and we can access the data columns with csql_instance.row[index], other way it returns false

And finally, to use the class I wrote a pair of examples (main.cpp):

#include <iostream>
#include "csql.h"

using namespace std;

int main(int argc, char *argv[])
{

csql sql;//we use the sql class

cout << "Connecting to database.." << endl;

if (!sql.connect("localhost","root","rpgdata","rpgdata"))//we connect using the serverip, username, password and database
{
    cout << "Error connecting to database" << endl;
    return -1;
}

cout << "DELETING.." << endl;

sql.executequery("delete from accounts");//executing delete query

sql.executequery("select * from accounts");//executing select query
while (sql.getrow())
{
    cout << sql.row[0] << " " << sql.row[1] << " " << sql.row[2] << " " << sql.row[3] << " " << sql.row[4] << endl;
}

cout << "INSERTING Jorge.." << endl;

sql.executequery("insert into accounts values (NULL, 'Jorge','jorge@hotmail.com','pass',NULL)");//executing insert query

sql.executequery("select * from accounts");//executing select query
while (sql.getrow())
{
    cout << sql.row[0] << " " << sql.row[1] << " " << sql.row[2] << " " << sql.row[3] << " " << sql.row[4] << endl;
}

cout << "INSERTING Paul.." << endl;

sql.executequery("insert into accounts values (NULL, 'Paul','paul@gmail.com','pass',NULL)");//executing insert query

sql.executequery("select * from accounts");//executing select query
while (sql.getrow())
{
    cout << sql.row[0] << " " << sql.row[1] << " " << sql.row[2] << " " << sql.row[3] << " " << sql.row[4] << endl;
}

    return 0;
}

I make an instance from the mysql class, then I connect to the server, clean the table "accounts", and then I insert a pair of data rows, I display the results from select queries too, there are other SQL commands like UPDATE which I have not used, but I think that it is quite enough to show the use from the class.

<Notice that I am using the password "rpgdata", the database "rpgdata", the table "accounts".. if you don't use the same database or table names, the example wont work(I explained how to set it up in the previous two posts)>
 
Finally there is one final thing you need to set up to make the project compile:

• Go to "project" > "build options"

• Then in "compiler" > "other options"

`mysql_config --cflags`

• And in "linker" > "other linker options"

`mysql_config --libs`

Other way the compiler wont find mysql and it will refuse to work...

<So basically we have a simple class to use the freshly new installed MySql server, now we can use it in any project, but obviously I'm going to add it to the socket server, in the next post I will show another new I'm going to use to host the project development...>

See you!

MYSQL + Command Line

<In the last post I spoke about my decision to start using MySql , now I will speak about how to set up a simple test database using the command line prompt>

CONNECTING TO THE SERVER

MySql has a basic tool to set up (almost) everything you need from your SQL server.

After installing MySql you just need to use the command:

mysql -h ServerIp -u username -p

That will connect to the mysql server with ServerIp, and try to validate, if you don't write the IP it will default to localhost, the username is the username used to login into the server, and finally -p makes mysql to ask for the password of the username:

jbea@eeepc1005HA:~$ mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Here we are, logged in..

CREATING DATABASES AND TABLES

Next step would be very simple, create our database:
 

mysql> CREATE DATABASE rpgdata;
Query OK, 1 row affected (0.00 sec)

Notice the semicolon ";", every instruction in the mysql command prompt must finish with ";", it's the end of sentence command

It will create a new database "rpgdata" , then we can check the databases in our server:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| rpgdata            |
+--------------------+
3 rows in set (0.00 sec)

We can delete the databases with the command:

mysql>DROP DATABASE rpgdata; 

After creating a database and before we can actually work with it we need to "use" it with the command:

mysql>USE rpgdata; 

Next step would be creating the tables to store the data:

mysql>CREATE TABLE table (datafield1, datafield2..); 

Example:

mysql> CREATE TABLE accounts (
    -> id INT AUTO_INCREMENT,
    -> fullname VARCHAR(60),
    -> email VARCHAR(120),
    -> password VARCHAR(30),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.06 sec)

Now we can check the tables we have in our database:

mysql> SHOW TABLES;
+-------------------+
| Tables_in_rpgdata |
+-------------------+
| accounts          |
+-------------------+
1 row in set (0.00 sec)

And another interesting thing: we can check the database structure with the command: 

mysql> DESCRIBE accounts;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| fullname | varchar(60)  | YES  |     | NULL    |                |
| email    | varchar(120) | YES  |     | NULL    |                |
| password | varchar(30)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

DATA HANDLING

At this point, we can start using standard SQL sentences to handle the data:

>INSERT INTO > Insert data

>UPDATE > update data

>DELETE FROM > delete data

>SELECT FROM > Get data 

A pair of examples:

mysql> INSERT INTO accounts VALUES
    -> (NULL, 'Jorge Bea Frau','jbeafrau@gmail.com','password');
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> SELECT * FROM accounts;
+----+----------------+--------------------+----------+
| id | fullname       | email              | password |
+----+----------------+--------------------+----------+
|  1 | Jorge Bea Frau | jbeafrau@gmail.com | password |
+----+----------------+--------------------+----------+
1 row in set (0.00 sec)

mysql>

SQL Language is almost a programming language itself so it's way beyond my hand to teach the language, anyway I will explain the sentences when I use them so you don't have to worry about it.

BACKUPS AND RESTORES

Finally, to make a backup from a database to a plain text file and restore it later we have two commands:

mysqldump -h localhost -u root -p DATABASE > backupfile.sql

To make a backup from a database to a file...

mysql -h localhost -u root -p DATABASE < backupfile.sql

To restore a database from a backup file

<Notice that in both commands you have to execute the commands from the terminal window, not from the mysql prompt>

And that's all for now, that's all we are going to need to start using the mysql server, creating our database and the tables inside, we can now design our game database, and we could even insert the desired data inside (I Wont recommend that!), but instead  we are going to use c++ to connect to the mysql server and launch SQL sentences...

In the next post: Using mysql from c++ + code::blocks.

Finally, MYSQL + code::blocks!

Hello everybody, today I'll make a "postmortem analysis" and will install and set up MySql server

     <Been out for a long time <again>, had many things to do and my project got stucked again, but it didn't get forgotten! I have been reading all the code again and had time to think about it, the things I have done right and which things should I change / improve, and made an small list:>

GOOD THINGS

1. The server actually works!
2. Client program able to create players, connect, and "move around"
3. Worked quite fast in a desktop computer using small amounts of RAM

BAD THINGS

1. Ugly code, I feel really ashamed about that, but it is the result of writing code in a "fast 15 min way"
2. Server is unstable, yeah, after fixing many, many bugs it did worked, but still had things to fix.
3. I was not using a real scenario , data was not being loaded from server, so player creation, maps, etc was everything client-side, obviously that is not good, for many reasons (security, for example)
4. World didn't had persistent data, players, objects, etc were not stored  after closing client program

In other words I just did a network demo, I was so anxious to make the server work with thousands of clients that I didn't realized I was starting to loose the focus in my objective: MAKE THE GAME!

So I took a hard decision, dropped the code and to make a second server with all the good things the first server had + all the things it SHOULD HAVE like persistent data, and a true server-client model.

Having persistent data meant to use some kind of static storage, so I thought in using just plain files plus standard c++ io functions to write data, but I dropped the idea fast because there are already libraries to solve the "writing another text parser" like TinyXml , but after playing with that again I found that if I used that library I will be making two times the work because:

1. First I will have to write all the data in .xml files
2. To later rewrite the code again to switch to a real database..

I went back to my very first objectives and the name MySql, came to my mind...

<NOTE: I have completely dropped support for windows in the server part, that means I wont be posting how to install and make it run under Windows OS family, basically I made that because that's the OS I will be using to make and run the server and I cant loose more time in something I wont be using, although anybody could check how to download and install the required libraries for the OS you choose..>

So I installed the mysql server:

sudo apt-get install mysql-server

It just asked me for the "root" database password, it is very important because it will be used to connect to MySql and use the database, NOTE: it is not the linux root account so you can / should have different passwords .

I installed too MySql tuner:

sudo apt-get install mysqltuner

Used to get advice to tune the databases

And the client libraries:

sudo apt-get install libmysqlclient-dev

Used to connect from the client app

To check that the MySql server is running:

sudo netstat -tap | grep mysql

  
You can restart the service with:

sudo service mysql restart

 
<You can see installing MySql server is quite simple, in the next post I will write about comand-line database management, and finally start using it with code::blocks>

Blocking-sockets Windows binaries released!

        I have compiled the projects under MS Windows, surprisingly almost everything worked as expected and the binaries are available in  Downloads(-RPGServer / Client / Bot Tutorial ->Windows Binaries), feel free to download and test it.

      There were only two problems:

1. Server include paths were wrong, I had <SDL/SDL.h> so I replaced them with <SDL.h> and compiled fine
2. The bot was giving me errors in the line srand(time(NULL)), solved with a #include <time.h>, curious it worked fine under linux.

When running there is only an issue: no cout output under windows so server starts, and actually works! but there is no output at all, anyway it's ok to test.

        The server has certain limits like not being able to accept more than 1018 clients and to use too much resources, but if you plan to work with a limited amount of users (200-300) it will make the job.

        I would be glad to hear (constructive) comments about the code, the bugs you may found and well, basically your opinion.

Thanks to everybody!

Blocking-Sockets server code released

Hello again!

         After I decided to stop the development for a while I tought it would be nice to share the code with everybody interested, so after a huge cleanup (probably not enought) I have uploaded the code for the server, the client and a silly bot I did to test multiple client connections, they are now in Downloads (-RPGServer / Client / Bot Tutorial -> Source code), feel free to download and test it.

        The server has certain limits like not being able to accept more than 1018 clients and to use too much resources, but if you plan to work with a limited amount of users (200-300) it will make the job.

     Server is untested under windows (it works!), client and bot wont care too much for the OS (will work in linux / windows), I don't have a machine with Mac OS X to test so I couldn't test it (any volunteer to try with an apple computer?)

        I would be glad to hear (constructive) comments about the code, the bugs you may found and well, basically your opinion.

Thanks to everybody!

So I want make an MMORPG, Where do I start from? PART 4

Messaging and optimizations:

<Messaging:>

Networking is the core of an mmorpg, and just in the top of the network we have the messaging system. What we need is to create a way to communicate between server and clients.

Clients will send commands to the server for example: move forward, turn right, atack!... In the server we could have comands to send the map, the clients data like player / npc position,etc...

Most of the people (should) use serialization to send data, for example an item could be packed in an object / serialize it and send over the net, in the destination it would be deserialized and here we have our object/item again, but I have used another way to transfer messages that is hard to code but easier to understand: Plain-text messages.

Probably now you are wondering about how does it works, suppose we want our client to send to the server we are moving forward, then we will just send a command telling the server we have pressed "forward", suppose we decide the text command is going to be "/acce", when the server receives a string "/acce" from our client it will understand the client is moving forward so it will move the client forward in the map and return the new updated position to the client; The server will send "/ping" to the clients every x seconds ,when the clients receive "/ping" it will return "/pong" to measure delay in the communication between server/clients.

What are the advantages when using this method? basically it simplifies debugging , it's easier to understand an incoming packet when you can actually see the command and not a binary-format hex value, basically it is good for didactic purposes.

The disadvantages? performance and scalability , "/acce" uses 5 bytes to send "forward", too much for a simple commands in the other side for every new command you have to modify both client and server, that can be overwhelming!

I resume here all the commands I used for the demo:

Client side:

• /nick > Set players nickname
• /imag > Set players image
• /acce  > Move forward
• /dece > Move backward
• /righ > Turn right
• /left > Turn left
• /idle > Stay idle
• /pong > Answer a /ping request
• /quit > Exit server

Server side:

• /SUID > Send Unique identifier to the player
• /upda > Send non-player updates (X,Y,Nicknames...)
• /updb > Send player updates (X,Y...)
• /ping > Send a /ping request to calculate client latency
• /kill > Sent when a player logoffs

So we have our server running, in our client we create a player and then connect to the server, then the client will wait until our server sends "/SUID" with the UID for our client, the UID wont change until we logoff, after receiving the UID our client will send our nick and image, that is how our "enemies" will see us, then we are ready to explore.

We will start moving around, when we move we will be sending "/acce", "/dece", "/righ" and "/left" to the server that will update our position in the map, if we don't move a "/idle" message will be sent, when the server updates our position it will mark if the client has changed state so it will send the command "/upda" with our updated x and y to the clients in a range (500px), so server wont send updates to clients that can't see our moves, that will limit too network traffic.

When a clients exits the game it sends a "/quit" to the server, server will clean the players data and then it will send a "/kill" command to the clients so they will clean the exiting client...

<9 messages for the client + 5 messages for the server = total 14 diferent messages! , Probably the simplest messaging system you will have ever seen>


<Optimizations:>

After I made the server I saw two simple ways to improve performance: first one in the client second one in the server:

1. In the client I was sending 25 messages/ second to the server: I was sending data no mather if it was moving or if it was idle, but I thought that instead sending messages all the time I could only send data when a change was detected, for example: the client is idle, so the first time, it will send "/idle", but if it keeps standing still it wont keep sending "/idle", server will save the command until a new one arrives, so after a while I start to move forward, I send a "/acce" to the server first time, but again if I keep on moving forward it wont send again "/acce".. that divided outgoing messages from 25 to 2 or 3 messages when moving and 0! standing still.
2. In the server I checked the messages and found that "/upda" >> (client updates) was about 99% of the outgoing traffic, that generated a huge problem when a client was in a crowded area, suppose there are 50 clients near, all them are moving so every client is generating 50 updates / frame, with a frame rate of 20 /s that was 50 * 50 * 20 = 50.000 messages in a second!!! that was making the server collapse, so I had an idea, why not nest packets? when a "/upda" is generated I made the server to don't send that message instantly , instead of that it "packed"  into a bigger message, when more "/upda" messages arraived they were packed up to 10 updates in a bigger one, so in the same "crowded"  are messages sent will be reduced to 5.000, that was much more reasonable.

 With that pair of improvements server was able to handle 1000 clients at the same time with almost no delays , traffic had spikes of 18.000 incoming messages and 200.000 messages sent in a second, but then I reached blocking socket's limits...


<Blocking versus non blocking sockets in the next post>

So I want make an MMORPG, Where do I start from? PART 3

<It had been a long time since I made my last post , part of the problem was because I've been on a trip to Paris (A city everybody should visit at least once in their life), another part of the problem is my fight with non - blocking sockets, but now i'm back and I feel refreshed , with energy to continue with the project.>

Today I'm going to speak about a few things: the server structure ,about messaging  and finally about blocking sockets vs non blocking sockets:

Server Structure:

I plan to make my server architecture based in something like that:

Clients (a.k.a. players) will connect to a login/ authentication server , when validated they will connect to the proxy server, proxy will redirect players to the less loaded server, (balancing server load), server instances will be deployed across the servers on demand ( if a server is more powerful it could handle more instances), all server instances + login server + proxy srv will access the SQL server to write /read all persistent data to be stored, such as:

• Usernames / passwords for the login server
• Maps , players and objects for the server instances
• Number of instances, server instance sockets and health status of the instances (load and failures) for the proxy server.

That is a draft of the design I plan to do, at the moment I'm just working to make a server instance + sql server working, the remaining features will be done later (LOGIN / PROXY).

<Messaging in the next post>

So I want make an MMORPG, Where do I start from? PART 2

Welcome back  to the MMORPG series, part 2!

<Incoming connections....>

After expending one year to learn c++ and SDL I felt ready for the next step: Networking.

I did a chat server just to learn how SDL_net works, It is very interesting and I recommend to try to code one to everybody who dares to create an RPG/MMORPG server because it is basically the same with lower processing needs..

In a chat server (basically ) what happens is:

1. Clients connect to the server
2. They Login with user name / password
3. Every time they type something and press <ENTER> message is sent to all the other connected clients.

(I'm not including here channels and other things... it is just for demonstration purposes only)

If you want you can Download the sources for the server and client and test it, (Unfortunately it only works in Linux because if you run it under windows, it will redirect all the output to a file, so basically is useless..).

But for the MMORPG server I had to analyze it before starting....

What happens (basically )in a MMORPG server is:

(Client side:)

1. Clients connect to the server
2. They Login with user name / password (Or they can create a new user...)
3. Wait for the server to send UID (See below), and world data
4. Transmit actions to the server (update position, use skills, pic objects, attack, etc)
5. Update world state from data received from the server.
6. Goto point 4...

 (Server side:)

1. Allocate resources to accept and process data from incoming clients  (That includes queues and threads to process data)
2. For every client that logs into the server, assign an Unique identifier that is not going to change until client disconnects, the UID is going to be the identifier for all the process in the server (and the client too), the client will receive too the basic world data to start playing (like map data, players and enemies...)
3. Server processes incoming data from connected clients (that includes moving around / and all the skills the player have), for example a player moves forward, server receives a message from client(x) that wants to move forward, so it's going to increase client(x)->Speed
4. Update world state , here it comes were timing is important, the amount of times a world update is applied will change the speed of the game for ALL PLAYERS, for example, players wont send position changes to the server, but  move forward, backward, turn right/left commands, what happens when a world update is executed is that clients will move according to their speed and direction and update X/Y position, so no matter if a client tries to hack the client to run at 100 FPS and not the 25FPS set as default, it wont run faster...
5. Make a list of the players that have changed state (position, skills used)..
6. Send updated data from the clients with changed state to all the clients connected and in a X range...
7. Go to point 3....

I know it is a very raw way to describe what the server does, and you may be missing many features such as maps, skills, enemies / NPCS, AI, etc... but my aim is to make it a simple yet stable network server, then I will add the persistence to do world with  a database (Mysql).

To describe how my server works I'm going to explain how it evolved so it's easier to understand why I changed things...

So let's start to speak with the 3 versions / milestones my server has reached:

<Version Number 0.1:>

Classes:

• cSockServer: Stores global data , a vector to store all the clients connected, server socket, etc...
• cSockClient:Stores data for every client such as socket to communicate

Threads:

• 1x Master thread: Master thread listen for the clients and assigns a new socket to everyone.
• 1xProcessor thread:It processes the data into the input queues and updates wold data
• 1xUpdater thread: Insert updated data to the output queues
• 1xListener Thread/Client:listen for incoming data and inserts it into the Input queue.
• 1xSender thread/Client:Sends data from the output queue to the client.

Queues / vectors:

• Clients vector:Here I store clients data (sockets, UIDs, etc)
• Input queue:Every client has one, used to store incoming data
• Output Queue:Every client has one, used to store outgoing data

Good things of the design:

• Very simple.
• Functional.

Bad Things of the design:

• Very bad performance, when there was more than 3 clients connected you started to feel it lagging even working with the loop-back interface...
• There is no time control, so the server will have to be confident in the clients <Horrid mistake>
• Wasted data: Two threads per client, two queues per client.. too much

What I learned from this version:
Threads are a must when speaking of a socket server, so in this very first version what I saw is I needed to use them in a more efficient way, I was using many threads to just send and receive data to the client but only three to process data.. other big mistake was to use so many queues in the clients, it was a pain in the a** to check them and probably not efficient at all.., with all this things in my head I decided to make an updated version:


<Version Number 0.1.7:>


Classes:

• cSockServer: Stores global data , a vector to store all the clients connected, server socket, etc...
  
• cSockClient:Stores data for every client such as socket to communicate

Threads:

• 1x Master thread: Master thread listen for the clients and assigns a new socket to everyone.
• 2xProcessor thread:It processes the data into the input queue and updates wold data
• 1xUpdater Thread, inserts into output queue the data to send
• XxSender threads: Send data from the output queue
• 1xListener Thread/Client:listen for incoming data and inserts it into the Input queue.

Queues / vectors:

• Clients vector:Here I store clients data (sockets, UIDs, etc)
  
• Input queue:One in the server, used to store incoming data
• Output Queue:One in the server, used to store outgoing data

Good things of the design:

• Centralizing queues make it to waste less data.
• Having more threads to process data makes it to be able to handle more clients (watch the screen-shot in the top of the post), 101 clients connected...
• Timing centralized in the server, more secure now
  

Bad Things of the design:

• Still bad performance,after adding 20 clients it started to degrade performance no matter how many threads you added to the server

What I learned from this version:
Threads collide, if you use semaphores / mutexes you will prevent it from happening but, after adding 5 threads to process data from a queue, there is no performance improvement because threads have to wait each-other. It was a design fault, to continue increasing performance I had to add more queues with different semaphores so the threads wont collide so much, so that brings me to the last version:



<Version Number 0.1.12:>

<867 Clients connected at the same time, everyone constantly moving sending data to the server, 25 incoming packets /s per client, total, +2.000 incoming messages processed/s,  peak:18.000 messages SENT in a second, that is like 18 packets /millisecond, latency/lag for the clients from 49ms to 113ms average 70-80ms>



Classes:

• cSockServer: Stores global data , a vector to store all the clients connected, server socket, etc...
  
• cSockClient:Stores data for every client such as socket to communicate

Threads:

• 1x Master thread: Master thread listen for the clients and assigns a new socket to everyone.
• 2xProcessor threads:It processes the data into the input queue and updates wold data
• 1xUpdater Thread: inserts clients with updated data to a queue to update
• 4xUpdaterQueues:it gets the client UID from the UpdaterQueue and check which clients are near and need to get an update state, so it inserts the outgoing data to the Pool of Output queues
• Pool of Sender threads:The thread gets data from the Pool of Queues and Send data to the clients
• 1xListener Thread/Client:listen for incoming data and inserts it into the Input queue.

Queues / vectors:

• Clients vector:Here I store clients data (sockets, UIDs, etc)
  
• Input queue:One in the server, used to store incoming data
• <vector>Pool Output Queue:It can handle many queues so if the client number rise, you can add more queues to prevent thread collisions.
• <vector>Pool Sender thread Queue: Used to store / add Sender threads dynamically.

Good things of the design:

• Finally threads start to unleash their power, as many threads / output queues you add, as many users can handle, 867 clients (BOTs) are starting to be something good for a server....

Bad Things of the design:

• No optimized at all, there are many parts in the server that could change to make it work smoother, but for didactic purposes, I wont change them at the moment.

What I learned from this version:
I have learned to be very careful with the threads, many threads can access to a variable and read from it and processing power will go up, but when writing data you are forced to use semaphores, so speed stops increasing after a certain number of threads.

I added a command for the server that was "AddPower" it adds one output queue and 5 sender threads, so you can adjust performance, but speaking with a friend he told me that I could automate it so automatically it added more queues /threads, so I did! checking if the queues were filled and needed to be processed faster was easy, and the result is the server you have in the picture absorving 867 clients data....

Thanks Jhonny D!

See you soon in the MMORPG series....

So I want make an MMORPG, Where do I start from? PART 1

That is indeed a good question, asked so many times and answered many times too, in many sites the answer is like:

<If you have a team>

1. Get a team: coders + artists + world designers +well like 10 persons.
2. Get ready to expend 2 years (at least) on it
3. Pay big amount of money to the ISP
4. Have a design document

<Lone developer>

1. Forget about it :(

 What can I add to that? I basically decided to made it no matter how hard it was; I'm not somebody that has never programmed and think that their "RPG maker experience is enough", I'm not asking how to make it because If you are asking that, you probably are so far from the objective you desire that you wont get it never.

I tried to divide the BIG puzzle into small pieces, c++, SDL, SDL_image, SDL_ttf, SDL_NET, SDL_thread, Gimp, MYSQL....

With those pieces I made some bigger ones: A tileset, a simple game engine, game states, player creation process, a simple chat server....

And now it comes the good thing, I got a Simple (RPG DEMO) server with a client, I didn't made it on time (1 year was my objective), but now , after millions of doubts I know I will finish the game...

My objectives now are:

1. Start a series of posts explaining how I did it.
2. Clean Server / client code and upload them (Yes, I'm going to release it soon)
3. Set up MYSQL server

So first thing first:

Q:What do I need to learn to send / receive date across the network?
A:Network sockets

Q: How does network sockets work?
A: Network sockets handles data send/received to an IP network, they are both used by TCP (Connection oriented ) and UDP (Connectionless oriented), in the server you "open" a socket to listen for incoming data, open a socket means bind the program to a particular IP port of the computer, so we could open a socket at port 80 to create a web server, for example, when we create a server socket it waits until a Client machine (program) connects to it, then we create a client sockets that is going to actually communicate with the client. The clients just open a client socket and establish a connection with the server socket.

Q:Why do I need threads?
A: Threads are used to do many things at the same time, for example, a Client for the MMORPG, could have a thread to draw the images on the screen while another one sends/receives network data , the server could have one thread (maybe more?) to handle network data, another one for the AI, another one for Environment events... In the modern multi-core computers, it's a way to use 100% of the processor power

Q:Why are you using SDL_NET? isn't it an ancient library?
A:It is old, but network sockets are older and are still used today (and probably are going to be used for many, many years), just thing the new IP protocol (IPv6) uses them too... SDL_NET has other advantages too, it can be used in a multi-platform environment (I use it in my client and I can compile it under windows / Linux without touching a single line from the code to make it work :) ), finally it is a low-level library so you feel you have the control over what you are working, there are libraries made upon SDL_NET that add things (NET2 uses multi-threading for example), but you have less control about what is happening, another problem is find documentation , SDL_NET is so small that when you read the manual , you realize how easy is to use it.

<More Q/A soon...>

In the next post: Server code and explanations..

JB

An Ubuntu Server (1/X)

<Open case view>

<I have been playing around with the tiles and the maps for a while, it was good but if i have to finish the project in time , it's the right moment to make my own server to test SDL_Net >


<First i needed to set up a server, probably you will think i was going to use a dual xeon computer with terabytes of hard disk .... from my experience with the servers and the power needed to run the app i don't need so much hard, in fact i have decided to run it in a quite old computer, that's the server configuration:>

• Intel 3.06Ghz CPU
• 1GB RAM
• 2x9GB SCSI HDDs
• 100 MBs LAN

<The most important thing in a server is data redundancy , if the server power supply fails, you will have a server down, but you can bring it to live just replacing it, But what will happen if the hard disk crashes?, just guess.... so i decided to set up a software RAID 1 device with two SCSI hard disks, the processor and RAM are weak for a desktop gamer, but i think they are going to handle the expected amount of users, at least in the beginning>

<Dual SCSI disks>

<Now into the software, the final server configuration is going to use the Ubuntu Server edition but at the moment i need to debug and compile the project in the server, so I'm going to use the Ubuntu 10.10 Alternate edition instead of the server one>

<You may ask "Why to use the 10.10 version when there is a new version (11.04)?" , it is indeed a good question, and the quick answer is "Unity"... for all the people that have worked with Ubuntu from a few years ago , you are used to the Gnome desktop manager, so you were not forced (in the past) to use a (quite) bloated desktop manager like KDE, I'm not telling you to don't use KDE, it's good of course, but for me it smells too much like MS products...., but now Gnome has decided that to use the desktop manager you are going to need a powerful opengl card (maybe it's ok for a modern desktop computer), but it's not so ok for a server computer.., Unity tries to solve this problem, but if you have ever used the "Ubuntu Netbook Remix" you will know it's NOT a mature product, you have the feeling that is sort of mac OS/X clone, can't set up the desktop and bars the same way than in standard Gnome and finally i'm pretty sure it is much more slower than the (old) Ubuntu 10.10>

<For me it's quite clear which o.s. to use, just go and get Ubuntu Alternate 10.10 and burn the cd to start installing it>

In the next Post, installing wit Ubuntu Alternate, software RAIDs and more....