src/media-svc-db-utils.c

   1 /*
   2  * libmedia-service
   3  *
   4  * Copyright (c) 2000 - 2011 Samsung Electronics Co., Ltd. All rights reserved.
   5  *
   6  * Licensed under the Apache License, Version 2.0 (the "License");
   7  * you may not use this file except in compliance with the License.
   8  * You may obtain a copy of the License at
   9  *
  10  * http://www.apache.org/licenses/LICENSE-2.0
  11  *
  12  * Unless required by applicable law or agreed to in writing, software
  13  * distributed under the License is distributed on an "AS IS" BASIS,
  14  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  15  * See the License for the specific language governing permissions and
  16  * limitations under the License.
  17  *
  18  */
  19
  20 #include <unistd.h>
  21 #include <media-util-db.h>
  22 #include <errno.h>
  23
  24 #include "media-svc-env.h"
  25 #include "media-svc-debug.h"
  26 #include "media-svc-util.h"
  27 #include "media-svc-db-utils.h"
  28 #include "media-svc-media.h"
  29
  30 static GHashTable *table;
  31 static GSList *column_list[DB_LIST_MAX];
  32
  33 typedef struct {
  34         char *trigger_name;
  35         char *view_name;
  36         char *event_table;
  37         char *action_table;
  38 } table_info_s;
  39
  40 typedef struct {
  41         char *name;
  42         char *type;
  43         char *option;
  44         bool is_unique;
  45         bool is_trigger;
  46         bool is_view;
  47 } column_info_s;
  48
  49 static void __add_table_info(const char *name,
  50                                                         const char *trigger_name,
  51                                                         const char *event_table,
  52                                                         const char *action_table,
  53                                                         const char *view_name)
  54 {
  55         table_info_s *tbl = NULL;
  56
  57         if (!name)
  58                 return;
  59
  60         if (trigger_name) {
  61                 if(!event_table || !action_table)
  62                         return;
  63         }
  64
  65         tbl = g_new0(table_info_s, 1);
  66
  67         if (trigger_name) {
  68                 tbl->trigger_name = g_strdup(trigger_name);
  69                 tbl->event_table = g_strdup(event_table);
  70                 tbl->action_table = g_strdup(action_table);
  71         }
  72
  73         if (view_name)
  74                 tbl->view_name = g_strdup(view_name);
  75
  76         g_hash_table_insert(table, (gpointer)name, (gpointer)tbl);
  77 }
  78
  79 static void __add_column_info(GSList **slist,
  80                                                                 const char *name,
  81                                                                 const char *type,
  82                                                                 const char *option,
  83                                                                 bool is_unique,
  84                                                                 bool is_trigger,
  85                                                                 bool is_view)
  86 {
  87         column_info_s *col = g_new0(column_info_s, 1);
  88
  89         col->name = g_strdup(name);
  90         col->type = g_strdup(type);
  91         col->option = g_strdup(option);
  92
  93         col->is_unique = is_unique;
  94         col->is_trigger = is_trigger;
  95         col->is_view = is_view;
  96
  97         *slist = g_slist_append(*slist, col);
  98 }
  99
 100 static int __create_playlist_view(uid_t uid)
 101 {
 102         int ret = MS_MEDIA_ERR_NONE;
 103         GSList *iter = NULL;
 104         column_info_s *col_ptr = NULL;
 105         char *sql = NULL;
 106         GString *table_query = g_string_new(NULL);
 107         media_svc_retvm_if(!table_query, MS_MEDIA_ERR_INTERNAL, "g_string_new failed");
 108
 109         for (iter = column_list[DB_LIST_PLAYLIST]; iter; iter = g_slist_next(iter)) {
 110                 col_ptr = iter->data;
 111
 112                 if (!col_ptr)
 113                         continue;
 114
 115                 if (col_ptr->is_view) {
 116                         if (table_query->len != 0) {
 117                                 if (strncmp(col_ptr->name, DB_COLUMN_THUMBNAIL, strlen(DB_COLUMN_THUMBNAIL)) == 0)
 118                                         g_string_append_printf(table_query, ", playlist.%s AS p_thumbnail_path", col_ptr->name);
 119                                 else
 120                                         g_string_append_printf(table_query, ", playlist.%s", col_ptr->name);
 121                         } else {
 122                                 g_string_append_printf(table_query, "playlist.%s", col_ptr->name);
 123                         }
 124                 }
 125         }
 126
 127         for (iter = column_list[DB_LIST_PLAYLIST_MAP]; iter; iter = g_slist_next(iter)) {
 128                 col_ptr = iter->data;
 129
 130                 if (!col_ptr)
 131                         continue;
 132
 133                 if (col_ptr->is_view) {
 134                         if (strncmp(col_ptr->name, DB_COLUMN_MAP_ID, strlen(DB_COLUMN_MAP_ID)) == 0)
 135                                 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);
 136                         else
 137                                 g_string_append_printf(table_query, ", playlist_map.%s", col_ptr->name);
 138                 }
 139         }
 140
 141         for (iter = column_list[DB_LIST_MEDIA]; iter; iter = g_slist_next(iter)) {
 142                 col_ptr = iter->data;
 143
 144                 if (!col_ptr)
 145                         continue;
 146
 147                 if (col_ptr->is_view)
 148                         g_string_append_printf(table_query, ", media.%s", col_ptr->name);
 149         }
 150
 151         sql = sqlite3_mprintf(DB_QUERY_VIEW_PLAYLIST, DB_VIEW_PLAYLIST, table_query->str);
 152         g_string_free(table_query, TRUE);
 153         ret = _media_svc_sql_query(sql, uid);
 154         SQLITE3_SAFE_FREE(sql);
 155
 156         return ret;
 157 }
 158
 159 static int __create_tag_view(uid_t uid)
 160 {
 161         int ret = MS_MEDIA_ERR_NONE;
 162         GSList *iter = NULL;
 163         column_info_s *col_ptr = NULL;
 164         char *sql = NULL;
 165         GString *table_query = g_string_new(NULL);
 166         media_svc_retvm_if(!table_query, MS_MEDIA_ERR_INTERNAL, "g_string_new failed");
 167
 168         for (iter = column_list[DB_LIST_TAG]; iter; iter = g_slist_next(iter)) {
 169                 col_ptr = iter->data;
 170
 171                 if (!col_ptr)
 172                         continue;
 173
 174                 if (col_ptr->is_view) {
 175                         if (table_query->len != 0)
 176                                 g_string_append_printf(table_query, ", tag.%s", col_ptr->name);
 177                         else
 178                                 g_string_append_printf(table_query, "tag.%s", col_ptr->name);
 179                 }
 180         }
 181
 182         for (iter = column_list[DB_LIST_TAG_MAP]; iter; iter = g_slist_next(iter)) {
 183                 col_ptr = iter->data;
 184
 185                 if (!col_ptr)
 186                         continue;
 187
 188                 if (col_ptr->is_view) {
 189                         if (strncmp(col_ptr->name, DB_COLUMN_MAP_ID, strlen(DB_COLUMN_MAP_ID)) == 0)
 190                                 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);
 191                         else
 192                                 g_string_append_printf(table_query, ", tag_map.%s", col_ptr->name);
 193                 }
 194         }
 195
 196         for (iter = column_list[DB_LIST_MEDIA]; iter; iter = g_slist_next(iter)) {
 197                 col_ptr = iter->data;
 198
 199                 if (!col_ptr)
 200                         continue;
 201
 202                 if (col_ptr->is_view)
 203                         g_string_append_printf(table_query, ", media.%s", col_ptr->name);
 204         }
 205
 206         sql = sqlite3_mprintf(DB_QUERY_VIEW_TAG, DB_VIEW_TAG, table_query->str);
 207         g_string_free(table_query, TRUE);
 208         ret = _media_svc_sql_query(sql, uid);
 209         SQLITE3_SAFE_FREE(sql);
 210
 211         return ret;
 212 }
 213
 214 int _media_svc_make_table_query(const char *table_name, media_svc_table_slist_e list, uid_t uid)
 215 {
 216         int ret = MS_MEDIA_ERR_NONE;
 217         GSList *iter = NULL;
 218         table_info_s *tb = NULL;
 219         column_info_s *col_ptr = NULL;
 220         char *sql = NULL;
 221         GString *table_query = g_string_new(NULL);
 222         GString *trigger_query = g_string_new(NULL);
 223         GString *unique_query = g_string_new(NULL);
 224
 225         if (!table_query || !trigger_query || !unique_query) {
 226                 media_svc_error("g_string_new failed");
 227                 ret = MS_MEDIA_ERR_INTERNAL;
 228                 goto ERROR;
 229         }
 230
 231         tb = g_hash_table_lookup(table, table_name);
 232         if (tb == NULL) {
 233                 media_svc_debug("lookup fail.. table name [%s] ", table_name);
 234                 ret = MS_MEDIA_ERR_INTERNAL;
 235                 goto ERROR;
 236         }
 237
 238         for (iter = column_list[list]; iter; iter = g_slist_next(iter)) {
 239                 col_ptr = iter->data;
 240                 if (!col_ptr)
 241                         continue;
 242
 243                 /*create table */
 244                 if (col_ptr->option) {
 245                         if (table_query->len != 0)
 246                                 g_string_append_printf(table_query, ", %s %s %s", col_ptr->name, col_ptr->type, col_ptr->option);
 247                         else
 248                                 g_string_append_printf(table_query, "%s %s %s", col_ptr->name, col_ptr->type, col_ptr->option);
 249                 } else {
 250                         if (table_query->len != 0)
 251                                 g_string_append_printf(table_query, ", %s %s", col_ptr->name, col_ptr->type);
 252                         else
 253                                 g_string_append_printf(table_query, "%s %s", col_ptr->name, col_ptr->type);
 254                 }
 255
 256                 /*unique */
 257                 if (col_ptr->is_unique) {
 258                         if (unique_query->len != 0)
 259                                 g_string_append_printf(unique_query, ", %s", col_ptr->name);
 260                         else
 261                                 g_string_append_printf(unique_query, "%s", col_ptr->name);
 262                 }
 263
 264                 /*create trigger */
 265                 if (col_ptr->is_trigger) {
 266                         if (tb->trigger_name) {
 267                                 if (strncmp(table_name, DB_TABLE_ALBUM, strlen(DB_TABLE_ALBUM)) == 0) {
 268                                         g_string_append_printf(trigger_query, DB_QUERY_TRIGGER_WITH_COUNT,
 269                                                                                         tb->trigger_name, tb->event_table, tb->action_table, tb->event_table,
 270                                                                                         col_ptr->name, col_ptr->name, col_ptr->name, col_ptr->name);
 271                                 } else {
 272                                         g_string_append_printf(trigger_query, DB_QUERY_TRIGGER,
 273                                                                                         tb->trigger_name, tb->event_table, tb->action_table,
 274                                                                                         col_ptr->name, col_ptr->name);
 275                                 }
 276                         } else {
 277                                 media_svc_error("invalid trigger name");
 278                         }
 279                 }
 280         }
 281
 282         /*send queries */
 283         if (unique_query->len > 0)
 284                 sql = sqlite3_mprintf(DB_QUERY_TABLE_WITH_UNIQUE, table_name, table_query->str, unique_query->str);
 285         else
 286                 sql = sqlite3_mprintf(DB_QUERY_TABLE, table_name, table_query->str);
 287
 288         ret = _media_svc_sql_query(sql, uid);
 289         SQLITE3_SAFE_FREE(sql);
 290         if (ret != MS_MEDIA_ERR_NONE)
 291                 goto ERROR;
 292
 293         if (trigger_query->len > 0) {
 294                 ret = _media_svc_sql_query(trigger_query->str, uid);
 295                 if (ret != MS_MEDIA_ERR_NONE)
 296                         goto ERROR;
 297         }
 298
 299         /*create view */
 300         if (strncmp(table_name, DB_TABLE_PLAYLIST, strlen(DB_TABLE_PLAYLIST)) == 0)
 301                 ret = __create_playlist_view(uid);
 302         else if (strncmp(table_name, DB_TABLE_TAG, strlen(DB_TABLE_TAG)) == 0)
 303                 ret = __create_tag_view(uid);
 304
 305 ERROR:
 306         if (trigger_query)
 307                 g_string_free(trigger_query, TRUE);
 308         if (unique_query)
 309                 g_string_free(unique_query, TRUE);
 310         if (table_query)
 311                 g_string_free(table_query, TRUE);
 312
 313         return ret;
 314 }
 315
 316 static void __media_svc_table_free(gpointer data)
 317 {
 318         table_info_s *tb = (table_info_s *) data;
 319
 320         g_free(tb->trigger_name);
 321         g_free(tb->view_name);
 322         g_free(tb->event_table);
 323         g_free(tb->action_table);
 324         g_free(tb);
 325 }
 326
 327 static void __media_svc_column_free(gpointer data)
 328 {
 329         column_info_s *col = (column_info_s *) data;
 330
 331         g_free(col->name);
 332         g_free(col->type);
 333         g_free(col->option);
 334         g_free(col);
 335 }
 336
 337 int _media_svc_init_table_query(void)
 338 {
 339         int ret = MS_MEDIA_ERR_NONE;
 340
 341         /*variable initialize.. */
 342         table = g_hash_table_new_full(g_str_hash, g_str_equal, NULL, __media_svc_table_free);
 343
 344         /*table specification.. (table_name, trigger name, event table, action table, view name) */
 345         __add_table_info(DB_TABLE_MEDIA, NULL, NULL, NULL, NULL);
 346         __add_table_info(DB_TABLE_FOLDER, NULL, NULL, NULL, NULL);
 347         __add_table_info(DB_TABLE_PLAYLIST_MAP, DB_TRIGGER_PLAYLIST_MAP, DB_TABLE_MEDIA, DB_TABLE_PLAYLIST_MAP, NULL);
 348         __add_table_info(DB_TABLE_PLAYLIST, DB_TRIGGER_PLAYLIST_MAP1, DB_TABLE_PLAYLIST, DB_TABLE_PLAYLIST_MAP, DB_VIEW_PLAYLIST);
 349         __add_table_info(DB_TABLE_ALBUM, DB_TRIGGER_ALBUM, DB_TABLE_MEDIA, DB_TABLE_ALBUM, NULL);
 350         __add_table_info(DB_TABLE_TAG_MAP, DB_TRIGGER_TAG_MAP, DB_TABLE_MEDIA, DB_TABLE_TAG_MAP, NULL);
 351         __add_table_info(DB_TABLE_TAG, DB_TRIGGER_TAG_MAP1, DB_TABLE_TAG, DB_TABLE_TAG_MAP, DB_VIEW_TAG);
 352         __add_table_info(DB_TABLE_BOOKMARK, DB_TRIGGER_BOOKMARK, DB_TABLE_MEDIA, DB_TABLE_BOOKMARK, NULL);
 353         __add_table_info(DB_TABLE_STORAGE, NULL, NULL, NULL, NULL);
 354         __add_table_info(DB_TABLE_FACE_SCAN_LIST, DB_TRIGGER_FACE_SCAN_LIST, DB_TABLE_MEDIA, DB_TABLE_FACE_SCAN_LIST, NULL);
 355         __add_table_info(DB_TABLE_FACE, DB_TRIGGER_FACE, DB_TABLE_FACE_SCAN_LIST, DB_TABLE_FACE, NULL);
 356
 357         /*insert column info.. */
 358         /*media*/
 359         __add_column_info(&column_list[DB_LIST_MEDIA], "media_id", DB_TYPE_TEXT, "PRIMARY KEY", false, false, true);
 360         __add_column_info(&column_list[DB_LIST_MEDIA], "media_path", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, false, true);
 361         __add_column_info(&column_list[DB_LIST_MEDIA], "media_display_name", DB_TYPE_TEXT, "NOT NULL", false, false, true);
 362         __add_column_info(&column_list[DB_LIST_MEDIA], "media_type", DB_TYPE_INT, NULL, false, false, true);
 363         __add_column_info(&column_list[DB_LIST_MEDIA], "media_mime_type", DB_TYPE_TEXT, NULL, false, false, true);
 364         __add_column_info(&column_list[DB_LIST_MEDIA], "media_size", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 365         __add_column_info(&column_list[DB_LIST_MEDIA], "media_added_time", DB_TYPE_INT, "DEFAULT (unixepoch())", false, false, true);
 366         __add_column_info(&column_list[DB_LIST_MEDIA], "media_modified_time", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 367         __add_column_info(&column_list[DB_LIST_MEDIA], "folder_id", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 368         __add_column_info(&column_list[DB_LIST_MEDIA], "media_thumbnail_path", DB_TYPE_TEXT, NULL, false, false, true);
 369         __add_column_info(&column_list[DB_LIST_MEDIA], "media_title", DB_TYPE_TEXT, NULL, false, false, true);
 370         __add_column_info(&column_list[DB_LIST_MEDIA], "album_id", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 371         __add_column_info(&column_list[DB_LIST_MEDIA], "media_album", DB_TYPE_TEXT, NULL, false, false, true);
 372         __add_column_info(&column_list[DB_LIST_MEDIA], "media_artist", DB_TYPE_TEXT, NULL, false, false, true);
 373         __add_column_info(&column_list[DB_LIST_MEDIA], "media_album_artist", DB_TYPE_TEXT, NULL, false, false, true);
 374         __add_column_info(&column_list[DB_LIST_MEDIA], "media_genre", DB_TYPE_TEXT, NULL, false, false, true);
 375         __add_column_info(&column_list[DB_LIST_MEDIA], "media_composer", DB_TYPE_TEXT, "DEFAULT ''", false, false, true);
 376         __add_column_info(&column_list[DB_LIST_MEDIA], "media_year", DB_TYPE_TEXT, NULL, false, false, true);
 377         __add_column_info(&column_list[DB_LIST_MEDIA], "media_recorded_date", DB_TYPE_TEXT, "DEFAULT ''", false, false, true);
 378         __add_column_info(&column_list[DB_LIST_MEDIA], "media_copyright", DB_TYPE_TEXT, "DEFAULT ''", false, false, true);
 379         __add_column_info(&column_list[DB_LIST_MEDIA], "media_track_num", DB_TYPE_TEXT, NULL, false, false, true);
 380         __add_column_info(&column_list[DB_LIST_MEDIA], "media_description", DB_TYPE_TEXT, "DEFAULT ''", false, false, true);
 381         __add_column_info(&column_list[DB_LIST_MEDIA], "media_bitrate", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 382         __add_column_info(&column_list[DB_LIST_MEDIA], "media_bitpersample", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 383         __add_column_info(&column_list[DB_LIST_MEDIA], "media_samplerate", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 384         __add_column_info(&column_list[DB_LIST_MEDIA], "media_channel", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 385         __add_column_info(&column_list[DB_LIST_MEDIA], "media_duration", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 386         __add_column_info(&column_list[DB_LIST_MEDIA], "media_longitude", DB_TYPE_DOUBLE, "DEFAULT -200", false, false, true);
 387         __add_column_info(&column_list[DB_LIST_MEDIA], "media_latitude", DB_TYPE_DOUBLE, "DEFAULT -200", false, false, true);
 388         __add_column_info(&column_list[DB_LIST_MEDIA], "media_altitude", DB_TYPE_DOUBLE, "DEFAULT 0", false, false, true);
 389         __add_column_info(&column_list[DB_LIST_MEDIA], "exposure_time", DB_TYPE_TEXT, NULL, false, false, true);
 390         __add_column_info(&column_list[DB_LIST_MEDIA], "fnumber", DB_TYPE_DOUBLE, "DEFAULT 0", false, false, true);
 391         __add_column_info(&column_list[DB_LIST_MEDIA], "iso", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 392         __add_column_info(&column_list[DB_LIST_MEDIA], "model", DB_TYPE_TEXT, NULL, false, false, true);
 393         __add_column_info(&column_list[DB_LIST_MEDIA], "media_width", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 394         __add_column_info(&column_list[DB_LIST_MEDIA], "media_height", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 395         __add_column_info(&column_list[DB_LIST_MEDIA], "media_datetaken", DB_TYPE_TEXT, NULL, false, false, true);
 396         __add_column_info(&column_list[DB_LIST_MEDIA], "orientation", DB_TYPE_INT, "DEFAULT -1", false, false, true);
 397         __add_column_info(&column_list[DB_LIST_MEDIA], "media_rating", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 398         __add_column_info(&column_list[DB_LIST_MEDIA], "media_favourite", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 399         __add_column_info(&column_list[DB_LIST_MEDIA], "media_is_drm", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 400         __add_column_info(&column_list[DB_LIST_MEDIA], "media_timeline", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 401         __add_column_info(&column_list[DB_LIST_MEDIA], "storage_uuid", DB_TYPE_TEXT, NULL, false, false, true);
 402         __add_column_info(&column_list[DB_LIST_MEDIA], "validity", DB_TYPE_INT, "DEFAULT 1", false, false, false);
 403         __add_column_info(&column_list[DB_LIST_MEDIA], "media_360", DB_TYPE_INT, "DEFAULT 0", false, false, true);
 404
 405         /*folder*/
 406         __add_column_info(&column_list[DB_LIST_FOLDER], "folder_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, false);
 407         __add_column_info(&column_list[DB_LIST_FOLDER], "folder_path", DB_TYPE_TEXT, "NOT NULL", true, false, false);
 408         __add_column_info(&column_list[DB_LIST_FOLDER], "folder_name", DB_TYPE_TEXT, "NOT NULL", false, false, false);
 409         __add_column_info(&column_list[DB_LIST_FOLDER], "storage_uuid", DB_TYPE_TEXT, NULL, true, false, false);
 410         __add_column_info(&column_list[DB_LIST_FOLDER], "validity", DB_TYPE_INT, "DEFAULT 1", false, false, false);
 411
 412         /*playlist_map*/
 413         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, true);
 414         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "playlist_id", DB_TYPE_INT, "NOT NULL", false, false, false);
 415         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "media_id", DB_TYPE_TEXT, "NOT NULL", false, true, false);
 416         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "playlist_member_order", DB_TYPE_INT, "NOT NULL", false, false, true);
 417
 418         /*playlist*/
 419         __add_column_info(&column_list[DB_LIST_PLAYLIST], "playlist_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, true, true);
 420         __add_column_info(&column_list[DB_LIST_PLAYLIST], "playlist_name", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, false, true);
 421         __add_column_info(&column_list[DB_LIST_PLAYLIST], "thumbnail_path", DB_TYPE_TEXT, NULL, false, false, true);
 422
 423         /*album*/
 424         __add_column_info(&column_list[DB_LIST_ALBUM], "album_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, true, false);
 425         __add_column_info(&column_list[DB_LIST_ALBUM], "name", DB_TYPE_TEXT, "NOT NULL", false, false, false);
 426         __add_column_info(&column_list[DB_LIST_ALBUM], "artist", DB_TYPE_TEXT, NULL, false, false, false);
 427         __add_column_info(&column_list[DB_LIST_ALBUM], "album_art", DB_TYPE_TEXT, NULL, false, false, false);
 428
 429         /*tag_map*/
 430         __add_column_info(&column_list[DB_LIST_TAG_MAP], "_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, true);
 431         __add_column_info(&column_list[DB_LIST_TAG_MAP], "tag_id", DB_TYPE_INT, "NOT NULL", true, false, false);
 432         __add_column_info(&column_list[DB_LIST_TAG_MAP], "media_id", DB_TYPE_TEXT, "NOT NULL", true, true, false);
 433
 434         /*tag*/
 435         __add_column_info(&column_list[DB_LIST_TAG], "tag_id ", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, true, true);
 436         __add_column_info(&column_list[DB_LIST_TAG], "tag_name", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, false, true);
 437
 438         /*bookmark*/
 439         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, false);
 440         __add_column_info(&column_list[DB_LIST_BOOKMARK], "media_id", DB_TYPE_TEXT, "NOT NULL", true, true, false);
 441         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_marked_time", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 442         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_thumbnail_path", DB_TYPE_TEXT, NULL, false, false, false);
 443         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_name", DB_TYPE_TEXT, NULL, false, false, false);
 444
 445         /*storage*/
 446         __add_column_info(&column_list[DB_LIST_STORAGE], "storage_id", DB_TYPE_TEXT, "PRIMARY KEY", false, false, false);
 447         __add_column_info(&column_list[DB_LIST_STORAGE], "storage_path", DB_TYPE_TEXT, "NOT NULL", false, false, false);
 448         __add_column_info(&column_list[DB_LIST_STORAGE], "validity", DB_TYPE_INT, "DEFAULT 1", false, false, false);
 449
 450         /*face scan list*/
 451         __add_column_info(&column_list[DB_LIST_FACE_SCAN_LIST], "media_id", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, true, false);
 452         __add_column_info(&column_list[DB_LIST_FACE_SCAN_LIST], "modified_time", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 453
 454         /*face*/
 455         __add_column_info(&column_list[DB_LIST_FACE], "media_face_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, false);
 456         __add_column_info(&column_list[DB_LIST_FACE], "media_id", DB_TYPE_TEXT, "NOT NULL", true, true, false);
 457         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_x", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 458         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_y", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 459         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_w", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 460         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_h", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 461         __add_column_info(&column_list[DB_LIST_FACE], "face_orientation", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 462         __add_column_info(&column_list[DB_LIST_FACE], "media_face_tag", DB_TYPE_TEXT, NULL, false, false, false);
 463
 464         return ret;
 465 }
 466
 467 void _media_svc_destroy_table_query(void)
 468 {
 469         int i = 0;
 470
 471         /* Table Free */
 472         g_hash_table_destroy(table);
 473         table = NULL;
 474
 475         /* Column Free */
 476         for (i = 0; i < DB_LIST_MAX; i++) {
 477                 g_slist_free_full(column_list[i], __media_svc_column_free);
 478                 column_list[i] = NULL;
 479         }
 480 }
 481
 482 int _media_svc_sql_query(const char *sql_str, uid_t uid)
 483 {
 484         return media_db_request_update_db(sql_str, uid);
 485 }
 486
 487 #define MAX_RETRY 9
 488 #define SLEEP_TIME 1000 * 1000
 489 static int __media_svc_query_direct(sqlite3 *handle, const char *query, uid_t uid)
 490 {
 491         int ret = MS_MEDIA_ERR_NONE;
 492         char *zErrMsg = NULL;
 493         int retry_count = 0;
 494
 495 EXEC_RETRY:
 496         ret = sqlite3_exec(handle, query, NULL, NULL, &zErrMsg);
 497         if (SQLITE_OK != ret) {
 498                 media_svc_sec_error("Error[%s],Query[%s]", zErrMsg, query);
 499                 SQLITE3_SAFE_FREE(zErrMsg);
 500                 if (ret == SQLITE_BUSY) {
 501                         ret = MS_MEDIA_ERR_DB_BUSY_FAIL;
 502                 } else if (ret == SQLITE_CONSTRAINT) {
 503                         ret = MS_MEDIA_ERR_DB_CONSTRAINT_FAIL;
 504                 } else if (ret == SQLITE_FULL) {
 505                         ret = MS_MEDIA_ERR_DB_FULL_FAIL;
 506                 } else if (ret == SQLITE_LOCKED) {
 507                         if (retry_count < MAX_RETRY) {
 508                                 media_svc_error("Locked retry[%d]", retry_count);
 509                                 retry_count++;
 510                                 usleep(SLEEP_TIME);
 511                                 goto EXEC_RETRY;
 512                         }
 513                         ret = MS_MEDIA_ERR_DB_INTERNAL;
 514                 } else {
 515                         ret = MS_MEDIA_ERR_DB_INTERNAL;
 516                 }
 517         }
 518
 519         return ret;
 520 }
 521
 522 int _media_svc_sql_query_direct(const char *sql_str, uid_t uid)
 523 {
 524         int ret = MS_MEDIA_ERR_NONE;
 525         sqlite3 *handle = NULL;
 526
 527         ret = media_db_connect(&handle, uid, true);
 528         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "DB connection failed");
 529         ret = __media_svc_query_direct(handle, sql_str, uid);
 530         media_db_disconnect(handle);
 531
 532         return ret;
 533 }
 534
 535 int _media_svc_check_table_exist(sqlite3 *db_handle, bool *exist)
 536 {
 537         int ret = MS_MEDIA_ERR_NONE;
 538         sqlite3_stmt *sql_stmt = NULL;
 539         const char *sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='media';";
 540
 541         ret = _media_svc_get_result(db_handle, sql, &sql_stmt);
 542         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "_media_svc_get_result failed");
 543         if (sqlite3_step(sql_stmt) != SQLITE_ROW) {
 544                 media_svc_debug("Need to create table");
 545                 *exist = false;
 546         } else {
 547                 media_svc_debug("Already exists");
 548                 *exist = true;
 549         }
 550
 551         SQLITE3_FINALIZE(sql_stmt);
 552
 553         return MS_MEDIA_ERR_NONE;
 554 }
 555
 556 static int __media_svc_check_record(sqlite3_stmt **stmt)
 557 {
 558         media_svc_retvm_if(!stmt, MS_MEDIA_ERR_INVALID_PARAMETER, "invalid statement");
 559
 560         if (sqlite3_step(*stmt) != SQLITE_ROW) {
 561                 media_svc_debug("No record");
 562                 SQLITE3_FINALIZE(*stmt);
 563                 return MS_MEDIA_ERR_DB_NO_RECORD;
 564         }
 565
 566         return MS_MEDIA_ERR_NONE;
 567 }
 568
 569 static int __media_svc_get_result(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
 570 {
 571         int err = SQLITE_OK;
 572
 573         media_svc_retvm_if(!handle, MS_MEDIA_ERR_INVALID_PARAMETER, "handle is NULL");
 574         media_svc_retvm_if(!STRING_VALID(sql_str), MS_MEDIA_ERR_INVALID_PARAMETER, "invalid query");
 575
 576         media_svc_sec_debug("Query[%s]", sql_str);
 577
 578         err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
 579         if (err != SQLITE_OK) {
 580                 media_svc_error("prepare error %d[%s]", err, sqlite3_errmsg(handle));
 581                 if (err == SQLITE_CORRUPT)
 582                         return MS_MEDIA_ERR_DB_CORRUPT;
 583
 584                 return MS_MEDIA_ERR_DB_INTERNAL;
 585         }
 586
 587         return MS_MEDIA_ERR_NONE;
 588 }
 589
 590 int _media_svc_get_result_with_check_record(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
 591 {
 592         int ret = __media_svc_get_result(handle, sql_str, stmt);
 593         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "__media_svc_get_result failed");
 594
 595         return __media_svc_check_record(stmt);
 596 }
 597
 598 int _media_svc_get_result(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
 599 {
 600         return __media_svc_get_result(handle, sql_str, stmt);
 601 }
 602
 603 int _media_svc_sql_query_list(GList **query_list, uid_t uid)
 604 {
 605         int ret = MS_MEDIA_ERR_NONE;
 606         int idx = 0;
 607         int length = g_list_length(*query_list);
 608         char *sql = NULL;
 609
 610         media_svc_debug("query list length : [%d]", length);
 611
 612         for (idx = 0; idx < length; idx++) {
 613                 sql = (char *)g_list_nth_data(*query_list, idx);
 614                 if (STRING_VALID(sql)) {
 615                         ret = media_db_request_update_db(sql, uid);
 616                         if (ret != MS_MEDIA_ERR_NONE)
 617                                 media_svc_error("media_db_request_update_db failed : %d", ret);
 618                 }
 619
 620                 SQLITE3_SAFE_FREE(sql);
 621         }
 622
 623         _media_svc_sql_query_release(query_list);
 624
 625         return MS_MEDIA_ERR_NONE;
 626 }
 627
 628 int _media_svc_sql_query_list_direct(GList **query_list, uid_t uid)
 629 {
 630         int ret = MS_MEDIA_ERR_NONE;
 631         int idx = 0;
 632         int length = g_list_length(*query_list);
 633         char *sql = NULL;
 634         char *zErrMsg = NULL;
 635         sqlite3 *handle = NULL;
 636         bool with_transaction = true;
 637
 638         media_svc_debug("query list length[%d]", length);
 639         if (length == 0)
 640                 goto ZERO_LEN;
 641
 642         ret = media_db_connect(&handle, uid, true);
 643         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "DB connection failed");
 644
 645         ret = sqlite3_exec(handle, "BEGIN;", NULL, NULL, &zErrMsg);
 646         if (SQLITE_OK != ret) {
 647                 media_svc_sec_error("Transaction failed[%s]. Try an individual insert.", zErrMsg);
 648                 SQLITE3_SAFE_FREE(zErrMsg);
 649                 with_transaction = false;
 650         }
 651
 652         for (idx = 0; idx < length; idx++) {
 653                 sql = (char *)g_list_nth_data(*query_list, idx);
 654                 if (STRING_VALID(sql)) {
 655                         ret = __media_svc_query_direct(handle, sql, uid);
 656                         if (ret != MS_MEDIA_ERR_NONE)
 657                                 media_svc_debug("_media_svc_query_direct failed[%s]", sql);
 658
 659                         SQLITE3_SAFE_FREE(sql);
 660                 }
 661         }
 662
 663         if (with_transaction) {
 664                 ret = sqlite3_exec(handle, "COMMIT;", NULL, NULL, &zErrMsg);
 665                 if (SQLITE_OK != ret) {
 666                         media_svc_sec_error("Commit failed[%s]", zErrMsg);
 667                         SQLITE3_SAFE_FREE(zErrMsg);
 668                         media_db_disconnect(handle);
 669                         _media_svc_sql_query_release(query_list);
 670                         return MS_MEDIA_ERR_DB_INTERNAL;
 671                 }
 672         }
 673
 674         media_db_disconnect(handle);
 675
 676 ZERO_LEN:
 677         _media_svc_sql_query_release(query_list);
 678
 679         return MS_MEDIA_ERR_NONE;
 680 }
 681
 682 void _media_svc_sql_query_add(GList **query_list, char **query)
 683 {
 684         *query_list = g_list_append(*query_list, *query);
 685 }
 686
 687 void _media_svc_sql_query_release(GList **query_list)
 688 {
 689         if (*query_list) {
 690                 g_list_free(*query_list);
 691                 *query_list = NULL;
 692         }
 693 }