Monday, August 20, 2012

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!

    Tuesday, August 7, 2012

    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.


    Friday, August 3, 2012

    SVN UP! New code repository

    Hello again

         Before going any further (and after all the things that happened with Megaupload), I knew I wont be able to continue uploading my project / tools / whatever to 4shared, and yes I was right, in a few days 4shared started to ask for "user accounts" to be able to download things...

    I had to start using another away to store my project.. and I needed a way to control the versions from my project.

    The solution was quite simple (basically because a good friend showed me from the advantages of using subversion aka "svn")

    So I went back to Google.. again.. went to http://code.google.com and signed for a new account, created a new project (http://code.google.com/p/jbfprj/) and voila! I got my own svn server :)

    So now anybody can checkout in my software repository and get the source for my projects, at the moment I just uploaded SimpleSQL:

    <copied from code.google.com>
    Use this command to anonymously check out the latest project source code:
    # Non-members may check out a read-only working copy anonymously over HTTP.
    svn checkout http://jbfprj.googlecode.com/svn/trunk/ jbfprj-read-only
    Notice you will need to install subversion: 
    sudo apt-get install subversion
    
    under linux or you can use another client for windows like tortoise-svn...
    Anyway, that's all you will need to get the source, and of course, type "svn up" whenever you want to check if there are updates...>

    In the next post I'll be back to the server program, but now I'm going to reduce the posts to a size that anybody can handle, starting for login to the server..

    Thursday, August 2, 2012

    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.