src/app-stats/DbHandle.cpp

   1 /*
   2  * Copyright (c) 2015 Samsung Electronics Co., Ltd.
   3  *
   4  * Licensed under the Apache License, Version 2.0 (the "License");
   5  * you may not use this file except in compliance with the License.
   6  * You may obtain a copy of the License at
   7  *
   8  * http://www.apache.org/licenses/LICENSE-2.0
   9  *
  10  * Unless required by applicable law or agreed to in writing, software
  11  * distributed under the License is distributed on an "AS IS" BASIS,
  12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13  * See the License for the specific language governing permissions and
  14  * limitations under the License.
  15  */
  16
  17 #include <sstream>
  18 #include <Types.h>
  19 #include "AppStatisticsTypes.h"
  20 #include "DbHandle.h"
  21
  22 using namespace ctx;
  23
  24 AppDbHandle::AppDbHandle(ContextProvider *provider) :
  25         StatsDbHandleBase(provider)
  26 {
  27 }
  28
  29 AppDbHandle::~AppDbHandle()
  30 {
  31 }
  32
  33 int AppDbHandle::read(Json filter)
  34 {
  35         std::string query;
  36         const char *subject = reqProvider->getSubject();
  37
  38         if (STR_EQ(subject, APP_SUBJ_RECENTLY_USED)) {
  39                 query = createSqlRecentlyUsed(filter);
  40
  41         } else if (STR_EQ(subject, APP_SUBJ_FREQUENTLY_USED)) {
  42                 query = createSqlFrequentlyUsed(filter);
  43
  44         } else if (STR_EQ(subject, APP_SUBJ_RARELY_USED)) {
  45                 query = createSqlRarelyUsed(filter);
  46
  47         } else if (STR_EQ(subject, APP_SUBJ_PEAK_TIME)) {
  48                 query = createSqlPeakTime(filter);
  49
  50         } else if (STR_EQ(subject, APP_SUBJ_COMMON_SETTING)) {
  51                 query = createSqlCommonSetting(filter);
  52
  53         } else if (STR_EQ(subject, APP_SUBJ_FREQUENCY)) {
  54                 isTriggerItem = true;
  55                 query = createSqlFrequency(filter);
  56         }
  57
  58         IF_FAIL_RETURN(!query.empty(), ERR_OPERATION_FAILED);
  59
  60         bool ret = executeQuery(filter, query.c_str());
  61         IF_FAIL_RETURN(ret, ERR_OPERATION_FAILED);
  62
  63         return ERR_NONE;
  64 }
  65
  66 std::string AppDbHandle::createWhereClauseWithDeviceStatus(Json filter)
  67 {
  68         std::stringstream whereClause;
  69         std::string bssid;
  70         int audioJack;
  71
  72         whereClause << StatsDbHandleBase::createWhereClause(filter);
  73
  74         if (filter.get(NULL, KEY_BSSID, &bssid))
  75                 whereClause << " AND " KEY_BSSID " = '" << bssid << "'";
  76
  77         if (filter.get(NULL, KEY_AUDIO_JACK, &audioJack))
  78                 whereClause << " AND " KEY_AUDIO_JACK " = " << audioJack;
  79
  80         return whereClause.str();
  81 }
  82
  83 std::string AppDbHandle::createSqlPeakTime(Json filter)
  84 {
  85         return StatsDbHandleBase::createSqlPeakTime(filter, APP_TABLE_USAGE_LOG, createWhereClause(filter));
  86 }
  87
  88 std::string AppDbHandle::createSqlCommonSetting(Json filter)
  89 {
  90         return StatsDbHandleBase::createSqlCommonSetting(filter, APP_TABLE_USAGE_LOG, createWhereClause(filter));
  91 }
  92
  93 std::string AppDbHandle::createSqlFrequency(Json filter)
  94 {
  95         Json filterCleaned;
  96         std::string weekStr;
  97         std::string timeOfDay;
  98         std::string appId;
  99
 100         if (!filter.get(NULL, KEY_APP_ID, &appId)) {
 101                 _E("Invalid parameter");
 102                 return "";
 103         }
 104
 105         if (filter.get(NULL, KEY_DAY_OF_WEEK, &weekStr))
 106                 filterCleaned.set(NULL, KEY_DAY_OF_WEEK, weekStr);
 107
 108         if (filter.get(NULL, KEY_TIME_OF_DAY, &timeOfDay))
 109                 filterCleaned.set(NULL, KEY_TIME_OF_DAY, timeOfDay);
 110
 111         std::string whereClause = createWhereClause(filterCleaned);
 112
 113         std::stringstream query;
 114
 115         query <<
 116                 "DELETE FROM " APP_TEMP_USAGE_FREQ ";";
 117
 118         query <<
 119                 "INSERT INTO " APP_TEMP_USAGE_FREQ \
 120                 " SELECT " KEY_APP_ID ", COUNT(*) AS " KEY_TOTAL_COUNT \
 121                 " FROM " APP_TABLE_USAGE_LOG \
 122                 " WHERE " << whereClause <<
 123                 " GROUP BY " KEY_APP_ID ";";
 124
 125         query <<
 126                 "INSERT OR IGNORE INTO " APP_TEMP_USAGE_FREQ " (" KEY_APP_ID ")" \
 127                 " VALUES ('" << appId << "');";
 128
 129         query <<
 130                 "SELECT S." KEY_APP_ID ", S." KEY_TOTAL_COUNT ", 1+COUNT(lesser." KEY_TOTAL_COUNT ") AS " KEY_RANK \
 131                 " FROM " APP_TEMP_USAGE_FREQ " AS S" \
 132                 " LEFT JOIN " APP_TEMP_USAGE_FREQ " AS lesser" \
 133                 " ON S." KEY_TOTAL_COUNT " < lesser." KEY_TOTAL_COUNT \
 134                 " WHERE S." KEY_APP_ID " = '" << appId << "'";
 135
 136         return query.str();
 137 }
 138
 139 std::string AppDbHandle::createSqlRecentlyUsed(Json filter)
 140 {
 141         std::stringstream query;
 142         int limit = DEFAULT_LIMIT;
 143
 144         filter.get(NULL, KEY_RESULT_SIZE, &limit);
 145
 146         query <<
 147                 "SELECT " KEY_APP_ID ", " \
 148                         "COUNT(*) AS " KEY_TOTAL_COUNT ", " \
 149                         "SUM(" KEY_DURATION ") AS " KEY_TOTAL_DURATION ", " \
 150                         "MAX(" KEY_UNIV_TIME ") AS " KEY_LAST_TIME \
 151                 " FROM " APP_TABLE_USAGE_LOG \
 152                 " WHERE " << createWhereClauseWithDeviceStatus(filter) <<
 153                 " GROUP BY " KEY_APP_ID \
 154                 " ORDER BY MAX(" KEY_UNIV_TIME ") DESC" \
 155                 " LIMIT " << limit;
 156
 157         return query.str();
 158 }
 159
 160 std::string AppDbHandle::createSqlFrequentlyUsed(Json filter)
 161 {
 162         std::stringstream query;
 163         int limit = DEFAULT_LIMIT;
 164
 165         filter.get(NULL, KEY_RESULT_SIZE, &limit);
 166
 167         query <<
 168                 "SELECT " KEY_APP_ID ", " \
 169                         "COUNT(*) AS " KEY_TOTAL_COUNT ", " \
 170                         "SUM(" KEY_DURATION ") AS " KEY_TOTAL_DURATION ", " \
 171                         "MAX(" KEY_UNIV_TIME ") AS " KEY_LAST_TIME \
 172                 " FROM " APP_TABLE_USAGE_LOG \
 173                 " WHERE " << createWhereClauseWithDeviceStatus(filter) <<
 174                 " GROUP BY " KEY_APP_ID \
 175                 " ORDER BY COUNT(*) DESC" \
 176                 " LIMIT " << limit;
 177
 178         return query.str();
 179 }
 180
 181 std::string AppDbHandle::createSqlRarelyUsed(Json filter)
 182 {
 183         std::stringstream query;
 184         int limit = DEFAULT_LIMIT;
 185
 186         filter.get(NULL, KEY_RESULT_SIZE, &limit);
 187
 188         query <<
 189                 "SELECT i." KEY_APP_ID ", " \
 190                         "COUNT(u." KEY_DURATION ") AS " KEY_TOTAL_COUNT ", " \
 191                         "IFNULL(SUM(u." KEY_DURATION "),0) AS " KEY_TOTAL_DURATION ", " \
 192                         "IFNULL(MAX(u." KEY_UNIV_TIME "),-1) AS " KEY_LAST_TIME \
 193                 " FROM " APP_TABLE_REMOVABLE_APP " i LEFT OUTER JOIN (" \
 194                         " SELECT * FROM " APP_TABLE_USAGE_LOG \
 195                         " WHERE " << createWhereClauseWithDeviceStatus(filter) << ") u" \
 196                         " ON i." KEY_APP_ID " = u." KEY_APP_ID \
 197                 " GROUP BY i." KEY_APP_ID \
 198                 " ORDER BY " KEY_TOTAL_COUNT " ASC" \
 199                 " LIMIT " << limit;
 200
 201         return query.str();
 202 }
 203
 204 void AppDbHandle::replyTriggerItem(int error, Json &jsonResult)
 205 {
 206         IF_FAIL_VOID_TAG(STR_EQ(reqProvider->getSubject(), APP_SUBJ_FREQUENCY), _E, "Invalid subject");
 207
 208         Json results;
 209         std::string valStr;
 210         int val;
 211
 212         jsonResult.get(NULL, KEY_APP_ID, &valStr);
 213         results.set(NULL, KEY_APP_ID, valStr);
 214         jsonResult.get(NULL, KEY_TOTAL_COUNT, &val);
 215         results.set(NULL, KEY_TOTAL_COUNT, val);
 216         jsonResult.get(NULL, KEY_RANK, &val);
 217         results.set(NULL, KEY_RANK, val);
 218
 219         reqProvider->replyToRead(reqFilter, error, results);
 220 }