From f13a4e7ae914f6421ec47318ce70ee034679e064 Mon Sep 17 00:00:00 2001 From: Minje Ahn Date: Wed, 22 Jan 2020 13:56:48 +0900 Subject: [PATCH] Clean up code for db upgrade Change-Id: Ib3d524e969a6bc4673ea13c6160c9387ae44d571 Signed-off-by: Minje Ahn --- src/common/media-svc-db-utils.c | 490 ++++++------------------------------- src/common/media-svc.c | 2 +- src/include/common/media-svc-env.h | 13 +- 3 files changed, 87 insertions(+), 418 deletions(-) diff --git a/src/common/media-svc-db-utils.c b/src/common/media-svc-db-utils.c index 85553bb..c4603f4 100755 --- a/src/common/media-svc-db-utils.c +++ b/src/common/media-svc-db-utils.c @@ -30,11 +30,6 @@ #include "media-util-db.h" #include "media-svc-media.h" -static int __media_svc_db_upgrade(sqlite3 *db_handle, int cur_version, uid_t uid); -static int __media_svc_rebuild_view_query(sqlite3 *db_handle, uid_t uid); -static int __media_svc_query_direct(sqlite3 *handle, const char *query, uid_t uid); - - static GHashTable *table; static GSList *column_list[MEDIA_SVC_DB_LIST_MAX]; @@ -339,6 +334,7 @@ ERROR: return ret; } +/* NOTICE : This function will be used someday.. Do not remove. static int __media_svc_upgrade_table_query(sqlite3 *db_handle, const char *table_name, media_svc_table_slist_e list, uid_t uid) { int ret = MS_MEDIA_ERR_NONE; @@ -366,7 +362,7 @@ static int __media_svc_upgrade_table_query(sqlite3 *db_handle, const char *table continue; if (col_ptr->version > cur_version) { - /*alter table */ + // alter table memset(temp, 0, sizeof(temp)); if (col_ptr->option) snprintf(temp, sizeof(temp), "%s %s %s", col_ptr->name, col_ptr->type, col_ptr->option); @@ -376,7 +372,7 @@ static int __media_svc_upgrade_table_query(sqlite3 *db_handle, const char *table ret = _media_svc_sql_query(sql, uid); SQLITE3_SAFE_FREE(sql); media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - /*create index */ + //create index if (col_ptr->index_name) { sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_INDEX, col_ptr->index_name, table_name, col_ptr->name); ret = _media_svc_sql_query(sql, uid); @@ -388,7 +384,7 @@ static int __media_svc_upgrade_table_query(sqlite3 *db_handle, const char *table return MS_MEDIA_ERR_NONE; } - +*/ static void __media_svc_table_free(gpointer data) { table_info_s *tb = (table_info_s *) data; @@ -684,7 +680,7 @@ void _media_svc_destroy_table_query() for (i = 0; i < MEDIA_SVC_DB_LIST_MAX; i++) g_slist_free_full(column_list[i], __media_svc_column_free); } - +/* NOTICE : This function will be used someday.. Do not remove. static int __alter_table(sqlite3 *db_handle, uid_t uid) { int ret = MS_MEDIA_ERR_NONE; @@ -715,403 +711,76 @@ static int __alter_table(sqlite3 *db_handle, uid_t uid) return ret; } - -static int __drop_tmp_table(uid_t uid) -{ - int ret = MS_MEDIA_ERR_NONE; - char *sql = NULL; - - sql = sqlite3_mprintf("DROP TABLE %q;", MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - - return ret; -} - -static int __alter_and_rename_table(const char *table, uid_t uid) -{ - int ret = MS_MEDIA_ERR_NONE; - char *sql = NULL; - - sql = sqlite3_mprintf("ALTER TABLE %q RENAME TO %q;", table, MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - - return ret; -} - -static int __update_db_from_V6_to_V8(uid_t uid) //tizen 4.0 to 5.5 -{ - int ret = MS_MEDIA_ERR_NONE; - char *sql = NULL; - - /******Upgrade Media Table******/ - /* remove user data columns */ - ret = __alter_and_rename_table(MEDIA_SVC_DB_TABLE_MEDIA, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __alter_and_rename_table [%d]", ret); - - ret = _media_svc_make_table_query(MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_LIST_MEDIA, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_make_table_query [%d]", ret); - - sql = sqlite3_mprintf("INSERT INTO %q (media_id, media_path, media_display_name, media_type, media_mime_type, media_size, media_added_time, media_modified_time, folder_id, media_thumbnail_path, media_title, album_id, media_album, media_artist, media_album_artist, media_genre, media_composer, media_year, media_recorded_date, media_copyright, media_track_num, media_description, media_bitrate, media_bitpersample, media_samplerate, media_channel, media_duration, media_longitude, media_latitude, media_altitude, exposure_time, fnumber, iso, model, media_width, media_height, media_datetaken, media_orientation, media_rating, media_favourite, media_is_drm, media_storage_type, media_timeline, sync_status, media_file_name_pinyin, media_title_pinyin, media_album_pinyin, media_artist_pinyin, media_album_artist_pinyin, media_genre_pinyin, media_composer_pinyin, media_copyright_pinyin, media_description_pinyin, age_rating_pinyin, keyword_pinyin, storage_uuid, validity, media_360) \ - SELECT media_uuid, path, file_name, media_type, mime_type, size, added_time, modified_time, folder_uuid, thumbnail_path, title, album_id, album, artist, album_artist, genre, composer, year, recorded_date, copyright, track_num, description, bitrate, bitpersample, samplerate, channel, duration, longitude, latitude, altitude, exposure_time, fnumber, iso, model, width, height, datetaken, orientation, rating, favourite, is_drm, storage_type, timeline, sync_status, file_name_pinyin, title_pinyin, album_pinyin, artist_pinyin, album_artist_pinyin, genre_pinyin, composer_pinyin, copyright_pinyin, description_pinyin, age_rating_pinyin, keyword_pinyin, storage_uuid, validity, is_360 FROM %q;", - MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - ret = __drop_tmp_table(uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __drop_tmp_table [%d]", ret); - - /******Upgrade Folder Table******/ - /* remove "scan_status" column */ - ret = __alter_and_rename_table(MEDIA_SVC_DB_TABLE_FOLDER, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __alter_and_rename_table [%d]", ret); - - ret = _media_svc_make_table_query(MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_LIST_FOLDER, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_make_table_query [%d]", ret); - - sql = sqlite3_mprintf("INSERT INTO %q (folder_id, folder_path, folder_name, folder_modified_time, folder_name_pinyin, folder_storage_type, storage_uuid, validity) SELECT folder_uuid, path, name, modified_time, name_pinyin, storage_type, storage_uuid, validity FROM %q;", MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - ret = __drop_tmp_table(uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __drop_tmp_table [%d]", ret); - - /******Upgrade Storage Table******/ - /* remove "scan_status", "storage_name" column */ - ret = __alter_and_rename_table(MEDIA_SVC_DB_TABLE_STORAGE, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __alter_and_rename_table [%d]", ret); - - ret = _media_svc_make_table_query(MEDIA_SVC_DB_TABLE_STORAGE, MEDIA_SVC_DB_LIST_STORAGE, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_make_table_query [%d]", ret); - - sql = sqlite3_mprintf("INSERT INTO %q (storage_id, storage_path, storage_type, validity) SELECT storage_uuid, storage_path, storage_type, validity FROM %q;", MEDIA_SVC_DB_TABLE_STORAGE, MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - ret = __drop_tmp_table(uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __drop_tmp_table [%d]", ret); - - /******Upgrade face Table******/ - /* Change "face_uuid" to "face_id" (face_uuid is TEXT, face_id is INTEGER) */ - ret = __alter_and_rename_table(MEDIA_SVC_DB_TABLE_FACE, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __alter_and_rename_table [%d]", ret); - - ret = _media_svc_make_table_query(MEDIA_SVC_DB_TABLE_FACE, MEDIA_SVC_DB_LIST_FACE, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_make_table_query [%d]", ret); - - sql = sqlite3_mprintf("INSERT INTO %q (media_id, face_rect_x, face_rect_y, face_rect_w, face_rect_h, face_orientation, media_face_tag) SELECT media_uuid, face_rect_x, face_rect_y, face_rect_w, face_rect_h, orientation, face_tag FROM %q;", MEDIA_SVC_DB_TABLE_FACE, MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - ret = __drop_tmp_table(uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __drop_tmp_table [%d]", ret); - - /******Upgrade face_scan_list Table******/ - /* Drop trigger first */ - sql = sqlite3_mprintf("DROP TRIGGER IF EXISTS %q_%q;DROP TRIGGER IF EXISTS %q_%q;", - MEDIA_SVC_DB_TRIGGER_FACE, MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, MEDIA_SVC_DB_TRIGGER_FACE_SCAN_LIST, MEDIA_SVC_DB_TABLE_MEDIA); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - /* remove "storage_uuid" column */ - ret = __alter_and_rename_table(MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __alter_and_rename_table [%d]", ret); - - ret = _media_svc_make_table_query(MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, MEDIA_SVC_DB_LIST_FACE_SCAN_LIST, uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_make_table_query [%d]", ret); - - sql = sqlite3_mprintf("INSERT INTO %q (media_id, modified_time) SELECT media_uuid, modified_time FROM %q;", MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, MEDIA_SVC_DB_TABLE_TMP_TABLE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - ret = __drop_tmp_table(uid); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __drop_tmp_table [%d]", ret); - - return ret; -} - -static int __update_db_to_latest(int cur_version, uid_t uid) //tizen 5.0(and before) to 5.5 +*/ +static void __media_svc_merge_table(gpointer data, gpointer user_data) { int ret = MS_MEDIA_ERR_NONE; - char *sql = NULL; - - if (cur_version == USER_V6) { - /* Drop trigger first */ - sql = sqlite3_mprintf("DROP TRIGGER IF EXISTS %q_%q;DROP TRIGGER IF EXISTS %q_%q;", - MEDIA_SVC_DB_TRIGGER_FACE, MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, MEDIA_SVC_DB_TRIGGER_FACE_SCAN_LIST, MEDIA_SVC_DB_TABLE_MEDIA); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - } - - /*playlist */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN play_order TO playlist_member_order;\ - ALTER TABLE %q RENAME COLUMN name TO playlist_name;\ - ALTER TABLE %q RENAME COLUMN media_uuid TO media_id;", - MEDIA_SVC_DB_TABLE_PLAYLIST_MAP, MEDIA_SVC_DB_TABLE_PLAYLIST, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - /*tag */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN name TO tag_name;\ - ALTER TABLE %q RENAME COLUMN media_uuid TO media_id;", - MEDIA_SVC_DB_TABLE_TAG, MEDIA_SVC_DB_TABLE_TAG_MAP); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - /*bookmark */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN marked_time TO bookmark_marked_time;\ - ALTER TABLE %q RENAME COLUMN name TO bookmark_name;\ - ALTER TABLE %q RENAME COLUMN thumbnail_path TO bookmark_thumbnail_path;\ - ALTER TABLE %q RENAME COLUMN media_uuid TO media_id;", - MEDIA_SVC_DB_TABLE_BOOKMARK, MEDIA_SVC_DB_TABLE_BOOKMARK, - MEDIA_SVC_DB_TABLE_BOOKMARK, MEDIA_SVC_DB_TABLE_BOOKMARK); + uid_t uid = GPOINTER_TO_INT(user_data); + char *storage_id = (char *)data; + char *sql = sqlite3_mprintf("INSERT INTO %q SELECT * FROM %Q;DROP TABLE %Q;", MEDIA_SVC_DB_TABLE_MEDIA, storage_id, storage_id); ret = _media_svc_sql_query(sql, uid); SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - if (cur_version == USER_V7) { - /*face */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN face_id TO media_face_id;\ - ALTER TABLE %q RENAME COLUMN orientation TO face_orientation;\ - ALTER TABLE %q RENAME COLUMN face_tag TO media_face_tag;\ - ALTER TABLE %q RENAME COLUMN media_uuid TO media_id;\ - ALTER TABLE %q RENAME COLUMN media_uuid TO media_id;", - MEDIA_SVC_DB_TABLE_FACE, MEDIA_SVC_DB_TABLE_FACE, MEDIA_SVC_DB_TABLE_FACE, - MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, MEDIA_SVC_DB_TABLE_FACE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - /*folder */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN modified_time TO folder_modified_time;\ - ALTER TABLE %q RENAME COLUMN path TO folder_path;\ - ALTER TABLE %q RENAME COLUMN name TO folder_name;\ - ALTER TABLE %q RENAME COLUMN name_pinyin TO folder_name_pinyin;\ - ALTER TABLE %q RENAME COLUMN storage_type TO folder_storage_type;\ - ALTER TABLE %q RENAME COLUMN folder_uuid TO folder_id;", - MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_FOLDER, - MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_FOLDER); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - /*storage */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN storage_uuid TO storage_id;", - MEDIA_SVC_DB_TABLE_STORAGE); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - - /*media */ - sql = sqlite3_mprintf("ALTER TABLE %q RENAME COLUMN file_name_pinyin TO media_file_name_pinyin;\ - ALTER TABLE %q RENAME COLUMN title_pinyin TO media_title_pinyin;\ - ALTER TABLE %q RENAME COLUMN album_pinyin TO media_album_pinyin;\ - ALTER TABLE %q RENAME COLUMN artist_pinyin TO media_artist_pinyin;\ - ALTER TABLE %q RENAME COLUMN album_artist_pinyin TO media_album_artist_pinyin;\ - ALTER TABLE %q RENAME COLUMN genre_pinyin TO media_genre_pinyin;\ - ALTER TABLE %q RENAME COLUMN composer_pinyin TO media_composer_pinyin;\ - ALTER TABLE %q RENAME COLUMN copyright_pinyin TO media_copyright_pinyin;\ - ALTER TABLE %q RENAME COLUMN description_pinyin TO media_description_pinyin;\ - ALTER TABLE %q RENAME COLUMN folder_uuid TO folder_id;\ - ALTER TABLE %q RENAME COLUMN media_uuid TO media_id;\ - ALTER TABLE %q RENAME COLUMN path TO media_path;\ - ALTER TABLE %q RENAME COLUMN file_name TO media_display_name;\ - ALTER TABLE %q RENAME COLUMN mime_type TO media_mime_type;\ - ALTER TABLE %q RENAME COLUMN size TO media_size;\ - ALTER TABLE %q RENAME COLUMN added_time TO media_added_time;\ - ALTER TABLE %q RENAME COLUMN modified_time TO media_modified_time;\ - ALTER TABLE %q RENAME COLUMN thumbnail_path TO media_thumbnail_path;\ - ALTER TABLE %q RENAME COLUMN title TO media_title;\ - ALTER TABLE %q RENAME COLUMN album TO media_album;\ - ALTER TABLE %q RENAME COLUMN artist TO media_artist;\ - ALTER TABLE %q RENAME COLUMN album_artist TO media_album_artist;\ - ALTER TABLE %q RENAME COLUMN genre TO media_genre;\ - ALTER TABLE %q RENAME COLUMN composer TO media_composer;\ - ALTER TABLE %q RENAME COLUMN year TO media_year;\ - ALTER TABLE %q RENAME COLUMN recorded_date TO media_recorded_date;\ - ALTER TABLE %q RENAME COLUMN copyright TO media_copyright;\ - ALTER TABLE %q RENAME COLUMN track_num TO media_track_num;\ - ALTER TABLE %q RENAME COLUMN description TO media_description;\ - ALTER TABLE %q RENAME COLUMN bitrate TO media_bitrate;\ - ALTER TABLE %q RENAME COLUMN bitpersample TO media_bitpersample;\ - ALTER TABLE %q RENAME COLUMN samplerate TO media_samplerate;\ - ALTER TABLE %q RENAME COLUMN channel TO media_channel;\ - ALTER TABLE %q RENAME COLUMN duration TO media_duration;\ - ALTER TABLE %q RENAME COLUMN longitude TO media_longitude;\ - ALTER TABLE %q RENAME COLUMN latitude TO media_latitude;\ - ALTER TABLE %q RENAME COLUMN altitude TO media_altitude;\ - ALTER TABLE %q RENAME COLUMN width TO media_width;\ - ALTER TABLE %q RENAME COLUMN height TO media_height;\ - ALTER TABLE %q RENAME COLUMN datetaken TO media_datetaken;\ - ALTER TABLE %q RENAME COLUMN orientation TO media_orientation;\ - ALTER TABLE %q RENAME COLUMN rating TO media_rating;\ - ALTER TABLE %q RENAME COLUMN favourite TO media_favourite;\ - ALTER TABLE %q RENAME COLUMN is_drm TO media_is_drm;\ - ALTER TABLE %q RENAME COLUMN storage_type TO media_storage_type;\ - ALTER TABLE %q RENAME COLUMN timeline TO media_timeline;\ - ALTER TABLE %q RENAME COLUMN is_360 TO media_360;", - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA, - MEDIA_SVC_DB_TABLE_MEDIA); - - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to _media_svc_sql_query [%d]", ret); - } - - return ret; + if (ret != MS_MEDIA_ERR_NONE) + media_svc_error("Merge failed[%s]", storage_id); } static int __media_svc_db_upgrade(sqlite3 *db_handle, int cur_version, uid_t uid) { int ret = MS_MEDIA_ERR_NONE; char *sql = NULL; - + char *storage_id = NULL; + sqlite3_stmt *stmt = NULL; + GPtrArray *storage_list = NULL; + + /* Basic upgrade routine + 1. Drop views (No need if media, playlist, playlist_map, tag, tag_map tables are not changed.) + 2. Alter table(add columns, remove columns, change naming, and etc.) + 3. Rebuild views (If views are dropped) + */ media_svc_debug_fenter(); - ret = _media_svc_init_table_query(MEDIA_SVC_DB_TABLE_MEDIA); - if (ret != MS_MEDIA_ERR_NONE) { - media_svc_error("Query initialization failed [%d]", ret); - goto ERROR; - } - - ret = __alter_table(db_handle, uid); - if (ret != MS_MEDIA_ERR_NONE) { - media_svc_error("fail to __alter_table [%d]", ret); - goto ERROR; - } - - /*drop media_view, playlist_view, tag_view */ + /* drop view */ ret = __drop_views(uid); media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret); - /* Upgrade issue tizen 4.0 to 5.5 */ - if (cur_version == USER_V6) { - ret = __update_db_from_V6_to_V8(uid); - if (ret != MS_MEDIA_ERR_NONE) { - media_svc_error("fail to __update_db_from_V6_to_V8 [%d]", ret); - goto ERROR; - } - } + /* Insert items in external tables into media table, and drop external tables (5.5 to 6.0)*/ + sql = sqlite3_mprintf("SELECT storage_id FROM %q", MEDIA_SVC_DB_TABLE_STORAGE); - /* Upgrade issue tizen 5.0 to 5.5 */ - if (cur_version < USER_V8) { - ret = __update_db_to_latest(cur_version, uid); - if (ret != MS_MEDIA_ERR_NONE) { - media_svc_error("fail to __update_db_from_V6_to_V8 [%d]", ret); - goto ERROR; - } - } + ret = media_db_get_result(db_handle, sql, &stmt); + media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "Query failed. err[%d]", ret); - if (cur_version == USER_V6) { - /* Create the removed trigger */ - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_TRIGGER, "face_cleanup_face_scan_list", MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, MEDIA_SVC_DB_TABLE_FACE, "media_id", "media_id"); + while (sqlite3_step(stmt) == SQLITE_ROW) { + if (!storage_list) + storage_list = g_ptr_array_new_with_free_func(g_free); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - if (ret != MS_MEDIA_ERR_NONE) { - media_svc_error("_media_svc_sql_query failed"); - goto ERROR; - } + storage_id = g_strdup((const char *)sqlite3_column_text(stmt, 0)); + g_ptr_array_add(storage_list, storage_id); + } - sql = sqlite3_mprintf(MEDIA_SVC_DB_QUERY_TRIGGER, "face_scan_list_cleanup_media", MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_FACE_SCAN_LIST, "media_id", "media_id"); + SQLITE3_FINALIZE(stmt); - ret = _media_svc_sql_query(sql, uid); - SQLITE3_SAFE_FREE(sql); - if (ret != MS_MEDIA_ERR_NONE) { - media_svc_error("_media_svc_sql_query failed"); - goto ERROR; - } + if (storage_list) { + g_ptr_array_foreach(storage_list, __media_svc_merge_table, GINT_TO_POINTER(uid)); + g_ptr_array_free(storage_list, TRUE); } /* Rebuilding view */ + ret = _media_svc_init_table_query(MEDIA_SVC_DB_TABLE_MEDIA); + media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "_media_svc_init_table_query failed"); ret = __media_svc_rebuild_view_query(db_handle, uid); + _media_svc_destroy_table_query(); + media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "__media_svc_rebuild_view_query failed"); /* Update user version */ - sql = sqlite3_mprintf("PRAGMA user_version=%d;", LATEST_VERSION_NUMBER); - + sql = sqlite3_mprintf("PRAGMA user_version=%d;", LATEST_DB_VERSION); + media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "Query failed. err[%d]", ret); ret = _media_svc_sql_query(sql, uid); SQLITE3_SAFE_FREE(sql); - _media_svc_destroy_table_query(); - media_svc_debug_fleave(); return ret; - -ERROR: - _media_svc_destroy_table_query(); - - return ret; } int _media_svc_sql_query(const char *sql_str, uid_t uid) @@ -1119,6 +788,41 @@ int _media_svc_sql_query(const char *sql_str, uid_t uid) return media_db_request_update_db(sql_str, uid); } +#define MAX_RETRY 9 +#define SLEEP_TIME 1000 * 1000 +static int __media_svc_query_direct(sqlite3 *handle, const char *query, uid_t uid) +{ + int ret = MS_MEDIA_ERR_NONE; + char *zErrMsg = NULL; + int retry_count = 0; + +EXEC_RETRY: + ret = sqlite3_exec(handle, query, NULL, NULL, &zErrMsg); + if (SQLITE_OK != ret) { + media_svc_sec_error("Error[%s],Query[%s]", zErrMsg, query); + SQLITE3_SAFE_FREE(zErrMsg); + if (ret == SQLITE_BUSY) { + ret = MS_MEDIA_ERR_DB_BUSY_FAIL; + } else if (ret == SQLITE_CONSTRAINT) { + ret = MS_MEDIA_ERR_DB_CONSTRAINT_FAIL; + } else if (ret == SQLITE_FULL) { + ret = MS_MEDIA_ERR_DB_FULL_FAIL; + } else if (ret == SQLITE_LOCKED) { + if (retry_count < MAX_RETRY) { + media_svc_error("Locked retry[%d]", retry_count); + retry_count++; + usleep(SLEEP_TIME); + goto EXEC_RETRY; + } + ret = MS_MEDIA_ERR_DB_INTERNAL; + } else { + ret = MS_MEDIA_ERR_DB_INTERNAL; + } + } + + return ret; +} + int _media_svc_sql_query_direct(const char *sql_str, uid_t uid) { int ret = MS_MEDIA_ERR_NONE; @@ -1271,42 +975,6 @@ int _media_svc_sql_query_list(GList **query_list, uid_t uid) _media_svc_sql_query_release(query_list); return MS_MEDIA_ERR_NONE; - -} - -#define MAX_RETRY 9 -#define SLEEP_TIME 1000 * 1000 -static int __media_svc_query_direct(sqlite3 *handle, const char *query, uid_t uid) -{ - int ret = MS_MEDIA_ERR_NONE; - char *zErrMsg = NULL; - int retry_count = 0; - -EXEC_RETRY: - ret = sqlite3_exec(handle, query, NULL, NULL, &zErrMsg); - if (SQLITE_OK != ret) { - media_svc_sec_error("Error[%s],Query[%s]", zErrMsg, query); - SQLITE3_SAFE_FREE(zErrMsg); - if (ret == SQLITE_BUSY) { - ret = MS_MEDIA_ERR_DB_BUSY_FAIL; - } else if (ret == SQLITE_CONSTRAINT) { - ret = MS_MEDIA_ERR_DB_CONSTRAINT_FAIL; - } else if (ret == SQLITE_FULL) { - ret = MS_MEDIA_ERR_DB_FULL_FAIL; - } else if (ret == SQLITE_LOCKED) { - if (retry_count < MAX_RETRY) { - media_svc_error("Locked retry[%d]", retry_count); - retry_count++; - usleep(SLEEP_TIME); - goto EXEC_RETRY; - } - ret = MS_MEDIA_ERR_DB_INTERNAL; - } else { - ret = MS_MEDIA_ERR_DB_INTERNAL; - } - } - - return ret; } int _media_svc_sql_query_list_direct(GList **query_list, uid_t uid) @@ -1358,8 +1026,8 @@ void _media_svc_sql_query_release(GList **query_list) int _media_svc_check_db_upgrade(sqlite3 *db_handle, int user_version, uid_t uid) { - if (user_version < LATEST_VERSION_NUMBER) { - media_svc_error("Current DB is out of date(%d).. So start to upgrade DB(%d)", user_version, LATEST_VERSION_NUMBER); + if (user_version < LATEST_DB_VERSION) { + media_svc_error("Current DB is out of date(%d).. So start to upgrade DB(%d)", user_version, LATEST_DB_VERSION); return __media_svc_db_upgrade(db_handle, user_version, uid); } else { return MS_MEDIA_ERR_NONE; diff --git a/src/common/media-svc.c b/src/common/media-svc.c index bb1a96c..3fc5c8e 100755 --- a/src/common/media-svc.c +++ b/src/common/media-svc.c @@ -137,7 +137,7 @@ int media_svc_create_table(uid_t uid) goto ERROR; } - sql = sqlite3_mprintf("pragma user_version = %d;", LATEST_VERSION_NUMBER); + sql = sqlite3_mprintf("pragma user_version = %d;", LATEST_DB_VERSION); ret = _media_svc_sql_query(sql, uid); if (ret != MS_MEDIA_ERR_NONE) { media_svc_error("user_version update fail."); diff --git a/src/include/common/media-svc-env.h b/src/include/common/media-svc-env.h index 6695157..59580ce 100755 --- a/src/include/common/media-svc-env.h +++ b/src/include/common/media-svc-env.h @@ -31,12 +31,6 @@ extern "C" { #endif /** - * DB information - */ - -#define LATEST_VERSION_NUMBER 8 - -/** * Notice : New table user version -1 : alter table issue */ #define USER_V1 1 @@ -47,6 +41,13 @@ extern "C" { #define USER_V6 6 /** Tizen 4.0*/ #define USER_V7 7 /** Tizen 5.0*/ #define USER_V8 8 /** Tizen 5.5*/ +#define USER_V9 9 /** Tizen 6.0*/ + +/** + * DB information + */ + +#define LATEST_DB_VERSION USER_V9 /** -- 2.7.4