From 1f36cf0f5e06d2448e64b46d4ff9645872891833 Mon Sep 17 00:00:00 2001 From: hj kim Date: Wed, 15 Jan 2020 14:08:02 +0900 Subject: [PATCH] unify dulicated code regarding db view and remove media_view related code. (media_view is not needed anymore) Change-Id: Ie538b8bc65dce404ef3715def2d0a95f0f48441f --- src/common/media-svc-db-utils.c | 176 ++++++++++++------------------------- src/include/common/media-svc-env.h | 1 - 2 files changed, 58 insertions(+), 119 deletions(-) diff --git a/src/common/media-svc-db-utils.c b/src/common/media-svc-db-utils.c index add00c4..9b38256 100755 --- a/src/common/media-svc-db-utils.c +++ b/src/common/media-svc-db-utils.c @@ -113,45 +113,35 @@ int __media_svc_add_column_info(GSList **slist, const char *name, const char *ty return MS_MEDIA_ERR_NONE; } -static int __media_svc_rebuild_view_query(sqlite3 *db_handle, uid_t uid) +static int __create_playlist_view(uid_t uid) { int ret = MS_MEDIA_ERR_NONE; + bool flag = false; column_info_s *col_ptr = NULL; + int len = 0; + int i = 0; char *sql = NULL; + char temp[1024] = {0 ,}; char table_query[MEDIA_SVC_QUERY_LEN_NORMAL] = {0, }; - char temp[1024] = {0, }; - bool sflag = false; - int i, len; - - /*drop playlist_view, tag_view */ - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_DROP_VIEW, MEDIA_SVC_DB_VIEW_PLAYLIST); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_DROP_VIEW, MEDIA_SVC_DB_VIEW_TAG); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - - /*create playlist_view */ len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_PLAYLIST]); for (i = 1; i < len; i++) { col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_PLAYLIST], i); if (col_ptr->is_view) { - if (sflag == true) { + if (flag == true) { if (strncmp(col_ptr->name, MEDIA_SVC_DB_COLUMN_THUMBNAIL, strlen(MEDIA_SVC_DB_COLUMN_THUMBNAIL)) == 0) snprintf(temp, sizeof(temp), ", playlist.%s AS p_thumbnail_path", col_ptr->name); else snprintf(temp, sizeof(temp), ", playlist.%s", col_ptr->name); } else { snprintf(temp, sizeof(temp), "playlist.%s", col_ptr->name); - sflag = true; + flag = true; } SAFE_STRLCAT(table_query, temp, sizeof(table_query)); } memset(temp, 0, sizeof(temp)); } + len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_PLAYLIST_MAP]); for (i = 1; i < len; i++) { col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_PLAYLIST_MAP], i); @@ -174,29 +164,40 @@ static int __media_svc_rebuild_view_query(sqlite3 *db_handle, uid_t uid) } memset(temp, 0, sizeof(temp)); } + sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_VIEW_PLAYLIST, MEDIA_SVC_DB_VIEW_PLAYLIST, table_query); ret = _media_svc_sql_query(sql, uid); SQLITE3_SAFE_FREE(sql); - media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - /*create tag_view */ - sflag = false; - memset(table_query, 0, sizeof(table_query)); + return ret; +} + +static int __create_tag_view(uid_t uid) +{ + int ret = MS_MEDIA_ERR_NONE; + bool flag = false; + column_info_s *col_ptr = NULL; + int len = 0; + int i = 0; + char *sql = NULL; + char temp[1024] = {0 ,}; + char table_query[MEDIA_SVC_QUERY_LEN_NORMAL] = {0, }; len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_TAG]); for (i = 1; i < len; i++) { col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_TAG], i); if (col_ptr->is_view) { - if (sflag == true) { + if (flag == true) { snprintf(temp, sizeof(temp), ", tag.%s", col_ptr->name); } else { snprintf(temp, sizeof(temp), "tag.%s", col_ptr->name); - sflag = true; + flag = true; } SAFE_STRLCAT(table_query, temp, sizeof(table_query)); } memset(temp, 0, sizeof(temp)); } + len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_TAG_MAP]); for (i = 1; i < len; i++) { col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_TAG_MAP], i); @@ -219,9 +220,36 @@ static int __media_svc_rebuild_view_query(sqlite3 *db_handle, uid_t uid) } memset(temp, 0, sizeof(temp)); } + sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_VIEW_TAG, MEDIA_SVC_DB_VIEW_TAG, table_query); ret = _media_svc_sql_query(sql, uid); SQLITE3_SAFE_FREE(sql); + + return ret; +} + +static int __media_svc_rebuild_view_query(sqlite3 *db_handle, uid_t uid) +{ + int ret = MS_MEDIA_ERR_NONE; + char *sql = NULL; + + /*drop playlist_view, tag_view */ + sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_DROP_VIEW, MEDIA_SVC_DB_VIEW_PLAYLIST); + ret = _media_svc_sql_query(sql, uid); + SQLITE3_SAFE_FREE(sql); + media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); + + sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_DROP_VIEW, MEDIA_SVC_DB_VIEW_TAG); + ret = _media_svc_sql_query(sql, uid); + SQLITE3_SAFE_FREE(sql); + media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); + + /*create playlist_view */ + ret = __create_playlist_view(uid); + media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); + + /*create tag_view */ + ret = __create_tag_view(uid); media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); return MS_MEDIA_ERR_NONE; @@ -342,101 +370,13 @@ int _media_svc_make_table_query(const char *table_name, media_svc_table_slist_e } /*create view */ - flag = false; - if (tb != NULL && tb->view_name != NULL) { - if (strncmp(table_name, MEDIA_SVC_DB_TABLE_MEDIA, strlen(MEDIA_SVC_DB_TABLE_MEDIA)) == 0) { - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_VIEW_MEDIA, tb->view_name, table_name); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - - } else if (strncmp(table_name, MEDIA_SVC_DB_TABLE_PLAYLIST, strlen(MEDIA_SVC_DB_TABLE_PLAYLIST)) == 0) { - len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_PLAYLIST]); - for (i = 1; i < len; i++) { - col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_PLAYLIST], i); - if (col_ptr->is_view) { - if (flag == true) { - if (strncmp(col_ptr->name, MEDIA_SVC_DB_COLUMN_THUMBNAIL, strlen(MEDIA_SVC_DB_COLUMN_THUMBNAIL)) == 0) - snprintf(temp, sizeof(temp), ", playlist.%s AS p_thumbnail_path", col_ptr->name); - else - snprintf(temp, sizeof(temp), ", playlist.%s", col_ptr->name); - } else { - snprintf(temp, sizeof(temp), "playlist.%s", col_ptr->name); - flag = true; - } - SAFE_STRLCAT(table_query, temp, sizeof(table_query)); - } - memset(temp, 0, sizeof(temp)); - } - len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_PLAYLIST_MAP]); - for (i = 1; i < len; i++) { - col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_PLAYLIST_MAP], i); - if (col_ptr->is_view) { - if (strncmp(col_ptr->name, MEDIA_SVC_DB_COLUMN_MAP_ID, strlen(MEDIA_SVC_DB_COLUMN_MAP_ID)) == 0) - snprintf(temp, sizeof(temp), ", playlist_media_count IS NOT NULL AS playlist_media_count, playlist_map.%s AS pm_id", col_ptr->name); - else - snprintf(temp, sizeof(temp), ", playlist_map.%s", col_ptr->name); - SAFE_STRLCAT(table_query, temp, sizeof(table_query)); - } - memset(temp, 0, sizeof(temp)); - } - - len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_MEDIA]); - for (i = 1; i < len; i++) { - col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_MEDIA], i); - if (col_ptr->is_view) { - snprintf(temp, sizeof(temp), ", media.%s", col_ptr->name); - SAFE_STRLCAT(table_query, temp, sizeof(table_query)); - } - memset(temp, 0, sizeof(temp)); - } - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_VIEW_PLAYLIST, tb->view_name, table_query); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - - } else { - len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_TAG]); - for (i = 1; i < len; i++) { - col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_TAG], i); - if (col_ptr->is_view) { - if (flag == true) { - snprintf(temp, sizeof(temp), ", tag.%s", col_ptr->name); - } else { - snprintf(temp, sizeof(temp), "tag.%s", col_ptr->name); - flag = true; - } - SAFE_STRLCAT(table_query, temp, sizeof(table_query)); - } - memset(temp, 0, sizeof(temp)); - } - len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_TAG_MAP]); - for (i = 1; i < len; i++) { - col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_TAG_MAP], i); - if (col_ptr->is_view) { - if (strncmp(col_ptr->name, MEDIA_SVC_DB_COLUMN_MAP_ID, strlen(MEDIA_SVC_DB_COLUMN_MAP_ID)) == 0) - snprintf(temp, sizeof(temp), ", tag_media_count IS NOT NULL AS tag_media_count, tag_map.%s AS tm_id", col_ptr->name); - else - snprintf(temp, sizeof(temp), ", tag_map.%s", col_ptr->name); - SAFE_STRLCAT(table_query, temp, sizeof(table_query)); - } - memset(temp, 0, sizeof(temp)); - } + if (strncmp(table_name, MEDIA_SVC_DB_TABLE_PLAYLIST, strlen(MEDIA_SVC_DB_TABLE_PLAYLIST)) == 0) { + ret = __create_playlist_view(uid); + media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - len = g_slist_length(column_list[MEDIA_SVC_DB_LIST_MEDIA]); - for (i = 1; i < len; i++) { - col_ptr = g_slist_nth_data(column_list[MEDIA_SVC_DB_LIST_MEDIA], i); - if (col_ptr->is_view) { - snprintf(temp, sizeof(temp), ", media.%s", col_ptr->name); - SAFE_STRLCAT(table_query, temp, sizeof(table_query)); - } - memset(temp, 0, sizeof(temp)); - } - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_VIEW_TAG, tb->view_name, table_query); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - } + } else if (strncmp(table_name, MEDIA_SVC_DB_TABLE_TAG, strlen(MEDIA_SVC_DB_TABLE_TAG)) == 0) { + ret = __create_tag_view(uid); + media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); } return MS_MEDIA_ERR_NONE; diff --git a/src/include/common/media-svc-env.h b/src/include/common/media-svc-env.h index 77440db..0015291 100755 --- a/src/include/common/media-svc-env.h +++ b/src/include/common/media-svc-env.h @@ -112,7 +112,6 @@ extern "C" { #define MEDIA_SVC_DB_QUERY_INDEX "CREATE INDEX IF NOT EXISTS %s on '%s' (%s);" #define MEDIA_SVC_DB_QUERY_TRIGGER "CREATE TRIGGER IF NOT EXISTS '%s' DELETE ON '%s' BEGIN DELETE FROM %s WHERE %s=old.%s;END;" #define MEDIA_SVC_DB_QUERY_TRIGGER_WITH_COUNT "CREATE TRIGGER IF NOT EXISTS '%s' DELETE ON '%s' BEGIN DELETE FROM %s WHERE (SELECT count(*) FROM '%s' WHERE %s=old.%s)=1 AND %s=old.%s;END;" -#define MEDIA_SVC_DB_QUERY_VIEW_MEDIA "CREATE VIEW IF NOT EXISTS %s AS SELECT * from %s;" #define MEDIA_SVC_DB_QUERY_VIEW_PLAYLIST "CREATE VIEW IF NOT EXISTS %s AS SELECT %s FROM playlist \ LEFT OUTER JOIN playlist_map ON playlist.playlist_id = playlist_map.playlist_id \ LEFT OUTER JOIN media ON (playlist_map.media_id = media.media_id AND media.validity=1) \ -- 2.7.4