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