4 * Copyright (c) 2000 - 2011 Samsung Electronics Co., Ltd. All rights reserved.
6 * Contact: Hyunjun Ko <zzoon.ko@samsung.com>, Haejeong Kim <backto.kim@samsung.com>
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
23 #include <media-util.h>
25 #include "media-svc-env.h"
26 #include "media-svc-debug.h"
27 #include "media-svc-util.h"
28 #include "media-svc-db-utils.h"
29 #include "media-util-err.h"
30 #include "media-util-db.h"
31 #include "media-svc-media.h"
33 static GHashTable *table;
34 static GSList *column_list[DB_LIST_MAX];
54 static void __add_table_info(const char *name,
55 const char *trigger_name,
56 const char *event_table,
57 const char *action_table,
58 const char *view_name)
60 table_info_s *tbl = NULL;
62 if (!STRING_VALID(name))
65 if (STRING_VALID(trigger_name)) {
66 if(!STRING_VALID(event_table) || !STRING_VALID(action_table))
70 tbl = g_new0(table_info_s, 1);
72 if (STRING_VALID(trigger_name)) {
73 tbl->trigger_name = g_strdup(trigger_name);
74 tbl->event_table = g_strdup(event_table);
75 tbl->action_table = g_strdup(action_table);
78 if (STRING_VALID(view_name))
79 tbl->view_name = g_strdup(view_name);
81 g_hash_table_insert(table, (gpointer)name, (gpointer)tbl);
84 static void __add_column_info(GSList **slist,
89 const char *index_name,
94 column_info_s *col = g_new0(column_info_s, 1);
96 col->name = g_strdup(name);
97 col->type = g_strdup(type);
98 col->option = g_strdup(option);
99 col->version = version;
100 col->index_name = g_strdup(index_name);
102 col->is_unique = is_unique;
103 col->is_trigger = is_trigger;
104 col->is_view = is_view;
106 *slist = g_slist_append(*slist, col);
109 static int __create_playlist_view(uid_t uid)
111 int ret = MS_MEDIA_ERR_NONE;
113 column_info_s *col_ptr = NULL;
115 GString *table_query = g_string_new(NULL);
116 media_svc_retvm_if(!table_query, MS_MEDIA_ERR_INTERNAL, "g_string_new failed");
118 for (iter = column_list[DB_LIST_PLAYLIST]; iter; iter = g_slist_next(iter)) {
119 col_ptr = iter->data;
124 if (col_ptr->is_view) {
125 if (table_query->len != 0) {
126 if (strncmp(col_ptr->name, DB_COLUMN_THUMBNAIL, strlen(DB_COLUMN_THUMBNAIL)) == 0)
127 g_string_append_printf(table_query, ", playlist.%s AS p_thumbnail_path", col_ptr->name);
129 g_string_append_printf(table_query, ", playlist.%s", col_ptr->name);
131 g_string_append_printf(table_query, "playlist.%s", col_ptr->name);
136 for (iter = column_list[DB_LIST_PLAYLIST_MAP]; iter; iter = g_slist_next(iter)) {
137 col_ptr = iter->data;
142 if (col_ptr->is_view) {
143 if (strncmp(col_ptr->name, DB_COLUMN_MAP_ID, strlen(DB_COLUMN_MAP_ID)) == 0)
144 g_string_append_printf(table_query, ", playlist_media_count IS NOT NULL AS playlist_media_count, playlist_map.%s AS pm_id", col_ptr->name);
146 g_string_append_printf(table_query, ", playlist_map.%s", col_ptr->name);
150 for (iter = column_list[DB_LIST_MEDIA]; iter; iter = g_slist_next(iter)) {
151 col_ptr = iter->data;
156 if (col_ptr->is_view)
157 g_string_append_printf(table_query, ", media.%s", col_ptr->name);
160 sql = sqlite3_mprintf(DB_QUERY_VIEW_PLAYLIST, DB_VIEW_PLAYLIST, table_query->str);
161 g_string_free(table_query, TRUE);
162 ret = _media_svc_sql_query(sql, uid);
163 SQLITE3_SAFE_FREE(sql);
168 static int __create_tag_view(uid_t uid)
170 int ret = MS_MEDIA_ERR_NONE;
172 column_info_s *col_ptr = NULL;
174 GString *table_query = g_string_new(NULL);
175 media_svc_retvm_if(!table_query, MS_MEDIA_ERR_INTERNAL, "g_string_new failed");
177 for (iter = column_list[DB_LIST_TAG]; iter; iter = g_slist_next(iter)) {
178 col_ptr = iter->data;
183 if (col_ptr->is_view) {
184 if (table_query->len != 0)
185 g_string_append_printf(table_query, ", tag.%s", col_ptr->name);
187 g_string_append_printf(table_query, "tag.%s", col_ptr->name);
191 for (iter = column_list[DB_LIST_TAG_MAP]; iter; iter = g_slist_next(iter)) {
192 col_ptr = iter->data;
197 if (col_ptr->is_view) {
198 if (strncmp(col_ptr->name, DB_COLUMN_MAP_ID, strlen(DB_COLUMN_MAP_ID)) == 0)
199 g_string_append_printf(table_query, ", tag_media_count IS NOT NULL AS tag_media_count, tag_map.%s AS tm_id", col_ptr->name);
201 g_string_append_printf(table_query, ", tag_map.%s", col_ptr->name);
205 for (iter = column_list[DB_LIST_MEDIA]; iter; iter = g_slist_next(iter)) {
206 col_ptr = iter->data;
211 if (col_ptr->is_view)
212 g_string_append_printf(table_query, ", media.%s", col_ptr->name);
215 sql = sqlite3_mprintf(DB_QUERY_VIEW_TAG, DB_VIEW_TAG, table_query->str);
216 g_string_free(table_query, TRUE);
217 ret = _media_svc_sql_query(sql, uid);
218 SQLITE3_SAFE_FREE(sql);
223 static int __drop_views(uid_t uid)
225 int ret = MS_MEDIA_ERR_NONE;
228 sql = sqlite3_mprintf("DROP VIEW IF EXISTS %q;DROP VIEW IF EXISTS %q;DROP VIEW IF EXISTS %q",
229 DB_VIEW_PLAYLIST, DB_VIEW_TAG, DB_VIEW_MEDIA);
231 ret = _media_svc_sql_query(sql, uid);
232 SQLITE3_SAFE_FREE(sql);
237 static int __media_svc_rebuild_view_query(sqlite3 *db_handle, uid_t uid)
239 int ret = MS_MEDIA_ERR_NONE;
241 /*create playlist_view */
242 ret = __create_playlist_view(uid);
243 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
246 ret = __create_tag_view(uid);
247 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
249 return MS_MEDIA_ERR_NONE;
252 int _media_svc_make_table_query(const char *table_name, media_svc_table_slist_e list, uid_t uid)
254 int ret = MS_MEDIA_ERR_NONE;
256 table_info_s *tb = NULL;
257 column_info_s *col_ptr = NULL;
259 GString *table_query = g_string_new(NULL);
260 GString *index_query = g_string_new(NULL);
261 GString *trigger_query = g_string_new(NULL);
262 GString *unique_query = g_string_new(NULL);
264 if (!table_query || !index_query || !trigger_query || !unique_query) {
265 media_svc_error("g_string_new failed");
266 ret = MS_MEDIA_ERR_INTERNAL;
270 tb = g_hash_table_lookup(table, table_name);
272 media_svc_debug("lookup fail.. table name [%s] ", table_name);
273 ret = MS_MEDIA_ERR_INTERNAL;
277 for (iter = column_list[list]; iter; iter = g_slist_next(iter)) {
278 col_ptr = iter->data;
283 if (col_ptr->option) {
284 if (table_query->len != 0)
285 g_string_append_printf(table_query, ", %s %s %s", col_ptr->name, col_ptr->type, col_ptr->option);
287 g_string_append_printf(table_query, "%s %s %s", col_ptr->name, col_ptr->type, col_ptr->option);
289 if (table_query->len != 0)
290 g_string_append_printf(table_query, ", %s %s", col_ptr->name, col_ptr->type);
292 g_string_append_printf(table_query, "%s %s", col_ptr->name, col_ptr->type);
296 if (col_ptr->is_unique) {
297 if (unique_query->len != 0)
298 g_string_append_printf(unique_query, ", %s", col_ptr->name);
300 g_string_append_printf(unique_query, "%s", col_ptr->name);
304 if (col_ptr->index_name)
305 g_string_append_printf(index_query, DB_QUERY_INDEX, col_ptr->index_name, table_name, col_ptr->name);
308 if (col_ptr->is_trigger) {
309 if (STRING_VALID(tb->trigger_name)) {
310 if (strncmp(table_name, DB_TABLE_ALBUM, strlen(DB_TABLE_ALBUM)) == 0) {
311 g_string_append_printf(trigger_query, DB_QUERY_TRIGGER_WITH_COUNT,
312 tb->trigger_name, tb->event_table, tb->action_table, tb->event_table,
313 col_ptr->name, col_ptr->name, col_ptr->name, col_ptr->name);
315 g_string_append_printf(trigger_query, DB_QUERY_TRIGGER,
316 tb->trigger_name, tb->event_table, tb->action_table,
317 col_ptr->name, col_ptr->name);
320 media_svc_error("invalid trigger name");
326 if (unique_query->len > 0)
327 sql = sqlite3_mprintf(DB_QUERY_TABLE_WITH_UNIQUE, table_name, table_query->str, unique_query->str);
329 sql = sqlite3_mprintf(DB_QUERY_TABLE, table_name, table_query->str);
331 ret = _media_svc_sql_query(sql, uid);
332 SQLITE3_SAFE_FREE(sql);
333 if (ret != MS_MEDIA_ERR_NONE)
336 if (index_query->len > 0) {
337 ret = _media_svc_sql_query(index_query->str, uid);
338 if (ret != MS_MEDIA_ERR_NONE)
342 if (trigger_query->len > 0) {
343 ret = _media_svc_sql_query(trigger_query->str, uid);
344 if (ret != MS_MEDIA_ERR_NONE)
349 if (strncmp(table_name, DB_TABLE_PLAYLIST, strlen(DB_TABLE_PLAYLIST)) == 0)
350 ret = __create_playlist_view(uid);
351 else if (strncmp(table_name, DB_TABLE_TAG, strlen(DB_TABLE_TAG)) == 0)
352 ret = __create_tag_view(uid);
356 g_string_free(index_query, TRUE);
358 g_string_free(trigger_query, TRUE);
360 g_string_free(unique_query, TRUE);
362 g_string_free(table_query, TRUE);
367 /* NOTICE : This function will be used someday.. Do not remove.
368 static int __media_svc_upgrade_table_query(sqlite3 *db_handle, const char *table_name, media_svc_table_slist_e list, uid_t uid)
370 int ret = MS_MEDIA_ERR_NONE;
372 column_info_s *col_ptr = NULL;
374 char temp[1024] = {0, };
376 sqlite3_stmt *sql_stmt = NULL;
378 sql = sqlite3_mprintf("PRAGMA user_version");
379 ret = _media_svc_sql_prepare_to_step(db_handle, sql, &sql_stmt);
381 if (ret != MS_MEDIA_ERR_NONE) {
382 media_svc_error("error when get user_version. err = [%d]", ret);
385 cur_version = sqlite3_column_int(sql_stmt, 0);
386 SQLITE3_FINALIZE(sql_stmt);
388 for (iter = column_list[list]; iter; iter = g_slist_next(iter)) {
389 col_ptr = iter->data;
394 if (col_ptr->version > cur_version) {
396 memset(temp, 0, sizeof(temp));
398 snprintf(temp, sizeof(temp), "%s %s %s", col_ptr->name, col_ptr->type, col_ptr->option);
400 snprintf(temp, sizeof(temp), "%s %s", col_ptr->name, col_ptr->type);
401 sql = sqlite3_mprintf(DB_QUERY_ALTER_TABLE, table_name, temp);
402 ret = _media_svc_sql_query(sql, uid);
403 SQLITE3_SAFE_FREE(sql);
404 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
406 if (col_ptr->index_name) {
407 sql = sqlite3_mprintf(DB_QUERY_INDEX, col_ptr->index_name, table_name, col_ptr->name);
408 ret = _media_svc_sql_query(sql, uid);
409 SQLITE3_SAFE_FREE(sql);
410 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
415 return MS_MEDIA_ERR_NONE;
418 static void __media_svc_table_free(gpointer data)
420 table_info_s *tb = (table_info_s *) data;
422 g_free(tb->trigger_name);
423 g_free(tb->view_name);
424 g_free(tb->event_table);
425 g_free(tb->action_table);
429 static void __media_svc_column_free(gpointer data)
431 column_info_s *col = (column_info_s *) data;
436 g_free(col->index_name);
440 int _media_svc_init_table_query(void)
442 int ret = MS_MEDIA_ERR_NONE;
444 /*variable initialize.. */
445 table = g_hash_table_new_full(g_str_hash, g_str_equal, NULL, __media_svc_table_free);
447 /*table specification.. (table_name, trigger name, event table, action table, view name) */
448 __add_table_info(DB_TABLE_MEDIA, NULL, NULL, NULL, NULL);
449 __add_table_info(DB_TABLE_FOLDER, NULL, NULL, NULL, NULL);
450 __add_table_info(DB_TABLE_PLAYLIST_MAP, DB_TRIGGER_PLAYLIST_MAP, DB_TABLE_MEDIA, DB_TABLE_PLAYLIST_MAP, NULL);
451 __add_table_info(DB_TABLE_PLAYLIST, DB_TRIGGER_PLAYLIST_MAP1, DB_TABLE_PLAYLIST, DB_TABLE_PLAYLIST_MAP, DB_VIEW_PLAYLIST);
452 __add_table_info(DB_TABLE_ALBUM, DB_TRIGGER_ALBUM, DB_TABLE_MEDIA, DB_TABLE_ALBUM, NULL);
453 __add_table_info(DB_TABLE_TAG_MAP, DB_TRIGGER_TAG_MAP, DB_TABLE_MEDIA, DB_TABLE_TAG_MAP, NULL);
454 __add_table_info(DB_TABLE_TAG, DB_TRIGGER_TAG_MAP1, DB_TABLE_TAG, DB_TABLE_TAG_MAP, DB_VIEW_TAG);
455 __add_table_info(DB_TABLE_BOOKMARK, DB_TRIGGER_BOOKMARK, DB_TABLE_MEDIA, DB_TABLE_BOOKMARK, NULL);
456 __add_table_info(DB_TABLE_STORAGE, NULL, NULL, NULL, NULL);
457 __add_table_info(DB_TABLE_FACE_SCAN_LIST, DB_TRIGGER_FACE_SCAN_LIST, DB_TABLE_MEDIA, DB_TABLE_FACE_SCAN_LIST, NULL);
458 __add_table_info(DB_TABLE_FACE, DB_TRIGGER_FACE, DB_TABLE_FACE_SCAN_LIST, DB_TABLE_FACE, NULL);
460 /*insert column info.. */
462 __add_column_info(&column_list[DB_LIST_MEDIA], "media_id", DB_TYPE_TEXT, "PRIMARY KEY", USER_V2, NULL, false, false, true);
463 __add_column_info(&column_list[DB_LIST_MEDIA], "media_path", DB_TYPE_TEXT, "NOT NULL UNIQUE", USER_V2, NULL, false, false, true);
464 __add_column_info(&column_list[DB_LIST_MEDIA], "media_display_name", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, false, false, true);
465 __add_column_info(&column_list[DB_LIST_MEDIA], "media_type", DB_TYPE_INT, NULL, USER_V2, NULL, false, false, true);
466 __add_column_info(&column_list[DB_LIST_MEDIA], "media_mime_type", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
467 __add_column_info(&column_list[DB_LIST_MEDIA], "media_size", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
468 __add_column_info(&column_list[DB_LIST_MEDIA], "media_added_time", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
469 __add_column_info(&column_list[DB_LIST_MEDIA], "media_modified_time", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
470 __add_column_info(&column_list[DB_LIST_MEDIA], "folder_id", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, false, false, false);
471 __add_column_info(&column_list[DB_LIST_MEDIA], "media_thumbnail_path", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
472 __add_column_info(&column_list[DB_LIST_MEDIA], "media_title", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
473 __add_column_info(&column_list[DB_LIST_MEDIA], "album_id", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, false);
474 __add_column_info(&column_list[DB_LIST_MEDIA], "media_album", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
475 __add_column_info(&column_list[DB_LIST_MEDIA], "media_artist", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
476 __add_column_info(&column_list[DB_LIST_MEDIA], "media_album_artist", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
477 __add_column_info(&column_list[DB_LIST_MEDIA], "media_genre", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
478 __add_column_info(&column_list[DB_LIST_MEDIA], "media_composer", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
479 __add_column_info(&column_list[DB_LIST_MEDIA], "media_year", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
480 __add_column_info(&column_list[DB_LIST_MEDIA], "media_recorded_date", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
481 __add_column_info(&column_list[DB_LIST_MEDIA], "media_copyright", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
482 __add_column_info(&column_list[DB_LIST_MEDIA], "media_track_num", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
483 __add_column_info(&column_list[DB_LIST_MEDIA], "media_description", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
484 __add_column_info(&column_list[DB_LIST_MEDIA], "media_bitrate", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
485 __add_column_info(&column_list[DB_LIST_MEDIA], "media_bitpersample", DB_TYPE_INT, "DEFAULT 0", USER_V3, NULL, false, false, true);
486 __add_column_info(&column_list[DB_LIST_MEDIA], "media_samplerate", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
487 __add_column_info(&column_list[DB_LIST_MEDIA], "media_channel", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
488 __add_column_info(&column_list[DB_LIST_MEDIA], "media_duration", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
489 __add_column_info(&column_list[DB_LIST_MEDIA], "media_longitude", DB_TYPE_DOUBLE, "DEFAULT 0", USER_V2, NULL, false, false, true);
490 __add_column_info(&column_list[DB_LIST_MEDIA], "media_latitude", DB_TYPE_DOUBLE, "DEFAULT 0", USER_V2, NULL, false, false, true);
491 __add_column_info(&column_list[DB_LIST_MEDIA], "media_altitude", DB_TYPE_DOUBLE, "DEFAULT 0", USER_V2, NULL, false, false, true);
492 __add_column_info(&column_list[DB_LIST_MEDIA], "exposure_time", DB_TYPE_TEXT, NULL, USER_V4, NULL, false, false, true);
493 __add_column_info(&column_list[DB_LIST_MEDIA], "fnumber", DB_TYPE_DOUBLE, "DEFAULT 0", USER_V4, NULL, false, false, true);
494 __add_column_info(&column_list[DB_LIST_MEDIA], "iso", DB_TYPE_INT, "DEFAULT -1", USER_V4, NULL, false, false, true);
495 __add_column_info(&column_list[DB_LIST_MEDIA], "model", DB_TYPE_TEXT, NULL, USER_V4, NULL, false, false, true);
496 __add_column_info(&column_list[DB_LIST_MEDIA], "media_width", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
497 __add_column_info(&column_list[DB_LIST_MEDIA], "media_height", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
498 __add_column_info(&column_list[DB_LIST_MEDIA], "media_datetaken", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
499 __add_column_info(&column_list[DB_LIST_MEDIA], "media_orientation", DB_TYPE_INT, "DEFAULT -1", USER_V2, NULL, false, false, true);
500 __add_column_info(&column_list[DB_LIST_MEDIA], "media_rating", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
501 __add_column_info(&column_list[DB_LIST_MEDIA], "media_favourite", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
502 __add_column_info(&column_list[DB_LIST_MEDIA], "media_is_drm", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
503 __add_column_info(&column_list[DB_LIST_MEDIA], "media_storage_type", DB_TYPE_INT, NULL, USER_V2, NULL, false, false, true);
504 __add_column_info(&column_list[DB_LIST_MEDIA], "media_timeline", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, true);
505 __add_column_info(&column_list[DB_LIST_MEDIA], "media_file_name_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
506 __add_column_info(&column_list[DB_LIST_MEDIA], "media_title_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
507 __add_column_info(&column_list[DB_LIST_MEDIA], "media_album_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
508 __add_column_info(&column_list[DB_LIST_MEDIA], "media_artist_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
509 __add_column_info(&column_list[DB_LIST_MEDIA], "media_album_artist_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
510 __add_column_info(&column_list[DB_LIST_MEDIA], "media_genre_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
511 __add_column_info(&column_list[DB_LIST_MEDIA], "media_composer_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
512 __add_column_info(&column_list[DB_LIST_MEDIA], "media_copyright_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
513 __add_column_info(&column_list[DB_LIST_MEDIA], "media_description_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
514 /* storage_uuid column is added in DB v4. When doing DB upgrade to v4, if storage_uuid is NOT NULL, alter table failed. */
515 __add_column_info(&column_list[DB_LIST_MEDIA], "storage_uuid", DB_TYPE_TEXT, NULL, USER_V4, NULL, false, false, true);
516 __add_column_info(&column_list[DB_LIST_MEDIA], "validity", DB_TYPE_INT, "DEFAULT 1", USER_V2, NULL, false, false, false);
517 /* color column is added with dcm. (DB v5) */
518 __add_column_info(&column_list[DB_LIST_MEDIA], "media_360", DB_TYPE_INT, "DEFAULT 0", USER_V5, NULL, false, false, true);
521 __add_column_info(&column_list[DB_LIST_FOLDER], "folder_id", DB_TYPE_TEXT, "PRIMARY KEY", USER_V2, NULL, false, false, false);
522 __add_column_info(&column_list[DB_LIST_FOLDER], "folder_path", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, true, false, false);
523 __add_column_info(&column_list[DB_LIST_FOLDER], "folder_name", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, false, false, false);
524 __add_column_info(&column_list[DB_LIST_FOLDER], "folder_modified_time", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, false, false, false);
525 __add_column_info(&column_list[DB_LIST_FOLDER], "folder_name_pinyin", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
526 __add_column_info(&column_list[DB_LIST_FOLDER], "folder_storage_type", DB_TYPE_INT, NULL, USER_V2, NULL, false, false, false);
527 /* storage_uuid column is added in DB v4. When doing DB upgrade to v4, if storage_uuid is NOT NULL, alter table failed. */
528 __add_column_info(&column_list[DB_LIST_FOLDER], "storage_uuid", DB_TYPE_TEXT, NULL, USER_V4, NULL, true, false, false);
529 __add_column_info(&column_list[DB_LIST_FOLDER], "validity", DB_TYPE_INT, "DEFAULT 1", USER_V4, NULL, false, false, false);
532 __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V2, NULL, false, false, true);
533 __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "playlist_id", DB_TYPE_INT, "NOT NULL", USER_V2, NULL, false, false, false);
534 __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "media_id", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, false, true, false);
535 __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "playlist_member_order", DB_TYPE_INT, "NOT NULL", USER_V2, NULL, false, false, true);
538 __add_column_info(&column_list[DB_LIST_PLAYLIST], "playlist_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V2, NULL, false, true, true);
539 __add_column_info(&column_list[DB_LIST_PLAYLIST], "playlist_name", DB_TYPE_TEXT, "NOT NULL UNIQUE", USER_V2, NULL, false, false, true);
540 __add_column_info(&column_list[DB_LIST_PLAYLIST], "thumbnail_path", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, true);
543 __add_column_info(&column_list[DB_LIST_ALBUM], "album_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V2, NULL, false, true, false);
544 __add_column_info(&column_list[DB_LIST_ALBUM], "name", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, false, false, false);
545 __add_column_info(&column_list[DB_LIST_ALBUM], "artist", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
546 __add_column_info(&column_list[DB_LIST_ALBUM], "album_art", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
549 __add_column_info(&column_list[DB_LIST_TAG_MAP], "_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V2, NULL, false, false, true);
550 __add_column_info(&column_list[DB_LIST_TAG_MAP], "tag_id", DB_TYPE_INT, "NOT NULL", USER_V2, NULL, true, false, false);
551 __add_column_info(&column_list[DB_LIST_TAG_MAP], "media_id", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, true, true, false);
554 __add_column_info(&column_list[DB_LIST_TAG], "tag_id ", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V2, NULL, false, true, true);
555 __add_column_info(&column_list[DB_LIST_TAG], "tag_name", DB_TYPE_TEXT, "NOT NULL UNIQUE", USER_V2, NULL, false, false, true);
558 __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V2, NULL, false, false, false);
559 __add_column_info(&column_list[DB_LIST_BOOKMARK], "media_id", DB_TYPE_TEXT, "NOT NULL", USER_V2, NULL, true, true, false);
560 __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_marked_time", DB_TYPE_INT, "DEFAULT 0", USER_V2, NULL, true, false, false);
561 __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_thumbnail_path", DB_TYPE_TEXT, NULL, USER_V2, NULL, false, false, false);
562 __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_name", DB_TYPE_TEXT, NULL, USER_V6, NULL, false, false, false);
565 __add_column_info(&column_list[DB_LIST_STORAGE], "storage_id", DB_TYPE_TEXT, "PRIMARY KEY", USER_V3, NULL, false, false, false);
566 __add_column_info(&column_list[DB_LIST_STORAGE], "storage_path", DB_TYPE_TEXT, "NOT NULL", USER_V3, NULL, false, false, false);
567 __add_column_info(&column_list[DB_LIST_STORAGE], "storage_type", DB_TYPE_INT, "DEFAULT 0", USER_V3, NULL, false, false, false);
568 __add_column_info(&column_list[DB_LIST_STORAGE], "validity", DB_TYPE_INT, "DEFAULT 1", USER_V3, NULL, false, false, false);
571 __add_column_info(&column_list[DB_LIST_FACE_SCAN_LIST], "media_id", DB_TYPE_TEXT, "NOT NULL UNIQUE", USER_V4, NULL, false, true, false);
572 __add_column_info(&column_list[DB_LIST_FACE_SCAN_LIST], "modified_time", DB_TYPE_INT, "DEFAULT 0", USER_V6, NULL, false, false, false);
575 __add_column_info(&column_list[DB_LIST_FACE], "media_face_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", USER_V4, NULL, false, false, false);
576 __add_column_info(&column_list[DB_LIST_FACE], "media_id", DB_TYPE_TEXT, "NOT NULL", USER_V4, NULL, true, true, false);
577 __add_column_info(&column_list[DB_LIST_FACE], "face_rect_x", DB_TYPE_INT, "DEFAULT 0", USER_V4, NULL, true, false, false);
578 __add_column_info(&column_list[DB_LIST_FACE], "face_rect_y", DB_TYPE_INT, "DEFAULT 0", USER_V4, NULL, true, false, false);
579 __add_column_info(&column_list[DB_LIST_FACE], "face_rect_w", DB_TYPE_INT, "DEFAULT 0", USER_V4, NULL, true, false, false);
580 __add_column_info(&column_list[DB_LIST_FACE], "face_rect_h", DB_TYPE_INT, "DEFAULT 0", USER_V4, NULL, true, false, false);
581 __add_column_info(&column_list[DB_LIST_FACE], "face_orientation", DB_TYPE_INT, "DEFAULT 0", USER_V4, NULL, false, false, false);
582 __add_column_info(&column_list[DB_LIST_FACE], "media_face_tag", DB_TYPE_TEXT, NULL, USER_V4, NULL, false, false, false);
587 void _media_svc_destroy_table_query(void)
592 g_hash_table_destroy(table);
595 for (i = 0; i < DB_LIST_MAX; i++)
596 g_slist_free_full(column_list[i], __media_svc_column_free);
598 /* NOTICE : This function will be used someday.. Do not remove.
599 static int __alter_table(sqlite3 *db_handle, uid_t uid)
601 int ret = MS_MEDIA_ERR_NONE;
603 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_MEDIA, DB_LIST_MEDIA, uid);
604 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
606 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_PLAYLIST_MAP, DB_LIST_PLAYLIST_MAP, uid);
607 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
609 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_PLAYLIST, DB_LIST_PLAYLIST, uid);
610 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
612 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_ALBUM, DB_LIST_ALBUM, uid);
613 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
615 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_TAG_MAP, DB_LIST_TAG_MAP, uid);
616 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
618 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_TAG, DB_LIST_TAG, uid);
619 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
621 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_BOOKMARK, DB_LIST_BOOKMARK, uid);
622 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
624 ret = __media_svc_upgrade_table_query(db_handle, DB_TABLE_STORAGE, DB_LIST_STORAGE, uid);
625 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "fail to __media_svc_upgrade_table_query [%d]", ret);
630 static void __media_svc_merge_table(gpointer data, gpointer user_data)
632 int ret = MS_MEDIA_ERR_NONE;
633 uid_t uid = GPOINTER_TO_INT(user_data);
634 char *storage_id = (char *)data;
635 char *sql = sqlite3_mprintf("INSERT INTO %q SELECT * FROM %Q;DROP TABLE %Q;", DB_TABLE_MEDIA, storage_id, storage_id);
637 ret = _media_svc_sql_query(sql, uid);
638 SQLITE3_SAFE_FREE(sql);
639 if (ret != MS_MEDIA_ERR_NONE)
640 media_svc_error("Merge failed[%s]", storage_id);
643 static int __media_svc_db_upgrade(sqlite3 *db_handle, int cur_version, uid_t uid)
645 int ret = MS_MEDIA_ERR_NONE;
647 char *storage_id = NULL;
648 sqlite3_stmt *stmt = NULL;
649 GPtrArray *storage_list = NULL;
651 /* Basic upgrade routine
652 1. Drop views (No need if media, playlist, playlist_map, tag, tag_map tables are not changed.)
653 2. Alter table(add columns, remove columns, change naming, and etc.)
654 3. Rebuild views (If views are dropped)
656 media_svc_debug_fenter();
659 ret = __drop_views(uid);
660 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
662 /* Insert items in external tables into media table, and drop external tables (5.5 to 6.0)*/
663 sql = sqlite3_mprintf("SELECT storage_id FROM %q", DB_TABLE_STORAGE);
665 ret = media_db_get_result(db_handle, sql, &stmt);
666 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "Query failed. err[%d]", ret);
668 while (sqlite3_step(stmt) == SQLITE_ROW) {
670 storage_list = g_ptr_array_new_with_free_func(g_free);
672 storage_id = g_strdup((const char *)sqlite3_column_text(stmt, 0));
673 g_ptr_array_add(storage_list, storage_id);
676 SQLITE3_FINALIZE(stmt);
679 g_ptr_array_foreach(storage_list, __media_svc_merge_table, GINT_TO_POINTER(uid));
680 g_ptr_array_free(storage_list, TRUE);
683 /* Rebuilding view */
684 ret = _media_svc_init_table_query();
685 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "_media_svc_init_table_query failed");
686 ret = __media_svc_rebuild_view_query(db_handle, uid);
687 _media_svc_destroy_table_query();
688 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "__media_svc_rebuild_view_query failed");
690 /* Update user version */
691 sql = sqlite3_mprintf("PRAGMA user_version=%d;", LATEST_DB_VERSION);
692 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "Query failed. err[%d]", ret);
693 ret = _media_svc_sql_query(sql, uid);
694 SQLITE3_SAFE_FREE(sql);
696 media_svc_debug_fleave();
701 int _media_svc_sql_query(const char *sql_str, uid_t uid)
703 return media_db_request_update_db(sql_str, uid);
707 #define SLEEP_TIME 1000 * 1000
708 static int __media_svc_query_direct(sqlite3 *handle, const char *query, uid_t uid)
710 int ret = MS_MEDIA_ERR_NONE;
711 char *zErrMsg = NULL;
715 ret = sqlite3_exec(handle, query, NULL, NULL, &zErrMsg);
716 if (SQLITE_OK != ret) {
717 media_svc_sec_error("Error[%s],Query[%s]", zErrMsg, query);
718 SQLITE3_SAFE_FREE(zErrMsg);
719 if (ret == SQLITE_BUSY) {
720 ret = MS_MEDIA_ERR_DB_BUSY_FAIL;
721 } else if (ret == SQLITE_CONSTRAINT) {
722 ret = MS_MEDIA_ERR_DB_CONSTRAINT_FAIL;
723 } else if (ret == SQLITE_FULL) {
724 ret = MS_MEDIA_ERR_DB_FULL_FAIL;
725 } else if (ret == SQLITE_LOCKED) {
726 if (retry_count < MAX_RETRY) {
727 media_svc_error("Locked retry[%d]", retry_count);
732 ret = MS_MEDIA_ERR_DB_INTERNAL;
734 ret = MS_MEDIA_ERR_DB_INTERNAL;
741 int _media_svc_sql_query_direct(const char *sql_str, uid_t uid)
743 int ret = MS_MEDIA_ERR_NONE;
744 sqlite3 *handle = NULL;
746 ret = media_db_connect(&handle, uid, true);
747 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "DB connection failed");
748 ret = __media_svc_query_direct(handle, sql_str, uid);
749 media_db_disconnect(handle);
754 int _media_svc_get_user_version(sqlite3 *db_handle, int *user_version)
756 int ret = MS_MEDIA_ERR_NONE;
757 sqlite3_stmt *sql_stmt = NULL;
758 char *sql = sqlite3_mprintf("PRAGMA user_version;");
760 ret = _media_svc_sql_prepare_to_step(db_handle, sql, &sql_stmt);
761 if (ret != MS_MEDIA_ERR_NONE) {
762 media_svc_error("error when get user_version.");
766 *user_version = sqlite3_column_int(sql_stmt, 0);
767 SQLITE3_FINALIZE(sql_stmt);
769 return MS_MEDIA_ERR_NONE;
772 int _media_svc_sql_prepare_to_step(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
776 media_svc_retvm_if(sql_str == NULL, MS_MEDIA_ERR_INVALID_PARAMETER, "invalid query");
778 if (handle == NULL) {
779 media_svc_error("handle is NULL");
780 sqlite3_free((char *)sql_str);
781 return MS_MEDIA_ERR_INVALID_PARAMETER;
784 media_svc_sec_debug("Query[%s]", sql_str);
786 err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
787 sqlite3_free((char *)sql_str);
789 if (err != SQLITE_OK) {
790 media_svc_error("prepare error %d[%s]", err, sqlite3_errmsg(handle));
791 if (err == SQLITE_CORRUPT)
792 return MS_MEDIA_ERR_DB_CORRUPT;
794 return MS_MEDIA_ERR_DB_INTERNAL;
797 err = sqlite3_step(*stmt);
798 if (err != SQLITE_ROW) {
799 media_svc_debug("No record");
800 SQLITE3_FINALIZE(*stmt);
801 return MS_MEDIA_ERR_DB_NO_RECORD;
804 return MS_MEDIA_ERR_NONE;
807 int _media_svc_sql_prepare_to_step_simple(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
811 media_svc_retvm_if(handle == NULL, MS_MEDIA_ERR_INVALID_PARAMETER, "handle is NULL");
813 media_svc_sec_debug("Query[%s]", sql_str);
815 if (!STRING_VALID(sql_str)) {
816 media_svc_error("invalid query");
817 return MS_MEDIA_ERR_INVALID_PARAMETER;
820 err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
821 sqlite3_free((char *)sql_str);
823 if (err != SQLITE_OK) {
824 media_svc_error("prepare error %d[%s]", err, sqlite3_errmsg(handle));
825 if (err == SQLITE_CORRUPT)
826 return MS_MEDIA_ERR_DB_CORRUPT;
828 return MS_MEDIA_ERR_DB_INTERNAL;
831 return MS_MEDIA_ERR_NONE;
834 int _media_svc_sql_query_list(GList **query_list, uid_t uid)
836 int ret = MS_MEDIA_ERR_NONE;
838 int length = g_list_length(*query_list);
840 char query_bundle[MEDIA_SVC_QUERY_LEN_MAX] = {0, };
844 media_svc_debug("query list length : [%d]", length);
846 memset(query_bundle, 0, sizeof(query_bundle));
848 for (idx = 0; idx < length; idx++) {
849 sql = (char *)g_list_nth_data(*query_list, idx);
850 if (STRING_VALID(sql)) {
851 query_len = strlen(sql);
853 if (query_len >= (sizeof(query_bundle) - 1)) {
854 media_svc_error("NEED TO CHECK FILE : A single query size exceeds 8k [%d]", query_len);
855 SQLITE3_SAFE_FREE(sql);
859 if ((total_len + query_len) >= (sizeof(query_bundle) - 1)) {
860 ret = media_db_request_update_db(query_bundle, uid);
861 if (ret != MS_MEDIA_ERR_NONE)
862 media_svc_error("media_db_request_update_db failed : %d", ret);
864 memset(query_bundle, 0, sizeof(query_bundle));
866 /* NEED TO CHECK : If a single query size is over 8K, drop it. */
867 /* Consider inserting the basic information without metadata */
868 /* Refer : SQLITE3 spec In Tizen 3.0 */
869 /* Single query limit size = 1 billion bytes(1GiB) */
870 /* DB limit size = 1 billion bytes(1GiB) */
871 /* column limit = 2000 */
874 SAFE_STRLCAT(query_bundle, sql, sizeof(query_bundle));
875 total_len += query_len;
877 SQLITE3_SAFE_FREE(sql);
882 ret = media_db_request_update_db(query_bundle, uid);
883 if (ret != MS_MEDIA_ERR_NONE)
884 media_svc_error("media_db_request_update_db failed : %d", ret);
886 memset(query_bundle, 0, sizeof(query_bundle));
890 _media_svc_sql_query_release(query_list);
892 return MS_MEDIA_ERR_NONE;
895 int _media_svc_sql_query_list_direct(GList **query_list, uid_t uid)
897 int ret = MS_MEDIA_ERR_NONE;
899 int length = g_list_length(*query_list);
901 char *zErrMsg = NULL;
902 sqlite3 *handle = NULL;
903 bool with_transaction = true;
905 media_svc_debug("query list length[%d]", length);
909 ret = media_db_connect(&handle, uid, true);
910 media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "DB connection failed");
912 ret = sqlite3_exec(handle, "BEGIN;", NULL, NULL, &zErrMsg);
913 if (SQLITE_OK != ret) {
914 media_svc_sec_error("Transaction failed[%s]. Try an individual insert.", zErrMsg);
915 SQLITE3_SAFE_FREE(zErrMsg);
916 with_transaction = false;
919 for (idx = 0; idx < length; idx++) {
920 sql = (char *)g_list_nth_data(*query_list, idx);
921 if (STRING_VALID(sql)) {
922 ret = __media_svc_query_direct(handle, sql, uid);
923 if (ret != MS_MEDIA_ERR_NONE)
924 media_svc_debug("_media_svc_query_direct failed[%s]", sql);
926 SQLITE3_SAFE_FREE(sql);
930 if (with_transaction) {
931 ret = sqlite3_exec(handle, "COMMIT;", NULL, NULL, &zErrMsg);
932 if (SQLITE_OK != ret) {
933 media_svc_sec_error("Commit failed[%s]", zErrMsg);
934 SQLITE3_SAFE_FREE(zErrMsg);
935 media_db_disconnect(handle);
936 _media_svc_sql_query_release(query_list);
937 return MS_MEDIA_ERR_DB_INTERNAL;
941 media_db_disconnect(handle);
944 _media_svc_sql_query_release(query_list);
946 return MS_MEDIA_ERR_NONE;
949 void _media_svc_sql_query_add(GList **query_list, char **query)
951 *query_list = g_list_append(*query_list, *query);
954 void _media_svc_sql_query_release(GList **query_list)
957 g_list_free(*query_list);
962 int _media_svc_check_db_upgrade(sqlite3 *db_handle, int user_version, uid_t uid)
964 if (user_version < LATEST_DB_VERSION) {
965 media_svc_error("Upgrade media DB from (%d) to (%d)", user_version, LATEST_DB_VERSION);
966 return __media_svc_db_upgrade(db_handle, user_version, uid);
968 return MS_MEDIA_ERR_NONE;