1 /*

     2 * Copyright (c) 2009 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 // System includes

    19 #include <QSqlDatabase>

    20 #include <QSqlQuery>

    21 #include <QSqlError>

    22 #include <QStringList>

    23 #include <QFile>

    24 #include <QDateTime>

    25 #include <QVariant>

    26 //#include <QSqlRecord>

    27 

    28 // User includes

    29 #include "radiohistorydatabase.h"

    30 #include "radiologger.h"

    31 

    32 static const QLatin1String DATABASE_NAME    ( "radioplayhistory.db" );

    33 static const QLatin1String DATABASE_DRIVER  ( "QSQLITE" );

    34 static const QLatin1String HISTORY_TABLE    ( "history" );

    35 static const QLatin1String SQL_CREATE_TABLE ( "CREATE TABLE history ("

    36                                                 "id INTEGER PRIMARY KEY AUTOINCREMENT, "

    37                                                 "artist TEXT NOT NULL, "

    38                                                 "title TEXT NOT NULL, "

    39                                                 "station TEXT NOT NULL, "

    40                                                 "frequency INTEGER NOT NULL, "

    41                                                 "tagged INTEGER NOT NULL DEFAULT 0, "

    42                                                 "fromRds INTEGER NOT NULL DEFAULT 1, "

    43                                                 "time INTEGER NOT NULL)" );

    44 

    45 static const QLatin1String SQL_ADD_ITEM     ( "INSERT INTO history (artist,title,station,frequency,fromRds,time) "

    46                                                 "VALUES ( ?,?,?,?,?,? )" );

    47 

    48 static const QLatin1String SQL_SELECT_ALL   ( "SELECT * FROM history ORDER BY id DESC" );

    49 static const QLatin1String SQL_SELECT_TAGGED( "SELECT * FROM history WHERE tagged=1" );// ORDER BY id DESC";

    50 

    51 static const QLatin1String SQL_DELETE_ALL   ( "DELETE FROM history" );

    52 static const QLatin1String SQL_DELETE_RECENT( "DELETE FROM history WHERE tagged=0" );

    53 //static const QLatin1String SQL_DELETE_TAGGED    = "DELETE FROM history WHERE tagged=1";

    54 static const QLatin1String SQL_CLEAR_TAGS   ( "UPDATE history SET tagged = 0 WHERE tagged = 1" );

    55 

    56 //static static const QLatin1String SQL_FIND_ITEM_BY_ID( "SELECT * FROM history WHERE id = ?" );

    57 static const QLatin1String SQL_TOGGLE_TAG   ( "UPDATE history SET tagged = ? WHERE id = ?" );

    58 

    59 #ifdef LOGGING_ENABLED

    60 #   define GET_ERR( param ) GETSTRING( param.lastError().text() )

    61 #   define GET_ERR_PTR( param ) GETSTRING( param->lastError().text() )

    62 #endif // LOGGING_ENABLED

    63 

    64 /*!

    65  *

    66  */

    67 RadioHistoryDatabase::RadioHistoryDatabase()

    68 {

    69     connectToDatabase();

    70 }

    71 

    72 /*!

    73  *

    74  */

    75 RadioHistoryDatabase::~RadioHistoryDatabase()

    76 {

    77     if ( mDatabase && mDatabase->isOpen() ) {

    78         mDatabase->close();

    79     }

    80 }

    81 

    82 /*!

    83  *

    84  */

    85 void RadioHistoryDatabase::addItem( const QString& artist,

    86                                     const QString& title,

    87                                     const QString& stationName,

    88                                     uint frequency,

    89                                     bool fromRds )

    90 {

    91     LOG_FORMAT( "RadioHistoryModelPrivate::addItem. Artist: %s, Title: %s", GETSTRING( artist ), GETSTRING( title ) );

    92 

    93     QSqlQuery query( *mDatabase );

    94     mDatabase->transaction();

    95 

    96     query.prepare( SQL_ADD_ITEM );

    97     query.addBindValue( artist );

    98     query.addBindValue( title );

    99     query.addBindValue( stationName );

   100     query.addBindValue( static_cast<int>( frequency / 1000 ) );

   101     query.addBindValue( fromRds );

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

   103 

   104     commitTransaction( query );

   105 }

   106 

   107 /*!

   108  *

   109  */

   110 void RadioHistoryDatabase::clearRecent()

   111 {

   112     QSqlQuery query( *mDatabase );

   113     mDatabase->transaction();

   114 

   115     query.prepare( SQL_CLEAR_TAGS );

   116 

   117     commitTransaction( query );

   118 }

   119 

   120 /*!

   121  *

   122  */

   123 void RadioHistoryDatabase::clearTagged()

   124 {

   125     QSqlQuery query( *mDatabase );

   126     mDatabase->transaction();

   127 

   128     query.prepare( SQL_DELETE_ALL );

   129 

   130     commitTransaction( query );

   131 }

   132 

   133 /*!

   134  *

   135  */

   136 bool RadioHistoryDatabase::connectToDatabase()

   137 {

   138     LOG_METHOD;

   139     QSqlDatabase db = QSqlDatabase::addDatabase( DATABASE_DRIVER );

   140     if ( db.isValid() ) {

   141         mDatabase.reset( new QSqlDatabase( db ) );

   142         mDatabase->setDatabaseName( DATABASE_NAME );

   143 

   144         if ( !mDatabase->open() ) {

   145             LOG_FORMAT( "Failed to open database! error = %s", GET_ERR_PTR( mDatabase ) );

   146             mDatabase.reset();

   147             return false;

   148         }

   149 

   150         // Create the table if it does not exist

   151         if ( !mDatabase->tables().contains( HISTORY_TABLE ) ) {

   152             LOG( "RadioHistoryModelPrivate::connectToDatabase: Creating database tables." );

   153             QSqlQuery query;

   154             if ( !query.exec( SQL_CREATE_TABLE ) ) {

   155                 LOG_FORMAT( "Database creation failed! error = %s", GET_ERR( query ) );

   156                 mDatabase->close();

   157                 mDatabase.reset();

   158                 return false;

   159             }

   160         }

   161     } else {

   162         LOG_FORMAT( "Invalid database! error = %s", GET_ERR( db ) );

   163         return false;

   164     }

   165 

   166     return true;

   167 }

   168 

   169 /*!

   170  *

   171  */

   172 void RadioHistoryDatabase::commitTransaction( QSqlQuery& query )

   173 {

   174     LOG_METHOD;

   175 

   176     bool success = false;

   177     Q_UNUSED( success );

   178     if ( query.exec() ) {

   179 

   180         success = mDatabase->commit();

   181         LOG_ASSERT( success, LOG_FORMAT( "Commit failed! err: %s", GET_ERR_PTR( mDatabase ) ) );

   182 

   183     } else {

   184         LOG_FORMAT( "RadioHistoryModelPrivate::commitTransaction FAILED, rolling back: error = %s", GET_ERR( query ) );

   185         success = mDatabase->rollback();

   186         LOG_ASSERT( success, LOG_FORMAT( "Rollback failed! err: %s", GET_ERR_PTR( mDatabase ) ) );

   187     }

   188 }