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 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>