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.

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

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.