src/audio/audio-svc-music-table.c

   1 /*
   2  * libmedia-service
   3  *
   4  * Copyright (c) 2000 - 2011 Samsung Electronics Co., Ltd. All rights reserved.
   5  *
   6  * Contact: Hyunjun Ko <zzoon.ko@samsung.com>, Haejeong Kim <backto.kim@samsung.com>
   7  *
   8  * Licensed under the Apache License, Version 2.0 (the "License");
   9  * you may not use this file except in compliance with the License.
  10  * You may obtain a copy of the License at
  11  *
  12  * http://www.apache.org/licenses/LICENSE-2.0
  13  *
  14  * Unless required by applicable law or agreed to in writing, software
  15  * distributed under the License is distributed on an "AS IS" BASIS,
  16  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17  * See the License for the specific language governing permissions and
  18  * limitations under the License.
  19  *
  20  */
  21
  22 /**
  23  * This file defines structure and functions related to database for managing music item.
  24  *
  25  * @file        audio-svc-music-table.c
  26  * @version     0.1
  27  */
  28
  29 #include <glib.h>
  30 #include <sys/stat.h>
  31 #include "media-svc-util.h"
  32 #include "audio-svc-error.h"
  33 #include "audio-svc-music-table.h"
  34 #include "audio-svc-playlist-table.h"
  35 #include "audio-svc-debug.h"
  36 #include "audio-svc-types-priv.h"
  37 #include "audio-svc-utils.h"
  38 #include <drm-service.h>
  39 #include "audio-svc-db-utils.h"
  40
  41
  42 typedef enum {
  43         AUDIO_SVC_AUDIO_INFO_AUDIO_ID,
  44         AUDIO_SVC_AUDIO_INFO_PATH,
  45         AUDIO_SVC_AUDIO_INFO_FILE_NAME,
  46         AUDIO_SVC_AUDIO_INFO_THUMBNAIL_PATH,
  47         AUDIO_SVC_AUDIO_INFO_TITLE,
  48         AUDIO_SVC_AUDIO_INFO_ALBUM,
  49         AUDIO_SVC_AUDIO_INFO_ARTIST,
  50         AUDIO_SVC_AUDIO_INFO_GENRE,
  51         AUDIO_SVC_AUDIO_INFO_AUTHOR,
  52         AUDIO_SVC_AUDIO_INFO_YEAR,
  53         AUDIO_SVC_AUDIO_INFO_COPYRIGHT,
  54         AUDIO_SVC_AUDIO_INFO_DESCRIPTION,
  55         AUDIO_SVC_AUDIO_INFO_FORMAT,
  56         AUDIO_SVC_AUDIO_INFO_BITRATE,
  57         AUDIO_SVC_AUDIO_INFO_TRACK,
  58         AUDIO_SVC_AUDIO_INFO_DURATION,
  59         AUDIO_SVC_AUDIO_INFO_RATING,
  60         AUDIO_SVC_AUDIO_INFO_PLAYCOUNT,
  61         AUDIO_SVC_AUDIO_INFO_PLAYTIME,
  62         AUDIO_SVC_AUDIO_INFO_ADDTIME,
  63         AUDIO_SVC_AUDIO_INFO_RATEDTIME,
  64         AUDIO_SVC_AUDIO_INFO_ALBUM_RATING,
  65         AUDIO_SVC_AUDIO_INFO_DATE_MODIFIED,
  66         AUDIO_SVC_AUDIO_INFO_SIZE,
  67         AUDIO_SVC_AUDIO_INFO_CATEGORY,
  68         AUDIO_SVC_AUDIO_INFO_VALID,
  69         AUDIO_SVC_AUDIO_INFO_FOLDER_ID,
  70         AUDIO_SVC_AUDIO_INFO_STORAGE,
  71         AUDIO_SVC_AUDIO_INFO_FAVOURATE,
  72         AUDIO_SVC_AUDIO_INFO_CONTENT_TYPE,
  73 } audio_svc_audio_info_e;
  74
  75 #define AUDIO_SVC_ORDER_BY_TITLE                "ORDER BY title COLLATE NOCASE"
  76 #define AUDIO_SVC_ORDER_BY_ALBUM                "ORDER BY album COLLATE NOCASE"
  77 #define AUDIO_SVC_ORDER_BY_GENRE                "ORDER BY genre COLLATE NOCASE"
  78 #define AUDIO_SVC_ORDER_BY_AUTHOR       "ORDER BY author COLLATE NOCASE"
  79 #define AUDIO_SVC_ORDER_BY_ARTIST               "ORDER BY artist COLLATE NOCASE"
  80 #define AUDIO_SVC_COLLATE_NOCASE                "COLLATE NOCASE"
  81
  82 static __thread GList *g_audio_svc_item_valid_query_list = NULL;
  83 static __thread GList *g_audio_svc_move_item_query_list = NULL;
  84 static __thread GList *g_audio_svc_insert_item_query_list = NULL;
  85
  86
  87 static int __audio_svc_create_music_db_table(sqlite3 *handle);
  88 static void __audio_svc_get_next_record(audio_svc_audio_item_s *item, sqlite3_stmt *stmt);
  89 static int __audio_svc_count_invalid_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e storage_type);
  90 static int __audio_svc_count_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e storage_type);
  91 static int __audio_svc_get_invalid_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e storage_type, int count, mp_thumbnailpath_record_t * thumb_path);
  92 static int __audio_svc_get_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e storage_type, int count, mp_thumbnailpath_record_t * thumb_path);
  93
  94
  95 static int __audio_svc_create_music_db_table(sqlite3 *handle)
  96 {
  97         int err = -1;
  98
  99         char *sql = sqlite3_mprintf("create table if not exists %s (\
 100                                 audio_uuid              text primary key, \
 101                                 path                            text unique, \
 102                                 thumbnail_path  text, \
 103                                 title                           text, \
 104                                 album                   text, \
 105                                 artist                  text, \
 106                                 genre                   text, \
 107                                 author                  text, \
 108                                 year                            integer default -1, \
 109                                 copyright               text, \
 110                                 description             text, \
 111                                 format                  text, \
 112                                 bitrate                 integer default -1, \
 113                                 track_num               integer default -1, \
 114                                 duration                        integer default -1, \
 115                                 rating                  integer default 0, \
 116                                 played_count            integer default 0, \
 117                                 last_played_time        integer default -1, \
 118                                 added_time              integer,\
 119                                 rated_time              integer,\
 120                                 album_rating            integer default 0,\
 121                                 modified_date   integer default 0, \
 122                                 size                            integer default 0, \
 123                                 category                INTEGER default 0, \
 124                                 valid                           integer default 0, \
 125                                 folder_uuid                     TEXT NOT NULL, \
 126                                 storage_type            integer, \
 127                                 favourite                       integer default 0, \
 128                                 content_type            integer default %d);",
 129                                 AUDIO_SVC_DB_TABLE_AUDIO,
 130                                 AUDIO_SVC_CONTENT_TYPE);
 131
 132         err = _audio_svc_sql_query(handle, sql);
 133         sqlite3_free(sql);
 134         if (err != SQLITE_OK) {
 135                 audio_svc_error("It failed to create db table (%d)", err);
 136                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 137                         return err;
 138                 }
 139                 return AUDIO_SVC_ERROR_DB_CREATE_TABLE;
 140         }
 141
 142         return AUDIO_SVC_ERROR_NONE;
 143 }
 144
 145 static void __audio_svc_get_next_record(audio_svc_audio_item_s *item,
 146                                         sqlite3_stmt *stmt)
 147 {
 148         _strncpy_safe(item->audio_uuid,
 149                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_AUDIO_ID), sizeof(item->audio_uuid));
 150         _strncpy_safe(item->pathname,
 151                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_PATH), sizeof(item->pathname));
 152         _strncpy_safe(item->thumbname,
 153                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_THUMBNAIL_PATH), sizeof(item->thumbname));
 154         _strncpy_safe(item->audio.title,
 155                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_TITLE), sizeof(item->audio.title));
 156         _strncpy_safe(item->audio.album,
 157                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_ALBUM), sizeof(item->audio.album));
 158         _strncpy_safe(item->audio.artist,
 159                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_ARTIST), sizeof(item->audio.artist));
 160         _strncpy_safe(item->audio.genre,
 161                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_GENRE), sizeof(item->audio.genre));
 162         _strncpy_safe(item->audio.author,
 163                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_AUTHOR), sizeof(item->audio.author));
 164         _strncpy_safe(item->audio.year,
 165                       _year_2_str(sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_YEAR)), sizeof(item->audio.year));
 166         _strncpy_safe(item->audio.copyright,
 167                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_COPYRIGHT), sizeof(item->audio.copyright));
 168         _strncpy_safe(item->audio.description,
 169                       (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_DESCRIPTION), sizeof(item->audio.description));
 170         _strncpy_safe(item->audio.format,
 171                         (const char *)sqlite3_column_text(stmt, AUDIO_SVC_AUDIO_INFO_FORMAT), sizeof(item->audio.format));
 172
 173         item->audio.bitrate = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_BITRATE);
 174         item->audio.track = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_TRACK);
 175         item->audio.duration = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_DURATION);
 176         item->rating = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_RATING);
 177         item->played_count = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_PLAYCOUNT);
 178         item->time_played = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_PLAYTIME);
 179         item->time_added = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_ADDTIME);
 180         item->audio.album_rating = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_ALBUM_RATING);
 181         item->category = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_CATEGORY);
 182         item->storage_type = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_STORAGE);
 183         item->favourate = sqlite3_column_int(stmt, AUDIO_SVC_AUDIO_INFO_FAVOURATE);
 184
 185 }
 186
 187 static int
 188 __audio_svc_count_invalid_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e
 189                                                  storage_type)
 190 {
 191         int ret = AUDIO_SVC_ERROR_NONE;
 192         int count = -1;
 193         sqlite3_stmt *sql_stmt = NULL;
 194         char *sql =
 195             sqlite3_mprintf
 196             ("select count(*) from %s where valid=0 and storage_type=%d and thumbnail_path is not null",
 197              AUDIO_SVC_DB_TABLE_AUDIO, storage_type);
 198
 199         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
 200
 201         if (ret != AUDIO_SVC_ERROR_NONE) {
 202                 audio_svc_error
 203                     ("error when __audio_svc_count_invalid_records_with_thumbnail. err = [%d]",
 204                      ret);
 205                 return count;
 206         }
 207
 208         count = sqlite3_column_int(sql_stmt, 0);
 209
 210         SQLITE3_FINALIZE(sql_stmt);
 211
 212         return count;
 213
 214 }
 215
 216 static int __audio_svc_count_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e
 217                                                     storage_type)
 218 {
 219         int ret = AUDIO_SVC_ERROR_NONE;
 220         int count = -1;
 221         sqlite3_stmt *sql_stmt = NULL;
 222         char *sql =
 223             sqlite3_mprintf
 224             ("select count(*) from %s where storage_type=%d and thumbnail_path is not null",
 225              AUDIO_SVC_DB_TABLE_AUDIO, storage_type);
 226
 227         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
 228
 229         if (ret != AUDIO_SVC_ERROR_NONE) {
 230                 audio_svc_error
 231                     ("error when __audio_svc_count_records_with_thumbnail. err = [%d]",
 232                      ret);
 233                 return count;
 234         }
 235
 236         count = sqlite3_column_int(sql_stmt, 0);
 237
 238         SQLITE3_FINALIZE(sql_stmt);
 239
 240         return count;
 241
 242 }
 243
 244 static int
 245 __audio_svc_get_invalid_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e
 246                                                storage_type, int count,
 247                                                mp_thumbnailpath_record_t *
 248                                                thumb_path)
 249 {
 250         int err = -1;
 251         int idx = 0;
 252         sqlite3_stmt *sql_stmt = NULL;
 253
 254         char *sql =
 255             sqlite3_mprintf
 256             ("select thumbnail_path from %s where valid=0 and storage_type=%d and thumbnail_path is not null",
 257              AUDIO_SVC_DB_TABLE_AUDIO, storage_type);
 258
 259         audio_svc_debug("[SQL query] : %s", sql);
 260
 261         err = sqlite3_prepare_v2(handle, sql, -1, &sql_stmt, NULL);
 262         sqlite3_free(sql);
 263         if (err != SQLITE_OK) {
 264                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
 265                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 266         }
 267
 268         while (sqlite3_step(sql_stmt) == SQLITE_ROW) {
 269                 _strncpy_safe(thumb_path[idx].thumbnail_path,
 270                               (const char *)sqlite3_column_text(sql_stmt, 0),
 271                               sizeof(thumb_path[idx]));
 272                 idx++;
 273                 audio_svc_debug("thumb_path[%d]=[%s]", idx,
 274                                 thumb_path[idx].thumbnail_path);
 275         }
 276
 277         SQLITE3_FINALIZE(sql_stmt);
 278
 279         return AUDIO_SVC_ERROR_NONE;
 280 }
 281
 282 static int __audio_svc_get_records_with_thumbnail(sqlite3 *handle, audio_svc_storage_type_e
 283                                                   storage_type, int count,
 284                                                   mp_thumbnailpath_record_t *
 285                                                   thumb_path)
 286 {
 287         int err = -1;
 288         int idx = 0;
 289         sqlite3_stmt *sql_stmt = NULL;
 290
 291         char *sql =
 292             sqlite3_mprintf
 293             ("select thumbnail_path from %s where storage_type=%d and thumbnail_path is not null",
 294              AUDIO_SVC_DB_TABLE_AUDIO, storage_type);
 295
 296         audio_svc_debug("[SQL query] : %s", sql);
 297
 298         err = sqlite3_prepare_v2(handle, sql, -1, &sql_stmt, NULL);
 299         sqlite3_free(sql);
 300         if (err != SQLITE_OK) {
 301                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
 302                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 303         }
 304
 305         while (sqlite3_step(sql_stmt) == SQLITE_ROW) {
 306                 _strncpy_safe(thumb_path[idx].thumbnail_path,
 307                               (const char *)sqlite3_column_text(sql_stmt, 0),
 308                               sizeof(thumb_path[idx]));
 309                 idx++;
 310                 audio_svc_debug("thumb_path[%d]=[%s]", idx,
 311                                 thumb_path[idx].thumbnail_path);
 312         }
 313
 314         SQLITE3_FINALIZE(sql_stmt);
 315
 316         return AUDIO_SVC_ERROR_NONE;
 317 }
 318
 319 int _audio_svc_create_music_table(sqlite3 *handle)
 320 {
 321         int ret = AUDIO_SVC_ERROR_NONE;
 322
 323         ret = __audio_svc_create_music_db_table(handle);
 324         audio_svc_retv_if(ret != AUDIO_SVC_ERROR_NONE, ret);
 325
 326         return AUDIO_SVC_ERROR_NONE;
 327 }
 328
 329 int _audio_svc_truncate_music_table(sqlite3 *handle, audio_svc_storage_type_e storage_type)
 330 {
 331         int idx = 0;
 332         mp_thumbnailpath_record_t *thumbpath_record = NULL;
 333         int err = -1;
 334         int invalid_count = 0;
 335         int ret = AUDIO_SVC_ERROR_NONE;
 336
 337         invalid_count = __audio_svc_count_records_with_thumbnail(handle, storage_type);
 338         audio_svc_debug("invalid count: %d\n", invalid_count);
 339
 340         if (invalid_count > 0) {
 341                 thumbpath_record =
 342                     (mp_thumbnailpath_record_t *)
 343                     malloc(sizeof(mp_thumbnailpath_record_t) * invalid_count);
 344                 if (thumbpath_record == NULL) {
 345                         audio_svc_debug("fail to memory allocation");
 346                         return AUDIO_SVC_ERROR_OUT_OF_MEMORY;
 347                 }
 348                 memset(thumbpath_record, 0,
 349                        sizeof(mp_thumbnailpath_record_t) * invalid_count);
 350
 351                 ret = __audio_svc_get_records_with_thumbnail(handle, storage_type, invalid_count, thumbpath_record);
 352                 if (ret != AUDIO_SVC_ERROR_NONE) {
 353                         audio_svc_error("error when get thumbnail record");
 354                         SAFE_FREE(thumbpath_record);
 355                         return ret;
 356                 }
 357         } else {
 358                 audio_svc_debug("There is no item with thumbnail");
 359         }
 360
 361         char *sql =
 362             sqlite3_mprintf("delete from %s where storage_type=%d",
 363                             AUDIO_SVC_DB_TABLE_AUDIO, storage_type);
 364         err = _audio_svc_sql_query(handle, sql);
 365         sqlite3_free(sql);
 366         if (err != SQLITE_OK) {
 367                 audio_svc_error("It failed to truncate table (%d)", err);
 368                 SAFE_FREE(thumbpath_record);
 369
 370                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 371                         return err;
 372                 }
 373                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 374         }
 375
 376         for (idx = 0; idx < invalid_count; idx++) {
 377                 if (strlen(thumbpath_record[idx].thumbnail_path) > 0) {
 378                         ret = _audio_svc_check_and_remove_thumbnail(handle, thumbpath_record[idx].thumbnail_path);
 379                         if (ret != AUDIO_SVC_ERROR_NONE) {
 380                                 audio_svc_error
 381                                     ("error _audio_svc_check_and_remove_thumbnail");
 382                                 SAFE_FREE(thumbpath_record);
 383                                 return ret;
 384                         }
 385                 }
 386         }
 387
 388         SAFE_FREE(thumbpath_record);
 389         return AUDIO_SVC_ERROR_NONE;
 390
 391 }
 392
 393 int _audio_svc_create_folder_table(sqlite3 *handle)
 394 {
 395         int err = -1;
 396         char *sql = sqlite3_mprintf("create table if not exists %s (\
 397                         folder_uuid             text primary key, \
 398                         path                            text,\
 399                         folder_name             text,\
 400                         storage_type            integer,\
 401                         modified_date   integer default 0);", AUDIO_SVC_DB_TABLE_AUDIO_FOLDER);
 402
 403         err = _audio_svc_sql_query(handle, sql);
 404         sqlite3_free(sql);
 405         if (err != SQLITE_OK) {
 406                 audio_svc_error("error while create folder table");
 407                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 408                         return err;
 409                 }
 410                 return AUDIO_SVC_ERROR_DB_CREATE_TABLE;
 411         }
 412
 413         return AUDIO_SVC_ERROR_NONE;
 414 }
 415
 416 int _audio_svc_delete_folder(sqlite3 *handle, audio_svc_storage_type_e storage_type, const char *folder_id)
 417 {
 418         int err = -1;
 419         char *sql = NULL;
 420         if ((storage_type != AUDIO_SVC_STORAGE_PHONE)
 421             && (storage_type != AUDIO_SVC_STORAGE_MMC)) {
 422                 audio_svc_error("Invalid storage type");
 423                 return AUDIO_SVC_ERROR_INVALID_PARAMETER;
 424         }
 425
 426         if ((storage_type == AUDIO_SVC_STORAGE_MMC) && (folder_id == NULL)) {   /* when mmc card removed. */
 427                 sql = sqlite3_mprintf("delete from %s where storage_type=%d",
 428                                     AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, storage_type);
 429                 err = _audio_svc_sql_query(handle, sql);
 430                 sqlite3_free(sql);
 431                 if (err != SQLITE_OK) {
 432                         audio_svc_debug("It failed to delete item (%d)", err);
 433                         if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 434                                 return err;
 435                         }
 436                         return AUDIO_SVC_ERROR_DB_INTERNAL;
 437                 }
 438         } else {
 439                 sql =
 440                     sqlite3_mprintf("delete from %s where folder_uuid='%q'",
 441                                     AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, folder_id);
 442                 err = _audio_svc_sql_query(handle, sql);
 443                 sqlite3_free(sql);
 444                 if (err != SQLITE_OK) {
 445                         audio_svc_debug("It failed to delete item (%d)", err);
 446                         if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 447                                 return err;
 448                         }
 449                         return AUDIO_SVC_ERROR_DB_INTERNAL;
 450                 }
 451         }
 452
 453         return AUDIO_SVC_ERROR_NONE;
 454 }
 455
 456 int _audio_svc_insert_item_with_data(sqlite3 *handle, audio_svc_audio_item_s *item, bool stack_query)
 457 {
 458         int err = -1;
 459         int ret = AUDIO_SVC_ERROR_NONE;
 460         struct stat st;
 461         int modified_date = -1;
 462         int size = -1;
 463         char folder_id[AUDIO_SVC_UUID_SIZE+1] = {0,};
 464         int year = -1;
 465         char *audio_id = NULL;
 466         char *g_audio_svc_music_fields = "audio_uuid, path, file_name, thumbnail_path, title, album, artist, genre, author, year,\
 467         copyright, description, format, bitrate,track_num,duration, rating, played_count, last_played_time, added_time, modified_date, size, category, valid, folder_uuid, storage_type";
 468         char * file_name = NULL;
 469
 470         if (item == NULL) {
 471                 audio_svc_error("Invalid handle");
 472                 return AUDIO_SVC_ERROR_INVALID_PARAMETER;
 473         }
 474
 475         audio_id = _media_info_generate_uuid();
 476         if(audio_id == NULL ) {
 477                 audio_svc_error("Invalid UUID");
 478                 return AUDIO_SVC_ERROR_INTERNAL;
 479         }
 480
 481         /* set creation date */
 482         memset(&st, 0, sizeof(struct stat));
 483         if (stat(item->pathname, &st) == 0) {
 484                 modified_date = st.st_mtime;
 485                 size = st.st_size;
 486         }
 487
 488         year = str_2_year(item->audio.year);
 489         if (year <= 0) {
 490                 year = -1;
 491                 audio_svc_debug("year = %d", year);
 492                 _strncpy_safe(item->audio.year, AUDIO_SVC_TAG_UNKNOWN,
 493                               sizeof(item->audio.year));
 494         }
 495
 496         ret = _audio_svc_get_and_append_folder_id_by_path(handle, item->pathname,  item->storage_type, folder_id);
 497         audio_svc_retv_if(ret != AUDIO_SVC_ERROR_NONE, ret);
 498
 499         file_name = g_path_get_basename( item->pathname);
 500
 501         char *sql =
 502             sqlite3_mprintf
 503             ("insert into %s (%s) values ('%q', '%q', '%q', '%q', '%q', '%q','%q', '%q','%q',%d,'%q','%q','%q',%d,%d,%d,%d,%d,%d,%d,%d, %d, %d, %d, '%q', %d);",
 504              AUDIO_SVC_DB_TABLE_AUDIO, g_audio_svc_music_fields,
 505              audio_id,
 506              item->pathname,
 507              file_name,
 508              item->thumbname,
 509              item->audio.title,
 510              item->audio.album,
 511              item->audio.artist,
 512              item->audio.genre,
 513              item->audio.author,
 514              str_2_year(item->audio.year),
 515              item->audio.copyright,
 516              item->audio.description,
 517              item->audio.format,
 518              item->audio.bitrate,
 519              item->audio.track,
 520              item->audio.duration,
 521              item->rating,
 522              item->played_count,
 523              item->time_played,
 524              item->time_added,
 525              modified_date,
 526              size,
 527              item->category,
 528              1,
 529              folder_id,
 530              item->storage_type);
 531
 532         audio_svc_debug("query : %s", sql);
 533
 534         SAFE_FREE(file_name);
 535
 536         if(!stack_query) {
 537                 err = _audio_svc_sql_query(handle, sql);
 538                 sqlite3_free(sql);
 539                 if (err != SQLITE_OK) {
 540                         audio_svc_error("failed to insert music record");
 541
 542                         if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 543                                 return err;
 544                         }
 545                         return AUDIO_SVC_ERROR_DB_INTERNAL;
 546                 }
 547         } else {
 548                 _audio_svc_sql_query_add(&g_audio_svc_insert_item_query_list, &sql);
 549         }
 550
 551         //item->audio_uuid = sqlite3_last_insert_rowid(handle);
 552         return AUDIO_SVC_ERROR_NONE;
 553 }
 554
 555 int _audio_svc_select_music_record_by_audio_id(sqlite3 *handle, const char *audio_id,
 556                                                audio_svc_audio_item_s *item)
 557 {
 558         int ret = AUDIO_SVC_ERROR_NONE;
 559         sqlite3_stmt *sql_stmt = NULL;
 560
 561         char *sql =
 562             sqlite3_mprintf
 563             ("select * from %s where audio_uuid='%q' and valid=1 and category=%d",
 564              AUDIO_SVC_DB_TABLE_AUDIO, audio_id, AUDIO_SVC_CATEGORY_MUSIC);
 565
 566         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
 567
 568         if (ret != AUDIO_SVC_ERROR_NONE) {
 569                 audio_svc_error("error when _audio_svc_select_music_record_by_audio_id. ret = [%d]", ret);
 570                 return ret;
 571         }
 572
 573         __audio_svc_get_next_record(item, sql_stmt);
 574
 575         SQLITE3_FINALIZE(sql_stmt);
 576
 577         return AUDIO_SVC_ERROR_NONE;
 578 }
 579
 580 int _audio_svc_select_music_record_by_path(sqlite3 *handle, const char *path,
 581                                            audio_svc_audio_item_s *item)
 582 {
 583         int ret = AUDIO_SVC_ERROR_NONE;
 584         sqlite3_stmt *sql_stmt = NULL;
 585
 586         char *sql =
 587             sqlite3_mprintf("select * from %s where valid=1 and path='%q' ",
 588                             AUDIO_SVC_DB_TABLE_AUDIO, path);
 589
 590         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
 591
 592         if (ret != AUDIO_SVC_ERROR_NONE) {
 593                 audio_svc_error
 594                     ("error when _audio_svc_select_music_record_by_path. ret=[%d]",
 595                      ret);
 596                 return ret;
 597         }
 598
 599         __audio_svc_get_next_record(item, sql_stmt);
 600
 601         SQLITE3_FINALIZE(sql_stmt);
 602
 603         return AUDIO_SVC_ERROR_NONE;
 604 }
 605
 606 int _audio_svc_delete_music_record_by_audio_id(sqlite3 *handle, const char *audio_id)
 607 {
 608         int err = -1;
 609         char *sql =
 610             sqlite3_mprintf("delete from %s where valid=1 and audio_uuid='%q'",
 611                             AUDIO_SVC_DB_TABLE_AUDIO, audio_id);
 612
 613         err = _audio_svc_sql_query(handle, sql);
 614         sqlite3_free(sql);
 615         if (err != SQLITE_OK) {
 616                 audio_svc_error("It failed to delete item (%d)", err);
 617                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 618                         return err;
 619                 }
 620                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 621         }
 622         return AUDIO_SVC_ERROR_NONE;
 623 }
 624
 625 int _audio_svc_update_metadata_in_music_record(sqlite3 *handle, const char *audio_id,
 626                                                audio_svc_audio_item_s *item)
 627 {
 628         int err = -1;
 629         char *sql =
 630             sqlite3_mprintf
 631             ("update %s set thumbnail_path='%q',title='%q',album='%q',artist='%q',arist_seq='%q' ,genre='%q',author='%q',\
 632                 year=%d,copyright='%q',description='%q',format='%q',track_num=%d,duration=%d,bitrate=%d where valid=1 and audio_uuid='%q'",
 633              AUDIO_SVC_DB_TABLE_AUDIO,
 634              item->thumbname,
 635              item->audio.title,
 636              item->audio.album,
 637              item->audio.artist,
 638              item->audio.genre,
 639              item->audio.author,
 640
 641              str_2_year(item->audio.year),
 642
 643              item->audio.copyright,
 644              item->audio.description,
 645              item->audio.format,
 646
 647              item->audio.track, item->audio.duration, item->audio.bitrate,
 648              audio_id);
 649
 650         err = _audio_svc_sql_query(handle, sql);
 651         sqlite3_free(sql);
 652         if (err != SQLITE_OK) {
 653                 audio_svc_error("It failed to update metadata (%d)", err);
 654                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 655                         return err;
 656                 }
 657                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 658         }
 659
 660         return AUDIO_SVC_ERROR_NONE;
 661 }
 662
 663 int _audio_svc_update_path_in_music_record(sqlite3 *handle, const char *src_path,
 664                                            const char *path, const char *title)
 665 {
 666         char *sql = NULL;
 667         int err = -1;
 668
 669         if (STRING_VALID(title)) {
 670                 sql =
 671                     sqlite3_mprintf
 672                     ("update %s set path='%q', title='%q', where valid=1 and path='%q'",
 673                      AUDIO_SVC_DB_TABLE_AUDIO, path, title, src_path);
 674         } else {
 675                 sql =
 676                     sqlite3_mprintf
 677                     ("update %s set path='%q', where valid=1 and path='%q'",
 678                      AUDIO_SVC_DB_TABLE_AUDIO, path, src_path);
 679         }
 680
 681         err = _audio_svc_sql_query(handle, sql);
 682         sqlite3_free(sql);
 683         if (err != SQLITE_OK) {
 684                 audio_svc_error("It failed to update metadata (%d)", err);
 685                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 686                         return err;
 687                 }
 688                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 689         }
 690
 691         return AUDIO_SVC_ERROR_NONE;
 692 }
 693
 694 int _audio_svc_update_path_and_storage_in_music_record(sqlite3 *handle, const char *src_path, const char *path, audio_svc_storage_type_e storage_type)
 695 {
 696         int err = -1;
 697         char *sql = sqlite3_mprintf("update %s set path='%q', storage_type=%d where valid=1 and path='%q'",
 698                                  AUDIO_SVC_DB_TABLE_AUDIO, path, storage_type, src_path);
 699
 700         err = _audio_svc_sql_query(handle, sql);
 701         sqlite3_free(sql);
 702         if (err != SQLITE_OK) {
 703                 audio_svc_error("It failed to update metadata (%d)", err);
 704                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 705                         return err;
 706                 }
 707                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 708         }
 709
 710         return AUDIO_SVC_ERROR_NONE;
 711 }
 712
 713 int _audio_svc_update_folder_id_in_music_record(sqlite3 *handle, const char *path, const char *folder_id)
 714 {
 715         int err = -1;
 716         char *sql = sqlite3_mprintf("update %s set folder_uuid='%q' where path='%q'",
 717                             AUDIO_SVC_DB_TABLE_AUDIO, folder_id, path);
 718
 719         err = _audio_svc_sql_query(handle, sql);
 720         sqlite3_free(sql);
 721         if (err != SQLITE_OK) {
 722                 audio_svc_error("It failed to update metadata (%d)", err);
 723                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 724                         return err;
 725                 }
 726                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 727         }
 728
 729         return AUDIO_SVC_ERROR_NONE;
 730 }
 731
 732 int _audio_svc_update_thumb_path_in_music_record(sqlite3 *handle, const char *file_path,
 733                                                  const char *path)
 734 {
 735         int err = -1;
 736
 737         char *sql =
 738             sqlite3_mprintf
 739             ("update %s set thumbnail_path='%q' where valid=1 and path='%q'",
 740              AUDIO_SVC_DB_TABLE_AUDIO, path, file_path);
 741
 742         err = _audio_svc_sql_query(handle, sql);
 743         sqlite3_free(sql);
 744         if (err != SQLITE_OK) {
 745                 audio_svc_error("It failed to update thumb path (%d)", err);
 746                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 747                         return err;
 748                 }
 749                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 750         }
 751         return AUDIO_SVC_ERROR_NONE;
 752 }
 753
 754 int _audio_svc_update_rating_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
 755 {
 756         int err = -1;
 757         int rated_time = -1;
 758         rated_time = time(NULL);
 759
 760         char *sql =
 761             sqlite3_mprintf
 762             ("update %s set rating=%d, rated_time=%d where valid=1 and audio_uuid='%q'",
 763              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, rated_time, audio_id);
 764
 765         err = _audio_svc_sql_query(handle, sql);
 766         sqlite3_free(sql);
 767         if (err != SQLITE_OK) {
 768                 audio_svc_debug("To update rating is failed(%d)", err);
 769                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 770                         return err;
 771                 }
 772                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 773         }
 774
 775         return AUDIO_SVC_ERROR_NONE;
 776 }
 777
 778 int _audio_svc_update_playtime_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
 779 {
 780
 781         int err = -1;
 782         char *sql =
 783             sqlite3_mprintf
 784             ("update %s set last_played_time=%d where valid=1 and audio_uuid='%q'",
 785              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 786
 787         err = _audio_svc_sql_query(handle, sql);
 788         sqlite3_free(sql);
 789         if (err != SQLITE_OK) {
 790                 audio_svc_error("To update last_played_time is failed(%d)",
 791                                 err);
 792                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 793                         return err;
 794                 }
 795                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 796         }
 797
 798         return AUDIO_SVC_ERROR_NONE;
 799 }
 800
 801 int _audio_svc_update_playcount_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
 802 {
 803
 804         int err = -1;
 805         char *sql =
 806             sqlite3_mprintf
 807             ("update %s set played_count=%d where valid=1 and audio_uuid='%q'",
 808              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 809
 810         err = _audio_svc_sql_query(handle, sql);
 811         sqlite3_free(sql);
 812         if (err != SQLITE_OK) {
 813                 audio_svc_error("To update played count is failed(%d)", err);
 814                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 815                         return err;
 816                 }
 817                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 818         }
 819
 820         return AUDIO_SVC_ERROR_NONE;
 821 }
 822
 823 int _audio_svc_update_addtime_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
 824 {
 825         int err = -1;
 826         char *sql =
 827             sqlite3_mprintf
 828             ("update %s set added_time=%d where valid=1 and audio_uuid='%q'",
 829              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 830         err = _audio_svc_sql_query(handle, sql);
 831         sqlite3_free(sql);
 832         if (err != SQLITE_OK) {
 833                 audio_svc_error("To update added_time is failed(%d)", err);
 834                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 835                         return err;
 836                 }
 837                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 838         }
 839
 840         return AUDIO_SVC_ERROR_NONE;
 841 }
 842
 843 int _audio_svc_update_track_num_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
 844 {
 845         int err = -1;
 846         char *sql =
 847             sqlite3_mprintf
 848             ("update %s set track_num=%d where valid=1 and audio_uuid='%q'",
 849              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 850         err = _audio_svc_sql_query(handle, sql);
 851         sqlite3_free(sql);
 852         if (err != SQLITE_OK) {
 853                 audio_svc_error("update track num is failed(%d)", err);
 854                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 855                         return err;
 856                 }
 857                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 858         }
 859
 860         return AUDIO_SVC_ERROR_NONE;
 861 }
 862
 863 int _audio_svc_update_album_rating_in_music_record(sqlite3 *handle, const char *audio_id,
 864                                                    int changed_value)
 865 {
 866         int err = -1;
 867         char *sql =
 868             sqlite3_mprintf
 869             ("update %s set album_rating=%d where valid=1 and audio_uuid='%q'",
 870              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 871         err = _audio_svc_sql_query(handle, sql);
 872         sqlite3_free(sql);
 873         if (err != SQLITE_OK) {
 874                 audio_svc_error("update album_rating is failed(%d)", err);
 875                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 876                         return err;
 877                 }
 878                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 879         }
 880
 881         return AUDIO_SVC_ERROR_NONE;
 882 }
 883
 884 int _audio_svc_update_year_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
 885 {
 886         int err = -1;
 887         char *sql =
 888             sqlite3_mprintf
 889             ("update %s set year=%d where valid=1 and audio_uuid='%q'",
 890              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 891         err = _audio_svc_sql_query(handle, sql);
 892         sqlite3_free(sql);
 893         if (err != SQLITE_OK) {
 894                 audio_svc_error("update year is failed(%d)", err);
 895                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 896                         return err;
 897                 }
 898                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 899         }
 900
 901         return AUDIO_SVC_ERROR_NONE;
 902 }
 903
 904 int _audio_svc_update_title_in_music_record(sqlite3 *handle, const char *audio_id,
 905                                             const char *changed_value)
 906 {
 907         int err = -1;
 908         char *sql =
 909             sqlite3_mprintf
 910             ("update %s set title='%q' where valid=1 and audio_uuid='%q'",
 911              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 912         err = _audio_svc_sql_query(handle, sql);
 913         sqlite3_free(sql);
 914         if (err != SQLITE_OK) {
 915                 audio_svc_error("To update title is failed(%d)", err);
 916                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 917                         return err;
 918                 }
 919                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 920         }
 921
 922         return AUDIO_SVC_ERROR_NONE;
 923 }
 924
 925 int _audio_svc_update_artist_in_music_record(sqlite3 *handle, const char *audio_id,
 926                                              const char *changed_value)
 927 {
 928         int err = -1;
 929         char *sql =
 930             sqlite3_mprintf
 931             ("update %s set artist='%q' where valid=1 and audio_uuid='%q'",
 932              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 933         err = _audio_svc_sql_query(handle, sql);
 934         sqlite3_free(sql);
 935         if (err != SQLITE_OK) {
 936                 audio_svc_error("To update artist is failed(%d)", err);
 937                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 938                         return err;
 939                 }
 940                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 941         }
 942
 943         return AUDIO_SVC_ERROR_NONE;
 944 }
 945
 946 int _audio_svc_update_album_in_music_record(sqlite3 *handle, const char *audio_id,
 947                                             const char *changed_value)
 948 {
 949         int err = -1;
 950         char *sql =
 951             sqlite3_mprintf
 952             ("update %s set album='%q' where valid=1 and audio_uuid='%q'",
 953              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 954         err = _audio_svc_sql_query(handle, sql);
 955         sqlite3_free(sql);
 956         if (err != SQLITE_OK) {
 957                 audio_svc_error("To update album is failed(%d)", err);
 958                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 959                         return err;
 960                 }
 961                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 962         }
 963
 964         return AUDIO_SVC_ERROR_NONE;
 965 }
 966
 967 int _audio_svc_update_genre_in_music_record(sqlite3 *handle, const char *audio_id,
 968                                             const char *changed_value)
 969 {
 970         int err = -1;
 971         char *sql =
 972             sqlite3_mprintf
 973             ("update %s set genre='%q' where valid=1 and audio_uuid='%q'",
 974              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 975         err = _audio_svc_sql_query(handle, sql);
 976         sqlite3_free(sql);
 977         if (err != SQLITE_OK) {
 978                 audio_svc_error("To update genre is failed(%d)", err);
 979                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
 980                         return err;
 981                 }
 982                 return AUDIO_SVC_ERROR_DB_INTERNAL;
 983         }
 984
 985         return AUDIO_SVC_ERROR_NONE;
 986 }
 987
 988 int _audio_svc_update_author_in_music_record(sqlite3 *handle, const char *audio_id,
 989                                              const char *changed_value)
 990 {
 991         int err = -1;
 992         char *sql =
 993             sqlite3_mprintf
 994             ("update %s set author='%q' where valid=1 and audio_uuid='%q'",
 995              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
 996         err = _audio_svc_sql_query(handle, sql);
 997         sqlite3_free(sql);
 998         if (err != SQLITE_OK) {
 999                 audio_svc_error("To update author is failed(%d)", err);
1000                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
1001                         return err;
1002                 }
1003                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1004         }
1005
1006         return AUDIO_SVC_ERROR_NONE;
1007 }
1008
1009 int _audio_svc_update_description_in_music_record(sqlite3 *handle, const char *audio_id,
1010                                                   const char *changed_value)
1011 {
1012         int err = -1;
1013         char *sql =
1014             sqlite3_mprintf
1015             ("update %s set description='%q' where valid=1 and audio_uuid='%q'",
1016              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
1017         err = _audio_svc_sql_query(handle, sql);
1018         sqlite3_free(sql);
1019         if (err != SQLITE_OK) {
1020                 audio_svc_error("To update description is failed(%d)", err);
1021                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
1022                         return err;
1023                 }
1024                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1025         }
1026
1027         return AUDIO_SVC_ERROR_NONE;
1028 }
1029
1030 int _audio_svc_update_favourite_in_music_record(sqlite3 *handle, const char *audio_id, int changed_value)
1031 {
1032         int err = -1;
1033         char *sql = sqlite3_mprintf("update %s set favourite=%d where valid=1 and audio_uuid='%q'",
1034              AUDIO_SVC_DB_TABLE_AUDIO, changed_value, audio_id);
1035         err = _audio_svc_sql_query(handle, sql);
1036         sqlite3_free(sql);
1037         if (err != SQLITE_OK) {
1038                 audio_svc_error("update album_rating is failed(%d)", err);
1039                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
1040                         return err;
1041                 }
1042                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1043         }
1044
1045         return AUDIO_SVC_ERROR_NONE;
1046 }
1047
1048 int _audio_svc_count_music_group_records(sqlite3 *handle, audio_svc_group_type_e group_type,
1049                                          const char *limit_string1,
1050                                          const char *limit_string2,
1051                                          const char *filter_string,
1052                                          const char *filter_string2, int *count)
1053 {
1054         char query[AUDIO_SVC_QUERY_SIZE] = { 0 };
1055         char filter_query[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
1056         char filter_query2[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
1057         bool filter_mode = FALSE;
1058         bool filter_mode2 = FALSE;
1059         int err = -1;
1060         int text_bind = 1;
1061
1062         sqlite3_stmt *sql_stmt = NULL;
1063
1064         if (filter_string) {
1065                 if (strlen(filter_string) > 0) {
1066                         snprintf(filter_query, sizeof(filter_query), "%%%s%%",
1067                                  filter_string);
1068                         filter_mode = TRUE;
1069                 }
1070         }
1071         if (filter_string2) {
1072                 if (strlen(filter_string2) > 0) {
1073                         snprintf(filter_query2, sizeof(filter_query2), "%%%s%%",
1074                                  filter_string2);
1075                         filter_mode2 = TRUE;
1076                 }
1077         }
1078
1079         switch (group_type) {
1080         case AUDIO_SVC_GROUP_BY_ALBUM:
1081                 {
1082                         snprintf(query, sizeof(query),
1083                                  "select count(distinct album) from %s where valid=1 and category=%d",
1084                                  AUDIO_SVC_DB_TABLE_AUDIO,
1085                                  AUDIO_SVC_CATEGORY_MUSIC);
1086                         if (filter_mode) {
1087                                 g_strlcat(query, " and album like ?",
1088                                           sizeof(query));
1089                         }
1090                         if (filter_mode2) {
1091                                 g_strlcat(query, " and album like ?",
1092                                           sizeof(query));
1093                         }
1094
1095                 }
1096                 break;
1097
1098         case AUDIO_SVC_GROUP_BY_ARTIST:
1099                 {
1100                         snprintf(query, sizeof(query),
1101                                  "select count(distinct artist) from %s where valid=1 and category=%d",
1102                                  AUDIO_SVC_DB_TABLE_AUDIO,
1103                                  AUDIO_SVC_CATEGORY_MUSIC);
1104                         if (filter_mode) {
1105                                 g_strlcat(query, " and artist like ?",
1106                                           sizeof(query));
1107                         }
1108                         if (filter_mode2) {
1109                                 g_strlcat(query, " and artist like ?",
1110                                           sizeof(query));
1111                         }
1112
1113                 }
1114                 break;
1115
1116         case AUDIO_SVC_GROUP_BY_GENRE:
1117                 {
1118                         snprintf(query, sizeof(query),
1119                                  "select count(distinct genre) from %s where valid=1 and category=%d",
1120                                  AUDIO_SVC_DB_TABLE_AUDIO,
1121                                  AUDIO_SVC_CATEGORY_MUSIC);
1122                         if (filter_mode) {
1123                                 g_strlcat(query, " and genre like ?",
1124                                           sizeof(query));
1125                         }
1126                         if (filter_mode2) {
1127                                 g_strlcat(query, " and genre like ?",
1128                                           sizeof(query));
1129                         }
1130
1131                 }
1132                 break;
1133
1134         case AUDIO_SVC_GROUP_BY_FOLDER:
1135                 {
1136                         snprintf(query, sizeof(query),
1137                                  "select count(distinct folder_uuid) from %s where valid=1 and category=%d",
1138                                  AUDIO_SVC_DB_TABLE_AUDIO,
1139                                  AUDIO_SVC_CATEGORY_MUSIC);
1140                         /*FIX ME. if filter_mode exist. */
1141                         if (filter_mode) {
1142                                 snprintf(query, sizeof(query),
1143                                          "select count(distinct a.folder_uuid) from %s a inner join %s b on a.folder_uuid=b.folder_uuid \
1144                                         where a.valid=1 and a.category=%d and b.path like ?",
1145                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, AUDIO_SVC_CATEGORY_MUSIC);
1146                         }
1147                         if (filter_mode2) {
1148                                 g_strlcat(query, " and b.path like ?",
1149                                           sizeof(query));
1150                         }
1151                 }
1152                 break;
1153
1154         case AUDIO_SVC_GROUP_BY_YEAR:
1155                 {
1156                         snprintf(query, sizeof(query),
1157                                  "select count(distinct year) from %s where valid=1 and category=%d",
1158                                  AUDIO_SVC_DB_TABLE_AUDIO,
1159                                  AUDIO_SVC_CATEGORY_MUSIC);
1160                         if (filter_mode) {
1161                                 g_strlcat(query, " and year like ?",
1162                                           sizeof(query));
1163                         }
1164                         if (filter_mode2) {
1165                                 g_strlcat(query, " and year like ?",
1166                                           sizeof(query));
1167                         }
1168
1169                 }
1170                 break;
1171
1172         case AUDIO_SVC_GROUP_BY_COMPOSER:
1173                 {
1174                         snprintf(query, sizeof(query),
1175                                  "select count(distinct author) from %s where valid=1 and category=%d",
1176                                  AUDIO_SVC_DB_TABLE_AUDIO,
1177                                  AUDIO_SVC_CATEGORY_MUSIC);
1178                         if (filter_mode) {
1179                                 g_strlcat(query, " and author like ?",
1180                                           sizeof(query));
1181                         }
1182                         if (filter_mode2) {
1183                                 g_strlcat(query, " and author like ?",
1184                                           sizeof(query));
1185                         }
1186
1187                 }
1188                 break;
1189
1190         case AUDIO_SVC_GROUP_BY_ARTIST_ALBUM:
1191                 {
1192                         snprintf(query, sizeof(query),
1193                                  "select count(distinct album) from %s where valid=1 and category=%d",
1194                                  AUDIO_SVC_DB_TABLE_AUDIO,
1195                                  AUDIO_SVC_CATEGORY_MUSIC);
1196                         if (limit_string1 && strlen(limit_string1) > 0) {
1197                                 g_strlcat(query, " and artist=?",
1198                                           sizeof(query));
1199                         } else {
1200                                 g_strlcat(query, " and artist is null",
1201                                           sizeof(query));
1202                         }
1203                         if (filter_mode) {
1204                                 g_strlcat(query, " and album like ?",
1205                                           sizeof(query));
1206                         }
1207                         if (filter_mode2) {
1208                                 g_strlcat(query, " and album like ?",
1209                                           sizeof(query));
1210                         }
1211
1212                 }
1213                 break;
1214
1215         case AUDIO_SVC_GROUP_BY_GENRE_ARTIST:
1216                 {
1217                         snprintf(query, sizeof(query),
1218                                  "select count(distinct artist) from %s where valid=1 and category=%d",
1219                                  AUDIO_SVC_DB_TABLE_AUDIO,
1220                                  AUDIO_SVC_CATEGORY_MUSIC);
1221                         if (limit_string1 && strlen(limit_string1) > 0) {
1222                                 g_strlcat(query, " and genre=?", sizeof(query));
1223                         } else {
1224                                 g_strlcat(query, " and genre is null",
1225                                           sizeof(query));
1226                         }
1227                         if (filter_mode) {
1228                                 g_strlcat(query, " and artist like ?",
1229                                           sizeof(query));
1230                         }
1231                         if (filter_mode2) {
1232                                 g_strlcat(query, " and artist like ?",
1233                                           sizeof(query));
1234                         }
1235
1236                 }
1237                 break;
1238
1239         case AUDIO_SVC_GROUP_BY_GENRE_ALBUM:
1240                 {
1241                         snprintf(query, sizeof(query),
1242                                  "select count(distinct album) from %s where valid=1 and category=%d",
1243                                  AUDIO_SVC_DB_TABLE_AUDIO,
1244                                  AUDIO_SVC_CATEGORY_MUSIC);
1245                         if (limit_string1 && strlen(limit_string1) > 0) {
1246                                 g_strlcat(query, " and genre=?", sizeof(query));
1247                         } else {
1248                                 g_strlcat(query, " and genre is null",
1249                                           sizeof(query));
1250                         }
1251                         if (filter_mode) {
1252                                 g_strlcat(query, " and album like ?",
1253                                           sizeof(query));
1254                         }
1255                         if (filter_mode2) {
1256                                 g_strlcat(query, " and album like ?",
1257                                           sizeof(query));
1258                         }
1259
1260                 }
1261                 break;
1262
1263         case AUDIO_SVC_GROUP_BY_GENRE_ARTIST_ALBUM:
1264                 {
1265                         snprintf(query, sizeof(query),
1266                                  "select count(distinct album) from %s where valid=1 and category=%d",
1267                                  AUDIO_SVC_DB_TABLE_AUDIO,
1268                                  AUDIO_SVC_CATEGORY_MUSIC);
1269                         if (limit_string1 && strlen(limit_string1) > 0) {
1270                                 if (limit_string2 && strlen(limit_string2) > 0) {
1271                                         g_strlcat(query,
1272                                                   " and genre=? and artist=?",
1273                                                   sizeof(query));
1274                                 } else {
1275                                         g_strlcat(query,
1276                                                   " and genre=? and artist is null",
1277                                                   sizeof(query));
1278                                 }
1279                         } else {
1280                                 if (limit_string2 && strlen(limit_string2) > 0) {
1281                                         g_strlcat(query,
1282                                                   " and genre is null and artist=?",
1283                                                   sizeof(query));
1284                                 } else {
1285                                         g_strlcat(query,
1286                                                   " and genre is null and artist is null",
1287                                                   sizeof(query));
1288                                 }
1289                         }
1290
1291                         if (filter_mode) {
1292                                 g_strlcat(query, " and album like ?",
1293                                           sizeof(query));
1294                         }
1295                         if (filter_mode2) {
1296                                 g_strlcat(query, " and album like ?",
1297                                           sizeof(query));
1298                         }
1299
1300                 }
1301                 break;
1302
1303         default:
1304                 {
1305                         audio_svc_error("Wrong type [%d]", group_type);
1306                         return AUDIO_SVC_ERROR_INVALID_PARAMETER;
1307                 }
1308         }
1309
1310         audio_svc_debug("[SQL query] : %s", query);
1311
1312         err = sqlite3_prepare_v2(handle, query, -1, &sql_stmt, NULL);
1313         if (err != SQLITE_OK) {
1314                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
1315                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1316         }
1317
1318         if (group_type != AUDIO_SVC_GROUP_BY_ALBUM
1319             && group_type != AUDIO_SVC_GROUP_BY_ARTIST
1320             && group_type != AUDIO_SVC_GROUP_BY_GENRE
1321             && group_type != AUDIO_SVC_GROUP_BY_FOLDER) {
1322                 if (limit_string1 && strlen(limit_string1) > 0) {
1323                         if (SQLITE_OK !=
1324                             (err =
1325                              sqlite3_bind_text(sql_stmt, text_bind,
1326                                                (char *)limit_string1,
1327                                                strlen(limit_string1), NULL))) {
1328                                 audio_svc_error
1329                                     ("limit_string(%s) binding is failed (%d)",
1330                                      limit_string1, err);
1331                                 SQLITE3_FINALIZE(sql_stmt);
1332                                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1333                         }
1334                         text_bind++;
1335                 }
1336
1337                 if (limit_string2 && strlen(limit_string2) > 0
1338                     && group_type == AUDIO_SVC_GROUP_BY_GENRE_ARTIST_ALBUM) {
1339                         if (SQLITE_OK !=
1340                             (err =
1341                              sqlite3_bind_text(sql_stmt, text_bind,
1342                                                (char *)limit_string2,
1343                                                strlen(limit_string2), NULL))) {
1344                                 audio_svc_error
1345                                     ("limit_string2(%s) binding is failed (%d)",
1346                                      limit_string2, err);
1347                                 SQLITE3_FINALIZE(sql_stmt);
1348                                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1349                         }
1350                         text_bind++;
1351                 }
1352         }
1353
1354         if (filter_mode) {
1355                 if (SQLITE_OK !=
1356                     (err =
1357                      sqlite3_bind_text(sql_stmt, text_bind,
1358                                        (char *)filter_query,
1359                                        strlen(filter_query), NULL))) {
1360                         audio_svc_error
1361                             ("filter_query(%s) binding is failed (%d)",
1362                              filter_query, err);
1363                         SQLITE3_FINALIZE(sql_stmt);
1364                         return AUDIO_SVC_ERROR_DB_INTERNAL;
1365                 }
1366                 text_bind++;
1367         }
1368         if (filter_mode2) {
1369                 if (SQLITE_OK !=
1370                     (err =
1371                      sqlite3_bind_text(sql_stmt, text_bind,
1372                                        (char *)filter_query2,
1373                                        strlen(filter_query2), NULL))) {
1374                         audio_svc_error
1375                             ("filter_query(%s) binding is failed (%d)",
1376                              filter_query2, err);
1377                         SQLITE3_FINALIZE(sql_stmt);
1378                         return AUDIO_SVC_ERROR_DB_INTERNAL;
1379                 }
1380         }
1381
1382         err = sqlite3_step(sql_stmt);
1383         if (err != SQLITE_ROW) {
1384                 audio_svc_error("end of row [%s]", sqlite3_errmsg(handle));
1385                 SQLITE3_FINALIZE(sql_stmt);
1386                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1387         }
1388
1389         *count = sqlite3_column_int(sql_stmt, 0);
1390
1391         SQLITE3_FINALIZE(sql_stmt);
1392
1393         return AUDIO_SVC_ERROR_NONE;
1394
1395 }
1396
1397 int _audio_svc_get_music_group_records(sqlite3 *handle, audio_svc_group_type_e group_type,
1398                                        const char *limit_string1,
1399                                        const char *limit_string2,
1400                                        const char *filter_string,
1401                                        const char *filter_string2, int offset,
1402                                        int rows, audio_svc_group_item_s *group)
1403 {
1404         char query[AUDIO_SVC_QUERY_SIZE] = { 0 };
1405         char tail_query[100] = { 0 };
1406         char filter_query[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
1407         char filter_query2[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
1408         bool filter_mode = FALSE;
1409         bool filter_mode2 = FALSE;
1410         int err = -1;
1411         int text_bind = 1;
1412         int idx = 0;
1413
1414         sqlite3_stmt *sql_stmt = NULL;
1415
1416         if (filter_string) {
1417                 if (strlen(filter_string) > 0) {
1418                         snprintf(filter_query, sizeof(filter_query), "%%%s%%",
1419                                  filter_string);
1420                         filter_mode = TRUE;
1421                 }
1422         }
1423         if (filter_string2) {
1424                 if (strlen(filter_string2) > 0) {
1425                         snprintf(filter_query2, sizeof(filter_query2), "%%%s%%",
1426                                  filter_string2);
1427                         filter_mode2 = TRUE;
1428                 }
1429         }
1430
1431         switch (group_type) {
1432         case AUDIO_SVC_GROUP_BY_ALBUM:
1433                 {
1434                         snprintf(query, sizeof(query),
1435                                  "select album, min(audio_uuid), artist, thumbnail_path, album_rating from %s where valid=1 and category=%d",
1436                                  AUDIO_SVC_DB_TABLE_AUDIO,
1437                                  AUDIO_SVC_CATEGORY_MUSIC);
1438
1439                         if (filter_mode) {
1440                                 g_strlcat(query, " and album like ?",
1441                                           sizeof(query));
1442                         }
1443                         if (filter_mode2) {
1444                                 g_strlcat(query, " and album like ?",
1445                                           sizeof(query));
1446                         }
1447
1448                         snprintf(tail_query, sizeof(tail_query), " group by album %s limit %d,%d", AUDIO_SVC_ORDER_BY_ALBUM, offset, rows);
1449                         g_strlcat(query, tail_query, sizeof(query));
1450                 }
1451                 break;
1452
1453         case AUDIO_SVC_GROUP_BY_ARTIST:
1454                 {
1455                         snprintf(query, sizeof(query),
1456                                  "select artist, min(audio_uuid), album, thumbnail_path, album_rating from %s where valid=1 and category=%d",
1457                                  AUDIO_SVC_DB_TABLE_AUDIO,
1458                                  AUDIO_SVC_CATEGORY_MUSIC);
1459
1460                         if (filter_mode) {
1461                                 g_strlcat(query, " and artist like ? ",
1462                                           sizeof(query));
1463                         }
1464                         if (filter_mode2) {
1465                                 g_strlcat(query, " and artist like ? ",
1466                                           sizeof(query));
1467                         }
1468
1469                         snprintf(tail_query, sizeof(tail_query), " group by artist %s limit %d,%d", AUDIO_SVC_ORDER_BY_ARTIST, offset, rows);
1470                         g_strlcat(query, tail_query, sizeof(query));
1471                 }
1472                 break;
1473
1474         case AUDIO_SVC_GROUP_BY_ARTIST_ALBUM:
1475                 {
1476                         if (limit_string1 && strlen(limit_string1) > 0) {
1477                                 snprintf(query, sizeof(query),
1478                                          "select album, min(audio_uuid), artist, thumbnail_path, album_rating \
1479                                 from %s where valid=1 and category=%d and artist=?",
1480                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1481                         } else {
1482                                 snprintf(query, sizeof(query),
1483                                          "select album, min(audio_uuid), artist, thumbnail_path, album_rating \
1484                                         from %s where valid=1 and category=%d and artist is null",
1485                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1486                         }
1487                         if (filter_mode) {
1488                                 g_strlcat(query, " and album like ?",
1489                                           sizeof(query));
1490                         }
1491                         if (filter_mode2) {
1492                                 g_strlcat(query, " and album like ?",
1493                                           sizeof(query));
1494                         }
1495
1496                         snprintf(tail_query, sizeof(tail_query), " group by album %s limit %d,%d", AUDIO_SVC_ORDER_BY_ALBUM, offset, rows);
1497                         g_strlcat(query, tail_query, sizeof(query));
1498                 }
1499                 break;
1500
1501         case AUDIO_SVC_GROUP_BY_GENRE:
1502                 {
1503                         snprintf(query, sizeof(query),
1504                                  "select genre, min(audio_uuid), album, thumbnail_path, album_rating from %s where valid=1 and category=%d",
1505                                  AUDIO_SVC_DB_TABLE_AUDIO,
1506                                  AUDIO_SVC_CATEGORY_MUSIC);
1507
1508                         if (filter_mode) {
1509                                 g_strlcat(query, " and genre like ?",
1510                                           sizeof(query));
1511                         }
1512                         if (filter_mode2) {
1513                                 g_strlcat(query, " and genre like ?",
1514                                           sizeof(query));
1515                         }
1516
1517                         snprintf(tail_query, sizeof(tail_query), " group by genre %s limit %d,%d", AUDIO_SVC_ORDER_BY_GENRE, offset, rows);
1518                         g_strlcat(query, tail_query, sizeof(query));
1519                 }
1520                 break;
1521
1522         case AUDIO_SVC_GROUP_BY_FOLDER:
1523                 {
1524                         snprintf(query, sizeof(query),
1525                                  "select b.folder_name, min(a.audio_uuid), (b.path), (a.thumbnail_path), (a.album_rating) \
1526                                                                 from %s as a inner join %s b on a.folder_uuid=b.folder_uuid where a.valid=1 and a.category=%d",
1527                                  AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, AUDIO_SVC_CATEGORY_MUSIC);
1528                         if (filter_mode) {
1529                                 snprintf(query, sizeof(query),
1530                                          "select b.folder_name, min(a.audio_uuid), (b.path), (a.thumbnail_path), (a.album_rating) \
1531                                         from %s as a inner join %s b on a.folder_uuid=b.folder_uuid where a.valid=1 and a.category=%d and b.path like ?",
1532                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, AUDIO_SVC_CATEGORY_MUSIC);
1533                         }
1534                         if (filter_mode2) {
1535                                 snprintf(query, sizeof(query),
1536                                          "select b.folder_name, min(a.audio_uuid), (b.path), (a.thumbnail_path), (a.album_rating) \
1537                                         from %s as a inner join %s b on a.folder_uuid=b.folder_uuid where a.valid=1 and a.category=%d and b.path like ? and b.path like ?",
1538                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, AUDIO_SVC_CATEGORY_MUSIC);
1539                         }
1540
1541                         snprintf(tail_query, sizeof(tail_query),
1542                                  " group by a.folder_uuid order by b.folder_name %s, b.path %s limit %d,%d",
1543                                  AUDIO_SVC_COLLATE_NOCASE, AUDIO_SVC_COLLATE_NOCASE, offset, rows);
1544                         g_strlcat(query, tail_query, sizeof(query));
1545                 }
1546                 break;
1547
1548         case AUDIO_SVC_GROUP_BY_YEAR:
1549                 {
1550                         snprintf(query, sizeof(query),
1551                                  "select year, min(audio_uuid), album, thumbnail_path, album_rating from %s where valid=1 and category=%d",
1552                                  AUDIO_SVC_DB_TABLE_AUDIO,
1553                                  AUDIO_SVC_CATEGORY_MUSIC);
1554
1555                         if (filter_mode) {
1556                                 g_strlcat(query, " and year like ?",
1557                                           sizeof(query));
1558                         }
1559                         if (filter_mode2) {
1560                                 g_strlcat(query, " and year like ?",
1561                                           sizeof(query));
1562                         }
1563
1564                         snprintf(tail_query, sizeof(tail_query),
1565                                  " group by year order by year desc limit %d,%d",
1566                                  offset, rows);
1567                         g_strlcat(query, tail_query, sizeof(query));
1568                 }
1569                 break;
1570
1571         case AUDIO_SVC_GROUP_BY_COMPOSER:
1572                 {
1573                         snprintf(query, sizeof(query),
1574                                  "select author, min(audio_uuid), album, thumbnail_path, album_rating from %s where valid=1 and category=%d",
1575                                  AUDIO_SVC_DB_TABLE_AUDIO,
1576                                  AUDIO_SVC_CATEGORY_MUSIC);
1577
1578                         if (filter_mode) {
1579                                 g_strlcat(query, " and author like ?",
1580                                           sizeof(query));
1581                         }
1582                         if (filter_mode2) {
1583                                 g_strlcat(query, " and author like ?",
1584                                           sizeof(query));
1585                         }
1586
1587                         snprintf(tail_query, sizeof(tail_query), " group by author %s limit %d,%d", AUDIO_SVC_ORDER_BY_AUTHOR, offset, rows);
1588                         g_strlcat(query, tail_query, sizeof(query));
1589                 }
1590                 break;
1591
1592         case AUDIO_SVC_GROUP_BY_GENRE_ARTIST:
1593                 {
1594                         if (limit_string1 && strlen(limit_string1) > 0) {
1595                                 snprintf(query, sizeof(query),
1596                                          "select artist, min(audio_uuid), album, thumbnail_path, album_rating \
1597                                         from %s where valid=1 and category=%d and genre=?",
1598                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1599                         } else {
1600                                 snprintf(query, sizeof(query),
1601                                          "select artist, min(audio_uuid), album, thumbnail_path, album_rating \
1602                                         from %s where valid=1 and category=%d and genre is null",
1603                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1604                         }
1605                         if (filter_mode) {
1606                                 g_strlcat(query, " and artist like ?",
1607                                           sizeof(query));
1608                         }
1609                         if (filter_mode2) {
1610                                 g_strlcat(query, " and artist like ?",
1611                                           sizeof(query));
1612                         }
1613
1614                         snprintf(tail_query, sizeof(tail_query), " group by artist %s limit %d,%d", AUDIO_SVC_ORDER_BY_ARTIST, offset, rows);
1615                         g_strlcat(query, tail_query, sizeof(query));
1616                 }
1617                 break;
1618
1619         case AUDIO_SVC_GROUP_BY_GENRE_ALBUM:
1620                 {
1621                         if (limit_string1 && strlen(limit_string1) > 0) {
1622                                 snprintf(query, sizeof(query),
1623                                          "select distinct album, min(audio_uuid), artist, thumbnail_path, album_rating \
1624                                         from %s where valid=1 and category=%d and genre=?",
1625                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1626                         } else {
1627                                 snprintf(query, sizeof(query),
1628                                          "select distinct album, min(audio_uuid), artist, thumbnail_path, album_rating \
1629                                         from %s where valid=1 and category=%d and genre is null",
1630                                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1631                         }
1632                         if (filter_mode) {
1633                                 g_strlcat(query, " and album like ?",
1634                                           sizeof(query));
1635                         }
1636                         if (filter_mode2) {
1637                                 g_strlcat(query, " and album like ?",
1638                                           sizeof(query));
1639                         }
1640
1641                         snprintf(tail_query, sizeof(tail_query), " group by album %s limit %d,%d", AUDIO_SVC_ORDER_BY_ALBUM, offset, rows);
1642                         g_strlcat(query, tail_query, sizeof(query));
1643                 }
1644                 break;
1645
1646         case AUDIO_SVC_GROUP_BY_GENRE_ARTIST_ALBUM:
1647                 {
1648                         if (limit_string1 && strlen(limit_string1) > 0) {
1649                                 if (limit_string2 && strlen(limit_string2) > 0) {
1650                                         snprintf(query, sizeof(query),
1651                                                  "select album, min(audio_uuid), artist, thumbnail_path, album_rating \
1652                                                 from %s where valid=1 and category=%d and genre=? and artist=?",
1653                                                  AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1654                                 } else {
1655                                         snprintf(query, sizeof(query),
1656                                                  "select album, min(audio_uuid), artist, thumbnail_path, album_rating \
1657                                                 from %s where valid=1 and category=%d and genre=? and artist is null",
1658                                                  AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1659                                 }
1660                         } else {
1661                                 if (limit_string2 && strlen(limit_string2) > 0) {
1662                                         snprintf(query, sizeof(query),
1663                                                  "select album, min(audio_uuid), artist, thumbnail_path, album_rating \
1664                                                 from %s where valid=1 and category=%d and genre is null and artist=?",
1665                                                  AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1666                                 } else {
1667                                         snprintf(query, sizeof(query),
1668                                                  "select album, min(audio_uuid), artist, thumbnail_path, album_rating \
1669                                                 from %s where valid=1 and category=%d and genre is null and artist is null",
1670                                                  AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1671                                 }
1672                         }
1673
1674                         if (filter_mode) {
1675                                 g_strlcat(query, " and album like ?",
1676                                           sizeof(query));
1677                         }
1678                         if (filter_mode2) {
1679                                 g_strlcat(query, " and album like ?",
1680                                           sizeof(query));
1681                         }
1682
1683                         snprintf(tail_query, sizeof(tail_query), " group by album %s limit %d,%d", AUDIO_SVC_ORDER_BY_ALBUM, offset, rows);
1684                         g_strlcat(query, tail_query, sizeof(query));
1685                 }
1686                 break;
1687
1688         }
1689
1690         audio_svc_debug("[SQL query] : %s", query);
1691
1692         err = sqlite3_prepare_v2(handle, query, -1, &sql_stmt, NULL);
1693         if (err != SQLITE_OK) {
1694                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
1695                 return AUDIO_SVC_ERROR_DB_INTERNAL;
1696         }
1697
1698         if ((limit_string1) && strlen(limit_string1)
1699             && (group_type != AUDIO_SVC_GROUP_BY_ALBUM)
1700             && (group_type != AUDIO_SVC_GROUP_BY_ARTIST)) {
1701                 if (SQLITE_OK !=
1702                     (err =
1703                      sqlite3_bind_text(sql_stmt, text_bind,
1704                                        (char *)limit_string1,
1705                                        strlen(limit_string1), NULL))) {
1706                         audio_svc_error
1707                             ("limit_string1(%s) binding is failed (%d)",
1708                              limit_string1, err);
1709                         SQLITE3_FINALIZE(sql_stmt);
1710                         return AUDIO_SVC_ERROR_DB_INTERNAL;
1711                 }
1712                 text_bind++;
1713         }
1714
1715         if ((limit_string2) && strlen(limit_string2)
1716             && (group_type == AUDIO_SVC_GROUP_BY_GENRE_ARTIST_ALBUM)) {
1717                 if (SQLITE_OK !=
1718                     (err =
1719                      sqlite3_bind_text(sql_stmt, text_bind,
1720                                        (char *)limit_string2,
1721                                        strlen(limit_string2), NULL))) {
1722                         audio_svc_error
1723                             ("limit_string2(%s) binding is failed (%d)",
1724                              limit_string2, err);
1725                         SQLITE3_FINALIZE(sql_stmt);
1726                         return AUDIO_SVC_ERROR_DB_INTERNAL;
1727                 }
1728                 text_bind++;
1729         }
1730
1731         if (filter_mode) {
1732                 if (SQLITE_OK !=
1733                     (err =
1734                      sqlite3_bind_text(sql_stmt, text_bind,
1735                                        (char *)filter_query,
1736                                        strlen(filter_query), NULL))) {
1737                         audio_svc_error
1738                             ("filter_query(%s) binding is failed (%d)",
1739                              filter_query, err);
1740                         SQLITE3_FINALIZE(sql_stmt);
1741                         return AUDIO_SVC_ERROR_DB_INTERNAL;
1742                 }
1743                 text_bind++;
1744         }
1745         if (filter_mode2) {
1746                 if (SQLITE_OK !=
1747                     (err =
1748                      sqlite3_bind_text(sql_stmt, text_bind,
1749                                        (char *)filter_query2,
1750                                        strlen(filter_query2), NULL))) {
1751                         audio_svc_error
1752                             ("filter_query(%s) binding is failed (%d)",
1753                              filter_query2, err);
1754                         SQLITE3_FINALIZE(sql_stmt);
1755                         return AUDIO_SVC_ERROR_DB_INTERNAL;
1756                 }
1757                 text_bind++;
1758         }
1759
1760         while (sqlite3_step(sql_stmt) == SQLITE_ROW) {
1761                 if (group_type == AUDIO_SVC_GROUP_BY_YEAR) {
1762                         int year = sqlite3_column_int(sql_stmt, 0);
1763                         if (year == -1 || year == 0) {
1764                                 _strncpy_safe(group[idx].maininfo,
1765                                               AUDIO_SVC_TAG_UNKNOWN,
1766                                               sizeof(group[idx].maininfo));
1767                         } else {
1768                                 _strncpy_safe(group[idx].maininfo,
1769                                               (const char *)
1770                                               sqlite3_column_text(sql_stmt, 0),
1771                                               sizeof(group[idx].maininfo));
1772                         }
1773                 } else {
1774                         _strncpy_safe(group[idx].maininfo,
1775                                       (const char *)
1776                                       sqlite3_column_text(sql_stmt, 0),
1777                                       sizeof(group[idx].maininfo));
1778                 }
1779
1780                 _strncpy_safe(group[idx].subinfo,
1781                               (const char *)sqlite3_column_text(sql_stmt, 2),
1782                               sizeof(group[idx].subinfo));
1783
1784                 _strncpy_safe(group[idx].thumbnail_path,
1785                               (const char *)sqlite3_column_text(sql_stmt, 3),
1786                               sizeof(group[idx].thumbnail_path));
1787
1788                 group[idx].album_rating = sqlite3_column_int(sql_stmt, 4);
1789
1790                 idx++;
1791         }
1792
1793         SQLITE3_FINALIZE(sql_stmt);
1794
1795         return AUDIO_SVC_ERROR_NONE;
1796
1797 }
1798
1799 int _audio_svc_count_music_track_records(sqlite3 *handle, audio_svc_track_type_e track_type,
1800                                          const char *type_string,
1801                                          const char *type_string2,
1802                                          const char *filter_string,
1803                                          const char *filter_string2, int *count)
1804 {
1805         char query[AUDIO_SVC_QUERY_SIZE] = { 0 };
1806         char filter_query[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
1807         char filter_query2[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
1808         bool filter_mode = FALSE;
1809         bool filter_mode2 = FALSE;
1810         int err = -1;
1811         int text_bind = 1;
1812
1813         sqlite3_stmt *sql_stmt = NULL;
1814
1815         if (filter_string) {
1816                 if (strlen(filter_string) > 0) {
1817                         snprintf(filter_query, sizeof(filter_query), "%%%s%%",
1818                                  filter_string);
1819                         filter_mode = TRUE;
1820                 }
1821         }
1822         if (filter_string2) {
1823                 if (strlen(filter_string2) > 0) {
1824                         snprintf(filter_query2, sizeof(filter_query2), "%%%s%%",
1825                                  filter_string2);
1826                         filter_mode2 = TRUE;
1827                 }
1828         }
1829
1830         switch (track_type) {
1831         case AUDIO_SVC_TRACK_ALL:
1832                 snprintf(query, sizeof(query),
1833                          "select count(*) from %s where valid=1 and category=%d",
1834                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1835                 break;
1836
1837         case AUDIO_SVC_TRACK_BY_ALBUM:
1838                 if (type_string && strlen(type_string) > 0) {
1839                         snprintf(query, sizeof(query),
1840                                  "select count(*) from %s where valid=1 and category=%d and album=?",
1841                                  AUDIO_SVC_DB_TABLE_AUDIO,
1842                                  AUDIO_SVC_CATEGORY_MUSIC);
1843                 } else {
1844                         snprintf(query, sizeof(query),
1845                                  "select count(*) from %s where valid=1 and category=%d and album is null",
1846                                  AUDIO_SVC_DB_TABLE_AUDIO,
1847                                  AUDIO_SVC_CATEGORY_MUSIC);
1848                 }
1849                 break;
1850
1851         case AUDIO_SVC_TRACK_BY_ARTIST_ALBUM:
1852                 if (type_string && strlen(type_string) > 0) {
1853                         if (type_string2 && strlen(type_string2) > 0) {
1854                                 snprintf(query, sizeof(query),
1855                                          "select count(*) from %s where valid=1 and category=%d and album=? and artist=?",
1856                                          AUDIO_SVC_DB_TABLE_AUDIO,
1857                                          AUDIO_SVC_CATEGORY_MUSIC);
1858                         } else {
1859                                 snprintf(query, sizeof(query),
1860                                          "select count(*) from %s where valid=1 and category=%d and album=? and artist is null",
1861                                          AUDIO_SVC_DB_TABLE_AUDIO,
1862                                          AUDIO_SVC_CATEGORY_MUSIC);
1863                         }
1864                 } else {
1865                         if (type_string2 && strlen(type_string2) > 0) {
1866                                 snprintf(query, sizeof(query),
1867                                          "select count(*) from %s where valid=1 and category=%d and album is null and artist=?",
1868                                          AUDIO_SVC_DB_TABLE_AUDIO,
1869                                          AUDIO_SVC_CATEGORY_MUSIC);
1870                         } else {
1871                                 snprintf(query, sizeof(query),
1872                                          "select count(*) from %s where valid=1 and category=%d and album is null and artist is null",
1873                                          AUDIO_SVC_DB_TABLE_AUDIO,
1874                                          AUDIO_SVC_CATEGORY_MUSIC);
1875                         }
1876                 }
1877
1878                 break;
1879
1880         case AUDIO_SVC_TRACK_BY_ARTIST:
1881                 if (type_string && strlen(type_string) > 0) {
1882                         snprintf(query, sizeof(query),
1883                                  "select count(*) from %s where valid=1 and category=%d and artist=?",
1884                                  AUDIO_SVC_DB_TABLE_AUDIO,
1885                                  AUDIO_SVC_CATEGORY_MUSIC);
1886                 } else {
1887                         snprintf(query, sizeof(query),
1888                                  "select count(*) from %s where valid=1 and category=%d and artist is null",
1889                                  AUDIO_SVC_DB_TABLE_AUDIO,
1890                                  AUDIO_SVC_CATEGORY_MUSIC);
1891                 }
1892                 break;
1893
1894         case AUDIO_SVC_TRACK_BY_ARTIST_GENRE:
1895                 if (type_string && strlen(type_string) > 0) {
1896                         if (type_string2 && strlen(type_string2) > 0) {
1897                                 snprintf(query, sizeof(query),
1898                                          "select count(*) from %s where valid=1 and category=%d and genre=? and artist=?",
1899                                          AUDIO_SVC_DB_TABLE_AUDIO,
1900                                          AUDIO_SVC_CATEGORY_MUSIC);
1901                         } else {
1902                                 snprintf(query, sizeof(query),
1903                                          "select count(*) from %s where valid=1 and category=%d and genre=? and artist is null",
1904                                          AUDIO_SVC_DB_TABLE_AUDIO,
1905                                          AUDIO_SVC_CATEGORY_MUSIC);
1906                         }
1907                 } else {
1908                         if (type_string2 && strlen(type_string2) > 0) {
1909                                 snprintf(query, sizeof(query),
1910                                          "select count(*) from %s where valid=1 and category=%d and genre is null and artist=?",
1911                                          AUDIO_SVC_DB_TABLE_AUDIO,
1912                                          AUDIO_SVC_CATEGORY_MUSIC);
1913                         } else {
1914                                 snprintf(query, sizeof(query),
1915                                          "select count(*) from %s where valid=1 and category=%d and genre is null and artist is null",
1916                                          AUDIO_SVC_DB_TABLE_AUDIO,
1917                                          AUDIO_SVC_CATEGORY_MUSIC);
1918                         }
1919
1920                 }
1921
1922                 break;
1923
1924         case AUDIO_SVC_TRACK_BY_GENRE:
1925                 if (type_string && strlen(type_string) > 0) {
1926                         snprintf(query, sizeof(query),
1927                                  "select count(*) from %s where valid=1 and category=%d and genre=?",
1928                                  AUDIO_SVC_DB_TABLE_AUDIO,
1929                                  AUDIO_SVC_CATEGORY_MUSIC);
1930                 } else {
1931                         snprintf(query, sizeof(query),
1932                                  "select count(*) from %s where valid=1 and category=%d and genre is null",
1933                                  AUDIO_SVC_DB_TABLE_AUDIO,
1934                                  AUDIO_SVC_CATEGORY_MUSIC);
1935                 }
1936                 break;
1937
1938         case AUDIO_SVC_TRACK_BY_FOLDER:
1939                 if (type_string && strlen(type_string) > 0) {
1940                         snprintf(query, sizeof(query),
1941                                  "select count(*) from %s where valid=1 and category=%d and folder_uuid=(select folder_uuid from %s where path = ?)",
1942                                  AUDIO_SVC_DB_TABLE_AUDIO,
1943                                  AUDIO_SVC_CATEGORY_MUSIC,
1944                                  AUDIO_SVC_DB_TABLE_AUDIO_FOLDER);
1945                 } else {
1946                         snprintf(query, sizeof(query),
1947                                  "select count(*) from %s where valid=1 and category=%d and folder_uuid is null",
1948                                  AUDIO_SVC_DB_TABLE_AUDIO,
1949                                  AUDIO_SVC_CATEGORY_MUSIC);
1950                 }
1951                 break;
1952
1953         case AUDIO_SVC_TRACK_BY_YEAR:
1954                 if (type_string && strlen(type_string) > 0) {
1955                         snprintf(query, sizeof(query),
1956                                  "select count(*) from %s where valid=1 and category=%d and year=?",
1957                                  AUDIO_SVC_DB_TABLE_AUDIO,
1958                                  AUDIO_SVC_CATEGORY_MUSIC);
1959                 } else {
1960                         snprintf(query, sizeof(query),
1961                                  "select count(*) from %s where valid=1 and category=%d and year is null",
1962                                  AUDIO_SVC_DB_TABLE_AUDIO,
1963                                  AUDIO_SVC_CATEGORY_MUSIC);
1964                 }
1965                 break;
1966
1967         case AUDIO_SVC_TRACK_BY_COMPOSER:
1968                 if (type_string && strlen(type_string) > 0) {
1969                         snprintf(query, sizeof(query),
1970                                  "select count(*) from %s where valid=1 and category=%d and author=?",
1971                                  AUDIO_SVC_DB_TABLE_AUDIO,
1972                                  AUDIO_SVC_CATEGORY_MUSIC);
1973                 } else {
1974                         snprintf(query, sizeof(query),
1975                                  "select count(*) from %s where valid=1 and category=%d and author is null",
1976                                  AUDIO_SVC_DB_TABLE_AUDIO,
1977                                  AUDIO_SVC_CATEGORY_MUSIC);
1978                 }
1979                 break;
1980
1981         case AUDIO_SVC_TRACK_BY_TOPRATING:
1982                 snprintf(query, sizeof(query),
1983                          "select count(*) from %s where valid=1 and category=%d and rating > %d",
1984                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC,
1985                          AUDIO_SVC_RATING_3);
1986                 break;
1987
1988         case AUDIO_SVC_TRACK_BY_PLAYED_TIME:
1989                 snprintf(query, sizeof(query),
1990                          "select count(*) from %s where valid=1 and category=%d and last_played_time > 0",
1991                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1992                 break;
1993
1994         case AUDIO_SVC_TRACK_BY_ADDED_TIME:
1995                 snprintf(query, sizeof(query),
1996                          "select count(*) from %s where valid=1 and category=%d and added_time > 0",
1997                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
1998                 break;
1999
2000         case AUDIO_SVC_TRACK_BY_PLAYED_COUNT:
2001                 snprintf(query, sizeof(query),
2002                          "select count(*) from %s where valid=1 and category=%d and played_count > 0",
2003                          AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC);
2004                 break;
2005
2006         case AUDIO_SVC_TRACK_BY_PLAYLIST:
2007                 {
2008                         snprintf(query, sizeof(query),
2009                                  "select count(*) from %s a, %s b where a.playlist_id=%d and b.audio_uuid=a.audio_uuid and b.valid=1 and b.category=%d",
2010                                  AUDIO_SVC_DB_TABLE_AUDIO_PLAYLISTS_MAP,
2011                                  AUDIO_SVC_DB_TABLE_AUDIO, (int)type_string,
2012                                  AUDIO_SVC_CATEGORY_MUSIC);
2013
2014                         if (filter_mode) {
2015                                 g_strlcat(query, " and b.title like ?",
2016                                           sizeof(query));
2017                         }
2018                         if (filter_mode2) {
2019                                 g_strlcat(query, " and b.title like ?",
2020                                           sizeof(query));
2021                         }
2022                 }
2023                 break;
2024
2025         default:
2026                 break;
2027         }
2028
2029         if (track_type != AUDIO_SVC_TRACK_BY_PLAYLIST) {
2030                 if (filter_mode) {
2031                         g_strlcat(query, " and title like ?", sizeof(query));
2032                 }
2033                 if (filter_mode2) {
2034                         g_strlcat(query, " and title like ?", sizeof(query));
2035                 }
2036         }
2037         audio_svc_debug("[SQL query] : %s", query);
2038
2039         err = sqlite3_prepare_v2(handle, query, -1, &sql_stmt, NULL);
2040         if (err != SQLITE_OK) {
2041                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
2042                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2043         }
2044
2045         if (filter_mode) {
2046                 if (SQLITE_OK !=
2047                     (err =
2048                      sqlite3_bind_text(sql_stmt, text_bind,
2049                                        (char *)filter_query,
2050                                        strlen(filter_query), NULL))) {
2051                         audio_svc_error
2052                             ("filter_query(%s) binding is failed (%d)",
2053                              filter_query, err);
2054                         SQLITE3_FINALIZE(sql_stmt);
2055                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2056                 }
2057                 text_bind++;
2058         }
2059         if (filter_mode2) {
2060                 if (SQLITE_OK !=
2061                     (err =
2062                      sqlite3_bind_text(sql_stmt, text_bind,
2063                                        (char *)filter_query2,
2064                                        strlen(filter_query2), NULL))) {
2065                         audio_svc_error
2066                             ("filter_query(%s) binding is failed (%d)",
2067                              filter_query2, err);
2068                         SQLITE3_FINALIZE(sql_stmt);
2069                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2070                 }
2071                 text_bind++;
2072         }
2073
2074         if ((track_type >= AUDIO_SVC_TRACK_BY_ALBUM)
2075             && (track_type <= AUDIO_SVC_TRACK_BY_COMPOSER)) {
2076                 if (type_string && strlen(type_string) > 0) {
2077                         if ((track_type == AUDIO_SVC_TRACK_BY_YEAR)
2078                             && (!strcmp(type_string, AUDIO_SVC_TAG_UNKNOWN))) {
2079                                 if (SQLITE_OK !=
2080                                     (err =
2081                                      sqlite3_bind_text(sql_stmt, text_bind,
2082                                                        "-1", strlen("-1"),
2083                                                        NULL))) {
2084                                         audio_svc_error
2085                                             ("cont_string1(%s) binding is failed (%d)",
2086                                              type_string, err);
2087                                         SQLITE3_FINALIZE(sql_stmt);
2088                                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2089                                 }
2090                                 text_bind++;
2091                         } else
2092                             if ((track_type == AUDIO_SVC_TRACK_BY_ARTIST_GENRE)
2093                                 || (track_type ==
2094                                     AUDIO_SVC_TRACK_BY_ARTIST_ALBUM)) {
2095                                 if (type_string2 && strlen(type_string2) > 0) {
2096                                         if (SQLITE_OK !=
2097                                             (err =
2098                                              sqlite3_bind_text(sql_stmt,
2099                                                                text_bind,
2100                                                                (char *)
2101                                                                type_string,
2102                                                                strlen
2103                                                                (type_string),
2104                                                                NULL))) {
2105                                                 audio_svc_error
2106                                                     ("cont_string1(%s) binding is failed (%d)",
2107                                                      type_string, err);
2108                                                 SQLITE3_FINALIZE(sql_stmt);
2109                                                 return
2110                                                     AUDIO_SVC_ERROR_DB_INTERNAL;
2111                                         }
2112                                         text_bind++;
2113                                         if (SQLITE_OK !=
2114                                             (err =
2115                                              sqlite3_bind_text(sql_stmt,
2116                                                                text_bind,
2117                                                                (char *)
2118                                                                type_string2,
2119                                                                strlen
2120                                                                (type_string2),
2121                                                                NULL))) {
2122                                                 audio_svc_error
2123                                                     ("cont_string2(%s) binding is failed (%d)",
2124                                                      type_string2, err);
2125                                                 SQLITE3_FINALIZE(sql_stmt);
2126                                                 return
2127                                                     AUDIO_SVC_ERROR_DB_INTERNAL;
2128                                         }
2129                                         text_bind++;
2130                                 } else {
2131                                         if (SQLITE_OK !=
2132                                             (err =
2133                                              sqlite3_bind_text(sql_stmt,
2134                                                                text_bind,
2135                                                                (char *)
2136                                                                type_string,
2137                                                                strlen
2138                                                                (type_string),
2139                                                                NULL))) {
2140                                                 audio_svc_error
2141                                                     ("cont_string1(%s) binding is failed (%d)",
2142                                                      type_string, err);
2143                                                 SQLITE3_FINALIZE(sql_stmt);
2144                                                 return
2145                                                     AUDIO_SVC_ERROR_DB_INTERNAL;
2146                                         }
2147                                 }
2148                         } else {
2149                                 if (SQLITE_OK !=
2150                                     (err =
2151                                      sqlite3_bind_text(sql_stmt, text_bind,
2152                                                        (char *)type_string,
2153                                                        strlen(type_string),
2154                                                        NULL))) {
2155                                         audio_svc_error
2156                                             ("cont_string1(%s) binding is failed (%d)",
2157                                              type_string, err);
2158                                         SQLITE3_FINALIZE(sql_stmt);
2159                                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2160                                 }
2161                         }
2162                         text_bind++;
2163                 }
2164         }
2165
2166         audio_svc_debug("[SQL query] : %s", query);
2167
2168         err = sqlite3_step(sql_stmt);
2169         if (err != SQLITE_ROW) {
2170                 audio_svc_error("end of row [%s]", sqlite3_errmsg(handle));
2171                 SQLITE3_FINALIZE(sql_stmt);
2172                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2173         }
2174         *count = sqlite3_column_int(sql_stmt, 0);
2175
2176         SQLITE3_FINALIZE(sql_stmt);
2177
2178         return AUDIO_SVC_ERROR_NONE;
2179
2180 }
2181
2182 int _audio_svc_get_music_track_records(sqlite3 *handle, audio_svc_track_type_e track_type,
2183                                        const char *type_string,
2184                                        const char *type_string2,
2185                                        const char *filter_string,
2186                                        const char *filter_string2, int offset,
2187                                        int rows, audio_svc_list_item_s *track)
2188 {
2189         char query[AUDIO_SVC_QUERY_SIZE] = { 0 };
2190         char *result_field = "audio_uuid, path, thumbnail_path, title, artist, duration, rating, album";
2191
2192         char tail_query[70] = { 0 };
2193         char filter_query[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
2194         char filter_query2[AUDIO_SVC_METADATA_LEN_MAX + 5] = { 0 };
2195         bool filter_mode = FALSE;
2196         bool filter_mode2 = FALSE;
2197         int err = -1;
2198         int len = -1;
2199         int text_bind = 1;
2200         int idx = 0;
2201
2202         sqlite3_stmt *sql_stmt = NULL;
2203
2204 #define filter_condition(filter_mode, filter_mode2, query)      \
2205         if ((filter_mode))      g_strlcat((query), " and title like ?", sizeof((query))); \
2206         if ((filter_mode2))     g_strlcat((query), " and title like ?", sizeof((query)));
2207
2208         filter_mode = STRING_VALID(filter_string);
2209         filter_mode2 = STRING_VALID(filter_string2);
2210
2211         if (filter_mode) {
2212                 snprintf(filter_query, sizeof(filter_query), "%%%s%%",
2213                          filter_string);
2214         }
2215         if (filter_mode2) {
2216                 snprintf(filter_query2, sizeof(filter_query2), "%%%s%%",
2217                          filter_string2);
2218         }
2219
2220         switch (track_type) {
2221         case AUDIO_SVC_TRACK_ALL:
2222                 {
2223                         snprintf(query, sizeof(query),
2224                                  "select %s from %s where valid=1 and category=%d",
2225                                  result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2226                                  AUDIO_SVC_CATEGORY_MUSIC);
2227                         filter_condition(filter_mode, filter_mode2, query);
2228                         snprintf(tail_query, sizeof(tail_query),
2229                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2230                         g_strlcat(query, tail_query, sizeof(query));
2231                 }
2232                 break;
2233
2234         case AUDIO_SVC_TRACK_BY_ALBUM:
2235                 {
2236                         if (type_string && strlen(type_string) > 0) {
2237                                 snprintf(query, sizeof(query),
2238                                          "select %s from %s where valid=1 and category=%d and album=?",
2239                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2240                                          AUDIO_SVC_CATEGORY_MUSIC);
2241                         } else {
2242                                 snprintf(query, sizeof(query),
2243                                          "select %s from %s where valid=1 and category=%d and album is null",
2244                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2245                                          AUDIO_SVC_CATEGORY_MUSIC);
2246                         }
2247                         filter_condition(filter_mode, filter_mode2, query);
2248                         snprintf(tail_query, sizeof(tail_query),
2249                                  " order by track_num limit %d,%d", offset,
2250                                  rows);
2251                         g_strlcat(query, tail_query, sizeof(query));
2252                 }
2253                 break;
2254
2255         case AUDIO_SVC_TRACK_BY_ARTIST_ALBUM:
2256                 {
2257                         if (type_string && strlen(type_string) > 0) {
2258                                 if (type_string2 && strlen(type_string2) > 0) {
2259                                         snprintf(query, sizeof(query),
2260                                                  "select %s from %s where valid=1 and category=%d and album=? and artist=?",
2261                                                  result_field,
2262                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2263                                                  AUDIO_SVC_CATEGORY_MUSIC);
2264                                 } else {
2265                                         snprintf(query, sizeof(query),
2266                                                  "select %s from %s where valid=1 and category=%d and album=? and artist is null",
2267                                                  result_field,
2268                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2269                                                  AUDIO_SVC_CATEGORY_MUSIC);
2270                                 }
2271                         } else {
2272                                 if (type_string2 && strlen(type_string2) > 0) {
2273                                         snprintf(query, sizeof(query),
2274                                                  "select %s from %s where valid=1 and category=%d and album is null and artist=?",
2275                                                  result_field,
2276                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2277                                                  AUDIO_SVC_CATEGORY_MUSIC);
2278                                 } else {
2279                                         snprintf(query, sizeof(query),
2280                                                  "select %s from %s where valid=1 and category=%d and album is null and artist is null",
2281                                                  result_field,
2282                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2283                                                  AUDIO_SVC_CATEGORY_MUSIC);
2284                                 }
2285                         }
2286
2287                         filter_condition(filter_mode, filter_mode2, query);
2288
2289                         snprintf(tail_query, sizeof(tail_query),
2290                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2291                         g_strlcat(query, tail_query, sizeof(query));
2292                 }
2293                 break;
2294
2295         case AUDIO_SVC_TRACK_BY_ARTIST:
2296                 {
2297                         if (type_string && strlen(type_string) > 0) {
2298                                 snprintf(query, sizeof(query),
2299                                          "select %s, album from %s where valid=1 and category=%d and artist=?",
2300                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2301                                          AUDIO_SVC_CATEGORY_MUSIC);
2302                         } else {
2303                                 snprintf(query, sizeof(query),
2304                                          "select %s, album from %s where valid=1 and category=%d and artist is null",
2305                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2306                                          AUDIO_SVC_CATEGORY_MUSIC);
2307                         }
2308                         filter_condition(filter_mode, filter_mode2, query);
2309                         snprintf(tail_query, sizeof(tail_query), " order by album %s, title %s limit %d,%d", AUDIO_SVC_COLLATE_NOCASE, AUDIO_SVC_COLLATE_NOCASE, offset, rows);
2310                         g_strlcat(query, tail_query, sizeof(query));
2311                 }
2312                 break;
2313
2314         case AUDIO_SVC_TRACK_BY_ARTIST_GENRE:
2315                 {
2316                         if (type_string && strlen(type_string) > 0) {
2317                                 if (type_string2 && strlen(type_string2) > 0) {
2318                                         snprintf(query, sizeof(query),
2319                                                  "select %s from %s where valid=1 and category=%d and genre=? and artist=?",
2320                                                  result_field,
2321                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2322                                                  AUDIO_SVC_CATEGORY_MUSIC);
2323                                 } else {
2324                                         snprintf(query, sizeof(query),
2325                                                  "select %s from %s where valid=1 and category=%d and genre=? and artist is null",
2326                                                  result_field,
2327                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2328                                                  AUDIO_SVC_CATEGORY_MUSIC);
2329                                 }
2330                         } else {
2331                                 if (type_string2 && strlen(type_string2) > 0) {
2332                                         snprintf(query, sizeof(query),
2333                                                  "select %s from %s where valid=1 and category=%d and genre is null and artist=?",
2334                                                  result_field,
2335                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2336                                                  AUDIO_SVC_CATEGORY_MUSIC);
2337                                 } else {
2338                                         snprintf(query, sizeof(query),
2339                                                  "select %s from %s where valid=1 and category=%d and genre is null and artist is null",
2340                                                  result_field,
2341                                                  AUDIO_SVC_DB_TABLE_AUDIO,
2342                                                  AUDIO_SVC_CATEGORY_MUSIC);
2343                                 }
2344                         }
2345
2346                         filter_condition(filter_mode, filter_mode2, query);
2347
2348                         snprintf(tail_query, sizeof(tail_query),
2349                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2350                         g_strlcat(query, tail_query, sizeof(query));
2351                 }
2352                 break;
2353
2354         case AUDIO_SVC_TRACK_BY_GENRE:
2355                 {
2356                         if (type_string && strlen(type_string) > 0) {
2357                                 snprintf(query, sizeof(query),
2358                                          "select %s from %s where valid=1 and category=%d and genre=?",
2359                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2360                                          AUDIO_SVC_CATEGORY_MUSIC);
2361                         } else {
2362                                 snprintf(query, sizeof(query),
2363                                          "select %s from %s where valid=1 and category=%d and genre is null",
2364                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2365                                          AUDIO_SVC_CATEGORY_MUSIC);
2366                         }
2367                         filter_condition(filter_mode, filter_mode2, query);
2368
2369                         snprintf(tail_query, sizeof(tail_query),
2370                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2371                         g_strlcat(query, tail_query, sizeof(query));
2372                 }
2373                 break;
2374
2375         case AUDIO_SVC_TRACK_BY_FOLDER:
2376                 {
2377                         if (type_string && strlen(type_string) > 0) {
2378                                 snprintf(query, sizeof(query),
2379                                          "select %s from %s where valid=1 and category=%d and folder_uuid=(select folder_uuid from %s where path = ?)",
2380                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2381                                          AUDIO_SVC_CATEGORY_MUSIC,
2382                                          AUDIO_SVC_DB_TABLE_AUDIO_FOLDER);
2383                         } else {
2384                                 snprintf(query, sizeof(query),
2385                                          "select %s from %s where valid=1 and category=%d and folder_uuid is null",
2386                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2387                                          AUDIO_SVC_CATEGORY_MUSIC);
2388                         }
2389                         filter_condition(filter_mode, filter_mode2, query);
2390
2391                         snprintf(tail_query, sizeof(tail_query),
2392                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2393                         g_strlcat(query, tail_query, sizeof(query));
2394                 }
2395                 break;
2396
2397         case AUDIO_SVC_TRACK_BY_YEAR:
2398                 {
2399                         if (type_string && strlen(type_string) > 0) {
2400                                 snprintf(query, sizeof(query),
2401                                          "select %s from %s where valid=1 and category=%d and year=?",
2402                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2403                                          AUDIO_SVC_CATEGORY_MUSIC);
2404                         } else {
2405                                 snprintf(query, sizeof(query),
2406                                          "select %s from %s where valid=1 and category=%d and year is null",
2407                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2408                                          AUDIO_SVC_CATEGORY_MUSIC);
2409                         }
2410                         filter_condition(filter_mode, filter_mode2, query);
2411
2412                         snprintf(tail_query, sizeof(tail_query),
2413                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2414                         g_strlcat(query, tail_query, sizeof(query));
2415                 }
2416                 break;
2417
2418         case AUDIO_SVC_TRACK_BY_COMPOSER:
2419                 {
2420                         if (type_string && strlen(type_string) > 0) {
2421                                 snprintf(query, sizeof(query),
2422                                          "select %s from %s where valid=1 and category=%d and author=?",
2423                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2424                                          AUDIO_SVC_CATEGORY_MUSIC);
2425                         } else {
2426                                 snprintf(query, sizeof(query),
2427                                          "select %s from %s where valid=1 and category=%d and author is null",
2428                                          result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2429                                          AUDIO_SVC_CATEGORY_MUSIC);
2430                         }
2431                         filter_condition(filter_mode, filter_mode2, query);
2432
2433                         snprintf(tail_query, sizeof(tail_query),
2434                                  " %s limit %d,%d", AUDIO_SVC_ORDER_BY_TITLE, offset, rows);
2435                         g_strlcat(query, tail_query, sizeof(query));
2436                 }
2437                 break;
2438
2439         case AUDIO_SVC_TRACK_BY_TOPRATING:
2440                 {
2441                         snprintf(query, sizeof(query),
2442                                  "select %s from %s where valid=1 and category=%d and rating >= %d",
2443                                  result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2444                                  AUDIO_SVC_CATEGORY_MUSIC, AUDIO_SVC_RATING_4);
2445
2446                         filter_condition(filter_mode, filter_mode2, query);
2447
2448                         snprintf(tail_query, sizeof(tail_query),
2449                                  " order by rating desc, rated_time desc limit %d,%d",
2450                                  offset, rows);
2451                         g_strlcat(query, tail_query, sizeof(query));
2452                 }
2453                 break;
2454
2455         case AUDIO_SVC_TRACK_BY_PLAYED_TIME:
2456                 {
2457                         snprintf(query, sizeof(query),
2458                                  "select %s from %s where valid=1 and category=%d and last_played_time > 0",
2459                                  result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2460                                  AUDIO_SVC_CATEGORY_MUSIC);
2461
2462                         filter_condition(filter_mode, filter_mode2, query);
2463
2464                         snprintf(tail_query, sizeof(tail_query),
2465                                  " order by last_played_time desc limit %d,%d",
2466                                  offset, rows);
2467                         g_strlcat(query, tail_query, sizeof(query));
2468                 }
2469                 break;
2470
2471         case AUDIO_SVC_TRACK_BY_ADDED_TIME:
2472                 {
2473                         snprintf(query, sizeof(query),
2474                                  "select %s from %s where valid=1 and category=%d and added_time > 0",
2475                                  result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2476                                  AUDIO_SVC_CATEGORY_MUSIC);
2477
2478                         filter_condition(filter_mode, filter_mode2, query);
2479
2480                         snprintf(tail_query, sizeof(tail_query),
2481                                  " order by added_time desc limit %d,%d",
2482                                  offset, rows);
2483                         g_strlcat(query, tail_query, sizeof(query));
2484                 }
2485                 break;
2486
2487         case AUDIO_SVC_TRACK_BY_PLAYED_COUNT:
2488                 {
2489                         snprintf(query, sizeof(query),
2490                                  "select %s from %s where valid=1 and category=%d and played_count > 0",
2491                                  result_field, AUDIO_SVC_DB_TABLE_AUDIO,
2492                                  AUDIO_SVC_CATEGORY_MUSIC);
2493
2494                         filter_condition(filter_mode, filter_mode2, query);
2495
2496                         snprintf(tail_query, sizeof(tail_query),
2497                                  " order by played_count desc limit %d,%d",
2498                                  offset, rows);
2499                         g_strlcat(query, tail_query, sizeof(query));
2500                 }
2501                 break;
2502
2503                 /* To permit duplicated track in a playlist, playlist item index should be returned. */
2504         case AUDIO_SVC_TRACK_BY_PLAYLIST:
2505                 {
2506                         char *result_field_for_playlist =
2507                             "b.audio_uuid, b.path, b.thumbnail_path, b.title, b.artist, b.duration, b.rating, b.album";
2508                         len =
2509                             snprintf(query, sizeof(query),
2510                                      "select %s from %s a, %s b where a.playlist_id=%d and b.audio_uuid=a.audio_uuid and b.valid=1 and b.category=%d ",
2511                                      result_field_for_playlist,
2512                                      AUDIO_SVC_DB_TABLE_AUDIO_PLAYLISTS_MAP,
2513                                      AUDIO_SVC_DB_TABLE_AUDIO, (int)type_string,
2514                                      AUDIO_SVC_CATEGORY_MUSIC);
2515
2516                         if (len < 1 || len >= sizeof(query)) {
2517                                 audio_svc_error
2518                                     ("snprintf error occured or truncated");
2519                                 return AUDIO_SVC_ERROR_INTERNAL;
2520                         } else {
2521                                 query[len] = '\0';
2522                         }
2523
2524                         if (filter_mode) {
2525                                 snprintf(tail_query, sizeof(tail_query),
2526                                          " and b.title like ?");
2527                                 g_strlcat(query, tail_query, sizeof(query));
2528                         }
2529                         if (filter_mode2) {
2530                                 snprintf(tail_query, sizeof(tail_query),
2531                                          " and b.title like ?");
2532                                 g_strlcat(query, tail_query, sizeof(query));
2533                         }
2534                         snprintf(tail_query, sizeof(tail_query),
2535                                  "  order by a.play_order limit %d,%d", offset,
2536                                  rows);
2537
2538                         g_strlcat(query, tail_query, sizeof(query));
2539                 }
2540                 break;
2541
2542         default:
2543                 audio_svc_error("Invalid track type");
2544                 return AUDIO_SVC_ERROR_INVALID_PARAMETER;
2545
2546         }
2547
2548         audio_svc_debug("[SQL query] : %s", query);
2549
2550         err = sqlite3_prepare_v2(handle, query, -1, &sql_stmt, NULL);
2551         if (err != SQLITE_OK) {
2552                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
2553                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2554         }
2555
2556         if ((track_type >= AUDIO_SVC_TRACK_BY_ALBUM)
2557             && (track_type <= AUDIO_SVC_TRACK_BY_COMPOSER)) {
2558                 if (type_string && strlen(type_string) > 0) {
2559                         if ((track_type == AUDIO_SVC_TRACK_BY_YEAR)
2560                             && (!strcmp(type_string, AUDIO_SVC_TAG_UNKNOWN))) {
2561                                 if (SQLITE_OK !=
2562                                     (err =
2563                                      sqlite3_bind_text(sql_stmt, text_bind,
2564                                                        "-1", strlen("-1"),
2565                                                        NULL))) {
2566                                         audio_svc_error
2567                                             ("cont_string1(%s) binding is failed (%d)",
2568                                              type_string, err);
2569                                         SQLITE3_FINALIZE(sql_stmt);
2570                                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2571                                 }
2572
2573                         } else {
2574                                 if (SQLITE_OK !=
2575                                     (err =
2576                                      sqlite3_bind_text(sql_stmt, text_bind,
2577                                                        (const char *)
2578                                                        type_string, -1,
2579                                                        NULL))) {
2580                                         audio_svc_error
2581                                             ("cont_string1(%s) binding is failed (%d)",
2582                                              type_string, err);
2583                                         SQLITE3_FINALIZE(sql_stmt);
2584                                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2585                                 }
2586                         }
2587                         text_bind++;
2588                 }
2589         }
2590
2591         if ((track_type == AUDIO_SVC_TRACK_BY_ARTIST_GENRE)
2592             || (track_type == AUDIO_SVC_TRACK_BY_ARTIST_ALBUM)) {
2593                 if (type_string2 && strlen(type_string2) > 0) {
2594                         if (SQLITE_OK !=
2595                             (err =
2596                              sqlite3_bind_text(sql_stmt, text_bind,
2597                                                (char *)type_string2,
2598                                                strlen(type_string2), NULL))) {
2599                                 audio_svc_error
2600                                     ("cont_string2(%s) binding is failed (%d)",
2601                                      type_string2, err);
2602                                 SQLITE3_FINALIZE(sql_stmt);
2603                                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2604                         }
2605                         text_bind++;
2606                 }
2607         }
2608
2609         if (filter_mode) {
2610                 if (SQLITE_OK !=
2611                     (err =
2612                      sqlite3_bind_text(sql_stmt, text_bind,
2613                                        (char *)filter_query,
2614                                        strlen(filter_query), NULL))) {
2615                         audio_svc_error
2616                             ("filter_query(%s) binding is failed (%d)",
2617                              filter_query, err);
2618                         SQLITE3_FINALIZE(sql_stmt);
2619                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2620                 }
2621                 text_bind++;
2622         }
2623         if (filter_mode2) {
2624                 if (SQLITE_OK !=
2625                     (err =
2626                      sqlite3_bind_text(sql_stmt, text_bind,
2627                                        (char *)filter_query2,
2628                                        strlen(filter_query2), NULL))) {
2629                         audio_svc_error
2630                             ("filter_query(%s) binding is failed (%d)",
2631                              filter_query2, err);
2632                         SQLITE3_FINALIZE(sql_stmt);
2633                         return AUDIO_SVC_ERROR_DB_INTERNAL;
2634                 }
2635                 text_bind++;
2636         }
2637
2638         while (sqlite3_step(sql_stmt) == SQLITE_ROW) {
2639                 _strncpy_safe(track[idx].audio_uuid,
2640                               (const char *)sqlite3_column_text(sql_stmt, AUDIO_SVC_LIST_ITEM_AUDIO_ID), sizeof(track[idx].audio_uuid));
2641                 _strncpy_safe(track[idx].pathname,
2642                               (const char *)sqlite3_column_text(sql_stmt, AUDIO_SVC_LIST_ITEM_PATHNAME), sizeof(track[idx].pathname));
2643                 _strncpy_safe(track[idx].thumbnail_path,
2644                               (const char *)sqlite3_column_text(sql_stmt, AUDIO_SVC_LIST_ITEM_THUMBNAIL_PATH), sizeof(track[idx].thumbnail_path));
2645                 _strncpy_safe(track[idx].title,
2646                               (const char *)sqlite3_column_text(sql_stmt, AUDIO_SVC_LIST_ITEM_TITLE), sizeof(track[idx].title));
2647                 _strncpy_safe(track[idx].artist,
2648                               (const char *)sqlite3_column_text(sql_stmt, AUDIO_SVC_LIST_ITEM_ARTIST), sizeof(track[idx].artist));
2649                 _strncpy_safe(track[idx].album,
2650                               (const char *)sqlite3_column_text(sql_stmt, AUDIO_SVC_LIST_ITEM_ALBUM), sizeof(track[idx].artist));
2651
2652                 track[idx].duration = sqlite3_column_int(sql_stmt, AUDIO_SVC_LIST_ITEM_DURATION);
2653                 track[idx].rating = sqlite3_column_int(sql_stmt, AUDIO_SVC_LIST_ITEM_RATING);
2654                 audio_svc_debug ("Index : %d : audio_uuid = %s, title = %s, pathname = %s, duration = %d",
2655                      idx, track[idx].audio_uuid, track[idx].title, track[idx].pathname, track[idx].duration);
2656
2657                 idx++;
2658         }
2659
2660         SQLITE3_FINALIZE(sql_stmt);
2661
2662         return AUDIO_SVC_ERROR_NONE;
2663
2664 }
2665
2666 int _audio_svc_delete_music_track_groups(sqlite3 *handle, audio_svc_group_type_e group_type,
2667                                          const char *type_string)
2668 {
2669         int err = -1;
2670         char *sql = NULL;
2671
2672         switch (group_type) {
2673         case AUDIO_SVC_GROUP_BY_ALBUM:
2674                 sql =
2675                     sqlite3_mprintf
2676                     ("delete from %s where album='%q' and valid=1 and category=%d",
2677                      AUDIO_SVC_DB_TABLE_AUDIO, type_string,
2678                      AUDIO_SVC_CATEGORY_MUSIC);
2679                 break;
2680         case AUDIO_SVC_GROUP_BY_ARTIST:
2681                 sql =
2682                     sqlite3_mprintf
2683                     ("delete from %s where artist='%q' and valid=1 and category=%d",
2684                      AUDIO_SVC_DB_TABLE_AUDIO, type_string,
2685                      AUDIO_SVC_CATEGORY_MUSIC);
2686                 break;
2687         case AUDIO_SVC_GROUP_BY_GENRE:
2688                 sql =
2689                     sqlite3_mprintf
2690                     ("delete from %s where genre='%q' and valid=1 and category=%d",
2691                      AUDIO_SVC_DB_TABLE_AUDIO, type_string,
2692                      AUDIO_SVC_CATEGORY_MUSIC);
2693                 break;
2694         case AUDIO_SVC_GROUP_BY_FOLDER:
2695                 sql =
2696                     sqlite3_mprintf
2697                     ("delete from %s where folder_uuid=(select folder_uuid from %s where path = '%q') and valid=1 and category=%d",
2698                      AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_DB_TABLE_AUDIO_FOLDER,
2699                      type_string, AUDIO_SVC_CATEGORY_MUSIC);
2700                 break;
2701         case AUDIO_SVC_GROUP_BY_YEAR:
2702                 sql =
2703                     sqlite3_mprintf
2704                     ("delete from %s where year= %d and valid=1 and category=%d",
2705                      AUDIO_SVC_DB_TABLE_AUDIO, str_2_year(type_string),
2706                      AUDIO_SVC_CATEGORY_MUSIC);
2707                 break;
2708         case AUDIO_SVC_GROUP_BY_COMPOSER:
2709                 sql =
2710                     sqlite3_mprintf
2711                     ("delete from %s where author='%q' and valid=1 and category=%d",
2712                      AUDIO_SVC_DB_TABLE_AUDIO, type_string,
2713                      AUDIO_SVC_CATEGORY_MUSIC);
2714                 break;
2715         default:
2716                 audio_svc_error("Invalid track type");
2717                 return AUDIO_SVC_ERROR_INVALID_PARAMETER;
2718         }
2719
2720         audio_svc_debug("query (%s)", sql);
2721
2722         err = _audio_svc_sql_query(handle, sql);
2723         sqlite3_free(sql);
2724         if (err != SQLITE_OK) {
2725                 audio_svc_error("To delete group is failed(%d)", err);
2726                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
2727                         return err;
2728                 }
2729                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2730         }
2731
2732         return AUDIO_SVC_ERROR_NONE;
2733 }
2734
2735 int _audio_svc_search_audio_id_by_path(sqlite3 *handle, const char *path, char *audio_id)
2736 {
2737         int ret = AUDIO_SVC_ERROR_NONE;
2738         *audio_id = -1;
2739         sqlite3_stmt *sql_stmt = NULL;
2740
2741         char *sql =
2742             sqlite3_mprintf
2743             ("select audio_uuid from %s where valid=1 and category=%d and path='%q'",
2744              AUDIO_SVC_DB_TABLE_AUDIO, AUDIO_SVC_CATEGORY_MUSIC, path);
2745
2746         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
2747
2748         if (ret != AUDIO_SVC_ERROR_NONE) {
2749                 audio_svc_error
2750                     ("error when _audio_svc_search_audio_id_by_path. ret = [%d]",
2751                      ret);
2752                 return ret;
2753         }
2754
2755         _strncpy_safe(audio_id, (const char *)sqlite3_column_text(sql_stmt, 0), AUDIO_SVC_UUID_SIZE+1);
2756
2757         SQLITE3_FINALIZE(sql_stmt);
2758
2759         return AUDIO_SVC_ERROR_NONE;
2760
2761 }
2762
2763 int _audio_svc_update_valid_of_music_records(sqlite3 *handle, audio_svc_storage_type_e
2764                                             storage_type, int valid)
2765 {
2766         audio_svc_debug("storage_type: %d", storage_type);
2767
2768         int err = -1;
2769         char *sql =
2770             sqlite3_mprintf("update %s set valid = %d where storage_type = %d",
2771                             AUDIO_SVC_DB_TABLE_AUDIO, valid, storage_type);
2772         err = _audio_svc_sql_query(handle, sql);
2773         sqlite3_free(sql);
2774         if (err != SQLITE_OK) {
2775                 audio_svc_error("To set all items as invalid is failed(%d)",
2776                                 err);
2777                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
2778                         return err;
2779                 }
2780                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2781         }
2782
2783         return AUDIO_SVC_ERROR_NONE;
2784 }
2785
2786 int _audio_svc_count_record_with_path(sqlite3 *handle, const char *path)
2787 {
2788
2789         int ret = AUDIO_SVC_ERROR_NONE;
2790         int count = -1;
2791         sqlite3_stmt *sql_stmt = NULL;
2792         char *sql =
2793             sqlite3_mprintf("select count(*) from %s where path='%q'",
2794                             AUDIO_SVC_DB_TABLE_AUDIO, path);
2795
2796         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
2797
2798         if (ret != AUDIO_SVC_ERROR_NONE) {
2799                 audio_svc_error
2800                     ("error when _audio_svc_count_record_with_path. err = [%d]",
2801                      ret);
2802                 return count;
2803         }
2804
2805         count = sqlite3_column_int(sql_stmt, 0);
2806
2807         SQLITE3_FINALIZE(sql_stmt);
2808
2809         return count;
2810
2811 }
2812
2813 int _audio_svc_delete_invalid_music_records(sqlite3 *handle, audio_svc_storage_type_e
2814                                             storage_type)
2815 {
2816         int idx = 0;
2817         mp_thumbnailpath_record_t *thumbpath_record = NULL;
2818         int err = -1;
2819         int invalid_count = 0;
2820         int ret = AUDIO_SVC_ERROR_NONE;
2821
2822         invalid_count = __audio_svc_count_invalid_records_with_thumbnail(handle, storage_type);
2823         audio_svc_debug("invalid count: %d\n", invalid_count);
2824
2825         if (invalid_count > 0) {
2826                 thumbpath_record = (mp_thumbnailpath_record_t *)malloc(sizeof(mp_thumbnailpath_record_t) * invalid_count);
2827                 if (thumbpath_record == NULL) {
2828                         audio_svc_debug("fail to memory allocation");
2829                         return AUDIO_SVC_ERROR_OUT_OF_MEMORY;
2830                 }
2831                 memset(thumbpath_record, 0, sizeof(mp_thumbnailpath_record_t) * invalid_count);
2832
2833                 ret = __audio_svc_get_invalid_records_with_thumbnail(handle, storage_type, invalid_count, thumbpath_record);
2834                 if (ret != AUDIO_SVC_ERROR_NONE) {
2835                         audio_svc_error("error when get thumbnail record");
2836                         SAFE_FREE(thumbpath_record);
2837                         return ret;
2838                 }
2839         } else {
2840                 audio_svc_debug("There is no item with thumbnail");
2841         }
2842
2843         char *sql = sqlite3_mprintf("delete from %s where valid = 0 and storage_type=%d", AUDIO_SVC_DB_TABLE_AUDIO, storage_type);
2844         err = _audio_svc_sql_query(handle, sql);
2845         sqlite3_free(sql);
2846         if (err != SQLITE_OK) {
2847                 audio_svc_error("To delete invalid items is failed(%d)", err);
2848                 SAFE_FREE(thumbpath_record);
2849
2850                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
2851                         return err;
2852                 }
2853                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2854         }
2855
2856 #if 0
2857         for (idx = 0; idx < invalid_count; idx++) {
2858                 if (strlen(thumbpath_record[idx].thumbnail_path) > 0) {
2859                         ret =
2860                             _audio_svc_check_and_remove_thumbnail
2861                             (thumbpath_record[idx].thumbnail_path);
2862                         if (ret != AUDIO_SVC_ERROR_NONE) {
2863                                 audio_svc_error
2864                                     ("error _audio_svc_check_and_remove_thumbnail");
2865                                 SAFE_FREE(thumbpath_record);
2866                                 return ret;
2867                         }
2868                 }
2869         }
2870
2871         SAFE_FREE(thumbpath_record);
2872 #endif
2873         for (idx = 0; idx < invalid_count; idx++) {
2874                 if (strlen(thumbpath_record[idx].thumbnail_path) > 0) {
2875                         if (_audio_svc_remove_file(thumbpath_record[idx].thumbnail_path) == FALSE) {
2876                                 audio_svc_error("fail to remove thumbnail file.");
2877                                 SAFE_FREE(thumbpath_record);
2878                                 return AUDIO_SVC_ERROR_INTERNAL;
2879                         }
2880                 }
2881         }
2882         SAFE_FREE(thumbpath_record);
2883
2884         return AUDIO_SVC_ERROR_NONE;
2885 }
2886
2887 int _audio_svc_update_valid_in_music_record(sqlite3 *handle, const char *path, int valid)
2888 {
2889         int err = -1;
2890
2891         char *sql = sqlite3_mprintf("update %s set valid=%d where path= '%q'",
2892                                     AUDIO_SVC_DB_TABLE_AUDIO, valid, path);
2893         err = _audio_svc_sql_query(handle, sql);
2894         sqlite3_free(sql);
2895         if (err != SQLITE_OK) {
2896                 audio_svc_error("To update item as valid is failed(%d)", err);
2897                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
2898                         return err;
2899                 }
2900                 return AUDIO_SVC_ERROR_DB_INTERNAL;
2901         }
2902         return AUDIO_SVC_ERROR_NONE;
2903
2904 }
2905
2906 int _audio_svc_update_valid_in_music_record_query_add(sqlite3 *handle, const char *path, int valid)
2907 {
2908         char *sql = sqlite3_mprintf("update %s set valid=%d where path= '%q'",
2909                                     AUDIO_SVC_DB_TABLE_AUDIO, valid, path);
2910
2911         audio_svc_debug("SQL = [%s]", sql);
2912
2913         _audio_svc_sql_query_add(&g_audio_svc_item_valid_query_list, &sql);
2914
2915         return AUDIO_SVC_ERROR_NONE;
2916 }
2917
2918 int _audio_svc_move_item_query_add(sqlite3 *handle, const char *src_path, const char *path, audio_svc_storage_type_e storage_type, const char *folder_id)
2919 {
2920         char *sql = NULL;
2921         sql = sqlite3_mprintf("update %s set path='%q', storage_type=%d where valid=1 and path='%q'",
2922                                         AUDIO_SVC_DB_TABLE_AUDIO, path, storage_type, src_path);
2923
2924         audio_svc_debug("SQL = [%s]", sql);
2925         _audio_svc_sql_query_add(&g_audio_svc_move_item_query_list, &sql);
2926
2927         sql = sqlite3_mprintf("update %s set folder_uuid='%q' where path='%q'",
2928                                         AUDIO_SVC_DB_TABLE_AUDIO, folder_id, path);
2929
2930         audio_svc_debug("SQL = [%s]", sql);
2931         _audio_svc_sql_query_add(&g_audio_svc_move_item_query_list, &sql);
2932
2933         return AUDIO_SVC_ERROR_NONE;
2934 }
2935
2936 //call this API after beginning transaction. this API do sqlite_exec for the g_audio_svc_sqli_query_list.
2937 int _audio_svc_list_query_do(sqlite3 *handle, audio_svc_query_type_e query_type)
2938 {
2939         int ret = AUDIO_SVC_ERROR_NONE;
2940
2941         ret = _audio_svc_sql_begin_trans(handle);
2942         audio_svc_retv_if(ret != AUDIO_SVC_ERROR_NONE, ret);
2943
2944         if (query_type == AUDIO_SVC_QUERY_SET_ITEM_VALID)
2945                 ret = _audio_svc_sql_query_list(handle, &g_audio_svc_item_valid_query_list);
2946         else if (query_type == AUDIO_SVC_QUERY_MOVE_ITEM)
2947                 ret = _audio_svc_sql_query_list(handle, &g_audio_svc_move_item_query_list);
2948         else if (query_type == AUDIO_SVC_QUERY_INSERT_ITEM)
2949                 ret = _audio_svc_sql_query_list(handle, &g_audio_svc_insert_item_query_list);
2950         else
2951                 ret = AUDIO_SVC_ERROR_INVALID_PARAMETER;
2952
2953         if (ret != AUDIO_SVC_ERROR_NONE) {
2954                 audio_svc_error("_audio_svc_list_query_do failed. start rollback");
2955                 _audio_svc_sql_rollback_trans(handle);
2956                 return ret;
2957         }
2958
2959         ret = _audio_svc_sql_end_trans(handle);
2960         if (ret != AUDIO_SVC_ERROR_NONE) {
2961                 audio_svc_error("mb_svc_sqlite3_commit_trans failed.. Now start to rollback\n");
2962                 _audio_svc_sql_rollback_trans(handle);
2963                 return ret;
2964         }
2965
2966         return AUDIO_SVC_ERROR_NONE;
2967 }
2968
2969 int _audio_svc_get_path(sqlite3 *handle, const char *audio_id, char *path)
2970 {
2971         int ret = AUDIO_SVC_ERROR_NONE;
2972         sqlite3_stmt *sql_stmt = NULL;
2973
2974         char *sql =
2975             sqlite3_mprintf
2976             ("select path from %s where audio_uuid='%q' and valid=1 and category=%d",
2977              AUDIO_SVC_DB_TABLE_AUDIO, audio_id, AUDIO_SVC_CATEGORY_MUSIC);
2978
2979         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
2980
2981         if (ret != AUDIO_SVC_ERROR_NONE) {
2982                 audio_svc_error("error when _audio_svc_get_path. ret = [%d]",
2983                                 ret);
2984                 return ret;
2985         }
2986
2987         _strncpy_safe(path, (const char *)sqlite3_column_text(sql_stmt, 0),
2988                       AUDIO_SVC_PATHNAME_SIZE);
2989
2990         SQLITE3_FINALIZE(sql_stmt);
2991
2992         return AUDIO_SVC_ERROR_NONE;
2993
2994 }
2995
2996 int _audio_svc_get_and_append_folder_id_by_path(sqlite3 *handle, const char *path, audio_svc_storage_type_e storage_type, char *folder_id)
2997 {
2998         char *path_name = NULL;
2999         int ret = AUDIO_SVC_ERROR_NONE;
3000
3001         path_name = g_path_get_dirname(path);
3002
3003         ret = _audio_svc_get_folder_id_by_foldername(handle, path_name, folder_id);
3004
3005         if(ret == AUDIO_SVC_ERROR_DB_NO_RECORD) {
3006                 char *folder_name = NULL;
3007                 int folder_modified_date = 0;
3008                 char *folder_uuid = _media_info_generate_uuid();
3009                 if(folder_uuid == NULL ) {
3010                         audio_svc_error("Invalid UUID");
3011                         SAFE_FREE(path_name);
3012                         return AUDIO_SVC_ERROR_INTERNAL;
3013                 }
3014
3015                 folder_name = g_path_get_basename(path_name);
3016                 folder_modified_date = _audio_svc_get_file_dir_modified_date(path_name);
3017
3018                 ret = _audio_svc_append_audio_folder(handle, storage_type, folder_uuid, path_name, folder_name, folder_modified_date);
3019                 SAFE_FREE(folder_name);
3020                 _strncpy_safe(folder_id, folder_uuid, AUDIO_SVC_UUID_SIZE+1);
3021         }
3022
3023         SAFE_FREE(path_name);
3024
3025         return ret;
3026 }
3027 int _audio_svc_get_folder_id_by_foldername(sqlite3 *handle, const char *folder_name, char *folder_id)
3028 {
3029         int ret = AUDIO_SVC_ERROR_NONE;
3030         sqlite3_stmt *sql_stmt = NULL;
3031
3032         char *sql = sqlite3_mprintf("SELECT folder_uuid FROM %s WHERE path = '%q';", AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, folder_name);
3033
3034         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
3035
3036         if (ret != AUDIO_SVC_ERROR_NONE) {
3037                 if(ret == AUDIO_SVC_ERROR_DB_NO_RECORD) {
3038                         audio_svc_debug("there is no folder.");
3039                 }
3040                 else {
3041                         audio_svc_error("error when _audio_svc_get_folder_id_by_foldername. err = [%d]", ret);
3042                 }
3043                 return ret;
3044         }
3045
3046         _strncpy_safe(folder_id, (const char *)sqlite3_column_text(sql_stmt, 0), AUDIO_SVC_UUID_SIZE+1);
3047
3048         SQLITE3_FINALIZE(sql_stmt);
3049
3050         return ret;
3051 }
3052
3053 int _audio_svc_append_audio_folder(sqlite3 *handle, audio_svc_storage_type_e storage_type,
3054                                     const char *folder_id, const char *path_name, const char *folder_name, int modified_date)
3055 {
3056         int err = -1;
3057
3058         char *sql = sqlite3_mprintf("insert into %s (folder_uuid, path, folder_name, storage_type, modified_date) values ('%q', '%q', '%q', '%d', '%d'); ",
3059                                              AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, folder_id, path_name, folder_name, storage_type, modified_date);
3060         err = _audio_svc_sql_query(handle, sql);
3061         sqlite3_free(sql);
3062         if (err != SQLITE_OK) {
3063                 audio_svc_error("failed to insert albums");
3064                 return AUDIO_SVC_ERROR_DB_INTERNAL;
3065         }
3066
3067         return AUDIO_SVC_ERROR_NONE;
3068 }
3069
3070 /* handle by one items. */
3071 int _audio_svc_check_and_update_folder_table(sqlite3 *handle, const char *path_name)
3072 {
3073         int err = -1;
3074         char folder_id[AUDIO_SVC_UUID_SIZE+1] = {0,};
3075         char *folder_path_name = NULL;
3076         int ret = AUDIO_SVC_ERROR_NONE;
3077
3078         folder_path_name = g_path_get_dirname(path_name);
3079         if (folder_path_name == NULL) {
3080                 audio_svc_error("error when get path name");
3081                 return AUDIO_SVC_ERROR_INTERNAL;
3082         }
3083
3084         ret = _audio_svc_get_folder_id_by_foldername(handle, folder_path_name, folder_id);
3085         SAFE_FREE(folder_path_name);
3086
3087         if(ret == AUDIO_SVC_ERROR_DB_NO_RECORD) {
3088                 audio_svc_error("error when _audio_svc_get_folder_id_by_foldername ");
3089                 return AUDIO_SVC_ERROR_DB_NO_RECORD;
3090         }
3091
3092         char *sql =
3093             sqlite3_mprintf
3094             ("delete from %s where folder_uuid='%q' and ((SELECT count(*) FROM %s WHERE folder_uuid='%q')=0);",
3095              AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, folder_id,
3096              AUDIO_SVC_DB_TABLE_AUDIO, folder_id);
3097         err = _audio_svc_sql_query(handle, sql);
3098         sqlite3_free(sql);
3099         if (err != SQLITE_OK) {
3100                 audio_svc_error("failed to delete audio_folder item");
3101                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
3102                         return err;
3103                 }
3104                 return AUDIO_SVC_ERROR_DB_INTERNAL;
3105         }
3106
3107         return AUDIO_SVC_ERROR_NONE;
3108 }
3109
3110 /* batch processing */
3111 int _audio_svc_update_folder_table(sqlite3 *handle)
3112 {
3113         int err = -1;
3114         char *sql = NULL;
3115
3116         sql =
3117             sqlite3_mprintf
3118             ("delete from %s where folder_uuid IN (select folder_uuid FROM %s where folder_uuid NOT IN (select folder_uuid from %s))",
3119              AUDIO_SVC_DB_TABLE_AUDIO_FOLDER, AUDIO_SVC_DB_TABLE_AUDIO_FOLDER,
3120              AUDIO_SVC_DB_TABLE_AUDIO);
3121
3122         err = _audio_svc_sql_query(handle, sql);
3123         sqlite3_free(sql);
3124         if (err != SQLITE_OK) {
3125                 audio_svc_error("failed to delete audio_folder item");
3126                 if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
3127                         return err;
3128                 }
3129                 return AUDIO_SVC_ERROR_DB_INTERNAL;
3130         }
3131
3132         return AUDIO_SVC_ERROR_NONE;
3133 }
3134
3135 int _audio_svc_check_and_update_albums_table(sqlite3 *handle, const char *album)
3136 {
3137         int err = -1;
3138         char *sql = NULL;
3139
3140         /* batch processing */
3141         if(!STRING_VALID(album)) {
3142                 sql = sqlite3_mprintf("delete from %s where album_id NOT IN (SELECT album_id FROM %s);",
3143                              AUDIO_SVC_DB_TABLE_ALBUMS, AUDIO_SVC_DB_TABLE_AUDIO);
3144                 err = _audio_svc_sql_query(handle, sql);
3145                 sqlite3_free(sql);
3146                 if (err != SQLITE_OK) {
3147                         audio_svc_error("failed to update albums table");
3148                         if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
3149                                 return err;
3150                         }
3151                         return AUDIO_SVC_ERROR_DB_INTERNAL;
3152                 }
3153         } else {
3154                 sql = sqlite3_mprintf("delete from %s where album='%q' and ((SELECT count(*) FROM %s WHERE album='%q')=0);",
3155                      AUDIO_SVC_DB_TABLE_ALBUMS, album, AUDIO_SVC_DB_TABLE_AUDIO, album);
3156                 err = _audio_svc_sql_query(handle, sql);
3157                 sqlite3_free(sql);
3158                 if (err != SQLITE_OK) {
3159                         audio_svc_error("failed to update albums table");
3160                         if (err == AUDIO_SVC_ERROR_DB_CONNECT) {
3161                                 return err;
3162                         }
3163                         return AUDIO_SVC_ERROR_DB_INTERNAL;
3164                 }
3165         }
3166
3167         return AUDIO_SVC_ERROR_NONE;
3168 }
3169
3170 int _audio_svc_get_thumbnail_path_by_path(sqlite3 *handle, const char *path, char *thumb_path)
3171 {
3172         int ret = AUDIO_SVC_ERROR_NONE;
3173         sqlite3_stmt *sql_stmt = NULL;
3174
3175         char *sql =
3176             sqlite3_mprintf
3177             ("select thumbnail_path from %s where valid=1 and path='%q' ",
3178              AUDIO_SVC_DB_TABLE_AUDIO, path);
3179
3180         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
3181
3182         if (ret != AUDIO_SVC_ERROR_NONE) {
3183                 audio_svc_error
3184                     ("error when _audio_svc_get_thumbnail_path_by_path. ret=[%d]",
3185                      ret);
3186                 return ret;
3187         }
3188
3189         _strncpy_safe(thumb_path,
3190                       (const char *)sqlite3_column_text(sql_stmt, 0),
3191                       AUDIO_SVC_PATHNAME_SIZE);
3192
3193         SQLITE3_FINALIZE(sql_stmt);
3194
3195         return AUDIO_SVC_ERROR_NONE;
3196 }
3197
3198 char *_audio_svc_get_thumbnail_path_by_album_id(sqlite3 *handle, int album_id)
3199 {
3200         int ret = AUDIO_SVC_ERROR_NONE;
3201         sqlite3_stmt *sql_stmt = NULL;
3202         char thumbnail_path[AUDIO_SVC_PATHNAME_SIZE] = { 0, };
3203
3204         char *sql =
3205             sqlite3_mprintf("select _data from %s where album_id=%d",
3206                             AUDIO_SVC_DB_TABLE_ALBUM_ART, album_id);
3207
3208         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
3209
3210         if (ret != AUDIO_SVC_ERROR_NONE) {
3211                 audio_svc_error
3212                     ("error when __audio_svc_get_genre_id. err = [%d]", ret);
3213                 return NULL;
3214         }
3215
3216         _strncpy_safe(thumbnail_path,
3217                       (const char *)sqlite3_column_text(sql_stmt, 0),
3218                       sizeof(thumbnail_path));
3219
3220         SQLITE3_FINALIZE(sql_stmt);
3221
3222         return g_strdup(thumbnail_path);
3223 }
3224
3225 int _audio_svc_check_and_remove_thumbnail(sqlite3 *handle, const char *thumbnail_path)
3226 {
3227         int ret = AUDIO_SVC_ERROR_NONE;
3228         int count = -1;
3229         sqlite3_stmt *sql_stmt = NULL;
3230         char *sql =
3231             sqlite3_mprintf("select count(*) from %s where thumbnail_path='%q'",
3232                             AUDIO_SVC_DB_TABLE_AUDIO, thumbnail_path);
3233
3234         ret = _audio_svc_sql_prepare_to_step(handle, sql, &sql_stmt);
3235         if (ret != AUDIO_SVC_ERROR_NONE) {
3236                 audio_svc_error
3237                     ("error when _audio_svc_check_and_remove_thumbnail. err = [%d]",
3238                      ret);
3239                 return ret;
3240         }
3241
3242         count = sqlite3_column_int(sql_stmt, 0);
3243         SQLITE3_FINALIZE(sql_stmt);
3244
3245         if (count < 1) {
3246                 if (_audio_svc_remove_file(thumbnail_path) == FALSE) {
3247                         audio_svc_error("fail to remove thumbnail file.");
3248                         return AUDIO_SVC_ERROR_INTERNAL;
3249                 }
3250         }
3251
3252         return AUDIO_SVC_ERROR_NONE;
3253 }
3254
3255 int _audio_svc_list_search(sqlite3 *handle, audio_svc_audio_item_s *item,
3256                                                         char *where_query,
3257                                                         audio_svc_search_order_e order_field,
3258                                                         int offset,
3259                                                         int count
3260                                                         )
3261 {
3262         int len = 0;
3263         int err = -1;
3264         char query[AUDIO_SVC_QUERY_SIZE] = { 0 };
3265         char condition_str[AUDIO_SVC_QUERY_SIZE] = { 0 };
3266         char order_str[AUDIO_SVC_QUERY_SIZE] = { 0 };
3267
3268         sqlite3_stmt *sql_stmt = NULL;
3269
3270         snprintf(query, sizeof(query), "SELECT * FROM audio_media WHERE %s", where_query);
3271
3272         audio_svc_debug("");
3273         if (_audio_svc_get_order_field_str(order_field, order_str,
3274                         AUDIO_SVC_QUERY_SIZE) < 0) {
3275                 audio_svc_error("_audio_svc_get_order_field_str failure");
3276                 return AUDIO_SVC_ERROR_INTERNAL;
3277         }
3278         audio_svc_debug("");
3279
3280         snprintf(condition_str, sizeof(condition_str), " ORDER BY %s ", order_str);
3281         len = g_strlcat(query, condition_str, sizeof(query));
3282
3283         if (len >= sizeof(query)) {
3284                 audio_svc_error("strlcat returns failure ( %d )", len);
3285                 return AUDIO_SVC_ERROR_INTERNAL;
3286         }
3287
3288         memset(condition_str, 0x00, sizeof(condition_str));
3289
3290         snprintf(condition_str, sizeof(condition_str), " LIMIT %d,%d ", offset, count);
3291         len = g_strlcat(query, condition_str, sizeof(query));
3292
3293         if (len >= sizeof(query)) {
3294                 audio_svc_error("strlcat returns failure ( %d )", len);
3295                 return AUDIO_SVC_ERROR_INTERNAL;
3296         }
3297
3298         audio_svc_debug("Query : %s", query);
3299
3300         err = sqlite3_prepare_v2(handle, query, -1, &sql_stmt, NULL);
3301         if (err != SQLITE_OK) {
3302                 audio_svc_error("prepare error [%s]", sqlite3_errmsg(handle));
3303                 return AUDIO_SVC_ERROR_DB_INTERNAL;
3304         }
3305
3306         while (sqlite3_step(sql_stmt) == SQLITE_ROW) {
3307                 __audio_svc_get_next_record(item, sql_stmt);
3308                 item++;
3309         }
3310
3311         SQLITE3_FINALIZE(sql_stmt);
3312
3313         return AUDIO_SVC_ERROR_NONE;
3314 }
3315