1 /*

     2 * Copyright (c) 2010 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: Location data lookup db implementation

    15 *

    16 */

    17 

    18 // INCLUDE FILES

    19 #include "locationdatalookupdb.h"

    20 #include <QSqlDatabase>

    21 #include <QSqlError>

    22 #include <QSqlQuery>

    23 #include <QSqlRecord>

    24 #include <QVariant>

    25 #include<locationservicedefines.h>

    26 

    27 // database name

    28 const QString KLocationDataLookupDbName = "c:\\locationdatalookupdb.db"; 

    29 

    30 // ================= MEMBER FUNCTIONS =======================

    31 //

    32 // ---------------------------------------------------------

    33 // LocationDataLookupDb::LocationDataLookupDb()

    34 // Constructor

    35 // ---------------------------------------------------------

    36 //

    37 LocationDataLookupDb::LocationDataLookupDb( QObject *parent) : QObject(parent)

    38 {

    39     mDb = new QSqlDatabase();

    40     *mDb = QSqlDatabase::addDatabase( "QSQLITE" );

    41     mDb->setDatabaseName( KLocationDataLookupDbName );

    42     if (!mDb->open())

    43     {

    44         delete mDb;

    45         mDb = NULL;

    46         return;

    47     }

    48 

    49     // create lookup table if doesnot exist

    50     QSqlQuery query(*mDb);

    51     query.exec("create table if not exists lplookup (id INTEGER PRIMARY KEY AUTOINCREMENT,"

    52                                              "duplicate int,"

    53                                              "sourceid int,"

    54                                              "sourcetype int,"

    55                                              "destid int,"

    56                                              "name varchar(255),"

    57                                              "street varchar(255),"

    58                                              "postal varchar(50),"

    59                                              "city varchar(50),"

    60                                              "state varchar(50),"

    61                                              "country varchar(50),"

    62                                              "latitude double,"

    63                                              "longitude double,"

    64                                              "icontype int,"

    65                                              "iconpath varchar(255),"

    66                                              "maptile varchar(255))");

    67 

    68     mDb->close();

    69 }

    70 

    71 // ---------------------------------------------------------

    72 // LocationDataLookupDb::~LocationDataLookupDb()

    73 // destructor

    74 // ---------------------------------------------------------

    75 LocationDataLookupDb::~LocationDataLookupDb()

    76 {

    77     if( mDb )

    78         delete mDb;

    79 }

    80 

    81 // ---------------------------------------------------------

    82 // LocationDataLookupDb::open()

    83 // ---------------------------------------------------------

    84 bool LocationDataLookupDb::open()

    85 {

    86     return mDb->open();

    87 }

    88 

    89 // ---------------------------------------------------------

    90 // LocationDataLookupDb::close()

    91 // ---------------------------------------------------------

    92 void LocationDataLookupDb::close()

    93 {

    94     if( mDb )

    95         mDb->close();

    96 }

    97 

    98 // ---------------------------------------------------------

    99 // LocationDataLookupDb::createEntry()

   100 // ---------------------------------------------------------

   101 void LocationDataLookupDb::createEntry( const QLookupItem& aLookupItem )

   102 {

   103     if( mDb )

   104     {

   105         QSqlQuery query(*mDb);

   106         query.prepare("INSERT INTO lplookup ("

   107                               "id, "

   108                               "duplicate, "

   109                               "sourceid, "

   110                               "sourcetype, "

   111                               "destid, "

   112                               "name, "

   113                               "street, "

   114                               "postal, "

   115                               "city, "

   116                               "state, "

   117                               "country, "

   118                               "latitude, "

   119                               "longitude, "

   120                               "icontype, "

   121                               "iconpath, "

   122                               "maptile) "

   123                       "VALUES ("

   124                               "NULL, "

   125                               ":duplicate, "

   126                               ":sourceid, "

   127                               ":sourcetype, "

   128                               ":destid, "

   129                               ":name, "

   130                               ":street, "

   131                               ":postal, "

   132                               ":city, "

   133                               ":state, "

   134                               ":country, "

   135                               ":latitude, "

   136                               ":longitude, "

   137                               ":icontype, "

   138                               ":iconpath, "

   139                               ":maptile)" );

   140         query.bindValue(":duplicate", aLookupItem.mIsDuplicate);

   141         query.bindValue(":sourceid", aLookupItem.mSourceUid);

   142         query.bindValue(":sourcetype", aLookupItem.mSourceType);

   143         query.bindValue(":destid", aLookupItem.mDestId);

   144         query.bindValue(":name", aLookupItem.mName);

   145         query.bindValue(":street", aLookupItem.mStreet);

   146         query.bindValue(":postal", aLookupItem.mPostalCode);

   147         query.bindValue(":city", aLookupItem.mCity);

   148         query.bindValue(":state", aLookupItem.mState);

   149         query.bindValue(":country", aLookupItem.mCountry);

   150         query.bindValue(":latitude", aLookupItem.mLatitude);

   151         query.bindValue(":longitude", aLookupItem.mLongitude);

   152         query.bindValue(":icontype", aLookupItem.mIconType);

   153         query.bindValue(":iconpath", aLookupItem.mIconPath);

   154         query.bindValue(":maptile", aLookupItem.mMapTilePath);

   155         query.exec();

   156     }

   157 }

   158 

   159 // ---------------------------------------------------------

   160 // LocationDataLookupDb::updateEntryBySourceIdAndType()

   161 // ---------------------------------------------------------

   162 void LocationDataLookupDb::updateEntryBySourceIdAndType( const QLookupItem& aLookupItem )

   163 {

   164     if( mDb )

   165     {

   166         QSqlQuery query(*mDb);

   167         query.prepare("UPDATE lplookup SET "

   168                         "id = ?, "

   169                         "duplicate = ?, "

   170                         "sourceid = ?, "

   171                         "sourcetype = ?, "

   172                         "destid = ?, "

   173                         "name = ?, "

   174                         "street = ?, "

   175                         "postal = ?, "

   176                         "city = ?, "

   177                         "state = ?, "

   178                         "country = ?, "

   179                         "latitude = ?, "

   180                         "longitude = ?, "

   181                         "icontype = ?, "

   182                         "iconpath = ?, "

   183                         "maptile = ? "

   184                       "WHERE sourceid = ? AND sourcetype = ?");

   185     

   186         query.addBindValue( aLookupItem.mId );

   187         query.addBindValue( aLookupItem.mIsDuplicate );

   188         query.addBindValue( aLookupItem.mSourceUid );

   189         query.addBindValue( aLookupItem.mSourceType );

   190         query.addBindValue( aLookupItem.mDestId );

   191         query.addBindValue( aLookupItem.mName );

   192         query.addBindValue( aLookupItem.mStreet );

   193         query.addBindValue( aLookupItem.mPostalCode );

   194         query.addBindValue( aLookupItem.mCity );

   195         query.addBindValue( aLookupItem.mState );

   196         query.addBindValue( aLookupItem.mCountry );

   197         query.addBindValue( aLookupItem.mLatitude );

   198         query.addBindValue( aLookupItem.mLongitude );

   199         query.addBindValue( aLookupItem.mIconType );

   200         query.addBindValue( aLookupItem.mIconPath );

   201         query.addBindValue( aLookupItem.mMapTilePath );

   202     

   203         query.addBindValue( aLookupItem.mSourceUid );

   204         query.addBindValue( aLookupItem.mSourceType );

   205     

   206         query.exec();

   207     }

   208 }

   209 

   210 // ---------------------------------------------------------

   211 // LocationDataLookupDb::updateMaptileBySourceIdAndType()

   212 // ---------------------------------------------------------

   213 void LocationDataLookupDb::updateMaptileBySourceIdAndType( quint32 aSourceId, 

   214         quint32 aSourceType, QString aImagePath )

   215 {

   216     if( mDb )

   217     {

   218         QSqlQuery query(*mDb);

   219         query.prepare("UPDATE lplookup SET "

   220                         "maptile = ? "

   221                       "WHERE sourceid = ? AND sourcetype = ?");

   222     

   223         query.addBindValue( aImagePath );

   224         query.addBindValue( aSourceId );

   225         query.addBindValue( aSourceType );

   226     

   227         query.exec();

   228     }

   229 }

   230 

   231 // ---------------------------------------------------------

   232 // LocationDataLookupDb::updateEntryById()

   233 // ---------------------------------------------------------

   234 void LocationDataLookupDb::updateEntryById( const QLookupItem& aLookupItem )

   235 {

   236     if( mDb )

   237     {

   238         QSqlQuery query(*mDb);

   239         query.prepare("UPDATE lplookup SET "

   240                         "id = ?, "

   241                         "duplicate = ?, "

   242                         "sourceid = ?, "

   243                         "sourcetype = ?, "

   244                         "destid = ?, "

   245                         "name = ?, "

   246                         "street = ?, "

   247                         "postal = ?, "

   248                         "city = ?, "

   249                         "state = ?, "

   250                         "country = ?, "

   251                         "latitude = ?, "

   252                         "longitude = ?, "

   253                         "icontype = ?, "

   254                         "iconpath = ?, "

   255                         "maptile = ? "

   256                       "WHERE id = ?");

   257     

   258         query.addBindValue( aLookupItem.mId );

   259         query.addBindValue( aLookupItem.mIsDuplicate );

   260         query.addBindValue( aLookupItem.mSourceUid );

   261         query.addBindValue( aLookupItem.mSourceType );

   262         query.addBindValue( aLookupItem.mDestId );

   263         query.addBindValue( aLookupItem.mName );

   264         query.addBindValue( aLookupItem.mStreet );

   265         query.addBindValue( aLookupItem.mPostalCode );

   266         query.addBindValue( aLookupItem.mCity );

   267         query.addBindValue( aLookupItem.mState );

   268         query.addBindValue( aLookupItem.mCountry );

   269         query.addBindValue( aLookupItem.mLatitude );

   270         query.addBindValue( aLookupItem.mLongitude );

   271         query.addBindValue( aLookupItem.mIconType );

   272         query.addBindValue( aLookupItem.mIconPath );

   273         query.addBindValue( aLookupItem.mMapTilePath );

   274     

   275         query.addBindValue( aLookupItem.mId );

   276     

   277         query.exec();

   278     }

   279 }

   280 

   281 // ---------------------------------------------------------

   282 // LocationDataLookupDb::deleteEntryBySourceIdAndType()

   283 // ---------------------------------------------------------

   284 void LocationDataLookupDb::deleteEntryBySourceIdAndType( const QLookupItem& aLookupItem )

   285 {

   286     if( mDb )

   287     {

   288         QSqlQuery query(*mDb);

   289         query.prepare( "DELETE FROM lplookup "

   290                        "WHERE sourceid = ? AND sourcetype = ?" );

   291     

   292         query.addBindValue( aLookupItem.mSourceUid );

   293         query.addBindValue( aLookupItem.mSourceType );

   294     

   295         query.exec();

   296     }

   297 }

   298 

   299 // ---------------------------------------------------------

   300 // LocationDataLookupDb::deleteEntryById()

   301 // ---------------------------------------------------------

   302 void LocationDataLookupDb::deleteEntryById( const QLookupItem& aLookupItem )

   303 {

   304     if( mDb )

   305     {

   306         QSqlQuery query(*mDb);

   307         query.prepare( "DELETE FROM lplookup "

   308                        "WHERE id = ?" );

   309     

   310         query.addBindValue( aLookupItem.mId );

   311         query.exec();

   312     }

   313 }

   314 

   315 // ---------------------------------------------------------

   316 // LocationDataLookupDb::findEntryBySourceIdAndType()

   317 // ---------------------------------------------------------

   318 bool LocationDataLookupDb::findEntryBySourceIdAndType( QLookupItem& aLookupItem )

   319 {

   320     if( mDb )

   321     {

   322         QSqlQuery query(*mDb);

   323         query.prepare( "SELECT * FROM lplookup "    

   324                     "WHERE sourceid = ? AND sourcetype = ?" );

   325         query.addBindValue( aLookupItem.mSourceUid );

   326         query.addBindValue( aLookupItem.mSourceType );

   327     

   328         query.exec();

   329         

   330         if( !query.first() )

   331         {

   332             return false;

   333         }

   334     

   335         fillLookupEntry( query, aLookupItem );

   336         return true;

   337     }

   338     return false;

   339 }

   340 

   341 // ---------------------------------------------------------

   342 // LocationDataLookupDb::findEntryById()

   343 // ---------------------------------------------------------

   344 bool LocationDataLookupDb::findEntryById( QLookupItem& aLookupItem )

   345 {

   346     if( mDb )

   347     {

   348         QSqlQuery query(*mDb);

   349         query.prepare( "SELECT * FROM lplookup "    

   350                     "WHERE id = ?" );

   351         query.addBindValue( aLookupItem.mId );

   352     

   353         query.exec();

   354         

   355         if( !query.first() )

   356         {

   357             return false;

   358         }

   359     

   360         fillLookupEntry( query, aLookupItem );    

   361         return true;

   362     }

   363     

   364     return false;

   365 }

   366 

   367 

   368 // ---------------------------------------------------------

   369 // LocationDataLookupDb::findEntriesByLandmarkId()

   370 // ---------------------------------------------------------

   371 void LocationDataLookupDb::findEntriesByLandmarkId( const quint32 aLandmarkId, 

   372         QList<QLookupItem>& aLookupItemArray )

   373 {

   374     if( mDb )

   375     {

   376 

   377         QSqlQuery query(*mDb);

   378         query.prepare( "SELECT * FROM lplookup "    

   379                        "WHERE destid = ?" );

   380     

   381         query.addBindValue( aLandmarkId );

   382         query.exec();

   383         

   384         while( query.next() )

   385         {    

   386             QLookupItem lookupItem;

   387             fillLookupEntry( query, lookupItem );        

   388             aLookupItemArray.append( lookupItem );

   389         } 

   390     }   

   391 }

   392 

   393 // ---------------------------------------------------------

   394 // LocationDataLookupDb::findEntriesBySourceType()

   395 // ---------------------------------------------------------

   396 void LocationDataLookupDb::findEntriesBySourceType( const quint32 aSourceType, 

   397         QList<QLookupItem>& aLookupItemArray )

   398 {

   399     if( mDb )

   400     {

   401         QSqlQuery query(*mDb);

   402         query.prepare( "SELECT * FROM lplookup "    

   403                        "WHERE sourceType = ?" );

   404         query.addBindValue( aSourceType );

   405         query.exec();

   406        

   407         while( query.next() )

   408         {    

   409             QLookupItem lookupItem;

   410             fillLookupEntry( query, lookupItem );

   411             aLookupItemArray.append( lookupItem );

   412         }

   413     }

   414 }

   415 

   416 // ---------------------------------------------------------

   417 // LocationDataLookupDb::getEntries()

   418 // ---------------------------------------------------------

   419 void LocationDataLookupDb::getEntries( QList<QLookupItem>& aLookupItemArray, const quint32 aCollectionId )

   420 {

   421     if( mDb )

   422     {

   423         QSqlQuery query(*mDb);

   424         if( aCollectionId == ESourceLandmarksContactsCat )

   425         {

   426             query.prepare( "SELECT * FROM lplookup " 

   427                     "WHERE sourcetype = ? OR sourcetype = ? OR sourcetype = ?" );

   428             query.addBindValue( ESourceContactsPref );

   429             query.addBindValue( ESourceContactsWork );

   430             query.addBindValue( ESourceContactsHome );

   431             query.exec();

   432         }

   433         else if( aCollectionId == ESourceLandmarksCalendarCat )

   434         {

   435             query.prepare( "SELECT * FROM lplookup " 

   436                     "WHERE sourcetype = ?" );

   437             query.addBindValue( ESourceCalendar );

   438             query.exec();

   439            

   440         }

   441         else if(  aCollectionId == ESourceLandmarks )

   442         {

   443             query.prepare( "SELECT * FROM lplookup " 

   444                     "WHERE sourcetype = ?" );

   445             query.addBindValue( ESourceLandmarks );

   446             query.exec();

   447            

   448         }

   449         else // all contents

   450         {

   451             query.prepare( "SELECT * FROM lplookup" );

   452             query.exec();

   453         }

   454         while( query.next() )

   455         {    

   456             QLookupItem lookupItem;            

   457             fillLookupEntry( query, lookupItem );

   458             aLookupItemArray.append( lookupItem );

   459         }    

   460     }

   461 }

   462 

   463 void LocationDataLookupDb::fillLookupEntry( QSqlQuery &aQuery, QLookupItem &aLookupItem )

   464 {

   465     QSqlRecord rec = aQuery.record();

   466     aLookupItem.mId = aQuery.value( rec.indexOf("id") ).toUInt();

   467     aLookupItem.mIsDuplicate = aQuery.value( rec.indexOf("duplicate") ).toUInt();

   468     aLookupItem.mSourceUid = aQuery.value( rec.indexOf("sourceid") ).toUInt();

   469     aLookupItem.mSourceType = aQuery.value( rec.indexOf("sourcetype") ).toUInt();

   470     aLookupItem.mDestId = aQuery.value( rec.indexOf("destid") ).toUInt();

   471     aLookupItem.mName = aQuery.value( rec.indexOf("name") ).toString();

   472     aLookupItem.mStreet = aQuery.value( rec.indexOf("street") ).toString();

   473     aLookupItem.mPostalCode = aQuery.value( rec.indexOf("postal") ).toString();

   474     aLookupItem.mCity = aQuery.value( rec.indexOf("city") ).toString();

   475     aLookupItem.mState = aQuery.value( rec.indexOf("state") ).toString();

   476     aLookupItem.mCountry = aQuery.value( rec.indexOf("country") ).toString();

   477     aLookupItem.mLatitude = aQuery.value( rec.indexOf("latitude") ).toDouble();

   478     aLookupItem.mLongitude = aQuery.value( rec.indexOf("longitude") ).toDouble();

   479     aLookupItem.mIconType = aQuery.value( rec.indexOf("icontype") ).toUInt();

   480     aLookupItem.mIconPath = aQuery.value( rec.indexOf("iconpath") ).toString();

   481     aLookupItem.mMapTilePath = aQuery.value( rec.indexOf("maptile") ).toString();

   482 

   483 }

   484 

   485 // End of file