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], "folder_modified_time", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 410         __add_column_info(&column_list[DB_LIST_FOLDER], "storage_uuid", DB_TYPE_TEXT, NULL, true, false, false);
 411         __add_column_info(&column_list[DB_LIST_FOLDER], "validity", DB_TYPE_INT, "DEFAULT 1", false, false, false);
 412
 413         /*playlist_map*/
 414         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, true);
 415         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "playlist_id", DB_TYPE_INT, "NOT NULL", false, false, false);
 416         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "media_id", DB_TYPE_TEXT, "NOT NULL", false, true, false);
 417         __add_column_info(&column_list[DB_LIST_PLAYLIST_MAP], "playlist_member_order", DB_TYPE_INT, "NOT NULL", false, false, true);
 418
 419         /*playlist*/
 420         __add_column_info(&column_list[DB_LIST_PLAYLIST], "playlist_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, true, true);
 421         __add_column_info(&column_list[DB_LIST_PLAYLIST], "playlist_name", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, false, true);
 422         __add_column_info(&column_list[DB_LIST_PLAYLIST], "thumbnail_path", DB_TYPE_TEXT, NULL, false, false, true);
 423
 424         /*album*/
 425         __add_column_info(&column_list[DB_LIST_ALBUM], "album_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, true, false);
 426         __add_column_info(&column_list[DB_LIST_ALBUM], "name", DB_TYPE_TEXT, "NOT NULL", false, false, false);
 427         __add_column_info(&column_list[DB_LIST_ALBUM], "artist", DB_TYPE_TEXT, NULL, false, false, false);
 428         __add_column_info(&column_list[DB_LIST_ALBUM], "album_art", DB_TYPE_TEXT, NULL, false, false, false);
 429
 430         /*tag_map*/
 431         __add_column_info(&column_list[DB_LIST_TAG_MAP], "_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, true);
 432         __add_column_info(&column_list[DB_LIST_TAG_MAP], "tag_id", DB_TYPE_INT, "NOT NULL", true, false, false);
 433         __add_column_info(&column_list[DB_LIST_TAG_MAP], "media_id", DB_TYPE_TEXT, "NOT NULL", true, true, false);
 434
 435         /*tag*/
 436         __add_column_info(&column_list[DB_LIST_TAG], "tag_id ", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, true, true);
 437         __add_column_info(&column_list[DB_LIST_TAG], "tag_name", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, false, true);
 438
 439         /*bookmark*/
 440         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, false);
 441         __add_column_info(&column_list[DB_LIST_BOOKMARK], "media_id", DB_TYPE_TEXT, "NOT NULL", true, true, false);
 442         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_marked_time", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 443         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_thumbnail_path", DB_TYPE_TEXT, NULL, false, false, false);
 444         __add_column_info(&column_list[DB_LIST_BOOKMARK], "bookmark_name", DB_TYPE_TEXT, NULL, false, false, false);
 445
 446         /*storage*/
 447         __add_column_info(&column_list[DB_LIST_STORAGE], "storage_id", DB_TYPE_TEXT, "PRIMARY KEY", false, false, false);
 448         __add_column_info(&column_list[DB_LIST_STORAGE], "storage_path", DB_TYPE_TEXT, "NOT NULL", false, false, false);
 449         __add_column_info(&column_list[DB_LIST_STORAGE], "validity", DB_TYPE_INT, "DEFAULT 1", false, false, false);
 450
 451         /*face scan list*/
 452         __add_column_info(&column_list[DB_LIST_FACE_SCAN_LIST], "media_id", DB_TYPE_TEXT, "NOT NULL UNIQUE", false, true, false);
 453         __add_column_info(&column_list[DB_LIST_FACE_SCAN_LIST], "modified_time", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 454
 455         /*face*/
 456         __add_column_info(&column_list[DB_LIST_FACE], "media_face_id", DB_TYPE_INT, "PRIMARY KEY AUTOINCREMENT", false, false, false);
 457         __add_column_info(&column_list[DB_LIST_FACE], "media_id", DB_TYPE_TEXT, "NOT NULL", true, true, false);
 458         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_x", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 459         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_y", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 460         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_w", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 461         __add_column_info(&column_list[DB_LIST_FACE], "face_rect_h", DB_TYPE_INT, "DEFAULT 0", true, false, false);
 462         __add_column_info(&column_list[DB_LIST_FACE], "face_orientation", DB_TYPE_INT, "DEFAULT 0", false, false, false);
 463         __add_column_info(&column_list[DB_LIST_FACE], "media_face_tag", DB_TYPE_TEXT, NULL, false, false, false);
 464
 465         return ret;
 466 }
 467
 468 void _media_svc_destroy_table_query(void)
 469 {
 470         int i = 0;
 471
 472         /* Table Free */
 473         g_hash_table_destroy(table);
 474         table = NULL;
 475
 476         /* Column Free */
 477         for (i = 0; i < DB_LIST_MAX; i++) {
 478                 g_slist_free_full(column_list[i], __media_svc_column_free);
 479                 column_list[i] = NULL;
 480         }
 481 }
 482
 483 int _media_svc_sql_query(const char *sql_str, uid_t uid)
 484 {
 485         return media_db_request_update_db(sql_str, uid);
 486 }
 487
 488 #define MAX_RETRY 9
 489 #define SLEEP_TIME 1000 * 1000
 490 static int __media_svc_query_direct(sqlite3 *handle, const char *query, uid_t uid)
 491 {
 492         int ret = MS_MEDIA_ERR_NONE;
 493         char *zErrMsg = NULL;
 494         int retry_count = 0;
 495
 496 EXEC_RETRY:
 497         ret = sqlite3_exec(handle, query, NULL, NULL, &zErrMsg);
 498         if (SQLITE_OK != ret) {
 499                 media_svc_sec_error("Error[%s],Query[%s]", zErrMsg, query);
 500                 SQLITE3_SAFE_FREE(zErrMsg);
 501                 if (ret == SQLITE_BUSY) {
 502                         ret = MS_MEDIA_ERR_DB_BUSY_FAIL;
 503                 } else if (ret == SQLITE_CONSTRAINT) {
 504                         ret = MS_MEDIA_ERR_DB_CONSTRAINT_FAIL;
 505                 } else if (ret == SQLITE_FULL) {
 506                         ret = MS_MEDIA_ERR_DB_FULL_FAIL;
 507                 } else if (ret == SQLITE_LOCKED) {
 508                         if (retry_count < MAX_RETRY) {
 509                                 media_svc_error("Locked retry[%d]", retry_count);
 510                                 retry_count++;
 511                                 usleep(SLEEP_TIME);
 512                                 goto EXEC_RETRY;
 513                         }
 514                         ret = MS_MEDIA_ERR_DB_INTERNAL;
 515                 } else {
 516                         ret = MS_MEDIA_ERR_DB_INTERNAL;
 517                 }
 518         }
 519
 520         return ret;
 521 }
 522
 523 int _media_svc_sql_query_direct(const char *sql_str, uid_t uid)
 524 {
 525         int ret = MS_MEDIA_ERR_NONE;
 526         sqlite3 *handle = NULL;
 527
 528         ret = media_db_connect(&handle, uid, true);
 529         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "DB connection failed");
 530         ret = __media_svc_query_direct(handle, sql_str, uid);
 531         media_db_disconnect(handle);
 532
 533         return ret;
 534 }
 535
 536 int _media_svc_check_table_exist(sqlite3 *db_handle, bool *exist)
 537 {
 538         int ret = MS_MEDIA_ERR_NONE;
 539         sqlite3_stmt *sql_stmt = NULL;
 540         const char *sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='media';";
 541
 542         ret = _media_svc_get_result(db_handle, sql, &sql_stmt);
 543         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "_media_svc_get_result failed");
 544         if (sqlite3_step(sql_stmt) != SQLITE_ROW) {
 545                 media_svc_debug("Need to create table");
 546                 *exist = false;
 547         } else {
 548                 media_svc_debug("Already exists");
 549                 *exist = true;
 550         }
 551
 552         SQLITE3_FINALIZE(sql_stmt);
 553
 554         return MS_MEDIA_ERR_NONE;
 555 }
 556
 557 static int __media_svc_check_record(sqlite3_stmt **stmt)
 558 {
 559         media_svc_retvm_if(!stmt, MS_MEDIA_ERR_INVALID_PARAMETER, "invalid statement");
 560
 561         if (sqlite3_step(*stmt) != SQLITE_ROW) {
 562                 media_svc_debug("No record");
 563                 SQLITE3_FINALIZE(*stmt);
 564                 return MS_MEDIA_ERR_DB_NO_RECORD;
 565         }
 566
 567         return MS_MEDIA_ERR_NONE;
 568 }
 569
 570 static int __media_svc_get_result(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
 571 {
 572         int err = SQLITE_OK;
 573
 574         media_svc_retvm_if(!handle, MS_MEDIA_ERR_INVALID_PARAMETER, "handle is NULL");
 575         media_svc_retvm_if(!STRING_VALID(sql_str), MS_MEDIA_ERR_INVALID_PARAMETER, "invalid query");
 576
 577         media_svc_sec_debug("Query[%s]", sql_str);
 578
 579         err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
 580         if (err != SQLITE_OK) {
 581                 media_svc_error("prepare error %d[%s]", err, sqlite3_errmsg(handle));
 582                 if (err == SQLITE_CORRUPT)
 583                         return MS_MEDIA_ERR_DB_CORRUPT;
 584
 585                 return MS_MEDIA_ERR_DB_INTERNAL;
 586         }
 587
 588         return MS_MEDIA_ERR_NONE;
 589 }
 590
 591 int _media_svc_get_result_with_check_record(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
 592 {
 593         int ret = __media_svc_get_result(handle, sql_str, stmt);
 594         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "__media_svc_get_result failed");
 595
 596         return __media_svc_check_record(stmt);
 597 }
 598
 599 int _media_svc_get_result(sqlite3 *handle, const char *sql_str, sqlite3_stmt **stmt)
 600 {
 601         return __media_svc_get_result(handle, sql_str, stmt);
 602 }
 603
 604 int _media_svc_sql_query_list(GList **query_list, uid_t uid)
 605 {
 606         int ret = MS_MEDIA_ERR_NONE;
 607         int idx = 0;
 608         int length = g_list_length(*query_list);
 609         char *sql = NULL;
 610
 611         media_svc_debug("query list length : [%d]", length);
 612
 613         for (idx = 0; idx < length; idx++) {
 614                 sql = (char *)g_list_nth_data(*query_list, idx);
 615                 if (STRING_VALID(sql)) {
 616                         ret = media_db_request_update_db(sql, uid);
 617                         if (ret != MS_MEDIA_ERR_NONE)
 618                                 media_svc_error("media_db_request_update_db failed : %d", ret);
 619                 }
 620
 621                 SQLITE3_SAFE_FREE(sql);
 622         }
 623
 624         _media_svc_sql_query_release(query_list);
 625
 626         return MS_MEDIA_ERR_NONE;
 627 }
 628
 629 int _media_svc_sql_query_list_direct(GList **query_list, uid_t uid)
 630 {
 631         int ret = MS_MEDIA_ERR_NONE;
 632         int idx = 0;
 633         int length = g_list_length(*query_list);
 634         char *sql = NULL;
 635         char *zErrMsg = NULL;
 636         sqlite3 *handle = NULL;
 637         bool with_transaction = true;
 638
 639         media_svc_debug("query list length[%d]", length);
 640         if (length == 0)
 641                 goto ZERO_LEN;
 642
 643         ret = media_db_connect(&handle, uid, true);
 644         media_svc_retvm_if(ret != MS_MEDIA_ERR_NONE, ret, "DB connection failed");
 645
 646         ret = sqlite3_exec(handle, "BEGIN;", NULL, NULL, &zErrMsg);
 647         if (SQLITE_OK != ret) {
 648                 media_svc_sec_error("Transaction failed[%s]. Try an individual insert.", zErrMsg);
 649                 SQLITE3_SAFE_FREE(zErrMsg);
 650                 with_transaction = false;
 651         }
 652
 653         for (idx = 0; idx < length; idx++) {
 654                 sql = (char *)g_list_nth_data(*query_list, idx);
 655                 if (STRING_VALID(sql)) {
 656                         ret = __media_svc_query_direct(handle, sql, uid);
 657                         if (ret != MS_MEDIA_ERR_NONE)
 658                                 media_svc_debug("_media_svc_query_direct failed[%s]", sql);
 659
 660                         SQLITE3_SAFE_FREE(sql);
 661                 }
 662         }
 663
 664         if (with_transaction) {
 665                 ret = sqlite3_exec(handle, "COMMIT;", NULL, NULL, &zErrMsg);
 666                 if (SQLITE_OK != ret) {
 667                         media_svc_sec_error("Commit failed[%s]", zErrMsg);
 668                         SQLITE3_SAFE_FREE(zErrMsg);
 669                         media_db_disconnect(handle);
 670                         _media_svc_sql_query_release(query_list);
 671                         return MS_MEDIA_ERR_DB_INTERNAL;
 672                 }
 673         }
 674
 675         media_db_disconnect(handle);
 676
 677 ZERO_LEN:
 678         _media_svc_sql_query_release(query_list);
 679
 680         return MS_MEDIA_ERR_NONE;
 681 }
 682
 683 void _media_svc_sql_query_add(GList **query_list, char **query)
 684 {
 685         *query_list = g_list_append(*query_list, *query);
 686 }
 687
 688 void _media_svc_sql_query_release(GList **query_list)
 689 {
 690         if (*query_list) {
 691                 g_list_free(*query_list);
 692                 *query_list = NULL;
 693         }
 694 }