1 /*

     2  * Copyright (c) 2007 Nokia Corporation and/or its subsidiary(-ies).

     3  * All rights reserved.

     4  * This component and the accompanying materials are made available

     5  * under the terms of "Eclipse Public License v1.0"

     6  * which accompanies this distribution, and is available

     7  * at the URL "http://www.eclipse.org/legal/epl-v10.html".

     8  *

     9  * Initial Contributors:

    10  * Nokia Corporation - initial contribution.

    11  *

    12  * Contributors:

    13  *

    14  * Description:

    15  *

    16  */

    17 

    18 #include <QList>

    19 #include <QDebug>

    20 #include <QString>

    21 #include <QtSql>

    22 #include <QMap>

    23 #include <QMapIterator>

    24 

    25 #include "caclientproxy.h"

    26 #include "caobjectadapter.h"

    27 

    28 #include "caentry.h"

    29 #include "caquery.h"

    30 #include "cadefs.h"

    31 #include "canotifier.h"

    32 #include "canotifier_p.h"

    33 #include "canotifiers.h"

    34 

    35 #include "caclient_defines.h"

    36 #include "hswidgetregistryservice.h"

    37 

    38 const char *DATABASE_CONNECTION_NAME = "CaService";

    39 const char *DATABASE_TYPE = "QSQLITE";

    40 const char *DATABASE_NAME = "castorage.db";

    41 

    42 static QSqlDatabase dbConnection()

    43 {

    44     return QSqlDatabase::database(DATABASE_CONNECTION_NAME, false);

    45 }

    46 

    47 //----------------------------------------------------------------------------

    48 //

    49 //----------------------------------------------------------------------------

    50 CaClientProxy::CaClientProxy() :

    51     mWidgetRegistryPath("private/20022F35/import/widgetregistry")

    52 {

    53 }

    54 

    55 //----------------------------------------------------------------------------

    56 //

    57 //----------------------------------------------------------------------------

    58 CaClientProxy::~CaClientProxy()

    59 {

    60     QSqlDatabase db = dbConnection();

    61     if (db.isOpen()) {

    62         db.close();

    63     }

    64     QSqlDatabase::removeDatabase(DATABASE_CONNECTION_NAME);

    65 }

    66 

    67 //----------------------------------------------------------------------------

    68 //

    69 //----------------------------------------------------------------------------

    70 ErrorCode CaClientProxy::connect()

    71 {

    72     ErrorCode errorCode = NotFoundErrorCode;

    73     QSqlDatabase db = QSqlDatabase::addDatabase(DATABASE_TYPE,

    74                       DATABASE_CONNECTION_NAME);

    75     if (db.isValid()) {

    76         db.setDatabaseName(DATABASE_NAME);

    77         if (db.open()) {

    78             errorCode = NoErrorCode;

    79             updateWidgets();

    80         }

    81     }

    82     if (errorCode) {

    83         qDebug("CaClientProxy::CaClientProxy FAILED");

    84     }

    85     return errorCode;

    86 }

    87 

    88 /*!

    89  Updates widgets.

    90  */

    91 void CaClientProxy::updateWidgets()

    92 {

    93     qDebug("CaClientProxy::updateWidgets start");

    94 

    95     HsWidgetRegistryService *rs =

    96         new HsWidgetRegistryService(mWidgetRegistryPath);

    97     QList<HsWidgetComponentDescriptor> widgets = rs->widgets();

    98 

    99     // Read widgets in order to add synchronize the content of the widgets

   100     // registry with Content Storage database.

   101     foreach(const HsWidgetComponentDescriptor &widgetToken, widgets) {

   102         int uid = widgetToken.uid;

   103         addWidgetEntry(widgetToken, hsWidgetId(uid));        

   104     }

   105 

   106     delete rs;

   107     qDebug("CaClientProxy::updateWidgets end");

   108 }

   109 

   110 /*!

   111  Returns true if a widget with the given uid exists in the database.

   112  */

   113 void CaClientProxy::addWidgetEntry(const HsWidgetComponentDescriptor &widgetToken, 

   114                                    int widgetId)

   115 {

   116     QString description = widgetToken.description;

   117     QString iconUri = widgetToken.iconUri;

   118     QString library = widgetToken.library;

   119     QString title = widgetToken.title;

   120     int uid = widgetToken.uid;

   121     QString uri = widgetToken.uri;

   122     QString previewImage = widgetToken.previewImage;

   123     QString translationFileName = widgetToken.translationFilename;

   124     QSqlDatabase db = dbConnection();

   125     QSqlQuery query(db);

   126     QString hexUid;

   127     hexUid.setNum(uid,16);

   128     QDir currentDir = QDir::current();

   129     if (widgetId == -1) {

   130         // Add icon.

   131         QString queryAddIcon =

   132             "INSERT INTO CA_ICON " \

   133             "(IC_FILENAME) " \

   134             "VALUES " \

   135             "(?)";

   136 

   137         query.prepare(queryAddIcon);

   138         query.addBindValue(iconUri);

   139         query.exec();

   140         qDebug() << query.executedQuery();

   141 

   142         // Add entry.

   143         QString queryAddEntry =

   144             "INSERT INTO CA_ENTRY " \

   145             "(EN_TEXT, EN_DESCRIPTION, EN_ROLE, EN_TYPE_NAME, EN_ICON_ID) " \

   146             "VALUES " \

   147             "(?, ?, 1, 'widget', last_insert_rowid())";

   148 

   149         query.prepare(queryAddEntry);

   150         query.addBindValue(title);

   151         query.addBindValue(description);

   152         query.exec();

   153         qDebug() << query.executedQuery();

   154 

   155         // Get last id

   156         QString queryLastId = "SELECT last_insert_rowid() AS LAST_ID";

   157         query.prepare(queryLastId);

   158         query.exec();

   159         query.next();

   160         widgetId = query.value(query.record().indexOf("LAST_ID")).toInt();

   161     } else {

   162         // update entry.

   163         QString queryUpdateEntry =

   164             "UPDATE CA_ENTRY SET EN_TEXT = ?, EN_DESCRIPTION = ? WHERE ENTRY_ID = ?";

   165             

   166         query.prepare(queryUpdateEntry);

   167         query.addBindValue(title);

   168         query.addBindValue(description);

   169         query.addBindValue(widgetId);

   170         query.exec();

   171         qDebug() << query.executedQuery();

   172 

   173         QString queryUpdateIcon =

   174             "UPDATE CA_ICON SET IC_FILENAME = ? WHERE" \

   175             " ICON_ID = (SELECT EN_ICON_ID FROM CA_ENTRY WHERE ENTRY_ID = ?)";

   176             

   177         query.prepare(queryUpdateIcon);

   178         query.addBindValue(iconUri);

   179         query.addBindValue(widgetId);

   180         query.exec();

   181         qDebug() << query.executedQuery();

   182 

   183         // delete old attribute         

   184         query.prepare("DELETE FROM CA_ATTRIBUTE WHERE AT_ENTRY_ID = ?");

   185         query.addBindValue(widgetId);

   186         query.exec();

   187         qDebug() << query.executedQuery();

   188     }

   189 

   190     // Add attribute packageuid

   191     QString queryAddAttribute1 =

   192         "INSERT INTO CA_ATTRIBUTE " \

   193         "(AT_ENTRY_ID, AT_NAME, AT_VALUE) " \

   194         "VALUES " \

   195         "(?, 'packageuid', ?)";

   196 

   197     query.prepare(queryAddAttribute1);

   198     query.addBindValue(widgetId);

   199     query.addBindValue(hexUid);

   200     query.exec();

   201     qDebug() << query.executedQuery();

   202 

   203     // Add attribute widget uri

   204     QString queryAddAttribute2 =

   205         "INSERT INTO CA_ATTRIBUTE " \

   206         "(AT_ENTRY_ID, AT_NAME, AT_VALUE) " \

   207         "VALUES " \

   208         "(?, 'widget:uri', ?)";

   209 

   210     query.prepare(queryAddAttribute2);

   211     query.addBindValue(widgetId);

   212     query.addBindValue(uri);

   213     query.exec();

   214     qDebug() << query.executedQuery();

   215 

   216     // Add attribute widget library

   217     QString queryAddAttribute3 =

   218         "INSERT INTO CA_ATTRIBUTE " \

   219         "(AT_ENTRY_ID, AT_NAME, AT_VALUE) " \

   220         "VALUES " \

   221         "(?, 'widget:library', ?)";

   222 

   223     query.prepare(queryAddAttribute3);

   224     query.addBindValue(widgetId);

   225     query.addBindValue(library);

   226     query.exec();

   227     qDebug() << query.executedQuery();

   228     

   229     // Add attribute preview image

   230     if (!previewImage.isEmpty()) {

   231         QString queryAddPreviewImage =

   232             "INSERT INTO CA_ATTRIBUTE " \

   233             "(AT_ENTRY_ID, AT_NAME, AT_VALUE) " \

   234             "VALUES " \

   235             "(?, 'preview_image_name', ?)";

   236 

   237         query.prepare(queryAddPreviewImage);

   238         query.addBindValue(widgetId);

   239         query.addBindValue(previewImage);

   240         query.exec();

   241         qDebug() << query.executedQuery();

   242     }

   243 

   244     // Add attribute widget:traslation_file

   245     if (!translationFileName.isEmpty()) {

   246         QString queryAddWidgetTranslationFile =

   247             "INSERT INTO CA_ATTRIBUTE " \

   248             "(AT_ENTRY_ID, AT_NAME, AT_VALUE) " \

   249             "VALUES " \

   250             "(?, 'widget:traslation_file', ?)";

   251 

   252         query.prepare(queryAddWidgetTranslationFile);

   253         query.addBindValue(widgetId);

   254         query.addBindValue(translationFileName);

   255         query.exec();

   256         qDebug() << query.executedQuery();

   257     }

   258 }

   259 

   260 /*!

   261  Returns true if a widget with the given uid exists in the database.

   262  */

   263 int CaClientProxy::hsWidgetId(int uid)

   264 {

   265     int result(-1);

   266     QSqlDatabase db = dbConnection();

   267     QSqlQuery query(db);

   268     QString hexUid;

   269     hexUid.setNum(uid,16);

   270 

   271     QString queryString =

   272         "SELECT " \

   273         "AT_ENTRY_ID " \

   274         "FROM " \

   275         "CA_ATTRIBUTE " \

   276         "WHERE " \

   277         "AT_VALUE LIKE ?";

   278 

   279     query.prepare(queryString);

   280     query.addBindValue(hexUid);

   281 

   282     if (query.exec() && query.next()) {        

   283         result = query.value(query.record().indexOf("AT_ENTRY_ID")).toInt();

   284     } 

   285     qDebug() << query.executedQuery();

   286 

   287     return result;

   288 }

   289 

   290 //----------------------------------------------------------------------------

   291 //

   292 //----------------------------------------------------------------------------

   293 ErrorCode CaClientProxy::addData(const CaEntry &entryToAdd,

   294                                  CaEntry &targetEntry)

   295 {

   296     qDebug() << "CaClientProxy::addData" << "entry id: "

   297              << entryToAdd.id();

   298 

   299     targetEntry = entryToAdd;

   300     QSqlDatabase db = dbConnection();

   301     QSqlQuery query(db);

   302     if (entryToAdd.id() == 0) {

   303         CaObjectAdapter::setId(targetEntry, 0);

   304     }

   305     query.exec("begin");

   306     bool success = (setIconInDb(&targetEntry)

   307                     && setEntryInDb(&targetEntry)

   308                     && setAttributesInDb(&targetEntry));

   309     if (success) {

   310         query.exec("commit");

   311         QList<int> parentIds;

   312         GetParentsIds(QList<int>() << targetEntry.id(), parentIds);

   313         if (entryToAdd.id() == 0) {

   314             CaNotifiers::Notify(targetEntry, AddChangeType, parentIds);

   315         } else {

   316             CaNotifiers::Notify(targetEntry, UpdateChangeType, parentIds);

   317         }

   318         return NoErrorCode;

   319     } else {

   320         query.exec("rollback");

   321         return UnknownErrorCode;

   322     }

   323 }

   324 

   325 //----------------------------------------------------------------------------

   326 //

   327 //----------------------------------------------------------------------------

   328 ErrorCode CaClientProxy::removeData(const QList<int> &entryIdList)

   329 {

   330     qDebug() << "CaClientProxy::removeData" << "entryIdList: "

   331              << entryIdList;

   332 

   333     QList< QSharedPointer<CaEntry> > entryList;

   334     getData(entryIdList, entryList);

   335     QList<QList<int> > parentsIds;

   336     foreach(QSharedPointer<CaEntry> entry, entryList) {

   337         QList<int> parentIds;

   338         GetParentsIds(QList<int>() << entry->id(), parentIds);

   339         parentsIds.append(parentIds);

   340     }

   341 

   342     QSqlDatabase db = dbConnection();

   343     QSqlQuery query(db);

   344     //begin transaction

   345     bool success(false);

   346     query.exec("begin");

   347     foreach(int entryId, entryIdList) {

   348         query.prepare(

   349             "SELECT ENTRY_ID FROM CA_ENTRY WHERE ENTRY_ID = ?");

   350         query.addBindValue(entryId);

   351         success = query.exec();

   352         if (success && query.next()) {

   353             success

   354             = query.value(query.record().indexOf("ENTRY_ID")).toInt()

   355               > 0;

   356         }

   357         if (!success) {

   358             break;

   359         }

   360 

   361         query.prepare(

   362             "SELECT EN_ICON_ID FROM CA_ENTRY WHERE ENTRY_ID = ?");

   363         query.addBindValue(entryId);

   364         int iconId(0);

   365         success = query.exec();

   366         if (success && query.next()) {

   367             iconId

   368             = query.value(query.record().indexOf("EN_ICON_ID")).toInt();

   369         } else {

   370             break;

   371         }

   372 

   373         query.prepare("DELETE FROM CA_LAUNCH WHERE LA_ENTRY_ID = ?");

   374         query.addBindValue(entryId);

   375         success = query.exec();

   376         if (success) {

   377             qDebug() << query.lastQuery() << " rows deleted: "

   378                      << query.numRowsAffected();

   379         } else {

   380             break;

   381         }

   382 

   383         query.prepare(

   384             "DELETE FROM CA_GROUP_ENTRY WHERE GE_ENTRY_ID = ?");

   385         query.addBindValue(entryId);

   386         success = query.exec();

   387         if (success) {

   388             qDebug() << query.lastQuery() << " rows deleted: "

   389                      << query.numRowsAffected();

   390         } else {

   391             break;

   392         }

   393 

   394         query.prepare(

   395             "DELETE FROM CA_GROUP_ENTRY WHERE GE_GROUP_ID = ?");

   396         query.addBindValue(entryId);

   397         success = query.exec();

   398         if (success) {

   399             qDebug() << query.lastQuery() << " rows deleted: "

   400                      << query.numRowsAffected();

   401         } else {

   402             break;

   403         }

   404 

   405         query.prepare("DELETE FROM CA_ATTRIBUTE WHERE AT_ENTRY_ID = ?");

   406         query.addBindValue(entryId);

   407         success = query.exec();

   408         if (success) {

   409             qDebug() << query.lastQuery() << " rows deleted: "

   410                      << query.numRowsAffected();

   411         } else {

   412             break;

   413         }

   414 

   415         query.prepare("DELETE FROM CA_ENTRY WHERE ENTRY_ID = ?");

   416         query.addBindValue(entryId);

   417         success = query.exec();

   418         if (success) {

   419             qDebug() << query.lastQuery() << " rows deleted: "

   420                      << query.numRowsAffected();

   421         } else {

   422             break;

   423         }

   424 

   425         if (iconId != 0) {

   426             query.prepare("DELETE FROM CA_ICON WHERE ICON_ID = ?");

   427             query.addBindValue(iconId);

   428             success = query.exec();

   429             if (success) {

   430                 qDebug() << query.lastQuery() << " rows deleted: "

   431                          << query.numRowsAffected();

   432             } else {

   433                 // ignore, this means that the icon cannot be removed

   434                 // because some other entry has the same icon.

   435                 success = true;

   436             }

   437         }

   438     }

   439 

   440     ErrorCode error(NoErrorCode);

   441     if (success) {

   442         query.exec("commit");

   443         if (parentsIds.count() == entryList.count()) {

   444             for (int i = 0; i < entryList.count(); i++) {

   445                 GetParentsIds(QList<int>() << entryList[i]->id(), parentsIds[i]);

   446                 CaNotifiers::Notify(*entryList[i], RemoveChangeType, parentsIds[i]);

   447             }

   448         }

   449     } else {

   450         query.exec("rollback");

   451         error = UnknownErrorCode;

   452     }

   453 

   454     return error;

   455 }

   456 

   457 //----------------------------------------------------------------------------

   458 //

   459 //----------------------------------------------------------------------------

   460 ErrorCode CaClientProxy::insertEntriesIntoGroup(int groupId,

   461         const QList<int> &entryIdList, int beforeEntryId)

   462 {

   463     qDebug() << "CaClientProxy::insertEntriesIntoGroup" << "groupId: "

   464              << groupId << "beforeEntryId: " << beforeEntryId << "entryIdList: "

   465              << entryIdList;

   466 

   467     removeEntriesFromGroup(groupId, entryIdList, false);

   468     QString queryText;

   469     if (beforeEntryId == AfterTheLastEntry) {

   470         queryText = QString("INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID,GE_ENTRY_ID,GE_POSITION) "

   471                             "VALUES ( ?, ?,(SELECT MAX(DATA) FROM ( SELECT MAX(GE_POSITION)+ 1 AS DATA FROM CA_GROUP_ENTRY "

   472                             "WHERE GE_GROUP_ID = %1 UNION SELECT 1 AS DATA FROM CA_GROUP_ENTRY ) ) )").arg(groupId);

   473     } else if (beforeEntryId == BeforeTheFirstEntry) {

   474         queryText = "INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID,GE_ENTRY_ID,GE_POSITION) VALUES ( ?, ?, 0 ) ";

   475     } else {

   476         queryText = QString("INSERT INTO CA_GROUP_ENTRY (GE_GROUP_ID,GE_ENTRY_ID,GE_POSITION) VALUES ( "

   477                             "?, ?, ( SELECT GE_POSITION FROM CA_GROUP_ENTRY WHERE GE_ENTRY_ID = %1 ) ) ").arg(beforeEntryId);

   478     }

   479 

   480     bool success = true;

   481     QSqlDatabase db = dbConnection();

   482     QSqlQuery query(db);

   483     query.exec("begin");

   484     for (int i = 0; i < entryIdList.count() && success; ++i) {

   485         query.prepare(queryText);

   486         query.addBindValue(groupId);

   487         query.addBindValue(entryIdList.at(i));

   488         success = query.exec();

   489     }

   490 

   491     ErrorCode error(NoErrorCode);

   492     if (success) {

   493         query.exec("commit");

   494         CaNotifiers::Notify(groupId);

   495     } else {

   496         query.exec("rollback");

   497         error = UnknownErrorCode;

   498     }

   499     return error;

   500 }

   501 

   502 //----------------------------------------------------------------------------

   503 //

   504 //----------------------------------------------------------------------------

   505 ErrorCode CaClientProxy::removeEntriesFromGroup(int groupId,

   506         const QList<int> &entryIdList,

   507         bool calledDirectly)

   508 {

   509     qDebug() << "CaClientProxy::removeEntriesFromGroup" << "groupId: "

   510              << groupId << "entryIdList: " << entryIdList;

   511 

   512     bool success = true;

   513     QSqlDatabase db = dbConnection();

   514     QSqlQuery query(db);

   515     query.exec("begin");

   516     for (int i = 0; i < entryIdList.count() && success; ++i) {

   517         query.prepare(

   518             "DELETE FROM CA_GROUP_ENTRY WHERE GE_ENTRY_ID = ? AND GE_GROUP_ID = ? ");

   519         query.addBindValue(entryIdList.at(i));

   520         query.addBindValue(groupId);

   521         success = query.exec();

   522     }

   523 

   524     ErrorCode error(NoErrorCode);

   525     if (success) {

   526         query.exec("commit");

   527         if (calledDirectly) {

   528             CaNotifiers::Notify(groupId);

   529         }

   530     } else {

   531         query.exec("rollback");

   532         error = UnknownErrorCode;

   533     }

   534     return error;

   535 }

   536 

   537 //----------------------------------------------------------------------------

   538 //

   539 //----------------------------------------------------------------------------

   540 ErrorCode CaClientProxy::getData(const QList<int> &entryIdList,

   541                                  QList< QSharedPointer<CaEntry> > &sourceList)

   542 {

   543     qDebug() << "CaClientProxy::getData" << "entryIdList: "

   544              << entryIdList;

   545 

   546     QSqlDatabase db = dbConnection();

   547 

   548     bool success(true);

   549     foreach(int i, entryIdList) {

   550         QSqlQuery query(db);

   551         query.prepare(

   552             "SELECT ENTRY_ID, EN_TEXT, EN_DESCRIPTION, EN_TYPE_NAME, EN_FLAGS, EN_ROLE, EN_UID,  \

   553                   ICON_ID, IC_FILENAME, IC_SKIN_ID, IC_APP_ID \

   554                   FROM CA_ENTRY LEFT JOIN CA_ICON ON EN_ICON_ID = ICON_ID WHERE ENTRY_ID = ?");

   555         query.addBindValue(i);

   556 

   557         success = query.exec();

   558         if (success && query.next()) {

   559             qDebug() << query.executedQuery();

   560             int role =

   561                 query.value(query.record().indexOf("EN_ROLE")).toInt();

   562             QSharedPointer<CaEntry> entry (new CaEntry((EntryRole) role));

   563             CaObjectAdapter::setId(*entry,

   564                                    query.value(query.record().indexOf("ENTRY_ID")).toInt());

   565             entry->setText(query.value(

   566                                query.record().indexOf("EN_TEXT")).toString());

   567             entry->setDescription(query.value(

   568                                       query.record().indexOf("EN_DESCRIPTION")).toString());

   569             entry->setEntryTypeName(query.value(query.record().indexOf(

   570                                                     "EN_TYPE_NAME")).toString());

   571             entry->setFlags(static_cast<EntryFlag>(query.value(

   572                     query.record().indexOf("EN_FLAGS")).toUInt()));

   573 

   574             CaIconDescription icon;

   575             CaObjectAdapter::setId(icon,

   576                                    query.value(query.record().indexOf("ICON_ID")).toInt());

   577 

   578             icon.setFilename(query.value(query.record().indexOf(

   579                                              "IC_FILENAME")).toString());

   580             icon.setSkinId(query.value(query.record().indexOf(

   581                                              "IC_SKIN_ID")).toString());

   582             icon.setApplicationId(query.value(query.record().indexOf(

   583                                              "IC_APP_ID")).toString());

   584             entry->setIconDescription(icon);

   585 

   586             // attributes

   587             // UID as attribute

   588             if (query.value(query.record().indexOf("EN_UID")).toString().length()

   589                     > 0) {

   590                 entry->setAttribute("application:uid", query.value(

   591                                         query.record().indexOf("EN_UID")).toString());

   592             }

   593 

   594             // fetch from DB attributes

   595             QSqlQuery attributesQuery(db);

   596             attributesQuery.prepare(

   597                 "SELECT AT_NAME, AT_VALUE FROM CA_ATTRIBUTE WHERE AT_ENTRY_ID  = ?");

   598             attributesQuery.addBindValue(i);

   599             success = attributesQuery.exec();

   600             if (success) {

   601                 while (attributesQuery.next()) {

   602                     entry->setAttribute(

   603                         attributesQuery.value(0).toString(),

   604                         attributesQuery.value(1).toString());

   605                 }

   606                 sourceList << entry;

   607             }

   608         } else {

   609             break;

   610         }

   611     }

   612 

   613     ErrorCode error = NoErrorCode;

   614     if (!success) {

   615         error = UnknownErrorCode;

   616     } else if ((entryIdList.count() != sourceList.count())) {

   617         error = NotFoundErrorCode;

   618     }

   619     return error;

   620 }

   621 

   622 //----------------------------------------------------------------------------

   623 //

   624 //----------------------------------------------------------------------------

   625 ErrorCode CaClientProxy::getData(const CaQuery &query,

   626                                  QList< QSharedPointer<CaEntry> > &sourceList)

   627 {

   628     QList<int> entryIdList;

   629     ErrorCode errorCode = getEntryIds(query, entryIdList);

   630     if (errorCode == NoErrorCode) {

   631         errorCode = getData(entryIdList, sourceList);

   632     }

   633     return errorCode;

   634 }

   635 

   636 //----------------------------------------------------------------------------

   637 //

   638 //----------------------------------------------------------------------------

   639 ErrorCode CaClientProxy::getEntryIds(const CaQuery &query,

   640                                      QList<int> &sourceIdList)

   641 {

   642     qDebug() << "CaClientProxy::getEntryIds";

   643 

   644     QSqlDatabase db = dbConnection();

   645 

   646     bool success(true);

   647     QString whereStatement;

   648     if (query.flagsOn() != 0)

   649         whereStatement.append(" AND ").append(QString().setNum(

   650                 query.flagsOn())).append(" & EN_FLAGS == ").append(

   651                     QString().setNum(query.flagsOn()));

   652     if (query.flagsOff() != 0)

   653         whereStatement.append(" AND ").append(QString().setNum(

   654                 query.flagsOff())).append(" & (~EN_FLAGS) == ").append(

   655                     QString().setNum(query.flagsOff()));

   656     if (query.entryRoles() != 0)

   657         whereStatement.append(" AND ").append(QString().setNum(

   658                 query.entryRoles())) .append(" | EN_ROLE == ").append(

   659                     QString().setNum(query.entryRoles()));

   660 

   661     if (query.entryTypeNames().count()) {

   662         whereStatement.append(" AND EN_TYPE_NAME IN (");

   663         for (int i = 0; i < query.entryTypeNames().count(); i++) {

   664             whereStatement.append("\'" + query.entryTypeNames()[i] + "\'");

   665             if (i < query.entryTypeNames().count() - 1)

   666                 whereStatement.append(",");

   667         }

   668         whereStatement.append(") ");

   669     }

   670 

   671     QString whereAttributes;

   672     if (query.attributes().count()) {

   673         QMap<QString, QString> attributes = query.attributes();

   674         QMapIterator<QString, QString> atrIt(attributes);

   675         int j = 1;

   676         while (atrIt.hasNext()) {

   677             atrIt.next();

   678 

   679             // at1.AT_NAME = 'Attribute_Name_1' AND at1.AT_VALUE = 'Attribute_VALUE_1'

   680             whereAttributes.append(" AND ").append(

   681             " at").append(QString().setNum(j)).append(".AT_NAME = \'").append(

   682             atrIt.key()).append("\' ").append(

   683             " AND ").append(

   684             " at").append(QString().setNum(j)).append(".AT_VALUE = \'").append(

   685             atrIt.value()).append("\' ");

   686 

   687             j++;

   688         }

   689         whereStatement.append(whereAttributes);

   690 

   691     }

   692 

   693     whereStatement.append(" GROUP BY ENTRY_ID ");

   694 

   695     QString leftJoins;

   696     if (query.attributes().count()) {

   697         for (int j=1; j<=query.attributes().count(); j++) {

   698             // LEFT JOIN CA_ATTRIBUTE as at1 ON ENTRY_ID = at1.AT_ENTRY_ID

   699             leftJoins.append(

   700             " LEFT JOIN CA_ATTRIBUTE as at").append(QString().setNum(j)).append(

   701             " ON ENTRY_ID = at").append(QString().setNum(j)).append(" .AT_ENTRY_ID ");

   702         }

   703     }

   704 

   705     QSqlQuery sqlquery(db);

   706     if (query.parentId() == 0) {

   707         // sort

   708         QString queryString("SELECT ENTRY_ID from CA_ENTRY ");

   709         queryString.append(leftJoins);

   710         queryString.append(" where 1=1 ");

   711         queryString.append(whereStatement);

   712 

   713         modifyQueryForSortOrder(queryString, query, false);

   714         if (query.count() > 0)

   715             queryString.append(" LIMIT ").append(query.count());

   716         qDebug() << "CaServicePrivate::getEntryIds query text: "

   717                  << queryString;

   718         success = sqlquery.prepare(queryString);

   719         success = sqlquery.exec();

   720         if (success) {

   721             while (sqlquery.next()) {

   722                 sourceIdList << sqlquery.value(sqlquery.record().indexOf(

   723                                                    "ENTRY_ID")).toInt();

   724             }

   725         }

   726     } else {

   727 

   728         QString queryString("SELECT ENTRY_ID FROM CA_ENTRY ");

   729         QString queryString2(" LEFT JOIN CA_GROUP_ENTRY ON GE_ENTRY_ID = ENTRY_ID WHERE GE_GROUP_ID  = ? ");

   730         queryString2.append(whereStatement);

   731         queryString.append(leftJoins);

   732         queryString.append(queryString2);

   733 

   734         modifyQueryForSortOrder(queryString, query, true);

   735         if (query.count() > 0)

   736             queryString.append(" LIMIT ").append(query.count());

   737         qDebug() << "CaServicePrivate::getEntryIds query text: "

   738                  << queryString;

   739         sqlquery.prepare(queryString);

   740         sqlquery.addBindValue(query.parentId());

   741         success = sqlquery.exec();

   742         if (success) {

   743             while (sqlquery.next()) {

   744                 sourceIdList << sqlquery.value(sqlquery.record().indexOf(

   745                                                    "ENTRY_ID")).toInt();

   746             }

   747         }

   748     }

   749     ErrorCode error = UnknownErrorCode;

   750     if (success) {

   751         error = NoErrorCode;

   752     }

   753     return error;

   754 }

   755 

   756 //----------------------------------------------------------------------------

   757 //

   758 //----------------------------------------------------------------------------

   759 ErrorCode CaClientProxy::touch(const CaEntry &entry)

   760 {

   761     int id = entry.id();

   762     if (id <= 0) {

   763         const int uid = entry.attribute(

   764             QString(APPLICATION_UID_ATTRIBUTE_NAME)).toInt();

   765         id = getEntryIdByUid(entry, uid);

   766     }

   767 

   768     qDebug() << "CaClientProxy::touch" << "id: " << id;

   769 

   770     QSqlDatabase db = dbConnection();

   771     QSqlQuery query(db);

   772     query.exec("begin");

   773 

   774     query.prepare(

   775         "INSERT INTO CA_LAUNCH (LA_ENTRY_ID,LA_LAUNCH_TIME) VALUES ( ?,? )");

   776     query.addBindValue(id);

   777     query.addBindValue(QDateTime::currentDateTime().toTime_t());

   778     bool success = query.exec();

   779 

   780     if (success) {

   781         query.prepare(

   782             "UPDATE CA_ENTRY SET EN_FLAGS = EN_FLAGS | ? WHERE ENTRY_ID = ?");

   783         query.addBindValue((int) UsedEntryFlag);

   784         query.addBindValue(id);

   785         success = query.exec();

   786     }

   787 

   788     ErrorCode error = NoErrorCode;

   789     if (success) {

   790         query.exec("commit");

   791         QList< QSharedPointer<CaEntry> > entryList;

   792         if (getData(QList<int>() << id, entryList) == NoErrorCode) {

   793             QList<int> parentIds;

   794             GetParentsIds(QList<int>() << id, parentIds);

   795             CaNotifiers::NotifyTouched(id);

   796         }

   797     } else {

   798         query.exec("rollback");

   799         error = UnknownErrorCode;

   800     }

   801 

   802     return error;

   803 }

   804 

   805 //----------------------------------------------------------------------------

   806 //

   807 //----------------------------------------------------------------------------

   808 ErrorCode CaClientProxy::customSort(const QList<int> &entryIdList,

   809                                     int groupId)

   810 {

   811     bool success = true;

   812     QSqlDatabase db = dbConnection();

   813     QSqlQuery query(db);

   814     query.exec("begin");

   815     for (int i = 0; i < entryIdList.count(); i++) {

   816         int position = i+1;

   817         query.prepare(

   818             "UPDATE CA_GROUP_ENTRY SET GE_POSITION = ? WHERE GE_ENTRY_ID = ? AND GE_GROUP_ID = ?");

   819         query.addBindValue(position);

   820         query.addBindValue(entryIdList.at(i));

   821         query.addBindValue(groupId);

   822         success = query.exec();

   823         if (!success) {

   824             break;

   825         }

   826     }

   827 

   828     ErrorCode error(NoErrorCode);

   829     if (success) {

   830         query.exec("commit");

   831         CaNotifiers::Notify(groupId);

   832     } else {

   833         query.exec("rollback");

   834         error = UnknownErrorCode;

   835     }

   836     return error;

   837 }

   838 

   839 //----------------------------------------------------------------------------

   840 //

   841 //----------------------------------------------------------------------------

   842 void CaClientProxy::modifyQueryForSortOrder(QString &queryString,

   843         const CaQuery &query, bool parent) const

   844 {

   845     SortAttribute sortAttribute;

   846     Qt::SortOrder sortOrder;

   847     query.getSort(sortAttribute, sortOrder);

   848     QString oldQueryString(queryString);

   849     queryString.clear();

   850 

   851     if (sortAttribute == NameSortAttribute) {

   852         queryString.append(oldQueryString).append(" ORDER BY EN_TEXT ");

   853     } else if (sortAttribute == CreatedTimestampSortAttribute) {

   854         queryString.append(oldQueryString).append(

   855             " ORDER BY EN_CREATION_TIME ");

   856     } else if (sortAttribute == MostUsedSortAttribute) {

   857         queryString.append("SELECT ENTRY_ID FROM (").append(oldQueryString).append(

   858             " \

   859                 ) LEFT JOIN \

   860                 (SELECT LA_ENTRY_ID, COUNT(*) AS USAGE_DATA FROM CA_LAUNCH GROUP BY LA_ENTRY_ID) \

   861                 ON ENTRY_ID = LA_ENTRY_ID ORDER BY USAGE_DATA ");

   862     } else if (sortAttribute == LastUsedSortAttribute) {

   863         queryString.append("SELECT ENTRY_ID FROM (").append(oldQueryString).append(

   864             " \

   865                 ) LEFT JOIN \

   866                 (SELECT LA_ENTRY_ID, MAX(LA_LAUNCH_TIME) AS USAGE_DATA FROM CA_LAUNCH GROUP BY LA_ENTRY_ID) \

   867                 ON ENTRY_ID = LA_ENTRY_ID ORDER BY USAGE_DATA ");

   868     } else if (parent && sortAttribute == DefaultSortAttribute) {

   869         queryString.append(oldQueryString).append(

   870             " ORDER BY GE_GROUP_ID, GE_POSITION ");

   871     } else if (!parent && sortAttribute == DefaultSortAttribute) {

   872         queryString.append(oldQueryString).append(" ORDER BY ENTRY_ID ");

   873     } else {

   874         queryString.append(oldQueryString);

   875     }

   876 

   877     if (sortAttribute == NameSortAttribute || sortAttribute

   878             == CreatedTimestampSortAttribute || sortAttribute

   879             == MostUsedSortAttribute || sortAttribute == LastUsedSortAttribute

   880             || (sortAttribute == DefaultSortAttribute && parent)) {

   881         if (sortOrder == Qt::AscendingOrder)

   882             queryString.append(" ASC ");

   883         else

   884             queryString.append(" DESC ");

   885     }

   886 

   887 }

   888 

   889 //----------------------------------------------------------------------------

   890 //

   891 //----------------------------------------------------------------------------

   892 bool CaClientProxy::setIconInDb(CaEntry *entryClone) const

   893 {

   894     //set icon information into db

   895     QSqlQuery query(dbConnection());

   896     query.prepare(

   897         "SELECT ICON_ID FROM CA_ICON WHERE IC_FILENAME = :IC_FILENAME"

   898         " AND IC_SKIN_ID = :IC_SKIN_ID"

   899         " AND IC_APP_ID = :IC_APP_ID");

   900     query.bindValue(":IC_FILENAME",

   901                     entryClone->iconDescription().filename());

   902     query.bindValue(":IC_SKIN_ID",

   903                     entryClone->iconDescription().filename());

   904     query.bindValue(":IC_APP_ID",

   905                     entryClone->iconDescription().filename());

   906 

   907 

   908     bool success = query.exec();

   909     if (success && query.next()) {

   910         qDebug() << "query.executedQuery() : " << query.executedQuery();

   911         int iconId = query.value(query.record().indexOf("ICON_ID")).toInt();

   912         qDebug() << "iconId = " << iconId;

   913         CaIconDescription iconDescription = entryClone->iconDescription();

   914         if (iconId <= 0

   915             && (!iconDescription.filename().isEmpty()

   916                 || !iconDescription.skinId().isEmpty()

   917                 || !iconDescription.applicationId().isEmpty())) {

   918             query.prepare(

   919                 "INSERT INTO CA_ICON"

   920                 " (IC_FILENAME, IC_SKIN_ID, IC_APP_ID)"

   921                 " VALUES ( ? , ? , ? , ? , ? )");

   922             query.addBindValue(iconDescription.filename());

   923             query.addBindValue(iconDescription.skinId());

   924             query.addBindValue(iconDescription.applicationId());

   925             success = query.exec();

   926             qDebug() << query.executedQuery();

   927             iconId = query.lastInsertId().toInt();

   928         }

   929         CaObjectAdapter::setId(iconDescription, iconId);

   930         entryClone->setIconDescription(iconDescription);

   931     }

   932     return success;

   933 }

   934 

   935 //----------------------------------------------------------------------------

   936 //

   937 //----------------------------------------------------------------------------

   938 bool CaClientProxy::setEntryInDb(CaEntry *entryClone) const

   939 {

   940     QSqlQuery query(dbConnection());

   941     bool isNewEntry(entryClone->id() <= 0);

   942     QString

   943     queryText(

   944         "INSERT INTO CA_ENTRY \

   945         (EN_TEXT,EN_ROLE,EN_TYPE_NAME,EN_FLAGS,EN_ICON_ID ) VALUES ( ?, ?, ?, ?, ");

   946     if (!isNewEntry)

   947         queryText

   948         = "REPLACE INTO CA_ENTRY \

   949         (ENTRY_ID,EN_TEXT,EN_ROLE,EN_TYPE_NAME,EN_FLAGS,EN_ICON_ID ) VALUES ( ?, ?, ?, ?, ?, ";

   950     if (entryClone->iconDescription().id() > 0) {

   951         queryText.append("?");

   952     } else {

   953         queryText.append("NULL");

   954     }

   955     queryText.append(")");

   956     query.prepare(queryText);

   957     if (!isNewEntry)

   958         query.addBindValue(entryClone->id());

   959     query.addBindValue(entryClone->text());

   960     query.addBindValue((int) entryClone->role());

   961     query.addBindValue(entryClone->entryTypeName());

   962     query.addBindValue((int) entryClone->flags());

   963     if (entryClone->iconDescription().id() > 0) {

   964         query.addBindValue(entryClone->iconDescription().id());

   965     }

   966 

   967     bool success = query.exec();

   968     if (success) {

   969         qDebug() << query.executedQuery();

   970         int newEntryId(0);

   971         //set entry creation time if new entry

   972         if (isNewEntry) {

   973             newEntryId = query.lastInsertId().toInt();

   974 

   975             CaObjectAdapter::setId(*entryClone, newEntryId);

   976             uint timestamp = QDateTime::currentDateTime().toTime_t();

   977             query.prepare(

   978                 "UPDATE CA_ENTRY SET EN_CREATION_TIME = ? WHERE ENTRY_ID = ?");

   979             query.addBindValue(timestamp);

   980             query.addBindValue(newEntryId);

   981             success = query.exec();

   982             qDebug() << "CaServicePrivate::setEntryInDb"

   983                      << query.executedQuery();

   984         }

   985     }

   986     return success;

   987 }

   988 

   989 //----------------------------------------------------------------------------

   990 //

   991 //----------------------------------------------------------------------------

   992 bool CaClientProxy::setAttributesInDb(CaEntry *entryClone) const

   993 {

   994     bool success = true;

   995     QSqlQuery query(dbConnection());

   996     if (entryClone->attributes().count() > 0) {

   997         QMap<QString, QString> attributesMap = entryClone->attributes();

   998         foreach(QString key, attributesMap.keys()) {

   999             query.prepare(

  1000                 "REPLACE INTO CA_ATTRIBUTE (AT_ENTRY_ID,AT_NAME,AT_VALUE) VALUES ( \

  1001                 :AT_ENTRY_ID,\

  1002                 :AT_NAME,\

  1003                 :AT_VALUE )");

  1004             query.bindValue(":AT_ENTRY_ID", entryClone->id());

  1005             query.bindValue(":AT_NAME", key);

  1006             query.bindValue(":AT_VALUE", attributesMap.value(key));

  1007             success = query.exec();

  1008             if (!success) {

  1009                 break;

  1010             }

  1011             qDebug() << "CaServicePrivate::setAttributesInDb"

  1012                      << query.boundValues();

  1013         }

  1014     }

  1015     return success;

  1016 }

  1017 

  1018 

  1019 void CaClientProxy::CreateGetParentsIdsQuery(

  1020     const QList<int> &entryIds,

  1021     const QList<int> &parentIds,

  1022     QString &query)

  1023 {

  1024     QString entryIdList;

  1025     int lastItemIndex = entryIds.count()-1;

  1026     for (int i = 0; i < lastItemIndex; i++) {

  1027         entryIdList.append(QString::number(entryIds[i]));

  1028         entryIdList.append(",");

  1029     }

  1030     if (lastItemIndex >= 0) {

  1031         entryIdList.append(QString::number(entryIds[lastItemIndex]));

  1032     }

  1033     query = QString("SELECT GE_GROUP_ID FROM CA_GROUP_ENTRY "

  1034                     "WHERE GE_ENTRY_ID IN ( %1 )").arg(entryIdList);

  1035 

  1036     int lastParentIndex = parentIds.count()-1;

  1037     if (lastParentIndex >= 0) {

  1038         QString parentIdList;

  1039         for (int i = 0; i < lastParentIndex; i++) {

  1040             parentIdList.append(QString::number(parentIds[i]));

  1041             parentIdList.append(",");

  1042         }

  1043         parentIdList.append(QString::number(parentIds[lastParentIndex]));

  1044         query.append(QString(" AND GE_GROUP_ID NOT IN( %1 )").arg(parentIdList));

  1045     }

  1046 }

  1047 

  1048 

  1049 bool CaClientProxy::GetParentsIds(const QList<int> &entryIds,

  1050                                   QList<int> &parentIds)

  1051 {

  1052     QString getParentIdsQuery;

  1053     CreateGetParentsIdsQuery(entryIds, parentIds, getParentIdsQuery);

  1054     QSqlQuery query(dbConnection());

  1055     bool success = query.exec(getParentIdsQuery);

  1056 

  1057     if (success && query.next()) {

  1058         QList<int> newParentIds;

  1059         do {

  1060             newParentIds << query.value(query.record().indexOf("GE_GROUP_ID")).toInt();

  1061         } while (query.next());

  1062         parentIds.append(newParentIds);

  1063         GetParentsIds(newParentIds, parentIds);

  1064     }

  1065     return success;

  1066 }

  1067 

  1068 int CaClientProxy::getEntryIdByUid(const CaEntry &entry, const int uid)

  1069 {

  1070     int result = -1;

  1071 

  1072     QSqlQuery query(dbConnection());

  1073     query.prepare("SELECT ENTRY_ID from CA_ENTRY where EN_UID=?");

  1074     query.addBindValue(uid);

  1075     bool success = query.exec();

  1076 

  1077     if (success && (query.first())) {

  1078         result = query.value(query.record().indexOf("ENTRY_ID")).toInt();

  1079     }

  1080 

  1081     return result;

  1082 }