1 /*! \file

     2     \brief File containing class description for DataStoreManager class.

     3 

     4     Copyright (c) 2010 Sasken Communication Technologies Ltd.

     5     All rights reserved.

     6     This component and the accompanying materials are made available

     7     under the terms of the "{License}"

     8     which accompanies  this distribution, and is available

     9     at the URL "{LicenseUrl}".

    10 

    11     \author Jaspinder Singh, Sasken Communication Technologies Ltd - Initial contribution

    12 

    13     \version 0.1

    14 

    15 */

    16 

    17 #include <dsm.h>

    18 #include <QDebug>

    19 

    20 // Static data initialization

    21 DataStoreManager* DataStoreManager::m_dsm_instance = NULL;

    22 const QString DataStoreManager::db_name = "dsm.db";

    23 DataStoreManagerState DataStoreManager::state = CLOSED;

    24 

    25 //! \fn getDataStoreManager()

    26 /*!

    27     \brief  To get a handle on the datastore instantiation.

    28     \brief  This function returns a pointer to the existing DataStoreManager instantiation(object).

    29             If no object exists, a new one is created.

    30 

    31     \return Pointer to the current instantiation of DataStoreManager.

    32 */

    33 DataStoreManager* DataStoreManager::getDataStoreManager(){

    34     if(m_dsm_instance == NULL){

    35         m_dsm_instance = new DataStoreManager(DataStoreManager::db_name);

    36         if(!(m_dsm_instance->InitializeDataBase())){

    37             return NULL;

    38         }

    39 

    40     }

    41 

    42     return  m_dsm_instance;

    43 }

    44 

    45 //! \fn ~DataStoreManager()

    46 /*!

    47     \brief  Destructor for DataStoreManager class.

    48 

    49     \note   Should this be public?

    50 */

    51 DataStoreManager::~DataStoreManager(){

    52     db.close();

    53     state = CLOSED;

    54     delete m_dsm_instance;

    55 }

    56 

    57 //! \fn getState()

    58 /*!

    59     \brief  Get the current state of the DataStoreManager object.

    60     \return The state of the DSM object.

    61 */

    62 DataStoreManagerState DataStoreManager::getState() const{

    63     return state;

    64 }

    65 

    66 //! \fn getError()

    67 /*!

    68     \brief  Get the last error message from the DataStoreManager object.

    69     \return The last error message string of the DSM object.

    70 */

    71 QString DataStoreManager::getError() const{

    72     return m_last_msg;

    73 }

    74 

    75 /* Refactor this. Think Signals. */

    76 QList <SMFSocialProfile> DataStoreManager::getAllRelated(const SMFUserProfile& user_profile) {

    77     QList <SMFSocialProfile> related_profiles;

    78     int usr_id = user_profile.userID();

    79 

    80     if (!(db.isOpen())){

    81         if(!(db.open())){

    82             state = ERROR;

    83             m_last_msg = db.lastError().text();

    84             return related_profiles;

    85             /* Do something to signal an error. Just returning an empty list is NOT ok */

    86         }

    87     }

    88 

    89     QSqlQuery qry;

    90     qry.prepare("SELECT social_profiles.social_profile_id , social_profiles.user_id , social_profiles.sns_id , "

    91                 " social_profiles.profile_url , social_profiles.screen_alias"

    92                 " FROM social_profiles JOIN user_profiles WHERE user_profiles.user_id = social_profiles.user_id"

    93                 " AND social_profiles.user_id = :user_id");

    94     qry.bindValue(":user_id", usr_id);

    95 

    96     if(!(qry.exec())){

    97         m_last_msg = qry.lastError().text();

    98         qry.finish();

    99         /* Do something to signal an error. Just returning an empty list is NOT ok */

   100         return related_profiles;

   101     }

   102 

   103     while(qry.next())

   104     {

   105         SMFSocialProfile _profile(qry.value(2).toInt()/* sns ID*/,

   106                                   qry.value(1).toInt() /* user ID */,

   107                                   qry.value(3).toString()/* url */,

   108                                   qry.value(4).toString()/* alias */);

   109         related_profiles << _profile;

   110 

   111     }

   112 /*

   113     for( int i = 0; i < related_profiles.size(); i++){

   114         qDebug() << related_profiles.at(i).associatedSnsID() << ", " << related_profiles.at(i).associatedUserID()

   115                 << related_profiles.at(i).profileURL() << related_profiles.at(i).screenAlias() ;

   116     }

   117 */

   118     qry.finish();

   119     db.close();

   120 

   121     return related_profiles;

   122 

   123 }

   124 

   125 

   126 SMFSocialProfile DataStoreManager::getRelatedByService(const SMFUserProfile& user_profile, const SMFSocialNetworkingSite& sns){

   127 

   128     SMFSocialProfile _profile;

   129     int usr_id = user_profile.userID();

   130     int sns_id = sns.snsID();

   131 

   132     if (!(db.isOpen())){

   133         if(!(db.open())){

   134             state = ERROR;

   135             m_last_msg = db.lastError().text();

   136             return _profile;

   137             /* Do something to signal an error. Just returning an empty list is NOT ok */

   138         }

   139     }

   140 

   141     QSqlQuery qry;

   142     qry.prepare("SELECT social_profiles.social_profile_id , social_profiles.user_id , social_profiles.sns_id , "

   143                 " social_profiles.profile_url , social_profiles.screen_alias"

   144                 " FROM social_profiles JOIN user_profiles WHERE user_profiles.user_id = social_profiles.user_id"

   145                 " AND social_profiles.user_id = :user_id AND social_profiles.sns_id = :sns_id");

   146     qry.bindValue(":user_id", usr_id);

   147     qry.bindValue(":sns_id", sns_id);

   148 

   149     if(!(qry.exec())){

   150         m_last_msg = qry.lastError().text();

   151         qry.finish();

   152         /* Do something to signal an error. Just returning an empty list is NOT ok */

   153         return _profile;

   154     }

   155 

   156     if(qry.next()) {

   157         _profile.setAssociatedSnsID(qry.value(2).toInt());

   158         _profile.setAssociatedUserID(qry.value(1).toInt());

   159         _profile.setProfileURL(qry.value(3).toString());

   160         _profile.setScreenAlias(qry.value(4).toString());

   161     }

   162 /*

   163     qDebug() << _profile.associatedSnsID() << ", " << _profile.associatedUserID() << ", "

   164             << _profile.profileURL() << ", "<< _profile.screenAlias() ;

   165 */

   166     qry.finish();

   167     db.close();

   168 

   169     return _profile;

   170 }

   171 

   172 /* Cannot return something like this. Make this async */

   173 SMFUserProfile DataStoreManager::getUserProfile(const QString& name, const QString& contact_id){    

   174     SMFUserProfile _profile;

   175 

   176     if (!(db.isOpen())){

   177         if(!(db.open())){

   178             state = ERROR;

   179             m_last_msg = db.lastError().text();

   180             return _profile;

   181             /* Do something to signal an error. Just returning an empty list is NOT ok */

   182         }

   183     }

   184 

   185     QSqlQuery qry;

   186     qry.prepare("SELECT user_id , name , contact_id FROM user_profiles WHERE name=:name  AND contact_id=:contact_id");

   187     qry.bindValue(":name", name);

   188     qry.bindValue(":contact_id", contact_id);

   189 

   190     if(!(qry.exec())){

   191         m_last_msg = qry.lastError().text();

   192         qry.finish();

   193         /* Do something to signal an error. Just returning an empty list is NOT ok */

   194         return _profile;

   195     }

   196 

   197     if(qry.next()){

   198         _profile.setName(qry.value(1).toString());

   199         _profile.setContactID(qry.value(2).toString());

   200         _profile.setUserID(qry.value(0).toInt());

   201     }

   202 

   203     qry.finish();

   204     db.close();

   205 

   206     return _profile;

   207 }

   208 

   209 /*! Fetches the Social Networking site identified by \a name */

   210 SMFSocialNetworkingSite DataStoreManager::getSNSEntry(const QString& name){

   211     SMFSocialNetworkingSite sns;

   212 

   213     if (!(db.isOpen())){

   214         if(!(db.open())){

   215             state = ERROR;

   216             m_last_msg = db.lastError().text();

   217             return sns;

   218             /* Do something to signal an error. Just returning an empty list is NOT ok */

   219         }

   220     }

   221 

   222     QSqlQuery qry;

   223     qry.prepare("SELECT sns_id, sns_name, sns_url FROM sns_base WHERE sns_name=:name");

   224     qry.bindValue(":name", name);

   225 

   226     if(!qry.exec()){

   227         m_last_msg = qry.lastError().text();

   228         qry.finish();

   229         return sns;

   230     }

   231 

   232     if(qry.next()){

   233         sns.setSnsID(qry.value(0).toInt());

   234         sns.setSnsName(qry.value(1).toString());

   235         sns.setSnsURL(qry.value(2).toString());

   236     }

   237 

   238     qry.finish();

   239     db.close();

   240 

   241     return sns;

   242 }

   243 

   244 /* Actually all these 'save' functions can be written as a template */

   245 void DataStoreManager::saveUserProfile(const SMFUserProfile& user_profile){

   246 

   247     if (!(db.isOpen())){

   248         if(!(db.open())){

   249             state = ERROR;

   250             m_last_msg = db.lastError().text();

   251         }

   252     }

   253 

   254     QSqlQuery qry;

   255     qry.prepare("UPDATE user_profiles"

   256                 " SET name=:name , contact_id=:contact_id"

   257                 " WHERE user_id=:user_id");

   258     qry.bindValue(":name", user_profile.name());

   259     qry.bindValue(":contact_id", user_profile.contactID());

   260     qry.bindValue(":user_id", user_profile.userID());

   261 

   262     if(!(qry.exec())){

   263         m_last_msg = qry.lastError().text();

   264         qry.finish();

   265     }

   266 

   267     qry.finish();

   268     db.close();

   269 }

   270 

   271 void DataStoreManager::saveSocialProfile(const SMFSocialProfile& social_profile){

   272 

   273     if (!(db.isOpen())){

   274         if(!(db.open())){

   275             state = ERROR;

   276             m_last_msg = db.lastError().text();

   277         }

   278     }

   279 

   280     QSqlQuery qry;

   281     qry.prepare("UPDATE social_profiles"

   282                 " SET user_id=:user_id, sns_id=:sns_id, profile_url=:profile_url, screen_alias=:screen_alias"

   283                 " WHERE social_profile_id=:social_profile_id");

   284     qry.bindValue(":user_id", social_profile.associatedUserID());

   285     qry.bindValue(":sns_id", social_profile.associatedSnsID());

   286     qry.bindValue(":profile_url", social_profile.profileURL());

   287     qry.bindValue(":screen_alias", social_profile.screenAlias());

   288     qry.bindValue(":social_profile_id", social_profile.profileID());

   289 

   290     if(!(qry.exec())){

   291         m_last_msg = qry.lastError().text();

   292         qry.finish();

   293     }

   294 

   295     qry.finish();

   296     db.close();

   297 }

   298 

   299 void DataStoreManager::saveSNSEntry(const SMFSocialNetworkingSite& sns){

   300 

   301     if (!(db.isOpen())){

   302         if(!(db.open())){

   303             state = ERROR;

   304             m_last_msg = db.lastError().text();

   305         }

   306     }

   307 

   308     QSqlQuery qry;

   309     qry.prepare("UPDATE sns_base"

   310                 " SET sns_name=:sns_name, sns_url=:sns_url"

   311                 " WHERE sns_id=:sns_id");

   312     qry.bindValue(":sns_name", sns.snsName());

   313     qry.bindValue(":sns_url", sns.snsURL());

   314     qry.bindValue(":sns_id", sns.snsID());

   315 

   316     if(!(qry.exec())){

   317         m_last_msg = qry.lastError().text();

   318         qry.finish();

   319     }

   320 

   321     qry.finish();

   322     db.close();

   323 

   324 }

   325 

   326 void DataStoreManager::modifyRelation(SMFSocialProfile& sns, SMFUserProfile& new_user_profile){

   327     sns.setAssociatedUserID(new_user_profile.userID());

   328 }

   329 

   330 

   331 int DataStoreManager::addUserProfile(SMFUserProfile& user_profile){

   332     int user_id = user_profile.userID();

   333 

   334     /* Check Required to identify multiple Entries */

   335 

   336     if (!(db.isOpen())){

   337         if(!(db.open())){

   338             state = ERROR;

   339             m_last_msg = db.lastError().text();

   340             return user_id;

   341         }

   342     }

   343 

   344     QSqlQuery qry;

   345     qry.prepare("INSERT INTO user_profiles (name, contact_id) VALUES (:name, :contact_id)");

   346     qry.bindValue(":name", user_profile.name());

   347     qry.bindValue(":contact_id", user_profile.contactID());

   348 

   349     if(!(qry.exec())){

   350         m_last_msg = qry.lastError().text();

   351         qry.finish();

   352         return user_id;

   353     }

   354 

   355     qry.prepare("SELECT user_id FROM user_profiles WHERE name=:name AND contact_id=:contact_id");

   356     qry.bindValue(":name", user_profile.name());

   357     qry.bindValue(":contact_id", user_profile.contactID());

   358 

   359     if(!(qry.exec())){

   360         m_last_msg = qry.lastError().text();

   361         qry.finish();

   362         return user_id;

   363     }

   364 

   365     if(qry.next()){

   366         user_id = qry.value(0).toInt();

   367         user_profile.setUserID(user_id);

   368     }

   369 

   370     qry.finish();

   371     db.close();

   372 

   373     return user_id;

   374 

   375 

   376 }

   377 

   378 int DataStoreManager::deleteUserProfile( SMFUserProfile& user_profile){

   379 

   380     if (!(db.isOpen())){

   381         if(!(db.open())){

   382             state = ERROR;

   383             m_last_msg = db.lastError().text();

   384             return -1;

   385         }

   386     }

   387 

   388     QSqlQuery qry;

   389     qry.prepare("DELETE FROM user_profiles WHERE user_id=:user_id");

   390     qry.bindValue(":user_id", user_profile.userID());

   391 

   392     if(!(qry.exec())){

   393         m_last_msg = qry.lastError().text();

   394         qry.finish();

   395         return -1;

   396     }

   397 

   398     qry.finish();

   399     db.close();

   400 

   401     return 0;

   402 }

   403 

   404 int DataStoreManager::addSocialProfile(SMFSocialProfile& social_profile){

   405     int social_prof_id = social_profile.profileID();

   406 

   407     /* Check Required to identify multiple entries */

   408 

   409     if (!(db.isOpen())){

   410         if(!(db.open())){

   411             state = ERROR;

   412             m_last_msg = db.lastError().text();

   413             return social_prof_id;

   414         }

   415     }

   416 

   417     /*  There might be a request to add a social profile with either user and/or sns profile(s)

   418         not stored in the database. What to do in that case? Automatically store the other profiles too?

   419     */

   420     QSqlQuery qry;

   421     qry.prepare("INSERT INTO social_profiles (user_id, sns_id, profile_url, screen_alias) "

   422                 "VALUES (:user_id, :sns_id, :profile_url, :screen_alias)");

   423     qry.bindValue(":user_id", social_profile.associatedUserID());

   424     qry.bindValue(":sns_id", social_profile.associatedSnsID());

   425     qry.bindValue(":profile_url", social_profile.profileURL());

   426     qry.bindValue(":screen_alias", social_profile.screenAlias());

   427 

   428     if(!(qry.exec())){

   429         m_last_msg = qry.lastError().text();

   430         qry.finish();

   431         return social_prof_id;

   432     }

   433 

   434     qry.prepare("SELECT social_profile_id FROM social_profiles  "

   435                 "WHERE user_id=:user_id AND sns_id=:sns_id AND profile_url=:profile_url AND screen_alias=:screen_alias");

   436     qry.bindValue(":user_id", social_profile.associatedUserID());

   437     qry.bindValue(":sns_id", social_profile.associatedSnsID());

   438     qry.bindValue(":profile_url", social_profile.profileURL());

   439     qry.bindValue(":screen_alias", social_profile.screenAlias());

   440 

   441     if(!(qry.exec())){

   442         m_last_msg = qry.lastError().text();

   443         qry.finish();

   444         return social_prof_id;

   445     }

   446 

   447     if(qry.next()){

   448         social_prof_id = qry.value(0).toInt();

   449         social_profile.setProfileID(social_prof_id);

   450     }

   451 

   452     qry.finish();

   453     db.close();

   454 

   455     return social_prof_id;

   456 }

   457 

   458 int DataStoreManager::deleteSocialProfile(SMFSocialProfile& social_profile){

   459 

   460     if (!(db.isOpen())){

   461         if(!(db.open())){

   462             state = ERROR;

   463             m_last_msg = db.lastError().text();

   464             return -1;

   465         }

   466     }

   467 

   468     QSqlQuery qry;

   469     qry.prepare("DELETE FROM social_profiles WHERE social_profile_id=:social_profile_id");

   470     qry.bindValue(":social_profile_id", social_profile.profileID());

   471 

   472     if(!(qry.exec())){

   473         m_last_msg = qry.lastError().text();

   474         qry.finish();

   475         return -1;

   476     }

   477 

   478     qry.finish();

   479     db.close();

   480 

   481     return 0;

   482 }

   483 

   484 int DataStoreManager::addSNSEntry( SMFSocialNetworkingSite& sns){

   485     int sns_id = sns.snsID();

   486 

   487     if (!(db.isOpen())){

   488         if(!(db.open())){

   489             state = ERROR;

   490             m_last_msg = db.lastError().text();

   491             return sns_id;

   492         }

   493     }

   494 

   495     QSqlQuery qry;

   496     qry.prepare("INSERT INTO sns_base (sns_name, sns_url) VALUES (:sns_name, :sns_url)");

   497     qry.bindValue(":sns_name", sns.snsName());

   498     qry.bindValue(":sns_url", sns.snsURL());

   499 

   500     if(!(qry.exec())){

   501         m_last_msg = qry.lastError().text();

   502         qry.finish();

   503         return sns_id;

   504     }

   505 

   506     qry.prepare("SELECT sns_id FROM sns_base WHERE sns_name=:sns_name AND sns_url=:sns_url");

   507     qry.bindValue(":sns_name", sns.snsName());

   508     qry.bindValue(":sns_url", sns.snsURL());

   509 

   510     if(!(qry.exec())){

   511         m_last_msg = qry.lastError().text();

   512         qry.finish();

   513         return sns_id;

   514     }

   515 

   516     if(qry.next()){

   517         sns_id = qry.value(0).toInt();

   518         sns.setSnsID(sns_id);

   519     }

   520 

   521     qry.finish();

   522     db.close();

   523 

   524     return sns_id;

   525 }

   526 

   527 int DataStoreManager::deleteSNSEntry(SMFSocialNetworkingSite& sns){

   528 

   529     if (!(db.isOpen())){

   530         if(!(db.open())){

   531             state = ERROR;

   532             m_last_msg = db.lastError().text();

   533             return -1;

   534         }

   535     }

   536 

   537     QSqlQuery qry;

   538     qry.prepare("DELETE FROM sns_base WHERE sns_id=:sns_id");

   539     qry.bindValue(":sns_id", sns.snsID());

   540 

   541     if(!(qry.exec())){

   542         m_last_msg = qry.lastError().text();

   543         qry.finish();

   544         return -1;

   545     }

   546 

   547     qry.finish();

   548     db.close();

   549 

   550     return 0;

   551 }

   552 

   553 int DataStoreManager::createRelation(const SMFUserProfile& user_profile, SMFSocialProfile& social_profile){

   554     social_profile.setAssociatedUserID(user_profile.userID());

   555     /* Save the socialProfile */

   556     //saveSocialProfile(social_profile);

   557     return 0;

   558 }

   559 

   560 int DataStoreManager::deleteRelation(const SMFUserProfile&,  SMFSocialProfile& social_profile){

   561     social_profile.setAssociatedUserID(-1);

   562     /* Save the profile */

   563     //saveSocialProfile(social_profile);

   564     return 0;

   565 }

   566 

   567 

   568 DataStoreManager::DataStoreManager(const QString& db_name, QObject* parent):QObject(parent){

   569     db = QSqlDatabase::addDatabase("QSQLITE");

   570     db.setDatabaseName(db_name);

   571 }

   572 

   573 bool DataStoreManager::InitializeDataBase(){

   574 

   575     if(!db.open()){

   576         state = ERROR;

   577         m_last_msg = db.lastError().text();

   578         return FALSE;

   579     }

   580     else

   581         state = READY;

   582 

   583     QSqlQuery create_tables[3];

   584 

   585     create_tables[0].prepare("CREATE TABLE IF NOT EXISTS user_profiles (user_id INTEGER PRIMARY KEY AUTOINCREMENT , name TEXT, contact_id TEXT)");

   586     create_tables[1].prepare("CREATE TABLE IF NOT EXISTS sns_base (sns_id INTEGER PRIMARY KEY AUTOINCREMENT , sns_name TEXT, sns_url TEXT)");

   587     create_tables[2].prepare("CREATE TABLE IF NOT EXISTS social_profiles (social_profile_id INTEGER PRIMARY KEY AUTOINCREMENT , user_id INTEGER, sns_id INTEGER, profile_url TEXT, screen_alias TEXT)");

   588 

   589 

   590 

   591     for(int i = 0; i < 3; i++){

   592         if(!create_tables[i].exec()){

   593             state = ERROR;

   594             m_last_msg = create_tables[i].lastError().text();

   595             return FALSE;

   596         }

   597         create_tables[i].finish();

   598     }

   599 

   600     db.close();

   601     state = CLOSED;

   602     return TRUE;

   603 }