Building on My SQL Storage Engines and APIs to Conduct Complex Analysis Using SQL Query Language: Part 3

• Download FaceBookFriendsSpaceEngineBinaries.zip - 162.7 KB
• Download source - 125.1 KB

Introduction

In Part 2 of this article series, I presented the idea of mysql storage engines which retrieve data from Win32 APIs related to process info and feed it to MYSQL. Specifically, I showed up to date information about running processes in the system on mysql console. This article series continues on, this time exposing facebook friends on mysql console for you to conduct complex analysis. This allows doing complex analysis not only on your friends but the friends of your friends and in turns their friends and so on i.e. your complete friend’s space. You can also limit the analysis to few friends.

FaceBookFriendsSpaceEngine

This mysql storage engine is different from storage engine presented in part 2 because it retrieves data from facebook graph apis and feeds to mysql. A table named Friends with engine FaceBookFriendsSpaceEngine is created.

create table Friends
(
  person_id varchar(1024), 
  first_name varchar(1024), 
  gender varchar(1024), 
  last_name  varchar(1024), 
  link  varchar(1024), 
  locale  varchar(1024), 
  name  varchar(1024), 
  updated_time  varchar(1024), 
  username  varchar(1024),
  friend_id varchar(1024), 
  friend_first_name varchar(1024), 
  friend_gender varchar(1024), 
  friend_last_name varchar(1024), 
  friend_link varchar(1024), 
  friend_locale varchar(1024), 
  friend_name varchar(1024), 
  friend_updated_time varchar(1024), 
  friend_username varchar(1024),
  access_token  varchar(2048)
)ENGINE= FaceBookFriendsSpaceEngine;

When user enters a select query on Friends table, the control passes to FaceBookFriendsSpaceEngine in rnd_init method. This method checks that table name is really “Friends”. Then it checks that where clause parse tree is present or not. If yes, then where clause parse tree is traversed to be converted to facebook graph api calls. Otherwise no records are fed to MySQL.

Before continuing to rnd_init following data structures need to be explained.

vector<Friends> m_friendsSpace; //(belongs to ha_example class and maintains all friends space for the query)

int m_friendsSpaceIndex; //(belongs to ha_example class and maintains the index to m_friendsSpace)

struct Person
{
    string id; 
    string first_name; 
    string gender; 
    string last_name;
    string link; 
    string locale; 
    string name; 
    string updated_time; 
    string username;

};
struct Friends
{
    Person A;
    Person B;
    string access_token;
};



int ha_example::rnd_init(bool scan)
{
    DBUG_ENTER("ha_example::rnd_init");
    
    if(_stricmp(table_share->table_name.str,"Friends")==0)
    {
        THD *thd=this->ha_thd();
        SELECT_LEX *select_lex=&thd->lex->select_lex;
        m_friendsSpaceIndex=0;

        if(select_lex->where==0)
        {
        }
        else
        {
            stack<ParseStackElement> parseStack;
            select_lex->where->traverse_cond(My_Cond_traverser,(void*)&parseStack,Item::traverse_order::POSTFIX);
            if(!parseStack.empty()&&parseStack.size()==1)
            {
                ParseStackElement topElem=parseStack.top();
                GetFaceBookFriendSpace(topElem.faceBookInputColumnValuesVector,m_friendsSpace);
            }
            else
            {
            }
        }
    }
    DBUG_RETURN(0);
}

The stack of ParseStackElement struct is created and maintained during post fix traversal of where clause parse tree.

struct ParseStackElement
{
    vector<FaceBookInputColumnValue> faceBookInputColumnValuesVector;
    Item *item;
};

The ParseStackElement contains vector of type FaceBookInputColumnValue:

struct FaceBookInputColumnValue
{
    string mAccessToken;
    string mUserId;
};

The FaceBookInputColumnValue contains facebook graph api access token and person id.

The rnd_init calls traverse_cond on where clause parse tree root node in order to traverse it in postfix order. The My_Cond_traverser function is specified to be called during traversal. The parse stack is passed to this function. This function checks for the following:

In operator: In this case, Item_func_in_case is called to get the set of access tokens or person ids specified in the “in clause” and pushed to the stack

Equality operator: In this case, Item_func_eq_case is called to get the acess token or person id specified and pushed to stack

And/Or operator: In this case, stack is popped until the children of item (My_Cond_traverser first argument) are coming. During pop, the vectors of type FaceBookInputColumnValue are Anded and Ored to get the resulting set.

void My_Cond_traverser (const Item *item, void *arg)
{
    stack<ParseStackElement> *parseStack=(stack<ParseStackElement> *)arg;
    vector<facebookinputcolumnvalue> faceBookInputColumnValuesVector;
    if(dynamic_cast<const  Item_func_in*>(item))
    {
        faceBookInputColumnValuesVector=Item_func_in_case(item);
        ParseStackElement elem;
        elem.faceBookInputColumnValuesVector=faceBookInputColumnValuesVector;
        elem.item=(Item *)item;
        parseStack->push(elem);
    }
    else if(dynamic_cast<const Item_func_eq*>(item))
    {
        faceBookInputColumnValuesVector=Item_func_eq_case(item);
        ParseStackElement elem;
        elem.faceBookInputColumnValuesVector=faceBookInputColumnValuesVector;
        elem.item=(Item *)item;
        parseStack->push(elem);
    }
    else if(dynamic_cast<const Item_cond*>(item))
    {
        const Item_cond *itemCondC=dynamic_cast<const>(item);
        Item_cond *itemCond=(Item_cond *)itemCondC;
        
        vector<facebookinputcolumnvalue> result;
        bool isAnd=false;
        if(dynamic_cast<const Item_cond_and*>(item))
        {
            isAnd=true;
        }
        if (!parseStack->empty()&&isChildOf((Item_cond*)item,parseStack->top().item))
        {
            result=parseStack->top().faceBookInputColumnValuesVector;
            parseStack->pop();
        }
        while (!parseStack->empty()&&isChildOf((Item_cond*)item,parseStack->top().item))    
        {
            if(isAnd)
            {
                result=And(result,parseStack->top().faceBookInputColumnValuesVector);
            }
            else
            {
                result=Or(result,parseStack->top().faceBookInputColumnValuesVector);
            }
            parseStack->pop();            
        }
        ParseStackElement elem;
        elem.faceBookInputColumnValuesVector=result;
        elem.item=(Item *)item;
        parseStack->push(elem);
    }
}

The following function extracts the access token or person id entered by user in the equal clause. The code checks that item is of Item_func type. Then it checks that the first argument is field and second is not field. Then it checks that the field is access_token or person_id.

vector<FaceBookInputColumnValue> Item_func_eq_case(const Item *item)
{
    vector<FaceBookInputColumnValue> faceBookInputColumnValuesVector;
    const Item_func * itemFunction=dynamic_cast<const Item_func*>(item);
    Item **arguments=0;
    if(itemFunction)
    {
        arguments=itemFunction->arguments();
    }
    else
    {
        return faceBookInputColumnValuesVector;
    }
    Item *field=0;
    Item *value=0;
    if(dynamic_cast <Item_field*>(arguments[0]))
    {
        field = arguments[0];
    }
    else
    {
        return faceBookInputColumnValuesVector;
    }
    if(dynamic_cast <Item_field*>(arguments[1]))
    {
        return faceBookInputColumnValuesVector;
    }
    else
    {
        value = arguments[1];
    }
    
    if(field&&field->item_name.ptr()&&_stricmp(field->item_name.ptr(),"access_token")==0)
    {
        FaceBookInputColumnValue myFaceBookInputColumns;
        myFaceBookInputColumns.mAccessToken=value->item_name.ptr();
        myFaceBookInputColumns.mUserId="";
        faceBookInputColumnValuesVector.push_back(myFaceBookInputColumns);
    }
    else if(field&&field->item_name.ptr()&&_stricmp(field->item_name.ptr(),"person_id")==0)
    {
        FaceBookInputColumnValue myFaceBookInputColumns;
        myFaceBookInputColumns.mAccessToken="";
        myFaceBookInputColumns.mUserId=value->item_name.ptr();
        faceBookInputColumnValuesVector.push_back(myFaceBookInputColumns);
    }
    return faceBookInputColumnValuesVector;
}

The following function extracts the set of access tokens or person ids specified in “in clause”. First check that item is of Item_func type. Then check that first argument is field and the following arguments are not field type.

vector<FaceBookInputColumnValue> Item_func_in_case(const Item *item)
{
    vector<FaceBookInputColumnValue> faceBookInputColumnValuesVector;
    const Item_func * itemFunction=dynamic_cast<const Item_func*>(item);
    Item **arguments=0;
    int inArgcount=0;
    if(itemFunction)
    {
        arguments=itemFunction->arguments();
        inArgcount=itemFunction->arg_count;
    }
    else
    {
        return faceBookInputColumnValuesVector;
    }
    Item *field=0;
    Item *value=0;
    if(dynamic_cast <Item_field*>(arguments[0]))
    {
        field = arguments[0];
    }
    else
    {
        return faceBookInputColumnValuesVector;
    }
    if(field&&field->item_name.ptr()&&_stricmp(field->item_name.ptr(),"access_token")==0)
    {
        LONG index;
        for (index = 1; index < inArgcount; index++)
        {
            if(dynamic_cast <Item_field*>(arguments[index]))
            {
                continue;
            }
            else
            {
                value = arguments[index];
            }
            FaceBookInputColumnValue myFaceBookInputColumns;
            myFaceBookInputColumns.mAccessToken=value->item_name.ptr();
            myFaceBookInputColumns.mUserId="";
            faceBookInputColumnValuesVector.push_back(myFaceBookInputColumns);
        }
    }
    else if(field&&field->item_name.ptr()&&_stricmp(field->item_name.ptr(),"person_id")==0)
    {
        LONG index;
        for (index = 1; index < inArgcount; index++)
        {
            if(dynamic_cast <Item_field*>(arguments[index]))
            {
                continue;
            }
            else
            {
                value = arguments[index];
            }
            FaceBookInputColumnValue myFaceBookInputColumns;
            myFaceBookInputColumns.mAccessToken="";
            myFaceBookInputColumns.mUserId=value->item_name.ptr();
            faceBookInputColumnValuesVector.push_back(myFaceBookInputColumns);
        }
    }
    return faceBookInputColumnValuesVector;
}

The following is a helper function used by My_Cond_traverser to check the parent child relationship

bool isChildOf(Item_cond *parent,Item *child)
{
    List_iterator<Item> li(*(parent->argument_list()));
    Item *it= NULL;
    while ((it= li++))    
    {
        if(child==it)
            return true;
    }
    return false;
}

The following function Ands the vector A of FaceBookInputColumnValue and vector B of FaceBookInputColumnValue. In this function, both vectors are traversed in a nested loop. If one struct of type FaceBookInputColumnValue from A matches other struct of same type from B, then this FaceBookInputColumnValue is added to the resultant vector. The match is found by checking access token and person id from struct (FaceBookInputColumnValue) belonging to A and B. If both access token are not empty then they should match otherwise skip them. Similarly if both person ids are not empty then they should match otherwise skip them. If access token in one is empty and other is not empty then common access token is equal to the non empty one. Similarly if person Id in one is empty and other is not empty then common person id is equal to the non empty one. The common value is inserted into the result vector.

vector<FaceBookInputColumnValue> And(vector<FaceBookInputColumnValue> A,vector<FaceBookInputColumnValue> B)
{
    vector<FaceBookInputColumnValue> result;

    for(vector<FaceBookInputColumnValue>::iterator iter1=A.begin();iter1!=A.end();iter1++)
    {
        for(vector<FaceBookInputColumnValue>::iterator iter2=B.begin();iter2!=B.end();iter2++)
        {
            string accessToken="";
            if(iter1->mAccessToken!=""&&iter2->mAccessToken!="")
            {
                if(iter1->mAccessToken==iter2->mAccessToken)
                {
                    accessToken=iter1->mAccessToken;
                }
                else
                {
                    continue;
                }
            }
            else if(iter1->mAccessToken!=""&&iter2->mAccessToken=="")
            {
                accessToken=iter1->mAccessToken;
            }
            else if(iter1->mAccessToken==""&&iter2->mAccessToken!="")
            {
                accessToken=iter2->mAccessToken;
            }
            else if(iter1->mAccessToken==""&&iter2->mAccessToken=="")
            {
            }

            string userId="";
            if(iter1->mUserId!=""&&iter2->mUserId!="")
            {
                if(iter1->mUserId==iter2->mUserId)
                {
                    userId=iter1->mUserId;
                }
                else
                {
                    continue;
                }
            }
            else if(iter1->mUserId!=""&&iter2->mUserId=="")
            {
                userId=iter1->mUserId;
            }
            else if(iter1->mUserId==""&&iter2->mUserId!="")
            {
                userId=iter2->mUserId;
            }
            else if(iter1->mUserId==""&&iter2->mUserId=="")
            {
            }
            FaceBookInputColumnValue myFaceBookInputColumns;
            myFaceBookInputColumns.mAccessToken=accessToken;
            myFaceBookInputColumns.mUserId=userId;
            result.push_back(myFaceBookInputColumns);
        }
    }
    return result;
}

The following function Ors the vector A of FaceBookInputColumnValue and vector B of FaceBookInputColumnValue. In this function, both vectors are traversed in a nested loop. If one struct of type FaceBookInputColumnValue from A has common with other struct of same type from B, then the common value is added to the resultant vector. The common is found by matching access token and person id from struct (FaceBookInputColumnValue) belonging to A and B. If both access token are not empty then they should match otherwise skip them. Similarly if both person ids are not empty then they should match otherwise skip them. If access token in one is empty and other is not empty then common access token is empty. Similarly if person Id in one is empty and other is not empty then common person id is empty. The common value is inserted into the result vector and erased from vector A and B. After nested loop completes, vector A is traversed and all elements are added to result vector. Similarly vector B is traversed and all elements are added to result vector.

vector<FaceBookInputColumnValue> Or(vector<FaceBookInputColumnValue> A,vector<FaceBookInputColumnValue> B)
{
    vector<FaceBookInputColumnValue> result;

    for(vector<FaceBookInputColumnValue>::iterator iter1=A.begin();iter1!=A.end();iter1++)
    {
        for(vector<FaceBookInputColumnValue>::iterator iter2=B.begin();iter2!=B.end();iter2++)
        {
            bool commonFound=false;
            string accessToken="";
            if(iter1->mAccessToken!=""&&iter2->mAccessToken!="")
            {
                if(iter1->mAccessToken==iter2->mAccessToken)
                {
                    accessToken=iter1->mAccessToken;
                    commonFound=true;
                }
                else
                {
                    continue;
                }
            }
            string userId="";
            if(iter1->mUserId!=""&&iter2->mUserId!="")
            {
                if(iter1->mUserId==iter2->mUserId)
                {
                    userId=iter1->mUserId;
                    commonFound=true;
                }
                else
                {
                    continue;
                }
            }
            if(commonFound==true)
            {
                FaceBookInputColumnValue myFaceBookInputColumns;
                myFaceBookInputColumns.mAccessToken=accessToken;
                myFaceBookInputColumns.mUserId=userId;
                result.push_back(myFaceBookInputColumns);
                A.erase(iter1);
                B.erase(iter2);
                iter1=A.begin();
                iter2=B.begin();
            }
        }
    }
    for(vector<FaceBookInputColumnValue>::iterator iter1=A.begin();iter1!=A.end();iter1++)
    {
        result.push_back(*iter1);
    }
    for(vector<FaceBookInputColumnValue>::iterator iter2=B.begin();iter2!=B.end();iter2++)
    {
        result.push_back(*iter2);
    }
    return result;
}

When traverse_cond function completes, a check is made that parse stack contains only one element of type ParseStackElement. The faceBookInputColumnValuesVector vector present in ParseStackElement is passed to GetFaceBookFriendSpace function which iterates through that vector and calls FaceBookAccessTokenFriendSpace for each element.

void GetFaceBookFriendSpace(vector<FaceBookInputColumnValue> faceBookInputColumnValuesVector,    vector<Friends> &friendsSpace)
{
    vector<FaceBookInputColumnValue>::iterator iter=faceBookInputColumnValuesVector.begin();
    for(;iter!=faceBookInputColumnValuesVector.end();iter++)
    {
        vector<Friends> friendsVec=FaceBookAccessTokenFriendSpace(*iter);
        for(vector<Friends>::iterator iter2=friendsVec.begin();iter2!=friendsVec.end();iter2++)
        {
            friendsSpace.push_back(*iter2);
        }
    }
}

The FaceBookAccessTokenFriendSpace function checks that in myFaceBookInputColumns both access token and user ids are not empty then it simply calls GetSingleUserFriends otherwise if access token is not empty then it gets the user id associated with the access token. After that it calls GetSingleUserFriends passing the user id retrieved. The function maintains two hash_set of type string namely, alreadyBrowsedFriends and currentFriends. The alreadyBrowsedFriends keeps the user ids whose friends are already traversed. The currentFriends keeps the user ids whose friends need to be traversed. The while loop keeps iterating until currentFriends is empty. Inside while loop it takes start user id from currentFriends. It checks that user id is not whose friends list is already traversed. If yes then it deletes it from currentFriends. Otherwise it calls GetSingleUserFriends for that user id. In the following for loop it adds the friends to friendsSpace. And after that it adds that user id to alreadyBrowsedFriends. At the end, function returns friendsSpace.

vector<Friends> FaceBookAccessTokenFriendSpace(FaceBookInputColumnValue myFaceBookInputColumns)
{
    hash_set<string> alreadyBrowsedFriends;

    hash_set<string> currentFriends;

    vector<Friends> friendsSpace;
    if(myFaceBookInputColumns.mUserId!=""&&myFaceBookInputColumns.mAccessToken!="")
    {
        friendsSpace=GetSingleUserFriends(myFaceBookInputColumns.mUserId,myFaceBookInputColumns.mAccessToken);
    }
    else if(myFaceBookInputColumns.mAccessToken!="")
    {
        string userId;
        userId=GetAccessTokenAssociatedUserId(myFaceBookInputColumns.mAccessToken);
        vector<Friends> myFriends=GetSingleUserFriends(userId,myFaceBookInputColumns.mAccessToken);
        for(vector<Friends>::iterator iter=myFriends.begin();iter!=myFriends.end();iter++)
        {
            currentFriends.insert(iter->B.id);
            friendsSpace.push_back(*iter);
        }
        alreadyBrowsedFriends.insert(userId);
        while(!currentFriends.empty())
        {
            userId=*currentFriends.begin();
            if(alreadyBrowsedFriends.find(userId)==alreadyBrowsedFriends.end())
            {
                myFriends=GetSingleUserFriends(userId,myFaceBookInputColumns.mAccessToken);
                for(vector<Friends>::iterator iter=myFriends.begin();iter!=myFriends.end();iter++)
                {
                    if(currentFriends.find(iter->B.id)==currentFriends.end())
                    {
                        currentFriends.insert(iter->B.id);
                    }
                    friendsSpace.push_back(*iter);
                }
                alreadyBrowsedFriends.insert(userId);
            }
            else
            {
                currentFriends.erase(userId);
            }
        }
    }
    return friendsSpace;
}

The GetSingleUserFriends function takes user id and access token and returns user friends. This function call GetUserDetails for user id passed as argument. After that it from request url to get user friends. The result is json containing friends list. The json is traversed using json path. The traversal completes when there are no more friends. In the while loop it calls GetUserDetails to get friends details.

string commonUrl="https://graph.facebook.com/";
vector<Friends> GetSingleUserFriends(string userId,string accessToken)
{
    vector<Friends> myfriends;
    Person A=GetUserDetails(userId,accessToken);
    string url=commonUrl+userId+"/friends?access_token=";
    url=url+accessToken;
    string friends=WinHttpClientResponse(url);
    int index=0;
    char num[100];
    itoa(index,num,10);
    string count=num;
    string friendId=CallJsonPath(friends,"$.data["+count+"].id");
    index++;
    while(friendId!="")
    {
        Friends f;
        f.A=A;
        f.B=GetUserDetails(friendId,accessToken);
        f.access_token=accessToken;
        myfriends.push_back(f);
        itoa(index,num,10);
        count=num;
        friendId=CallJsonPath(friends,"$.data["+count+"].id");
        index++;
    }
    return myfriends;
}

The GetUserDetails takes user id and access token and returns user details. It forms url to get user details. A request is made on that url. The result is user detail json. The json is traversed using json path and user details are extracted.

Person GetUserDetails(string userId,string accessToken)
{
    Person A;
    string url=commonUrl+userId+"?access_token=";
    url=url+accessToken;
    string details=WinHttpClientResponse(url);
    A.id=CallJsonPath(details,"$.id");
    A.first_name=CallJsonPath(details,"$.first_name");
    A.gender=CallJsonPath(details,"$.gender");
    A.last_name=CallJsonPath(details,"$.last_name");
    A.link=CallJsonPath(details,"$.link");
    A.locale=CallJsonPath(details,"$.locale");
    A.name=CallJsonPath(details,"$.name");
    return A;
}

The GetAccessTokenAssociatedUserId function takes access token and returns associated user id. The function forms URL to retrieve details about me. A request is made on that URL. The result json is traversed using Jason path to extract user id.

string GetAccessTokenAssociatedUserId(string accessToken)
{
    string url=commonUrl+"me?access_token=";
    url=url+accessToken;
    string me=WinHttpClientResponse(url);
    string userId;
    userId=CallJsonPath(me,"$.id");
    return userId;
}

The WinHttpClientResponse takes url and return the http response contents.

string WinHttpClientResponse(string url)
{
    wstring wUrl=StringToWString(url);
    WinHttpClient client(wUrl);
    client.SendHttpRequest();
    wstring httpResponseHeader = client.GetResponseHeader();
    wstring httpResponseContent = client.GetResponseContent();
    return WStringToString(httpResponseContent);
}

The CallJsonPath function takes json and path. It returns the value of the path.

string CallJsonPath(string json,string path)
{
    string result="";
    json_t *root;
    json_error_t error;
    root = json_loads(json.c_str(), 0, &error);
    if(root)
    {
        json_t *pathVal=json_path_get(root,path.c_str());
        if(pathVal)
        {
            result=json_string_value(pathVal);
        }
        json_decref(root);
    }
    
    return result;
}

Following are string conversion functions:

wstring StringToWString(string str)
{
    CA2W pszW(str.c_str());
    wstring wStr=pszW;
    return wStr;
}
string WStringToString(wstring wStr)
{
    CW2A pszA(wStr.c_str());
    string str=pszA;
    return str;
}

The rnd_next is called by MySQL to feed the next row. If m_friendsSpaceIndex is less than size of m_friendsSpace which means there are rows to feed then call feed_person for person A in friend struct. After that call feed_person B in friend struct. After that feed access token. After that increment m_friendsSpaceIndex. If there are no more friends to feed then return HA_ERR_END_OF_FILE otherwise 0

int ha_example::rnd_next(uchar *buf)
{
    int rc;
    DBUG_ENTER("ha_example::rnd_next");
    MYSQL_READ_ROW_START(table_share->db.str, table_share->table_name.str,
                    TRUE);
    if(m_friendsSpaceIndex<m_friendsSpace.size())
    {
        Field **field=table->field;      
        feed_person(m_friendsSpace[m_friendsSpaceIndex].A,field);
        feed_person(m_friendsSpace[m_friendsSpaceIndex].B,field);
        feed_person_fields(m_friendsSpace[m_friendsSpaceIndex].access_token,field);
        m_friendsSpaceIndex++;
        rc=0;
    }
    else
    {
        rc= HA_ERR_END_OF_FILE;
    }
    MYSQL_READ_ROW_DONE(rc);
    DBUG_RETURN(rc);
}

The feed_person_fields method takes field value and MySQL data structure Field and stores value into field.

void ha_example::feed_person_fields(string fieldStr,Field **&field)
{
        bitmap_set_bit(table->write_set, (*field)->field_index);
        (*field)->set_notnull();
        (*field)->store(fieldStr.c_str(),strlen(fieldStr.c_str()), system_charset_info);
        field++;
}

The feed_person takes person and MySQL struct Field and stores all the members of person to fields.

void ha_example::feed_person(Person p,Field **&field)
{
    feed_person_fields(p.id,field);
    feed_person_fields(p.first_name,field);
    feed_person_fields(p.gender,field);
    feed_person_fields(p.last_name,field);
    feed_person_fields(p.link,field);
    feed_person_fields(p.locale,field);
    feed_person_fields(p.name,field);
    feed_person_fields(p.updated_time,field);
    feed_person_fields(p.username,field);
}

How to Run

 

• Install MySQL 5.6.17 from the following link:
  • http://dev.mysql.com/downloads/installer/5.6.html
• Make sure that during installation, you select the 32 bit version.
• Copy FaceBookFriendsSpaceEngine.dll from FaceBookFriendsSpaceEngineBinaries.zip
• Go to the directory where you installed MYSQL.
• Find subdirectory lib and open it.
• Find and open the plugin directory inside.
• Paste FaceBookFriendsSpaceEngine.dll.
• Go to MySQL console and run the following command.
• Install plugin FaceBookFriendsSpaceEngine soname 'FaceBookFriendsSpaceEngine.dll';
• This will install the FaceBookFriendsSpaceEngine.
• Create a database name test.
• Now create a table named Friends and specify FaceBookFriendsSpaceEngine as the storage engine.

 

create table Friends
(
  person_id varchar(1024), 
  first_name varchar(1024), 
  gender varchar(1024), 
  last_name  varchar(1024), 
  link  varchar(1024), 
  locale  varchar(1024), 
  name  varchar(1024), 
  updated_time  varchar(1024), 
  username  varchar(1024),
  friend_id varchar(1024), 
  friend_first_name varchar(1024), 
  friend_gender varchar(1024), 
  friend_last_name varchar(1024), 
  friend_link varchar(1024), 
  friend_locale varchar(1024), 
  friend_name varchar(1024), 
  friend_updated_time varchar(1024), 
  friend_username varchar(1024),
  access_token  varchar(2048)
)ENGINE= FaceBookFriendsSpaceEngine;

• Goto following URL:
  • https://developers.facebook.com/tools/explorer/145634995501895/?method=GET&path=me&version=v2.0
• Click Get Access Token Button. Under User Data Permissions select user_friends and then click Get Access Token button.

Enter the following query:

select * from friends where access_token="Type your access token here";

Here is another query

select count(friend_gender) from friends where access_token=" Type your access token here " and person_id="facebook id of a person";

Now enter any query of your choice and see the results.

Next we demonstrate how you can do complex analysis about your friends using Tableau.

In the above gui click Connnect to data.

Select MySQL from On a server list

Type server name, user name and password in MySQL Connection dialog and click Connect

Select test database on the server

Select Friends table

Select Connect live

Drag access_token to Filters and select Custom Value List and enter the facebook access_token.

Click the + button

Similarly drag person_id to Filters and select custom value list.

Type any facebook user id and click + button.

Drag friends_id to Rows

Drag friend_name to rows

Drag friend_link to Rows

Where to Get the Source Code

• Download MYSQL source code from the following link:
  • http://dev.mysql.com/downloads/mysql/
• Extract the source code to C:/mysql-5.6.17

How to Build the Source Code

 

• Please follow the instruction given at:
  • http://dev.mysql.com/doc/refman/5.6/en/installing-source-distribution.html
• I build the source code using Visual Studio 2012 with the following instruction:
  • cmake . -G "Visual Studio 11"
• Download the attachment named FaceBookFriendsSpaceEngineSourceCode.zip with this article.
• It contains the source code of FaceBookFriendsSpaceEngine in folder example.
• Copy the example folder to mysql-5.6.17\storage

Conclusion

You should appreciate that why parse the where clause, why not just feed all the data. The next part will enhance friend analysis.