From f7a4c32736569a17fa175d01ba84b24706fd0637 Mon Sep 17 00:00:00 2001 From: Mu-Woong Date: Mon, 6 Jul 2015 21:52:04 +0900 Subject: [PATCH] Fix 'frequency' statistics for the trigger to emit proper results Change-Id: Ie1eae605ff59a743253699d93d15b76d66255995 Signed-off-by: Mu-Woong --- src/app/app_stats_types.h | 8 ++++---- src/app/db_handle.cpp | 20 ++++++++++++++++++-- src/app/db_init.cpp | 2 +- src/media/db_handle.cpp | 2 ++ src/shared/db_handle_base.cpp | 20 +++++++++++++++----- src/shared/db_handle_base.h | 1 + src/social/db_handle.cpp | 24 ++++++++++++++++++++---- src/social/log_aggregator.cpp | 4 +++- src/social/social_stats_types.h | 8 ++++---- 9 files changed, 68 insertions(+), 21 deletions(-) diff --git a/src/app/app_stats_types.h b/src/app/app_stats_types.h index 88b1313..4a72864 100644 --- a/src/app/app_stats_types.h +++ b/src/app/app_stats_types.h @@ -38,9 +38,9 @@ "UTC TIMESTAMP DEFAULT (strftime('%s', 'now')), " \ "LocalTime TIMESTAMP DEFAULT (strftime('%s', 'now', 'localtime'))" -#define APP_VIEW_USAGE_FREQ "View_AppLaunchFreq" -#define APP_VIEW_USAGE_FREQ_SQL \ - "CREATE VIEW IF NOT EXISTS " APP_VIEW_USAGE_FREQ " AS " \ - "SELECT AppId, COUNT(*) AS TotalCount FROM " APP_TABLE_USAGE_LOG " GROUP BY AppId" +#define APP_TEMP_USAGE_FREQ "Temp_AppLaunchFreq" +#define APP_TEMP_USAGE_FREQ_SQL \ + "CREATE TABLE IF NOT EXISTS " APP_TEMP_USAGE_FREQ \ + " (AppId TEXT NOT NULL UNIQUE, TotalCount INTEGER DEFAULT 0);" #endif diff --git a/src/app/db_handle.cpp b/src/app/db_handle.cpp index 2bc1b90..8297770 100644 --- a/src/app/db_handle.cpp +++ b/src/app/db_handle.cpp @@ -50,6 +50,7 @@ int ctx::app_db_handle::read(const char* subject, ctx::json filter) query = create_sql_common_setting(filter); } else if (STR_EQ(subject, APP_SUBJ_FREQUENCY)) { + is_trigger_item = true; query = create_sql_frequency(filter); } @@ -109,10 +110,25 @@ std::string ctx::app_db_handle::create_sql_frequency(ctx::json filter) std::string where_clause = create_where_clause(filter_cleaned); std::stringstream query; + + query << + "DELETE FROM " APP_TEMP_USAGE_FREQ ";"; + + query << + "INSERT INTO " APP_TEMP_USAGE_FREQ \ + " SELECT " STATS_APP_ID ", COUNT(*) AS " STATS_TOTAL_COUNT \ + " FROM " APP_TABLE_USAGE_LOG \ + " WHERE " << where_clause << + " GROUP BY " STATS_APP_ID ";"; + + query << + "INSERT OR IGNORE INTO " APP_TEMP_USAGE_FREQ " (" STATS_APP_ID ")" \ + " VALUES ('" << app_id << "');"; + query << "SELECT S." STATS_APP_ID ", S." STATS_TOTAL_COUNT ", 1+COUNT(lesser." STATS_TOTAL_COUNT ") AS Rank" \ - " FROM " APP_VIEW_USAGE_FREQ " AS S" \ - " LEFT JOIN " APP_VIEW_USAGE_FREQ " AS lesser" \ + " FROM " APP_TEMP_USAGE_FREQ " AS S" \ + " LEFT JOIN " APP_TEMP_USAGE_FREQ " AS lesser" \ " ON S." STATS_TOTAL_COUNT " < lesser." STATS_TOTAL_COUNT \ " WHERE S." STATS_APP_ID " = '" << app_id << "'"; diff --git a/src/app/db_init.cpp b/src/app/db_init.cpp index b38347f..a81a8df 100644 --- a/src/app/db_init.cpp +++ b/src/app/db_init.cpp @@ -41,7 +41,7 @@ void ctx::app_db_initializer::create_table() db_manager::create_table(0, APP_TABLE_USAGE_LOG, APP_TABLE_USAGE_LOG_COLUMNS, NULL, NULL); db_manager::create_table(0, APP_TABLE_REMOVABLE_APP, APP_TABLE_REMOVABLE_APP_COLUMNS, NULL, NULL); - db_manager::execute(0, APP_VIEW_USAGE_FREQ_SQL, NULL); + db_manager::execute(0, APP_TEMP_USAGE_FREQ_SQL, NULL); done = true; } diff --git a/src/media/db_handle.cpp b/src/media/db_handle.cpp index 430aad0..af6afdb 100644 --- a/src/media/db_handle.cpp +++ b/src/media/db_handle.cpp @@ -49,9 +49,11 @@ int ctx::media_db_handle::read(const char* subject, ctx::json filter) query = create_sql_common_setting(MEDIA_TYPE_VIDEO, filter); } else if (STR_EQ(subject, MEDIA_SUBJ_MUSIC_FREQUENCY)) { + is_trigger_item = true; query = create_sql_frequency(MEDIA_TYPE_MUSIC, filter); } else if (STR_EQ(subject, MEDIA_SUBJ_VIDEO_FREQUENCY)) { + is_trigger_item = true; query = create_sql_frequency(MEDIA_TYPE_VIDEO, filter); } diff --git a/src/shared/db_handle_base.cpp b/src/shared/db_handle_base.cpp index b4ed798..33813ed 100644 --- a/src/shared/db_handle_base.cpp +++ b/src/shared/db_handle_base.cpp @@ -25,7 +25,8 @@ #define HOUR_OF_DAY(SECOND) "CAST(strftime('%H', " SECOND ", 'unixepoch') AS INTEGER)" ctx::stats_db_handle_base::stats_db_handle_base(const char* zone) - : req_zone(zone) + : is_trigger_item(false) + , req_zone(zone) { } @@ -209,10 +210,19 @@ void ctx::stats_db_handle_base::json_vector_to_array(std::vector &vec_json void ctx::stats_db_handle_base::on_query_result_received(unsigned int query_id, int error, std::vector& records) { - json results = "{\"" STATS_QUERY_RESULT "\":[]}"; - json_vector_to_array(records, results); - - context_manager::reply_to_read(req_subject.c_str(), req_filter, error, results, req_zone.c_str()); + if (is_trigger_item) { + if (records.size() == 1) { + context_manager::reply_to_read(req_subject.c_str(), req_filter, error, records[0], req_zone.c_str()); + } else { + _E("Invalid query result"); + json dummy; + context_manager::reply_to_read(req_subject.c_str(), req_filter, ERR_OPERATION_FAILED, dummy, req_zone.c_str()); + } + } else { + json results = "{\"" STATS_QUERY_RESULT "\":[]}"; + json_vector_to_array(records, results); + context_manager::reply_to_read(req_subject.c_str(), req_filter, error, results, req_zone.c_str()); + } delete this; } diff --git a/src/shared/db_handle_base.h b/src/shared/db_handle_base.h index d2828cc..8c22d37 100644 --- a/src/shared/db_handle_base.h +++ b/src/shared/db_handle_base.h @@ -24,6 +24,7 @@ namespace ctx { class stats_db_handle_base : public db_listener_iface { protected: + bool is_trigger_item; std::string req_subject; stats_db_handle_base(const char* zone); diff --git a/src/social/db_handle.cpp b/src/social/db_handle.cpp index ea0d7da..1062bbd 100644 --- a/src/social/db_handle.cpp +++ b/src/social/db_handle.cpp @@ -39,6 +39,7 @@ int ctx::social_db_handle::read(const char* subject, ctx::json filter) query = create_sql_freq_address(filter); } else if (STR_EQ(subject, SOCIAL_SUBJ_FREQUENCY)) { + is_trigger_item = true; query = create_sql_frequency(filter); } @@ -110,19 +111,34 @@ std::string ctx::social_db_handle::create_sql_frequency(ctx::json filter) return ""; } + /* TODO: Enable after fixing the log aggregator if (filter.get(NULL, STATS_DAY_OF_WEEK, &week_str)) filter_cleaned.set(NULL, STATS_DAY_OF_WEEK, week_str); if (filter.get(NULL, STATS_TIME_OF_DAY, &time_of_day)) filter_cleaned.set(NULL, STATS_TIME_OF_DAY, time_of_day); - - std::string where_clause = create_where_clause(filter_cleaned); + */ std::stringstream query; + + query << + "DELETE FROM " SOCIAL_TEMP_CONTACT_FREQ ";"; + + query << + "INSERT INTO " SOCIAL_TEMP_CONTACT_FREQ \ + " SELECT " SOCIAL_ADDRESS ", COUNT(*) AS " STATS_TOTAL_COUNT \ + " FROM " SOCIAL_TABLE_CONTACT_LOG \ + " WHERE " << create_where_clause(filter_cleaned) << + " GROUP BY " SOCIAL_ADDRESS ";"; + + query << + "INSERT OR IGNORE INTO " SOCIAL_TEMP_CONTACT_FREQ " (" SOCIAL_ADDRESS ")" \ + " VALUES ('" << address << "');"; + query << "SELECT S." SOCIAL_ADDRESS ", S." STATS_TOTAL_COUNT ", 1+COUNT(lesser." STATS_TOTAL_COUNT ") AS Rank" \ - " FROM " SOCIAL_VIEW_CONTACT_FREQ " AS S" \ - " LEFT JOIN " SOCIAL_VIEW_CONTACT_FREQ " AS lesser" \ + " FROM " SOCIAL_TEMP_CONTACT_FREQ " AS S" \ + " LEFT JOIN " SOCIAL_TEMP_CONTACT_FREQ " AS lesser" \ " ON S." STATS_TOTAL_COUNT " < lesser." STATS_TOTAL_COUNT \ " WHERE S." SOCIAL_ADDRESS " = '" << address << "'"; diff --git a/src/social/log_aggregator.cpp b/src/social/log_aggregator.cpp index e3de297..f1488d9 100644 --- a/src/social/log_aggregator.cpp +++ b/src/social/log_aggregator.cpp @@ -40,7 +40,7 @@ void ctx::contact_log_aggregator::create_table() IF_FAIL_VOID(!done); db_manager::create_table(0, SOCIAL_TABLE_CONTACT_LOG, SOCIAL_TABLE_CONTACT_LOG_COLUMNS, NULL, NULL); - db_manager::execute(0, SOCIAL_VIEW_CONTACT_FREQ_SQL, NULL); + db_manager::execute(0, SOCIAL_TEMP_CONTACT_FREQ_SQL, NULL); done = true; } @@ -144,6 +144,8 @@ void ctx::contact_log_aggregator::insert_contact_log_list(contacts_list_h list) data.set(NULL, STATS_DURATION, duration); data.set(NULL, STATS_UNIV_TIME, accesstime); + /* TODO: Local Time */ + db_manager::insert(0, SOCIAL_TABLE_CONTACT_LOG, data, NULL); } while(contacts_list_next(list) == CONTACTS_ERROR_NONE); diff --git a/src/social/social_stats_types.h b/src/social/social_stats_types.h index 8f93273..18d2a2b 100644 --- a/src/social/social_stats_types.h +++ b/src/social/social_stats_types.h @@ -28,10 +28,10 @@ "Duration INTEGER NOT NULL DEFAULT 0, " \ "UTC TIMESTAMP DEFAULT (strftime('%s', 'now'))" -#define SOCIAL_VIEW_CONTACT_FREQ "View_ContactFreq" -#define SOCIAL_VIEW_CONTACT_FREQ_SQL \ - "CREATE VIEW IF NOT EXISTS " SOCIAL_VIEW_CONTACT_FREQ " AS " \ - "SELECT Address, COUNT(*) AS TotalCount FROM " SOCIAL_TABLE_CONTACT_LOG " GROUP BY Address" +#define SOCIAL_TEMP_CONTACT_FREQ "Temp_ContactFreq" +#define SOCIAL_TEMP_CONTACT_FREQ_SQL \ + "CREATE TABLE IF NOT EXISTS " SOCIAL_TEMP_CONTACT_FREQ \ + " (Address TEXT NOT NULL UNIQUE, TotalCount INTEGER DEFAULT 0);" #define SOCIAL_COMMUNICATION_TYPE "CommunicationType" #define SOCIAL_ADDRESS "Address" -- 2.7.4