server/db/ctsvc_db_query.c

   1 /*
   2  * Contacts Service
   3  *
   4  * Copyright (c) 2010 - 2015 Samsung Electronics Co., Ltd. All rights reserved.
   5  *
   6  * Licensed under the Apache License, Version 2.0 (the "License");
   7  * you may not use this file except in compliance with the License.
   8  * You may obtain a copy of the License at
   9  *
  10  * http://www.apache.org/licenses/LICENSE-2.0
  11  *
  12  * Unless required by applicable law or agreed to in writing, software
  13  * distributed under the License is distributed on an "AS IS" BASIS,
  14  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  15  * See the License for the specific language governing permissions and
  16  * limitations under the License.
  17  *
  18  */
  19 #include <glib.h>
  20 #include <phone_number.h>
  21
  22 #include "contacts.h"
  23 #include "contacts_internal.h"
  24
  25 #include "ctsvc_internal.h"
  26 #include "ctsvc_db_sqlite.h"
  27 #include "ctsvc_db_schema.h"
  28 #include "ctsvc_list.h"
  29 #include "ctsvc_record.h"
  30 #include "ctsvc_db_utils.h"
  31 #include "ctsvc_normalize.h"
  32 #include "ctsvc_number_utils.h"
  33 #include "ctsvc_db_init.h"
  34 #include "ctsvc_view.h"
  35 #include "ctsvc_inotify.h"
  36 #include "ctsvc_localize.h"
  37 #include "ctsvc_localize_utils.h"
  38 #include "ctsvc_server_setting.h"
  39 #include "ctsvc_notify.h"
  40 #include "ctsvc_utils_string.h"
  41
  42 #include "ctsvc_db_access_control.h"
  43 #include "ctsvc_db_plugin_person_helper.h"
  44 #include "ctsvc_db_plugin_group_helper.h"
  45 #include "ctsvc_db_plugin_company_helper.h"
  46 #include "ctsvc_db_query.h"
  47
  48 #ifdef _CONTACTS_IPC_SERVER
  49 #include "ctsvc_server_sim.h"
  50 #include "ctsvc_server_change_subject.h"
  51 #endif /* _CONTACTS_IPC_SERVER */
  52
  53 /* It is used to sort search results */
  54 const char *hangul_syllable[19][3] = {
  55         {"ㄱ", "가", "깋"},   /* AC00, AE3B */
  56         {"ㄲ", "까", "낗"},   /* AE3C, B097 */
  57         {"ㄴ", "나", "닣"},   /* B098, B2E3 */
  58         {"ㄷ", "다", "딯"},   /* B2E4, B52F */
  59         {"ㄸ", "따", "띻"},   /* B530, B77B */
  60         {"ㄹ", "라", "맇"},   /* B77C, B9C7 */
  61         {"ㅁ", "마", "밓"},   /* B9C8, BC13 */
  62         {"ㅂ", "바", "빟"},   /* BC14, BE5F */
  63         {"ㅃ", "빠", "삫"},   /* BE60, C0AB */
  64         {"ㅅ", "사", "싷"},   /* C0AC, C2F7 */
  65         {"ㅆ", "싸", "앃"},   /* C2F8, C543 */
  66         {"ㅇ", "아", "잏"},   /* C544, C78F */
  67         {"ㅈ", "자", "짛"},   /* C790, C9DB */
  68         {"ㅉ", "짜", "찧"},   /* C9DC, CC27 */
  69         {"ㅊ", "차", "칳"},   /* CC28, CE73 */
  70         {"ㅋ", "카", "킿"},   /* CE74, D0AF */
  71         {"ㅌ", "타", "팋"},   /* D0C0, D30B */
  72         {"ㅍ", "파", "핗"},   /* D30C, D557 */
  73         {"ㅎ", "하", "힣"},   /* D558, D7A3 */
  74 };
  75
  76 /* When one more information is matched in snippet API,
  77  * only one result is returned as below priority.
  78  * Priority order is set for UI concept.
  79  * To get name, with_range_for_snippet() API should be used with name range.
  80  */
  81 typedef enum {
  82         CTSVC_SNIPPET_PRIO_NICKNAME = 1,
  83         CTSVC_SNIPPET_PRIO_COMPANY,
  84         CTSVC_SNIPPET_PRIO_POSTAL,
  85         CTSVC_SNIPPET_PRIO_EMAIL,
  86         CTSVC_SNIPPET_PRIO_NOTE,
  87         CTSVC_SNIPPET_PRIO_NUMBER,
  88         CTSVC_SNIPPET_PRIO_NAME,
  89 } ctsvc_snippet_prio_e;
  90
  91
  92 typedef enum {
  93         QUERY_SORTKEY,
  94         QUERY_FILTER,
  95         QUERY_PROJECTION,
  96 } db_query_property_type_e;
  97
  98 static contacts_db_status_e __db_status = CONTACTS_DB_STATUS_NORMAL;
  99
 100 static const char * __ctsvc_db_get_property_field_name(const property_info_s *properties,
 101                 int count, db_query_property_type_e property_type, unsigned int property_id)
 102 {
 103         int i;
 104
 105         for (i = 0; i < count; i++) {
 106                 property_info_s *p = (property_info_s*)&(properties[i]);
 107                 if (property_id == p->property_id) {
 108                         if (p->fields) {
 109                                 if (property_type == QUERY_PROJECTION) {
 110                                         if (p->property_type == CTSVC_SEARCH_PROPERTY_PROJECTION
 111                                                         || p->property_type == CTSVC_SEARCH_PROPERTY_ALL) {
 112                                                 return p->fields;
 113                                         }
 114                                 } else if (property_type == QUERY_FILTER) {
 115                                         if (p->property_type == CTSVC_SEARCH_PROPERTY_FILTER
 116                                                         || p->property_type == CTSVC_SEARCH_PROPERTY_ALL) {
 117                                                 return p->fields;
 118                                         }
 119                                 } else if (property_type == QUERY_SORTKEY) {
 120                                         return p->fields;
 121                                 }
 122
 123                                 return NULL;
 124 #if 0
 125                         } else if (property_id == CTSVC_PROPERTY_PERSON_DISPLAY_NAME_INDEX) {
 126                                 if (property_type != QUERY_PROJECTION)
 127                                         return NULL;
 128                                 const char *temp = ctsvc_get_display_column();
 129                                 /* snprintf(temp, sizeof(temp), "_NORMALIZE_INDEX_(%s)",
 130                                    ctsvc_get_display_column()); */
 131                                 return "_NORMALIZE_INDEX_"temp;
 132 #endif
 133                         } else {
 134                                 return ctsvc_get_display_column();
 135                         }
 136                 }
 137         }
 138         return NULL;
 139 }
 140
 141 /*
 142  * return data type of the property
 143  * bool / int / long long int / char string / double / child record
 144  */
 145 static inline int __ctsvc_db_get_property_type(const property_info_s *properties,
 146                 int count, unsigned int property_id)
 147 {
 148         int i;
 149         for (i = 0; i < count; i++) {
 150                 property_info_s *p = (property_info_s*)&(properties[i]);
 151
 152                 if (property_id == p->property_id)
 153                         return (property_id & CTSVC_VIEW_DATA_TYPE_MASK);
 154         }
 155
 156         return -1;
 157 }
 158
 159 static inline int __ctsvc_db_create_int_condition(ctsvc_composite_filter_s *com_filter,
 160                 ctsvc_attribute_filter_s *filter, char **condition)
 161 {
 162         const char *field_name;
 163         char out_cond[CTS_SQL_MAX_LEN] = {0};
 164
 165         field_name = __ctsvc_db_get_property_field_name(com_filter->properties,
 166                         com_filter->property_count, QUERY_FILTER, filter->property_id);
 167         RETVM_IF(NULL == field_name, CONTACTS_ERROR_INVALID_PARAMETER,
 168                         "property id(%d)", filter->property_id);
 169
 170 #ifdef _CONTACTS_IPC_SERVER
 171         if (CTSVC_PROPERTY_PHONELOG_SIM_SLOT_NO == filter->property_id ||
 172                         CTSVC_PROPERTY_PHONELOG_STAT_SIM_SLOT_NO == filter->property_id) {
 173                 /* get real sim info id by SIM slot number 0/1 */
 174                 int sim_info_id = ctsvc_server_sim_get_info_id_by_sim_slot_no(filter->value.i);
 175                 if (0 < sim_info_id) {
 176                         snprintf(out_cond, sizeof(out_cond), "%s = %d", field_name, sim_info_id);
 177                         *condition = strdup(out_cond);
 178                         return CONTACTS_ERROR_NONE;
 179                 }
 180                 return CONTACTS_ERROR_INVALID_PARAMETER;
 181         }
 182 #endif /* _CONTACTS_IPC_SERVER */
 183
 184         switch (filter->match) {
 185         case CONTACTS_MATCH_EQUAL:
 186                 snprintf(out_cond, sizeof(out_cond), "%s = %d", field_name, filter->value.i);
 187                 break;
 188         case CONTACTS_MATCH_GREATER_THAN:
 189                 snprintf(out_cond, sizeof(out_cond), "%s > %d", field_name, filter->value.i);
 190                 break;
 191         case CONTACTS_MATCH_GREATER_THAN_OR_EQUAL:
 192                 snprintf(out_cond, sizeof(out_cond), "%s >= %d", field_name, filter->value.i);
 193                 break;
 194         case CONTACTS_MATCH_LESS_THAN:
 195                 snprintf(out_cond, sizeof(out_cond), "%s < %d", field_name, filter->value.i);
 196                 break;
 197         case CONTACTS_MATCH_LESS_THAN_OR_EQUAL:
 198                 snprintf(out_cond, sizeof(out_cond), "%s <= %d", field_name, filter->value.i);
 199                 break;
 200         case CONTACTS_MATCH_NOT_EQUAL:
 201                 snprintf(out_cond, sizeof(out_cond), "%s <> %d", field_name, filter->value.i);
 202                 break;
 203         case CONTACTS_MATCH_NONE:
 204                 snprintf(out_cond, sizeof(out_cond), "%s IS NULL", field_name);
 205                 break;
 206         default:
 207                 /* LCOV_EXCL_START */
 208                 ERR("Invalid : match rule(%d) is not supported", filter->match);
 209                 return CONTACTS_ERROR_INVALID_PARAMETER;
 210                 /* LCOV_EXCL_STOP */
 211         }
 212
 213         *condition = strdup(out_cond);
 214         return CONTACTS_ERROR_NONE;
 215 }
 216
 217 static inline int __ctsvc_db_create_double_condition(
 218                 ctsvc_composite_filter_s *com_filter,
 219                 ctsvc_attribute_filter_s *filter,
 220                 char **condition)
 221 {
 222         const char *field_name;
 223         char out_cond[CTS_SQL_MAX_LEN] = {0};
 224
 225         field_name = __ctsvc_db_get_property_field_name(com_filter->properties,
 226                         com_filter->property_count, QUERY_FILTER, filter->property_id);
 227         RETVM_IF(NULL == field_name, CONTACTS_ERROR_INVALID_PARAMETER,
 228                         "property id(%d)", filter->property_id);
 229
 230         switch (filter->match) {
 231         case CONTACTS_MATCH_EQUAL:
 232                 snprintf(out_cond, sizeof(out_cond), "%s = %lf", field_name, filter->value.d);
 233                 break;
 234         case CONTACTS_MATCH_GREATER_THAN:
 235                 snprintf(out_cond, sizeof(out_cond), "%s > %lf", field_name, filter->value.d);
 236                 break;
 237         case CONTACTS_MATCH_GREATER_THAN_OR_EQUAL:
 238                 snprintf(out_cond, sizeof(out_cond), "%s >= %lf", field_name, filter->value.d);
 239                 break;
 240         case CONTACTS_MATCH_LESS_THAN:
 241                 snprintf(out_cond, sizeof(out_cond), "%s < %lf", field_name, filter->value.d);
 242                 break;
 243         case CONTACTS_MATCH_LESS_THAN_OR_EQUAL:
 244                 snprintf(out_cond, sizeof(out_cond), "%s <= %lf", field_name, filter->value.d);
 245                 break;
 246         case CONTACTS_MATCH_NOT_EQUAL:
 247                 snprintf(out_cond, sizeof(out_cond), "%s <> %lf", field_name, filter->value.d);
 248                 break;
 249         case CONTACTS_MATCH_NONE:
 250                 snprintf(out_cond, sizeof(out_cond), "%s IS NULL", field_name);
 251                 break;
 252         default:
 253                 /* LCOV_EXCL_START */
 254                 ERR("Invalid : match rule(%d) is not supported", filter->match);
 255                 return CONTACTS_ERROR_INVALID_PARAMETER;
 256                 /* LCOV_EXCL_STOP */
 257         }
 258
 259         *condition = strdup(out_cond);
 260         return CONTACTS_ERROR_NONE;
 261 }
 262
 263 static inline int __ctsvc_db_create_lli_condition(ctsvc_composite_filter_s *com_filter,
 264                 ctsvc_attribute_filter_s *filter, char **condition)
 265 {
 266         const char *field_name;
 267         char out_cond[CTS_SQL_MAX_LEN] = {0};
 268
 269         field_name = __ctsvc_db_get_property_field_name(com_filter->properties,
 270                         com_filter->property_count, QUERY_FILTER, filter->property_id);
 271         RETVM_IF(NULL == field_name, CONTACTS_ERROR_INVALID_PARAMETER,
 272                         "property id(%d)", filter->property_id);
 273
 274         switch (filter->match) {
 275         case CONTACTS_MATCH_EQUAL:
 276                 snprintf(out_cond, sizeof(out_cond), "%s = %lld", field_name, filter->value.l);
 277                 break;
 278         case CONTACTS_MATCH_GREATER_THAN:
 279                 snprintf(out_cond, sizeof(out_cond), "%s > %lld", field_name, filter->value.l);
 280                 break;
 281         case CONTACTS_MATCH_GREATER_THAN_OR_EQUAL:
 282                 snprintf(out_cond, sizeof(out_cond), "%s >= %lld", field_name, filter->value.l);
 283                 break;
 284         case CONTACTS_MATCH_LESS_THAN:
 285                 snprintf(out_cond, sizeof(out_cond), "%s < %lld", field_name, filter->value.l);
 286                 break;
 287         case CONTACTS_MATCH_LESS_THAN_OR_EQUAL:
 288                 snprintf(out_cond, sizeof(out_cond), "%s <= %lld", field_name, filter->value.l);
 289                 break;
 290         case CONTACTS_MATCH_NOT_EQUAL:
 291                 snprintf(out_cond, sizeof(out_cond), "%s <> %lld", field_name, filter->value.l);
 292                 break;
 293         case CONTACTS_MATCH_NONE:
 294                 snprintf(out_cond, sizeof(out_cond), "%s IS NULL", field_name);
 295                 break;
 296         default:
 297                 /* LCOV_EXCL_START */
 298                 ERR("Invalid : match rule(%d) is not supported", filter->match);
 299                 return CONTACTS_ERROR_INVALID_PARAMETER;
 300                 /* LCOV_EXCL_STOP */
 301         }
 302
 303         *condition = strdup(out_cond);
 304         return CONTACTS_ERROR_NONE;
 305 }
 306
 307 #define CTSVC_DB_ESCAPE_CHAR    '\\'
 308
 309 static char* __db_get_str_with_escape(char *str, int len, bool with_escape)
 310 {
 311         int i, j = 0;
 312         char temp_str[len*2+1];
 313
 314         if (false == with_escape)
 315                 return strdup(str);
 316
 317         for (i = 0; i < len; i++) {
 318                 if (str[i] == '\'' || str[i] == '_' || str[i] == '%' || str[i] == '\\')
 319                         temp_str[j++] = CTSVC_DB_ESCAPE_CHAR;
 320
 321                 temp_str[j++] = str[i];
 322         }
 323         temp_str[j] = '\0';
 324
 325         return strdup(temp_str);
 326 }
 327
 328 static int __db_add_str_matching_rule(const char *field_name, int match,
 329                 char **condition, bool *with_escape)
 330 {
 331         int len = 0;
 332         char cond[CTS_SQL_MAX_LEN] = {0};
 333
 334         *with_escape = true;
 335         switch (match) {
 336         case CONTACTS_MATCH_EXACTLY:
 337                 len = snprintf(cond, sizeof(cond), "%s = ?", field_name);
 338                 *with_escape = false;
 339                 break;
 340         case CONTACTS_MATCH_FULLSTRING:
 341                 len = snprintf(cond, sizeof(cond), "%s LIKE ? ESCAPE '%c'",
 342                                 field_name, CTSVC_DB_ESCAPE_CHAR);
 343                 break;
 344         case CONTACTS_MATCH_CONTAINS:
 345                 len = snprintf(cond, sizeof(cond),      "%s LIKE ('%%' || ? || '%%') ESCAPE '%c'",
 346                                 field_name, CTSVC_DB_ESCAPE_CHAR);
 347                 break;
 348         case CONTACTS_MATCH_STARTSWITH:
 349                 len = snprintf(cond, sizeof(cond), "%s LIKE (? || '%%') ESCAPE '%c'",
 350                                 field_name, CTSVC_DB_ESCAPE_CHAR);
 351                 break;
 352         case CONTACTS_MATCH_ENDSWITH:
 353                 len = snprintf(cond, sizeof(cond), "%s LIKE ('%%' || ?) ESCAPE '%c'",
 354                                 field_name, CTSVC_DB_ESCAPE_CHAR);
 355                 break;
 356         case CONTACTS_MATCH_EXISTS:
 357                 len = snprintf(cond, sizeof(cond), "%s IS NOT NULL", field_name);
 358                 break;
 359         default:
 360                 /* LCOV_EXCL_START */
 361                 ERR("Invalid : match rule (%d) is not supported", match);
 362                 return CONTACTS_ERROR_INVALID_PARAMETER;
 363                 /* LCOV_EXCL_STOP */
 364         }
 365
 366         if (0 < len)
 367                 *condition = strdup(cond);
 368
 369         return len;
 370 }
 371
 372 static char* __ctsvc_db_make_search_keyword(const char *keyword)
 373 {
 374         int size;
 375         if (keyword == NULL)
 376                 return NULL;
 377
 378         size = strlen(keyword);
 379         if (strstr(keyword, " ")) {
 380                 int i = 0;
 381                 int j = 0;
 382                 char search_keyword[size * 2+1];
 383                 for (i = 0; i < size; i++) {
 384                         if (0 < j && keyword[i] == ' ') {
 385                                 if (search_keyword[j-1] != ' ')
 386                                         search_keyword[j++] = '*';
 387                         }
 388                         search_keyword[j++] = keyword[i];
 389                 }
 390                 if (0 < j && search_keyword[j-1] != ' ')
 391                         search_keyword[j++] = '*';
 392                 search_keyword[j] = '\0';
 393                 return strdup(search_keyword);
 394         } else {
 395                 char search_keyword[size+2];
 396                 snprintf(search_keyword, sizeof(search_keyword), "%s*", keyword);
 397                 return strdup(search_keyword);
 398         }
 399 }
 400
 401 static char *_get_search_query_for_name(const char *keyword)
 402 {
 403         int ret = 0;
 404         char *half_keyword = NULL;
 405         int keyword_size = 0;
 406         bool use_replaced_keyword = true;
 407
 408         ret = ctsvc_get_halfwidth_string(keyword, &half_keyword, &keyword_size);
 409         if (CONTACTS_ERROR_NONE != ret) {
 410                 /* LCOV_EXCL_START */
 411                 ERR("UChar converting error : ctsvc_get_halfwidth_string() Fail");
 412                 half_keyword = (char*)keyword;
 413                 use_replaced_keyword = false;
 414                 /* LCOV_EXCL_STOP */
 415         }
 416
 417         char *search_keyword = NULL;
 418         search_keyword = __ctsvc_db_make_search_keyword(half_keyword);
 419
 420         char *normalized_name = NULL;
 421         int lang = CTSVC_LANG_OTHERS;
 422         lang = ctsvc_normalize_str(half_keyword, &normalized_name);
 423         char *hiragana = NULL;
 424         char *search_hiragana = NULL;
 425
 426         if (CTSVC_LANG_JAPANESE == lang) {
 427                 ctsvc_convert_japanese_to_hiragana(half_keyword, &hiragana);
 428                 search_hiragana = __ctsvc_db_make_search_keyword(hiragana);
 429         }
 430
 431         int len_keyword = strlen(half_keyword);
 432         int len_chosung = 0;
 433         char *chosung = NULL;
 434         char *korean_pattern = NULL;
 435         char *search_chosung = NULL;
 436         char *search_normalized = NULL;
 437
 438         char *mod_keyword = NULL;
 439         switch (lang) {
 440         case CTSVC_LANG_KOREAN: /* search with chosung */
 441                 /*
 442                  * If try to find '홍길동' by 'ㄱ동'
 443                  * search by 'ㄱㄷ' in search_index table
 444                  * intersect
 445                  * search by '*ㄱ*동*' in name_lookup table
 446                  */
 447                 chosung = calloc(len_keyword * 5, sizeof(char));
 448                 len_chosung = ctsvc_get_chosung(half_keyword, chosung, len_keyword * 5);
 449                 mod_keyword = __ctsvc_db_make_search_keyword(0 < len_chosung ? chosung : half_keyword);
 450                 break;
 451         case CTSVC_LANG_JAPANESE:
 452                 mod_keyword = search_hiragana;
 453                 break;
 454         default:
 455                 if (CONTACTS_ERROR_NONE <= lang) { /* normalized string search */
 456                         search_normalized = __ctsvc_db_make_search_keyword(normalized_name);
 457                         mod_keyword = search_normalized;
 458                 } else {  /* original keyword search */
 459                         mod_keyword = search_keyword;
 460                 }
 461                 break;
 462         }
 463
 464         int temp_len = 0;
 465         int query_size = CTS_SQL_MAX_LEN;
 466         char *query = calloc(query_size, sizeof(char));
 467         int len = 0;
 468
 469         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " SELECT contact_id "
 470                         "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" "
 471                         "MATCH 'name:");
 472         if (0 <= temp_len)
 473                 len += temp_len;
 474         temp_len = SAFE_SNPRINTF(&query, &query_size, len, mod_keyword);
 475         if (0 <= temp_len)
 476                 len += temp_len;
 477         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " '");
 478         if (0 <= temp_len)
 479                 len += temp_len;
 480
 481
 482         switch (lang) {
 483         case CTSVC_LANG_KOREAN:
 484                 DBG("korean");
 485                 korean_pattern = calloc(len_keyword *5, sizeof(char));
 486                 ctsvc_get_korean_search_pattern(half_keyword, korean_pattern, len_keyword * 5);
 487                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " INTERSECT SELECT "
 488                                 "contact_id FROM "CTS_TABLE_NAME_LOOKUP" WHERE type = 0 AND name GLOB '*");
 489                 if (0 <= temp_len)
 490                         len += temp_len;
 491
 492                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, korean_pattern);
 493                 if (0 <= temp_len)
 494                         len += temp_len;
 495
 496                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "*' ");
 497                 if (0 <= temp_len)
 498                         len += temp_len;
 499
 500                 free(chosung);
 501                 free(korean_pattern);
 502                 free(search_chosung);
 503                 break;
 504         case CTSVC_LANG_JAPANESE:
 505                 break;
 506         default:
 507                 DBG("default");
 508                 free(search_normalized);
 509                 break;
 510         }
 511
 512         int i, j = 0;
 513         char temp_str[len_keyword * 2 + 1];
 514         for (i = 0; i < len_keyword; i++) {
 515                 if (half_keyword[i] == '\'' || half_keyword[i] == '_' ||
 516                                 half_keyword[i] == '%' || half_keyword[i] == '\\') {
 517                         temp_str[j++] = CTSVC_DB_ESCAPE_CHAR;
 518                 }
 519                 temp_str[j++] = half_keyword[i];
 520         }
 521         temp_str[j] = '\0';
 522         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " UNION SELECT contact_id "
 523                         " FROM "CTS_TABLE_NAME_LOOKUP" WHERE type = 0 AND name LIKE '%%");
 524         if (0 <= temp_len)
 525                 len += temp_len;
 526
 527         temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_str);
 528         if (0 <= temp_len)
 529                 len += temp_len;
 530
 531         /* CTSVC_DB_ESCAPE_CHAR */
 532         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%' ESCAPE '\\' ");
 533         if (0 <= temp_len)
 534                 len += temp_len;
 535
 536         free(normalized_name);
 537         free(hiragana);
 538         free(search_hiragana);
 539         free(search_keyword);
 540
 541         if (use_replaced_keyword)
 542                 free(half_keyword);
 543
 544         return strdup(query);
 545 }
 546
 547 static char *_get_search_query_for_number(const char *keyword)
 548 {
 549         int ret = 0;
 550         char *half_keyword = NULL;
 551         int keyword_size = 0;
 552         bool use_replaced_keyword = true;
 553
 554         ret = ctsvc_get_halfwidth_string(keyword, &half_keyword, &keyword_size);
 555         if (CONTACTS_ERROR_NONE != ret) {
 556                 /* LCOV_EXCL_START */
 557                 ERR("UChar converting error : ctsvc_get_halfwidth_string() Fail");
 558                 half_keyword = (char*)keyword;
 559                 use_replaced_keyword = false;
 560                 /* LCOV_EXCL_STOP */
 561         }
 562
 563         char *search_keyword = NULL;
 564         search_keyword = __ctsvc_db_make_search_keyword(half_keyword);
 565
 566         char *normalized_name = NULL;
 567         int lang = CTSVC_LANG_OTHERS;
 568         lang = ctsvc_normalize_str(half_keyword, &normalized_name);
 569         char *hiragana = NULL;
 570         char *search_hiragana = NULL;
 571
 572         if (CTSVC_LANG_JAPANESE == lang) {
 573                 ctsvc_convert_japanese_to_hiragana(half_keyword, &hiragana);
 574                 search_hiragana = __ctsvc_db_make_search_keyword(hiragana);
 575         }
 576
 577         char *mod_keyword = NULL;
 578         switch (lang) {
 579         case CTSVC_LANG_JAPANESE:
 580                 mod_keyword = search_hiragana;
 581                 break;
 582         default:
 583                 mod_keyword = search_keyword;
 584                 break;
 585         }
 586
 587         int temp_len = 0;
 588         int query_size = CTS_SQL_MAX_LEN;
 589         char *query = calloc(query_size, sizeof(char));
 590         int len = 0;
 591
 592         mod_keyword = search_hiragana ? search_hiragana : search_keyword;
 593         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT contact_id "
 594                         "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" "
 595                         "MATCH 'number:");
 596         if (0 <= temp_len)
 597                 len += temp_len;
 598
 599         temp_len = SAFE_SNPRINTF(&query, &query_size, len, mod_keyword);
 600         if (0 <= temp_len)
 601                 len += temp_len;
 602
 603         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " '");
 604         if (0 <= temp_len)
 605                 len += temp_len;
 606
 607         free(hiragana);
 608         free(search_hiragana);
 609         free(search_keyword);
 610
 611
 612
 613         do {
 614                 int len_keyword = strlen(half_keyword);
 615                 char *clean_number = calloc(len_keyword + 1, sizeof(char));
 616                 if (clean_number == NULL) {
 617                         ERR("Memory allocation failure : ctsvc_clean_number() fail ");
 618                         break;
 619                 }
 620                 int err = ctsvc_clean_number(half_keyword, clean_number, len_keyword + 1, false);
 621                 if (err <= 0) {
 622                         /* LCOV_EXCL_START */
 623                         ERR("ctsvc_clean_number() Fail(%d)", err);
 624                         free(clean_number);
 625                         break;
 626                         /* LCOV_EXCL_STOP */
 627                 }
 628
 629                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " UNION SELECT contact_id "
 630                                 "FROM "CTS_TABLE_PHONE_LOOKUP" WHERE number LIKE '%%");
 631                 if (0 <= temp_len)
 632                         len += temp_len;
 633
 634                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, clean_number);
 635                 if (0 <= temp_len)
 636                         len += temp_len;
 637
 638                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%' ");
 639                 if (0 <= temp_len)
 640                         len += temp_len;
 641
 642                 const char *cc = ctsvc_get_network_cc(false);
 643                 if (cc && cc[0] == '7' && clean_number[0] == '8') {   /* Russia */
 644                         char normal_num[strlen(clean_number)+1+5];   /* for cc */
 645                         int ret;
 646                         ret = ctsvc_normalize_number(clean_number, normal_num, sizeof(normal_num), false);
 647                         if (ret <= 0) {
 648                                 free(clean_number);
 649                                 break;
 650                         }
 651                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "OR number LIKE '%%");
 652                         if (0 <= temp_len)
 653                                 len += temp_len;
 654
 655                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, normal_num);
 656                         if (0 <= temp_len)
 657                                 len += temp_len;
 658
 659                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%' ");
 660                         if (0 <= temp_len)
 661                                 len += temp_len;
 662                 }
 663                 free(clean_number);
 664         } while (0);
 665
 666         if (use_replaced_keyword)
 667                 free(half_keyword);
 668
 669         return strdup(query);
 670 }
 671
 672 static char *_get_search_query_for_data(const char *keyword)
 673 {
 674         int ret = 0;
 675         char *half_keyword = NULL;
 676         int keyword_size = 0;
 677         bool use_replaced_keyword = true;
 678
 679         ret = ctsvc_get_halfwidth_string(keyword, &half_keyword, &keyword_size);
 680         if (CONTACTS_ERROR_NONE != ret) {
 681                 /* LCOV_EXCL_START */
 682                 ERR("UChar converting error : ctsvc_get_halfwidth_string() Fail");
 683                 half_keyword = (char*)keyword;
 684                 use_replaced_keyword = false;
 685                 /* LCOV_EXCL_STOP */
 686         }
 687
 688         char *search_keyword = NULL;
 689         search_keyword = __ctsvc_db_make_search_keyword(half_keyword);
 690
 691         char *normalized_name = NULL;
 692         int lang = CTSVC_LANG_OTHERS;
 693         lang = ctsvc_normalize_str(half_keyword, &normalized_name);
 694         char *hiragana = NULL;
 695         char *search_hiragana = NULL;
 696
 697         if (CTSVC_LANG_JAPANESE == lang) {
 698                 ctsvc_convert_japanese_to_hiragana(half_keyword, &hiragana);
 699                 search_hiragana = __ctsvc_db_make_search_keyword(hiragana);
 700         }
 701
 702         int tmp_len = 0;
 703         char *tmp_keyword = NULL;
 704         int i;
 705
 706         char *mod_keyword = NULL;
 707         switch (lang) {
 708         case CTSVC_LANG_JAPANESE:
 709                 mod_keyword = search_hiragana;
 710                 break;
 711         default:
 712                 /* replace '-' -> '_' because FTS does not support search '-' */
 713                 tmp_len = strlen(search_keyword);
 714                 tmp_keyword = calloc(tmp_len + 1, sizeof(char));
 715                 if (NULL == tmp_keyword) {
 716                         /* LCOV_EXCL_START */
 717                         ERR("calloc() Fail");
 718                         mod_keyword = search_keyword;
 719                         break;
 720                         /* LCOV_EXCL_STOP */
 721                 }
 722                 for (i = 0; i < tmp_len; i++)
 723                         tmp_keyword[i] = ('-' == search_keyword[i]) ? '_' : search_keyword[i];
 724                 tmp_keyword[i] = '\0';
 725                 mod_keyword = tmp_keyword;
 726                 break;
 727         }
 728
 729
 730         int temp_len = 0;
 731         int query_size = CTS_SQL_MAX_LEN;
 732         char *query = calloc(query_size, sizeof(char));
 733         int len = 0;
 734
 735         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " SELECT contact_id "
 736                         "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" "
 737                         "MATCH 'data:");
 738         if (0 <= temp_len)
 739                 len += temp_len;
 740
 741         temp_len = SAFE_SNPRINTF(&query, &query_size, len, mod_keyword);
 742         if (0 <= temp_len)
 743                 len += temp_len;
 744
 745         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " '");
 746         if (0 <= temp_len)
 747                 len += temp_len;
 748
 749         free(hiragana);
 750         free(search_hiragana);
 751         free(search_keyword);
 752         free(tmp_keyword);
 753
 754         if (use_replaced_keyword)
 755                 free(half_keyword);
 756
 757         return strdup(query);
 758 }
 759
 760 static inline void __db_create_str_condtion_number(
 761                 ctsvc_composite_filter_s *com_filter,
 762                 ctsvc_attribute_filter_s *filter,
 763                 bool with_escape,
 764                 GSList **bind_text,
 765                 int cond_len,
 766                 char *out_cond,
 767                 int out_cond_size)
 768 {
 769         /*
 770          * number filter is for matching number
 771          * depends on internal rule _NUMBER_COMPARE_
 772          */
 773         int ret;
 774         char clean_num[strlen(filter->value.s)+1+5];   /* for cc */
 775         char normal_num[strlen(filter->value.s)+1+5];  /* for cc */
 776         bool add_condition = false;
 777
 778         ret = ctsvc_clean_number(filter->value.s, clean_num, sizeof(clean_num), false);
 779         if (0 < ret) {
 780                 ret = ctsvc_normalize_number(clean_num, normal_num, sizeof(normal_num), false);
 781                 normal_num[sizeof(normal_num) -1] = '\0';
 782                 if (0 < ret) {
 783                         char min_match[sizeof(normal_num)+1];   /* for cc */
 784                         ret = ctsvc_get_minmatch_number(normal_num, min_match,
 785                                         sizeof(min_match), ctsvc_get_phonenumber_min_match_digit());
 786                         if (CONTACTS_ERROR_NONE == ret) {
 787                                 /* minmatch filter is to improve performance */
 788                                 size_t min_match_sz = strlen(min_match);
 789                                 *bind_text = g_slist_append(*bind_text,
 790                                                 __db_get_str_with_escape(min_match, min_match_sz, with_escape));
 791
 792                                 /* _NUMBER_COMPARE_(noraml_num, normalized keyword) */
 793                                 if (STRING_EQUAL != strcmp(normal_num, "+")) {
 794                                         const char *number_field = NULL;
 795                                         int property_id = filter->property_id;
 796                                         if (property_id == CTSVC_PROPERTY_NUMBER_NUMBER_FILTER) {
 797                                                 number_field = __ctsvc_db_get_property_field_name(
 798                                                                 com_filter->properties,
 799                                                                 com_filter->property_count,
 800                                                                 QUERY_FILTER,
 801                                                                 CTSVC_PROPERTY_NUMBER_NORMALIZED_NUMBER);
 802                                         } else if (property_id == CTSVC_PROPERTY_PHONELOG_ADDRESS_FILTER) {
 803                                                 number_field = __ctsvc_db_get_property_field_name(
 804                                                                 com_filter->properties,
 805                                                                 com_filter->property_count,
 806                                                                 QUERY_FILTER,
 807                                                                 CTSVC_PROPERTY_PHONELOG_NORMALIZED_ADDRESS);
 808                                         } else if (property_id == CTSVC_PROPERTY_SPEEDDIAL_NUMBER_FILTER) {
 809                                                 number_field = __ctsvc_db_get_property_field_name(
 810                                                                 com_filter->properties,
 811                                                                 com_filter->property_count,
 812                                                                 QUERY_FILTER,
 813                                                                 CTSVC_PROPERTY_SPEEDDIAL_NORMALIZED_NUMBER);
 814                                         }
 815
 816                                         if (number_field) {
 817                                                 cond_len += snprintf(out_cond+cond_len, out_cond_size - cond_len,
 818                                                                 " AND _NUMBER_COMPARE_(%s, ?, NULL, NULL)", number_field);
 819                                                 *bind_text = g_slist_append(*bind_text, strdup(normal_num));
 820                                                 add_condition = true;
 821                                         }
 822                                 }
 823                         }
 824                 }
 825         }
 826
 827         if (add_condition == false) {
 828                 /* If Failto get normalized number then compare with original number */
 829                 const char *number_field = NULL;
 830                 if (filter->property_id == CTSVC_PROPERTY_NUMBER_NUMBER_FILTER) {
 831                         number_field = __ctsvc_db_get_property_field_name(com_filter->properties,
 832                                         com_filter->property_count,
 833                                         QUERY_FILTER,
 834                                         CTSVC_PROPERTY_NUMBER_NUMBER);
 835                 } else if (filter->property_id == CTSVC_PROPERTY_PHONELOG_ADDRESS_FILTER) {
 836                         number_field = __ctsvc_db_get_property_field_name(com_filter->properties,
 837                                         com_filter->property_count,
 838                                         QUERY_FILTER,
 839                                         CTSVC_PROPERTY_PHONELOG_ADDRESS);
 840                 } else if (filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_NUMBER_FILTER) {
 841                         number_field = __ctsvc_db_get_property_field_name(com_filter->properties,
 842                                         com_filter->property_count,
 843                                         QUERY_FILTER,
 844                                         CTSVC_PROPERTY_SPEEDDIAL_NUMBER);
 845                 }
 846
 847                 if (number_field) {
 848                         snprintf(out_cond, out_cond_size, "%s = ?", number_field);
 849                         *bind_text = g_slist_append(*bind_text, strdup(filter->value.s));
 850                 }
 851         }
 852 }
 853
 854 static inline int __db_create_str_condtion_normalized_number(
 855                 ctsvc_composite_filter_s *com_filter,
 856                 ctsvc_attribute_filter_s *filter,
 857                 bool with_escape,
 858                 GSList **bind_text,
 859                 int cond_len,
 860                 char *out_cond,
 861                 int out_cond_sz)
 862 {
 863         int ret;
 864         char *tmp = NULL;
 865         char clean_num[strlen(filter->value.s)+1+5];   /* for cc */
 866
 867         ret = ctsvc_clean_number(filter->value.s, clean_num, sizeof(clean_num), false);
 868         if (0 < ret) {
 869                 bool add_condition = true;
 870                 const char *clean_field = NULL;
 871                 /* has clean number or normalized number */
 872                 const char *cc = ctsvc_get_network_cc(false);
 873                 if (cc && cc[0] == '7' && clean_num[0] == '8') {   /* Russia */
 874                         char normal_num[strlen(clean_num)+1+5];   /* for cc */
 875                         int normal_num_sz = sizeof(normal_num);
 876
 877                         ret = ctsvc_normalize_number(clean_num, normal_num, normal_num_sz, false);
 878                         if (0 < ret) {
 879                                 *bind_text = g_slist_append(*bind_text,
 880                                                 __db_get_str_with_escape(normal_num, normal_num_sz, with_escape));
 881                         } else {
 882                                 *bind_text = g_slist_append(*bind_text,
 883                                                 __db_get_str_with_escape(clean_num, normal_num_sz, with_escape));
 884                         }
 885                 } else if (STRING_EQUAL != strcmp(clean_num, "+")) {
 886                         *bind_text = g_slist_append(*bind_text,
 887                                         __db_get_str_with_escape(clean_num, strlen(clean_num), with_escape));
 888                 } else {
 889                         add_condition = false;
 890                 }
 891
 892                 if (filter->property_id == CTSVC_PROPERTY_NUMBER_NORMALIZED_NUMBER) {
 893                         clean_field = __ctsvc_db_get_property_field_name(com_filter->properties,
 894                                         com_filter->property_count,
 895                                         QUERY_FILTER,
 896                                         CTSVC_PROPERTY_NUMBER_CLEANED_NUMBER);
 897                 } else if (filter->property_id == CTSVC_PROPERTY_PHONELOG_NORMALIZED_ADDRESS) {
 898                         clean_field =  __ctsvc_db_get_property_field_name(com_filter->properties,
 899                                         com_filter->property_count,
 900                                         QUERY_FILTER,
 901                                         CTSVC_PROPERTY_PHONELOG_CLEANED_ADDRESS);
 902                 } else if (filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_NORMALIZED_NUMBER) {
 903                         clean_field =  __ctsvc_db_get_property_field_name(com_filter->properties,
 904                                         com_filter->property_count,
 905                                         QUERY_FILTER,
 906                                         CTSVC_PROPERTY_SPEEDDIAL_CLEANED_NUMBER);
 907                 }
 908
 909                 if (clean_field) {
 910                         if (add_condition)
 911                                 cond_len += snprintf(out_cond+cond_len, out_cond_sz - cond_len, " OR ");
 912                         ret = __db_add_str_matching_rule(clean_field, filter->match, &tmp,
 913                                         &with_escape);
 914                         if (ret <= 0) {
 915                                 /* LCOV_EXCL_START */
 916                                 ERR("__db_add_str_matching_rule() Fail");
 917                                 return CONTACTS_ERROR_INVALID_PARAMETER;
 918                                 /* LCOV_EXCL_STOP */
 919                         }
 920                         cond_len += snprintf(out_cond + cond_len, out_cond_sz - cond_len, "%s", tmp);
 921                         free(tmp);
 922                         tmp = NULL;
 923                         *bind_text = g_slist_append(*bind_text,
 924                                         __db_get_str_with_escape(clean_num, strlen(clean_num), with_escape));
 925                 }
 926         } else if (ret == 0) {
 927                 /* If Failto get cleaned number then compare with original number */
 928                 const char *number_field = NULL;
 929                 if (filter->property_id == CTSVC_PROPERTY_NUMBER_NORMALIZED_NUMBER) {
 930                         number_field = __ctsvc_db_get_property_field_name(com_filter->properties,
 931                                         com_filter->property_count,
 932                                         QUERY_FILTER,
 933                                         CTSVC_PROPERTY_NUMBER_NUMBER);
 934                 } else if (filter->property_id == CTSVC_PROPERTY_PHONELOG_NORMALIZED_ADDRESS) {
 935                         number_field =  __ctsvc_db_get_property_field_name(com_filter->properties,
 936                                         com_filter->property_count,
 937                                         QUERY_FILTER,
 938                                         CTSVC_PROPERTY_PHONELOG_ADDRESS);
 939                 } else if (filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_NORMALIZED_NUMBER) {
 940                         number_field =  __ctsvc_db_get_property_field_name(com_filter->properties,
 941                                         com_filter->property_count,
 942                                         QUERY_FILTER,
 943                                         CTSVC_PROPERTY_SPEEDDIAL_NUMBER);
 944                 }
 945
 946                 if (number_field) {
 947                         ret = __db_add_str_matching_rule(number_field, filter->match, &tmp,
 948                                         &with_escape);
 949                         if (ret <= 0) {
 950                                 /* LCOV_EXCL_START */
 951                                 ERR("__db_add_str_matching_rule() Fail");
 952                                 return CONTACTS_ERROR_INVALID_PARAMETER;
 953                                 /* LCOV_EXCL_STOP */
 954                         }
 955
 956                         cond_len = snprintf(out_cond, out_cond_sz, "%s", tmp);
 957                         free(tmp);
 958                         tmp = NULL;
 959                         *bind_text = g_slist_append(*bind_text,
 960                                         __db_get_str_with_escape(filter->value.s, strlen(filter->value.s),
 961                                                 with_escape)
 962                                         );
 963                 }
 964         } else {
 965                 return CONTACTS_ERROR_INVALID_PARAMETER;
 966         }
 967
 968         return CONTACTS_ERROR_NONE;
 969 }
 970
 971 static inline int __ctsvc_db_create_str_condition(ctsvc_composite_filter_s *com_filter,
 972                 ctsvc_attribute_filter_s *filter, char **condition, GSList **bind_text)
 973 {
 974         int ret;
 975         const char *field_name;
 976         char out_cond[CTS_SQL_MAX_LEN] = {0};
 977         char *tmp = NULL;
 978         int cond_len = 0;
 979         bool with_escape = true;
 980
 981         *condition = NULL;
 982
 983         field_name = __ctsvc_db_get_property_field_name(com_filter->properties,
 984                         com_filter->property_count, QUERY_FILTER, filter->property_id);
 985         RETVM_IF(NULL == field_name, CONTACTS_ERROR_INVALID_PARAMETER,
 986                         "property id(%d)", filter->property_id);
 987
 988         /*
 989          * number_filter condition is only used to find exactly matched number
 990          * based on internal logic : _NUMBER_COMPARE_
 991          */
 992         if (filter->property_id == CTSVC_PROPERTY_NUMBER_NUMBER_FILTER
 993                         || filter->property_id == CTSVC_PROPERTY_PHONELOG_ADDRESS_FILTER
 994                         || filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_NUMBER_FILTER)
 995                 filter->match = CONTACTS_MATCH_EXACTLY;
 996
 997         ret = __db_add_str_matching_rule(field_name, filter->match, &tmp, &with_escape);
 998         if (ret <= 0) {
 999                 /* LCOV_EXCL_START */
1000                 ERR("__db_add_str_matching_rule() Fail(%d)", ret);
1001                 free(tmp);
1002                 return CONTACTS_ERROR_INVALID_PARAMETER;
1003                 /* LCOV_EXCL_STOP */
1004         }
1005         cond_len = snprintf(out_cond, sizeof(out_cond), "%s", tmp);
1006         free(tmp);
1007         tmp = NULL;
1008
1009         if (filter->value.s) {
1010                 if (filter->property_id == CTSVC_PROPERTY_NUMBER_NUMBER_FILTER
1011                                 || filter->property_id == CTSVC_PROPERTY_PHONELOG_ADDRESS_FILTER
1012                                 || filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_NUMBER_FILTER) {
1013                         /* number filter */
1014                         __db_create_str_condtion_number(com_filter, filter, with_escape, bind_text,
1015                                         cond_len, out_cond, sizeof(out_cond));
1016                 } else if (filter->property_id == CTSVC_PROPERTY_NUMBER_NORMALIZED_NUMBER
1017                                 || filter->property_id == CTSVC_PROPERTY_PHONELOG_NORMALIZED_ADDRESS
1018                                 || filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_NORMALIZED_NUMBER) {
1019                         /* normalized number */
1020                         ret = __db_create_str_condtion_normalized_number(com_filter, filter,
1021                                         with_escape, bind_text, cond_len, out_cond, sizeof(out_cond));
1022                         if (CONTACTS_ERROR_NONE != ret) {
1023                                 /* LCOV_EXCL_START */
1024                                 ERR("__db_create_str_condtion_normalized_number() Fail(%d)", ret);
1025                                 return ret;
1026                                 /* LCOV_EXCL_STOP */
1027                         }
1028                 } else if (filter->property_id == CTSVC_PROPERTY_NUMBER_CLEANED_NUMBER
1029                                 || filter->property_id == CTSVC_PROPERTY_PHONELOG_CLEANED_ADDRESS
1030                                 || filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_CLEANED_NUMBER) {
1031                         /* cleaned number */
1032                         char clean_num[strlen(filter->value.s)+1+5];   /* for cc */
1033
1034                         ret = ctsvc_clean_number(filter->value.s, clean_num, sizeof(clean_num),
1035                                         false);
1036                         if (0 < ret) {
1037                                 *bind_text = g_slist_append(*bind_text,
1038                                                 __db_get_str_with_escape(clean_num, strlen(clean_num),
1039                                                         with_escape));
1040                         } else if (ret == 0) {
1041                                 /* If Failto get cleaned number then compare with original number */
1042                                 const char *number_field = NULL;
1043                                 if (filter->property_id == CTSVC_PROPERTY_NUMBER_CLEANED_NUMBER) {
1044                                         number_field = __ctsvc_db_get_property_field_name(
1045                                                         com_filter->properties,
1046                                                         com_filter->property_count,
1047                                                         QUERY_FILTER,
1048                                                         CTSVC_PROPERTY_NUMBER_NUMBER);
1049                                 } else if (filter->property_id == CTSVC_PROPERTY_PHONELOG_CLEANED_ADDRESS) {
1050                                         number_field =  __ctsvc_db_get_property_field_name(
1051                                                         com_filter->properties,
1052                                                         com_filter->property_count,
1053                                                         QUERY_FILTER,
1054                                                         CTSVC_PROPERTY_PHONELOG_ADDRESS);
1055                                 } else if (filter->property_id == CTSVC_PROPERTY_SPEEDDIAL_CLEANED_NUMBER) {
1056                                         number_field =  __ctsvc_db_get_property_field_name(
1057                                                         com_filter->properties,
1058                                                         com_filter->property_count,
1059                                                         QUERY_FILTER,
1060                                                         CTSVC_PROPERTY_SPEEDDIAL_NUMBER);
1061                                 }
1062
1063                                 if (number_field) {
1064                                         ret = __db_add_str_matching_rule(number_field, filter->match, &tmp,
1065                                                         &with_escape);
1066                                         RETVM_IF(ret <= 0, CONTACTS_ERROR_INVALID_PARAMETER,
1067                                                         "__db_add_str_matching_rule Fail");
1068                                         cond_len = snprintf(out_cond, sizeof(out_cond), "%s", tmp);
1069                                         free(tmp);
1070                                         tmp = NULL;
1071                                         *bind_text = g_slist_append(*bind_text,
1072                                                         __db_get_str_with_escape(filter->value.s,
1073                                                                 strlen(filter->value.s), with_escape));
1074                                 }
1075                         } else {
1076                                 return CONTACTS_ERROR_INVALID_PARAMETER;
1077                         }
1078                 } else if (filter->property_id == CTSVC_PROPERTY_CONTACT_IMAGE_THUMBNAIL
1079                                 || filter->property_id == CTSVC_PROPERTY_PERSON_IMAGE_THUMBNAIL
1080                                 || filter->property_id == CTSVC_PROPERTY_MY_PROFILE_IMAGE_THUMBNAIL
1081                                 || filter->property_id == CTSVC_PROPERTY_IMAGE_PATH) {
1082                         if (STRING_EQUAL == strncmp(filter->value.s, CTSVC_CONTACT_IMG_FULL_LOCATION,
1083                                                 strlen(CTSVC_CONTACT_IMG_FULL_LOCATION))) {
1084                                 *bind_text = g_slist_append(*bind_text,
1085                                                 __db_get_str_with_escape(
1086                                                         filter->value.s + strlen(CTSVC_CONTACT_IMG_FULL_LOCATION) + 1,
1087                                                         strlen(filter->value.s) - strlen(CTSVC_CONTACT_IMG_FULL_LOCATION) - 1,
1088                                                         with_escape));
1089                         } else {
1090                                 *bind_text = g_slist_append(*bind_text,
1091                                                 __db_get_str_with_escape(filter->value.s, strlen(filter->value.s),
1092                                                         with_escape));
1093                         }
1094                 } else if (filter->property_id == CTSVC_PROPERTY_GROUP_IMAGE) {
1095                         if (STRING_EQUAL == strncmp(filter->value.s, CTS_GROUP_IMAGE_LOCATION,
1096                                                 strlen(CTS_GROUP_IMAGE_LOCATION))) {
1097                                 *bind_text = g_slist_append(*bind_text,
1098                                                 __db_get_str_with_escape(
1099                                                         filter->value.s + strlen(CTS_GROUP_IMAGE_LOCATION) + 1,
1100                                                         strlen(filter->value.s) - strlen(CTS_GROUP_IMAGE_LOCATION)-1,
1101                                                         with_escape));
1102                         } else {
1103                                 *bind_text = g_slist_append(*bind_text,
1104                                                 __db_get_str_with_escape(filter->value.s, strlen(filter->value.s),
1105                                                         with_escape));
1106                         }
1107                 } else if (filter->property_id == CTSVC_PROPERTY_COMPANY_LOGO) {
1108                         if (STRING_EQUAL == strncmp(filter->value.s, CTS_LOGO_IMAGE_LOCATION,
1109                                                 strlen(CTS_LOGO_IMAGE_LOCATION))) {
1110                                 *bind_text = g_slist_append(*bind_text,
1111                                                 __db_get_str_with_escape(
1112                                                         filter->value.s + strlen(CTS_LOGO_IMAGE_LOCATION) + 1,
1113                                                         strlen(filter->value.s) - strlen(CTS_LOGO_IMAGE_LOCATION) - 1,
1114                                                         with_escape));
1115                         } else {
1116                                 *bind_text = g_slist_append(*bind_text,
1117                                                 __db_get_str_with_escape(filter->value.s, strlen(filter->value.s),
1118                                                         with_escape));
1119                         }
1120
1121                 } else {
1122                         *bind_text = g_slist_append(*bind_text,
1123                                         __db_get_str_with_escape(filter->value.s, strlen(filter->value.s),
1124                                                 with_escape));
1125                 }
1126         }
1127
1128         *condition = strdup(out_cond);
1129
1130         return CONTACTS_ERROR_NONE;
1131 }
1132
1133 static inline int __ctsvc_db_create_bool_condition(ctsvc_composite_filter_s *com_filter,
1134                 ctsvc_attribute_filter_s *filter, char **condition)
1135 {
1136         const char *field_name;
1137         char out_cond[CTS_SQL_MAX_LEN] = {0};
1138
1139         field_name = __ctsvc_db_get_property_field_name(com_filter->properties,
1140                         com_filter->property_count, QUERY_FILTER, filter->property_id);
1141         RETVM_IF(NULL == field_name, CONTACTS_ERROR_INVALID_PARAMETER,
1142                         "property id(%d)", filter->property_id);
1143
1144         snprintf(out_cond, sizeof(out_cond), "%s = %d", field_name, filter->value.b ? 1 : 0);
1145         *condition = strdup(out_cond);
1146         return CONTACTS_ERROR_NONE;
1147 }
1148
1149 static int __ctsvc_db_create_attribute_condition(ctsvc_composite_filter_s *com_filter,
1150                 ctsvc_attribute_filter_s *filter, char **condition, GSList **bind_text)
1151 {
1152         int ret;
1153         char *cond = NULL;
1154
1155         RETV_IF(NULL == filter, CONTACTS_ERROR_INVALID_PARAMETER);
1156
1157         switch (filter->filter_type) {
1158         case CTSVC_FILTER_INT:
1159                 ret = __ctsvc_db_create_int_condition(com_filter, filter, &cond);
1160                 break;
1161         case CTSVC_FILTER_BOOL:
1162                 ret = __ctsvc_db_create_bool_condition(com_filter, filter, &cond);
1163                 break;
1164         case CTSVC_FILTER_STR:
1165                 ret = __ctsvc_db_create_str_condition(com_filter, filter, &cond, bind_text);
1166                 break;
1167         case CTSVC_FILTER_LLI:
1168                 ret = __ctsvc_db_create_lli_condition(com_filter, filter, &cond);
1169                 break;
1170         case CTSVC_FILTER_DOUBLE:
1171                 ret = __ctsvc_db_create_double_condition(com_filter, filter, &cond);
1172                 break;
1173         default:
1174                 /* LCOV_EXCL_START */
1175                 ERR("The filter type is not supported (%d)", filter->filter_type);
1176                 return CONTACTS_ERROR_INVALID_PARAMETER;
1177                 /* LCOV_EXCL_STOP */
1178         }
1179
1180         if (CONTACTS_ERROR_NONE == ret)
1181                 *condition = cond;
1182
1183         return ret;
1184 }
1185
1186 #define CTSVC_FILTER_LENGTH     100
1187
1188 /*
1189  * If there are too many condition, sqlite return FailSQLITE_ERROR(1).
1190  * Expression tree is too large (maximum depth 1000).
1191  * It is related to SQLITE_LIMIT_EXPR_DEPTH.
1192  */
1193 static inline int __db_create_composite_condition(
1194                 ctsvc_composite_filter_s *com_filter, char **condition, GSList **bind_text)
1195 {
1196         RETV_IF(NULL == com_filter, CONTACTS_ERROR_INVALID_PARAMETER);
1197
1198         int len;
1199         int temp_len;
1200         int buf_size;
1201         char *cond;
1202         char *out_cond = NULL;
1203         GSList *cursor_filter;
1204         GSList *cursor_ops;
1205         ctsvc_filter_s *filter;
1206         contacts_filter_operator_e op;
1207         GSList *bind;
1208         GSList *filters = com_filter->filters;
1209         GSList *ops = com_filter->filter_ops;
1210
1211         *condition = NULL;
1212         /* the case : did not set filter condition after calling contacts_filter_create() */
1213
1214         RETV_IF(NULL == filters, CONTACTS_ERROR_NONE);
1215
1216         cond = NULL;
1217         bind = NULL;
1218
1219         filter = (ctsvc_filter_s*)filters->data;
1220         if (filter->filter_type == CTSVC_FILTER_COMPOSITE) {
1221                 __db_create_composite_condition((ctsvc_composite_filter_s*)filter, &cond,
1222                                 bind_text);
1223         } else {
1224                 __ctsvc_db_create_attribute_condition(com_filter,
1225                                 (ctsvc_attribute_filter_s*)filter, &cond, bind_text);
1226         }
1227
1228         buf_size = CTSVC_FILTER_LENGTH;
1229         out_cond = calloc(1, buf_size);
1230         if (NULL == out_cond) {
1231                 /* LCOV_EXCL_START */
1232                 ERR("calloc() Fail");
1233                 free(cond);
1234                 return CONTACTS_ERROR_OUT_OF_MEMORY;
1235                 /* LCOV_EXCL_STOP */
1236         }
1237
1238         len = 0;
1239         if (cond) {
1240                 temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, "(");
1241                 if (0 <= temp_len)
1242                         len += temp_len;
1243
1244                 temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, cond);
1245                 if (0 <= temp_len)
1246                         len += temp_len;
1247
1248                 temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, ")");
1249                 if (0 <= temp_len)
1250                         len += temp_len;
1251                 free(cond);
1252         }
1253
1254         cursor_filter = filters->next;
1255         for (cursor_ops = ops; cursor_ops && cursor_filter;
1256                         cursor_filter = cursor_filter->next, cursor_ops = cursor_ops->next) {
1257                 cond = NULL;
1258                 bind = NULL;
1259
1260                 filter = cursor_filter->data;
1261                 if (filter->filter_type == CTSVC_FILTER_COMPOSITE) {
1262                         __db_create_composite_condition((ctsvc_composite_filter_s*)filter,
1263                                         &cond, &bind);
1264                 } else {
1265                         __ctsvc_db_create_attribute_condition(com_filter,
1266                                         (ctsvc_attribute_filter_s*)filter, &cond, &bind);
1267                 }
1268
1269                 if (cond) {
1270                         op = (contacts_filter_operator_e)cursor_ops->data;
1271                         if (op == CONTACTS_FILTER_OPERATOR_AND)
1272                                 temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, " AND (");
1273                         else
1274                                 temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, " OR (");
1275                         if (0 <= temp_len)
1276                                 len += temp_len;
1277
1278                         temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, cond);
1279                         if (0 <= temp_len)
1280                                 len += temp_len;
1281                         temp_len = SAFE_SNPRINTF(&out_cond, &buf_size, len, ")");
1282                         if (0 <= temp_len)
1283                                 len += temp_len;
1284
1285                         if (bind)
1286                                 *bind_text = g_slist_concat(*bind_text, bind);
1287                         free(cond);
1288                 }
1289         }
1290
1291         *condition = out_cond;
1292
1293         return CONTACTS_ERROR_NONE;
1294 }
1295
1296 /* Make and execute 'UPDATE' sqlite statement to update record */
1297 int ctsvc_db_update_record_with_set_query(const char *set, GSList *bind_text,
1298                 const char *table, int id)
1299 {
1300         int ret = CONTACTS_ERROR_NONE;
1301         char query[CTS_SQL_MAX_LEN] = {0};
1302         sqlite3_stmt *stmt = NULL;
1303         GSList *cursor = NULL;
1304
1305         snprintf(query, sizeof(query), "UPDATE %s SET %s WHERE id = %d", table, set, id);
1306
1307         ret = ctsvc_query_prepare(query, &stmt);
1308         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
1309
1310         if (bind_text) {
1311                 int i = 0;
1312                 for (cursor = bind_text, i = 1; cursor; cursor = cursor->next, i++) {
1313                         const char *text = cursor->data;
1314                         if (text && *text)
1315                                 ctsvc_stmt_bind_text(stmt, i, text);
1316                 }
1317         }
1318         ret = ctsvc_stmt_step(stmt);
1319         if (CONTACTS_ERROR_NONE != ret) {
1320                 /* LCOV_EXCL_START */
1321                 ERR("ctsvc_stmt_step() Fail(%d)", ret);
1322                 ctsvc_stmt_finalize(stmt);
1323                 return ret;
1324                 /* LCOV_EXCL_STOP */
1325         }
1326         ctsvc_stmt_finalize(stmt);
1327         return ret;
1328 }
1329
1330 /* make 'SET' sqlite statement to update record */
1331 int ctsvc_db_create_set_query(contacts_record_h record, char **set, GSList **bind_text)
1332 {
1333         ctsvc_record_s *s_record;
1334         int i = 0;
1335         const property_info_s *property_info = NULL;
1336         unsigned int property_info_count = 0;
1337         char out_set[CTS_SQL_MAX_LEN] = {0};
1338         int len = 0;
1339         const char *field_name;
1340         int ret = CONTACTS_ERROR_NONE;
1341
1342         RETV_IF(record == NULL, CONTACTS_ERROR_INVALID_PARAMETER);
1343
1344         s_record = (ctsvc_record_s*)record;
1345         if (0 == s_record->property_max_count || NULL == s_record->properties_flags) {
1346                 /* LCOV_EXCL_START */
1347                 ERR("record don't have properties");
1348                 return CONTACTS_ERROR_INVALID_PARAMETER;
1349                 /* LCOV_EXCL_STOP */
1350         }
1351
1352         property_info = ctsvc_view_get_all_property_infos(s_record->view_uri,
1353                         &property_info_count);
1354
1355         for (i = 0; i < property_info_count; i++) {
1356                 if (ctsvc_record_check_property_flag(s_record, property_info[i].property_id,
1357                                         CTSVC_PROPERTY_FLAG_DIRTY)) {
1358                         field_name = property_info[i].fields;
1359                         if (NULL == field_name)
1360                                 continue;
1361
1362                         if (CTSVC_VIEW_CHECK_DATA_TYPE(property_info[i].property_id, CTSVC_VIEW_DATA_TYPE_BOOL)) {
1363                                 bool tmp = false;
1364                                 ret = contacts_record_get_bool(record, property_info[i].property_id, &tmp);
1365                                 if (ret != CONTACTS_ERROR_NONE)
1366                                         continue;
1367                                 if (len != 0)
1368                                         len += snprintf(out_set+len, sizeof(out_set)-len, ", ");
1369                                 len += snprintf(out_set+len, sizeof(out_set)-len, "%s=%d", field_name, tmp);
1370                         } else if (CTSVC_VIEW_CHECK_DATA_TYPE(property_info[i].property_id, CTSVC_VIEW_DATA_TYPE_INT)) {
1371                                 int tmp = 0;
1372                                 ret = contacts_record_get_int(record, property_info[i].property_id, &tmp);
1373                                 if (ret != CONTACTS_ERROR_NONE)
1374                                         continue;
1375                                 if (len != 0)
1376                                         len += snprintf(out_set+len, sizeof(out_set)-len, ", ");
1377                                 len += snprintf(out_set+len, sizeof(out_set)-len, "%s=%d", field_name, tmp);
1378                         } else if (CTSVC_VIEW_CHECK_DATA_TYPE(property_info[i].property_id, CTSVC_VIEW_DATA_TYPE_LLI)) {
1379                                 long long int tmp = 0;
1380                                 ret = contacts_record_get_lli(record, property_info[i].property_id, &tmp);
1381                                 if (ret != CONTACTS_ERROR_NONE)
1382                                         continue;
1383                                 if (len != 0)
1384                                         len += snprintf(out_set+len, sizeof(out_set)-len, ", ");
1385                                 len += snprintf(out_set+len, sizeof(out_set)-len, "%s=%lld", field_name, tmp);
1386                         } else if (CTSVC_VIEW_CHECK_DATA_TYPE(property_info[i].property_id, CTSVC_VIEW_DATA_TYPE_STR)) {
1387                                 char *tmp = NULL;
1388                                 ret = contacts_record_get_str_p(record, property_info[i].property_id, &tmp);
1389                                 if (ret != CONTACTS_ERROR_NONE)
1390                                         continue;
1391                                 if (len != 0)
1392                                         len += snprintf(out_set+len, sizeof(out_set)-len, ", ");
1393                                 len += snprintf(out_set+len, sizeof(out_set)-len, "%s=?", field_name);
1394                                 *bind_text = g_slist_append(*bind_text, strdup(SAFE_STR(tmp)));
1395                         } else if (CTSVC_VIEW_CHECK_DATA_TYPE(property_info[i].property_id, CTSVC_VIEW_DATA_TYPE_DOUBLE)) {
1396                                 double tmp = 0;
1397                                 ret = contacts_record_get_double(record, property_info[i].property_id, &tmp);
1398                                 if (ret != CONTACTS_ERROR_NONE)
1399                                         continue;
1400                                 if (len != 0)
1401                                         len += snprintf(out_set+len, sizeof(out_set)-len, ", ");
1402                                 len += snprintf(out_set+len, sizeof(out_set)-len, "%s=%lf", field_name,
1403                                                 tmp);
1404                         }
1405                 }
1406         }
1407         *set = strdup(out_set);
1408
1409         return CONTACTS_ERROR_NONE;
1410 }
1411
1412 static int __db_create_projection(const char *view_uri,
1413                 const property_info_s *properties, int ids_count,
1414                 unsigned int *projections,
1415                 int pro_count,
1416                 char **projection,
1417                 bool is_snippet)
1418 {
1419         bool first;
1420         int i;
1421         int len;
1422         const char *field_name = NULL;
1423         char out_projection[CTS_SQL_MAX_LEN] = {0};
1424         char temp[CTS_SQL_MAX_LEN] = {0};
1425         bool is_data1 = false;
1426         bool is_data2 = false;
1427
1428         len = 0;
1429         first = true;
1430         if (0 < pro_count) {
1431                 for (i = 0; i < pro_count; i++) {
1432                         if (projections[i] == CTSVC_PROPERTY_PERSON_DISPLAY_NAME_INDEX) {
1433                                 snprintf(temp, sizeof(temp), "_NORMALIZE_INDEX_(%s)",
1434                                                 ctsvc_get_sort_name_column());
1435                                 field_name = temp;
1436                         } else if (true == is_snippet &&
1437                                         projections[i] == CTSVC_PROPERTY_PERSON_SNIPPET_TYPE) {
1438                                 snprintf(temp, sizeof(temp), "temp_data.datatype");
1439                                 field_name = temp;
1440                                 is_data1 = true;
1441                         } else if (true == is_snippet &&
1442                                         projections[i] == CTSVC_PROPERTY_PERSON_SNIPPET_STRING) {
1443                                 snprintf(temp, sizeof(temp), "(CASE "
1444                                                 "WHEN datatype=1 THEN (datatype || '@' || display_name) "
1445                                                 "ELSE (datatype || '@' || temp_data._data) END)");
1446                                 field_name = temp;
1447                                 is_data2 = true;
1448                         } else {
1449                                 field_name = __ctsvc_db_get_property_field_name(properties, ids_count,
1450                                                 QUERY_PROJECTION, projections[i]);
1451                         }
1452
1453                         if (field_name) {
1454                                 if (first) {
1455                                         len += snprintf(out_projection+len, sizeof(out_projection)-len,
1456                                                         "%s", field_name);
1457                                         first = false;
1458                                 } else {
1459                                         len += snprintf(out_projection+len, sizeof(out_projection)-len,
1460                                                         ", %s", field_name);
1461                                 }
1462                         }
1463                 }
1464         } else {
1465                 /* add all properties */
1466                 for (i = 0; i < ids_count; i++) {
1467                         if (CTSVC_VIEW_DATA_TYPE_REC == (properties[i].property_id & CTSVC_VIEW_DATA_TYPE_MASK))
1468                                 continue;
1469
1470                         if (properties[i].fields) {
1471                                 field_name = properties[i].fields;
1472                         } else if (properties[i].property_id == CTSVC_PROPERTY_PERSON_DISPLAY_NAME_INDEX) {
1473                                 snprintf(temp, sizeof(temp), "_NORMALIZE_INDEX_(%s)",
1474                                                 ctsvc_get_sort_name_column());
1475                                 field_name = temp;
1476                         } else if (true == is_snippet &&
1477                                         properties[i].property_id == CTSVC_PROPERTY_PERSON_SNIPPET_TYPE) {
1478                                 snprintf(temp, sizeof(temp), "temp_data.datatype");
1479                                 field_name = temp;
1480                                 is_data1 = true;
1481                         } else if (true == is_snippet &&
1482                                         properties[i].property_id == CTSVC_PROPERTY_PERSON_SNIPPET_STRING) {
1483                                 snprintf(temp, sizeof(temp), "(CASE "
1484                                                 "WHEN datatype=1 THEN (datatype || '@' || display_name) "
1485                                                 "ELSE (datatype || '@' || temp_data._data) END)");
1486                                 field_name = temp;
1487                                 is_data2 = true;
1488                         } else {
1489                                 field_name = ctsvc_get_display_column();
1490                         }
1491
1492                         if (first) {
1493                                 len += snprintf(out_projection+len, sizeof(out_projection)-len, "%s",
1494                                                 field_name);
1495                                 first = false;
1496                         } else {
1497                                 len += snprintf(out_projection+len, sizeof(out_projection)-len, ", %s",
1498                                                 field_name);
1499                         }
1500                 }
1501         }
1502
1503         /* check extra data */
1504         if (true == is_data1 || true == is_data2) {
1505                 snprintf(temp, sizeof(temp), "MIN(temp_data._priority) ");
1506                 field_name = temp;
1507                 if (first) {
1508                         len += snprintf(out_projection+len, sizeof(out_projection)-len, "%s",
1509                                         field_name);
1510                         first = false;
1511                 } else {
1512                         len += snprintf(out_projection+len, sizeof(out_projection)-len, ", %s",
1513                                         field_name);
1514                 }
1515         }
1516
1517         *projection = strdup(out_projection);
1518         return CONTACTS_ERROR_NONE;
1519 }
1520
1521 static inline bool __ctsvc_db_view_has_display_name(const char *view_uri,
1522                 const property_info_s *properties, int count)
1523 {
1524         int i;
1525 #ifdef ENABLE_LOG_FEATURE
1526         if (STRING_EQUAL == strcmp(view_uri, _contacts_person_phone_log._uri))
1527                 return false;
1528 #endif /* ENABLE_LOG_FEATURE */
1529
1530         for (i = 0; i < count; i++) {
1531                 property_info_s *p = (property_info_s*)&(properties[i]);
1532                 switch (p->property_id) {
1533                 case CTSVC_PROPERTY_PERSON_DISPLAY_NAME:
1534                 case CTSVC_PROPERTY_CONTACT_DISPLAY_NAME:
1535                         return true;
1536                 default:
1537                         break;
1538                 }
1539         }
1540         return false;
1541 }
1542
1543 #define SORT_CHECK_LEN  7
1544
1545 int ctsvc_db_make_get_records_query_stmt(ctsvc_query_s *s_query, int offset, int limit,
1546                 cts_stmt *stmt)
1547 {
1548         char *query = NULL;
1549         char temp_str[100] = {0};
1550         int query_size = 0;
1551         int temp_len = 0;
1552         int len;
1553         int ret;
1554         int i;
1555         const char *table;
1556         const char *sortkey = NULL;
1557         char *condition = NULL;
1558         char *projection = NULL;
1559         GSList *bind_text = NULL;
1560         GSList *cursor;
1561
1562         ret = ctsvc_db_get_table_name(s_query->view_uri, &table);
1563         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "Invalid view uri(%s)", s_query->view_uri);
1564
1565         ret = __db_create_projection(s_query->view_uri, s_query->properties,
1566                         s_query->property_count, s_query->projection, s_query->projection_count,
1567                         &projection, false);
1568         if (CONTACTS_ERROR_NONE != ret) {
1569                 /* LCOV_EXCL_START */
1570                 ERR("__db_create_projection() Fail(%d)", ret);
1571                 return ret;
1572                 /* LCOV_EXCL_STOP */
1573         }
1574
1575         query_size = CTS_SQL_MAX_LEN;
1576         query = calloc(1, query_size);
1577         if (NULL == query) {
1578                 /* LCOV_EXCL_START */
1579                 ERR("calloc() Fail");
1580                 free(projection);
1581                 return CONTACTS_ERROR_OUT_OF_MEMORY;
1582                 /* LCOV_EXCL_STOP */
1583         }
1584
1585         len = 0;
1586         if (s_query->distinct)
1587                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT DISTINCT ");
1588         else
1589                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT ");
1590         if (0 <= temp_len) len += temp_len;
1591         temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
1592         if (0 <= temp_len) len += temp_len;
1593         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM ");
1594         if (0 <= temp_len) len += temp_len;
1595
1596         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " (");
1597         if (0 <= temp_len) len += temp_len;
1598         temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
1599         if (0 <= temp_len) len += temp_len;
1600         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ")");
1601         if (0 <= temp_len) len += temp_len;
1602
1603         if (s_query->filter) {
1604                 ret = __db_create_composite_condition(s_query->filter, &condition,
1605                                 &bind_text);
1606                 if (CONTACTS_ERROR_NONE != ret) {
1607                         /* LCOV_EXCL_START */
1608                         ERR("__db_create_composite_condition Fail(%d)", ret);
1609                         free(projection);
1610                         return ret;
1611                         /* LCOV_EXCL_STOP */
1612                 }
1613                 if (condition && *condition) {
1614                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE (");
1615                         if (0 <= temp_len) len += temp_len;
1616                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, condition);
1617                         if (0 <= temp_len) len += temp_len;
1618                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ")");
1619                         if (0 <= temp_len) len += temp_len;
1620                 }
1621         }
1622
1623         /* If the view_uri has display_name, default sortkey is display_name */
1624         if (__ctsvc_db_view_has_display_name(s_query->view_uri, s_query->properties, s_query->property_count))
1625                 sortkey = ctsvc_get_sort_column();
1626
1627         if (s_query->sort_property_id) {
1628                 const char *field_name;
1629
1630                 switch (s_query->sort_property_id) {
1631                 case CTSVC_PROPERTY_PERSON_DISPLAY_NAME:
1632                 case CTSVC_PROPERTY_CONTACT_DISPLAY_NAME:
1633                         if (sortkey) {
1634                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
1635                                 if (0 <= temp_len) len += temp_len;
1636                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, sortkey);
1637                                 if (0 <= temp_len) len += temp_len;
1638                                 if (false == s_query->sort_asc) {
1639                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " DESC ");
1640                                         if (0 <= temp_len) len += temp_len;
1641                                 }
1642                         }
1643                         break;
1644                 default:
1645                         field_name = __ctsvc_db_get_property_field_name(s_query->properties,
1646                                         s_query->property_count, QUERY_SORTKEY, s_query->sort_property_id);
1647                         if (field_name) {
1648                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
1649                                 if (0 <= temp_len) len += temp_len;
1650                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, field_name);
1651                                 if (0 <= temp_len) len += temp_len;
1652
1653                                 if (CTSVC_PROPERTY_GROUP_NAME == s_query->sort_property_id) {
1654                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " COLLATE _NAME_SORT_ ");
1655                                         if (0 <= temp_len) len += temp_len;
1656                                 }
1657
1658                                 if (false == s_query->sort_asc) {
1659                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " DESC ");
1660                                         if (0 <= temp_len) len += temp_len;
1661                                 }
1662                         } else if (sortkey) {
1663                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
1664                                 if (0 <= temp_len) len += temp_len;
1665                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, sortkey);
1666                                 if (0 <= temp_len) len += temp_len;
1667                         }
1668                         break;
1669                 }
1670         } else if (sortkey) {
1671                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
1672                 if (0 <= temp_len)
1673                         len += temp_len;
1674
1675                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, sortkey);
1676                 if (0 <= temp_len)
1677                         len += temp_len;
1678         } else if (STRING_EQUAL == strcmp(s_query->view_uri, CTSVC_VIEW_URI_GROUP)) {
1679                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY group_prio");
1680                 if (0 <= temp_len) len += temp_len;
1681         }
1682
1683         if (0 != limit) {
1684                 snprintf(temp_str, sizeof(temp_str), " LIMIT %d", limit);
1685                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_str);
1686                 if (0 <= temp_len) len += temp_len;
1687                 if (0 < offset) {
1688                         snprintf(temp_str, sizeof(temp_str), " OFFSET %d", offset);
1689                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_str);
1690                         if (0 <= temp_len) len += temp_len;
1691                 }
1692         }
1693
1694         free(condition);
1695         free(projection);
1696
1697         ret = ctsvc_query_prepare(query, stmt);
1698         free(query);
1699         if (NULL == *stmt) {
1700                 /* LCOV_EXCL_START */
1701                 ERR("ctsvc_query_prepare() Fail(%d)", ret);
1702                 for (cursor = bind_text; cursor; cursor = cursor->next)
1703                         free(cursor->data);
1704                 g_slist_free(bind_text);
1705                 return ret;
1706                 /* LCOV_EXCL_STOP */
1707         }
1708
1709         for (cursor = bind_text, i = 1; cursor; cursor = cursor->next, i++)
1710                 ctsvc_stmt_bind_copy_text(*stmt, i, cursor->data, strlen(cursor->data));
1711
1712         for (cursor = bind_text; cursor; cursor = cursor->next)
1713                 free(cursor->data);
1714         g_slist_free(bind_text);
1715
1716         return CONTACTS_ERROR_NONE;
1717 }
1718
1719 static int __ctsvc_db_get_records_with_query_exec(ctsvc_query_s *query, int offset,
1720                 int limit, contacts_list_h *out_list)
1721 {
1722         int ret;
1723         int i;
1724         int type;
1725         cts_stmt stmt = NULL;
1726         contacts_list_h list = NULL;
1727
1728         ret = ctsvc_db_make_get_records_query_stmt(query, offset, limit, &stmt);
1729         if (CONTACTS_ERROR_NONE != ret) {
1730                 /* LCOV_EXCL_START */
1731                 ERR("ctsvc_db_make_get_records_query_stmt() Fail(%d)", ret);
1732                 return ret;
1733                 /* LCOV_EXCL_STOP */
1734         }
1735
1736         contacts_list_create(&list);
1737         while ((ret = ctsvc_stmt_step(stmt))) {
1738                 contacts_record_h record;
1739                 int field_count;
1740                 if (1 != ret) {
1741                         /* LCOV_EXCL_START */
1742                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
1743                         ctsvc_stmt_finalize(stmt);
1744                         contacts_list_destroy(list, true);
1745                         return ret;
1746                         /* LCOV_EXCL_STOP */
1747                 }
1748
1749                 contacts_record_create(query->view_uri, (contacts_record_h*)&record);
1750
1751                 if (0 == query->projection_count) {
1752                         field_count = query->property_count;
1753                 } else {
1754                         field_count = query->projection_count;
1755
1756                         ret = ctsvc_record_set_projection_flags(record, query->projection,
1757                                         query->projection_count, query->property_count);
1758                         if (CONTACTS_ERROR_NONE != ret)
1759                                 ASSERT_NOT_REACHED("To set projection is Fail.\n");
1760                 }
1761
1762                 for (i = 0; i < field_count; i++) {
1763                         int property_id;
1764
1765                         if (0 == query->projection_count)
1766                                 property_id = query->properties[i].property_id;
1767                         else
1768                                 property_id = query->projection[i];
1769
1770                         type = __ctsvc_db_get_property_type(query->properties, query->property_count,
1771                                         property_id);
1772                         if (type == CTSVC_VIEW_DATA_TYPE_INT)
1773                                 ctsvc_record_set_int(record, property_id, ctsvc_stmt_get_int(stmt, i));
1774                         else if (type == CTSVC_VIEW_DATA_TYPE_STR)
1775                                 ctsvc_record_set_str(record, property_id, ctsvc_stmt_get_text(stmt, i));
1776                         else if (type == CTSVC_VIEW_DATA_TYPE_BOOL)
1777                                 ctsvc_record_set_bool(record, property_id, (ctsvc_stmt_get_int(stmt, i) ? true : false));
1778                         else if (type == CTSVC_VIEW_DATA_TYPE_LLI)
1779                                 ctsvc_record_set_lli(record, property_id, ctsvc_stmt_get_int64(stmt, i));
1780                         else if (type == CTSVC_VIEW_DATA_TYPE_DOUBLE)
1781                                 ctsvc_record_set_double(record, property_id, ctsvc_stmt_get_dbl(stmt, i));
1782                         else
1783                                 /* LCOV_EXCL_START */
1784                                 ERR("unknown type(%d)", type);
1785                         /* LCOV_EXCL_STOP */
1786                 }
1787                 ctsvc_list_prepend(list, record);
1788         }
1789         ctsvc_stmt_finalize(stmt);
1790         ctsvc_list_reverse(list);
1791
1792         *out_list = list;
1793         return CONTACTS_ERROR_NONE;
1794 }
1795
1796 static int __ctsvc_db_get_all_records_exec(const char *view_uri,
1797                 const property_info_s *properties,
1798                 int ids_count,
1799                 const char *projection,
1800                 int offset,
1801                 int limit,
1802                 contacts_list_h *out_list)
1803 {
1804         char query[CTS_SQL_MAX_LEN] = {0};
1805         const char *table;
1806         int len;
1807         int ret;
1808         int i;
1809         int type;
1810         cts_stmt stmt = NULL;
1811         contacts_list_h list = NULL;
1812         const char *sortkey;
1813
1814         ret = ctsvc_db_get_table_name(view_uri, &table);
1815         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "Invalid view uri (%s)", view_uri);
1816
1817         len = snprintf(query, sizeof(query), "SELECT %s FROM ", projection);
1818
1819         len += snprintf(query+len, sizeof(query)-len, " (%s)", table);
1820
1821         if (__ctsvc_db_view_has_display_name(view_uri, properties, ids_count)) {
1822                 sortkey = ctsvc_get_sort_column();
1823                 len += snprintf(query+len, sizeof(query)-len, " ORDER BY %s", sortkey);
1824         } else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_GROUP))
1825                 len += snprintf(query+len, sizeof(query)-len, " ORDER BY group_prio");
1826
1827         if (0 != limit) {
1828                 len += snprintf(query+len, sizeof(query)-len, " LIMIT %d", limit);
1829                 if (0 < offset)
1830                         len += snprintf(query+len, sizeof(query)-len, " OFFSET %d", offset);
1831         }
1832
1833         ret = ctsvc_query_prepare(query, &stmt);
1834         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
1835
1836         contacts_list_create(&list);
1837         while ((ret = ctsvc_stmt_step(stmt))) {
1838                 contacts_record_h record;
1839                 if (1 != ret) {
1840                         /* LCOV_EXCL_START */
1841                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
1842                         ctsvc_stmt_finalize(stmt);
1843                         contacts_list_destroy(list, true);
1844                         return ret;
1845                         /* LCOV_EXCL_STOP */
1846                 }
1847
1848                 contacts_record_create(view_uri, &record);
1849                 for (i = 0; i < ids_count; i++) {
1850                         type = (properties[i].property_id & CTSVC_VIEW_DATA_TYPE_MASK);
1851                         if (type == CTSVC_VIEW_DATA_TYPE_INT)
1852                                 ctsvc_record_set_int(record, properties[i].property_id, ctsvc_stmt_get_int(stmt, i));
1853                         else if (type == CTSVC_VIEW_DATA_TYPE_STR)
1854                                 ctsvc_record_set_str(record, properties[i].property_id, ctsvc_stmt_get_text(stmt, i));
1855                         else if (type == CTSVC_VIEW_DATA_TYPE_BOOL)
1856                                 ctsvc_record_set_bool(record, properties[i].property_id, (ctsvc_stmt_get_int(stmt, i) ? true : false));
1857                         else if (type == CTSVC_VIEW_DATA_TYPE_LLI)
1858                                 ctsvc_record_set_lli(record, properties[i].property_id, ctsvc_stmt_get_int64(stmt, i));
1859                         else if (type == CTSVC_VIEW_DATA_TYPE_DOUBLE)
1860                                 ctsvc_record_set_double(record, properties[i].property_id, ctsvc_stmt_get_dbl(stmt, i));
1861                         else
1862                                 /* LCOV_EXCL_START */
1863                                 ERR("unknown type (%d)", type);
1864                         /* LCOV_EXCL_STOP */
1865                 }
1866                 ctsvc_list_prepend(list, record);
1867         }
1868         ctsvc_stmt_finalize(stmt);
1869         ctsvc_list_reverse(list);
1870
1871         *out_list = (contacts_list_h)list;
1872         return CONTACTS_ERROR_NONE;
1873 }
1874
1875 static int __ctsvc_db_get_all_records(const char *view_uri, int offset, int limit,
1876                 contacts_list_h *out_list)
1877 {
1878         int ret;
1879         unsigned int count;
1880         char *projection;
1881
1882         const property_info_s *p = ctsvc_view_get_all_property_infos(view_uri, &count);
1883         ret = __db_create_projection(view_uri, p, count, NULL, 0, &projection, false);
1884         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "__db_create_projection Fail(%d)", ret);
1885
1886         ret = __ctsvc_db_get_all_records_exec(view_uri, p, count, projection, offset, limit, out_list);
1887         free(projection);
1888
1889         return ret;
1890 }
1891
1892 static bool __ctsvc_db_view_can_keyword_search(const char *view_uri, int *out_enum_uri)
1893 {
1894         RETV_IF(NULL == view_uri, false);
1895         RETV_IF(NULL == out_enum_uri, false);
1896
1897         int enum_uri = -1;
1898
1899         if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_PERSON))
1900                 enum_uri = CTSVC_ENUM_URI_PERSON;
1901         else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_READ_ONLY_PERSON_CONTACT))
1902                 enum_uri = CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT;
1903         else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_READ_ONLY_PERSON_NUMBER))
1904                 enum_uri = CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER;
1905         else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_READ_ONLY_PERSON_EMAIL))
1906                 enum_uri = CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL;
1907         else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_READ_ONLY_PERSON_GROUP))
1908                 enum_uri = CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP;
1909         else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_READ_ONLY_PERSON_GROUP_ASSIGNED))
1910                 enum_uri = CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED;
1911         else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED))
1912                 enum_uri = CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED;
1913         else
1914                 /* LCOV_EXCL_START */
1915                 ERR("Invalid uri[%s]", view_uri);
1916         /* LCOV_EXCL_STOP */
1917
1918         *out_enum_uri = enum_uri;
1919
1920         return (-1 == enum_uri) ? false : true;
1921 }
1922
1923 /*
1924  * to find contact which has number including keyword
1925  * FTS can only startwiths search
1926  */
1927 static int __db_append_search_query(const char *keyword, char **query, int *query_size,
1928                 int len, int range)
1929 {
1930         bool phonenumber = true;
1931         int ret = 0;
1932         int i = 0;
1933         int temp_len;
1934
1935         if (ctsvc_is_phonenumber(keyword) == false || STRING_EQUAL == strcmp(keyword, "+"))
1936                 phonenumber = false;
1937
1938         /*
1939          * If the search key is email address format,
1940          * DO NOT search it from NAME
1941          */
1942         if (strstr(keyword, "@"))
1943                 range &= ~CONTACTS_SEARCH_RANGE_NAME;
1944
1945         if (STRING_EQUAL == strcmp(keyword, "+"))
1946                 range &= ~CONTACTS_SEARCH_RANGE_NUMBER;
1947
1948         char *half_keyword = NULL;
1949         int keyword_size = 0;
1950         bool use_replaced_keyword = true;
1951         /*
1952          * full width characters -> half width characters
1953          * (apply to only FW ASCII & some symbols)
1954          */
1955         ret = ctsvc_get_halfwidth_string(keyword, &half_keyword, &keyword_size);
1956         if (CONTACTS_ERROR_NONE != ret) {
1957                 /* LCOV_EXCL_START */
1958                 ERR("UChar converting error : ctsvc_get_halfwidth_string() Fail");
1959                 half_keyword = (char*)keyword;
1960                 use_replaced_keyword = false;
1961                 /* LCOV_EXCL_STOP */
1962         }
1963
1964         char *search_keyword = NULL;
1965         search_keyword = __ctsvc_db_make_search_keyword(half_keyword);
1966         if (NULL == search_keyword) {
1967                 /* LCOV_EXCL_START */
1968                 ERR("__ctsvc_db_make_search_keyword() Fail");
1969                 if (use_replaced_keyword)
1970                         free(half_keyword);
1971                 return CONTACTS_ERROR_OUT_OF_MEMORY;
1972                 /* LCOV_EXCL_STOP */
1973         }
1974
1975         temp_len = SAFE_SNPRINTF(query, query_size, len, "(");
1976         if (0 <= temp_len)
1977                 len += temp_len;
1978
1979         if (phonenumber) {
1980                 bool need_or = false;
1981
1982                 temp_len = SAFE_SNPRINTF(query, query_size, len, "SELECT contact_id "
1983                                 "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" MATCH ");
1984                 if (0 <= temp_len)
1985                         len += temp_len;
1986
1987                 temp_len = SAFE_SNPRINTF(query, query_size, len, "'");
1988                 if (0 <= temp_len)
1989                         len += temp_len;
1990
1991                 if (range & CONTACTS_SEARCH_RANGE_NAME) {
1992                         temp_len = SAFE_SNPRINTF(query, query_size, len, "name:");
1993                         if (0 <= temp_len)
1994                                 len += temp_len;
1995                         temp_len = SAFE_SNPRINTF(query, query_size, len, search_keyword);
1996                         if (0 <= temp_len)
1997                                 len += temp_len;
1998                         need_or = true;
1999                 }
2000
2001                 if (range & CONTACTS_SEARCH_RANGE_NUMBER) {
2002                         if (need_or) {
2003                                 temp_len = SAFE_SNPRINTF(query, query_size, len, " OR ");
2004                                 if (0 <= temp_len)
2005                                         len += temp_len;
2006                         }
2007                         temp_len = SAFE_SNPRINTF(query, query_size, len, "number:");
2008                         if (0 <= temp_len)
2009                                 len += temp_len;
2010                         temp_len = SAFE_SNPRINTF(query, query_size, len, search_keyword);
2011                         if (0 <= temp_len
2012                            ) len += temp_len;
2013                         need_or = true;
2014                 }
2015
2016                 if (range & CONTACTS_SEARCH_RANGE_DATA) {
2017                         if (need_or) {
2018                                 temp_len = SAFE_SNPRINTF(query, query_size, len, " OR ");
2019                                 if (0 <= temp_len)
2020                                         len += temp_len;
2021                         }
2022                         temp_len = SAFE_SNPRINTF(query, query_size, len, "data:");
2023                         if (0 <= temp_len)
2024                                 len += temp_len;
2025                         temp_len = SAFE_SNPRINTF(query, query_size, len, search_keyword);
2026                         if (0 <= temp_len)
2027                                 len += temp_len;
2028                 }
2029
2030                 temp_len = SAFE_SNPRINTF(query, query_size, len, "' ");
2031                 if (0 <= temp_len)
2032                         len += temp_len;
2033
2034                 do {
2035                         if (!(range & CONTACTS_SEARCH_RANGE_NUMBER))
2036                                 break;
2037                         int len_keyword = strlen(half_keyword);
2038                         char *clean_number = calloc(len_keyword + 1, sizeof(char));
2039                         if(clean_number == NULL) {
2040                                 ERR("Memory allocation failure ");
2041                                 break;
2042                         }
2043                         int err = ctsvc_clean_number(half_keyword, clean_number, len_keyword + 1, false);
2044                         if (err <= 0) {
2045                                 /* LCOV_EXCL_START */
2046                                 ERR("ctsvc_clean_number() Fail(%d)", err);
2047                                 free(clean_number);
2048                                 break;
2049                                 /* LCOV_EXCL_STOP */
2050                         }
2051
2052                         const char *cc = ctsvc_get_network_cc(false);
2053                         temp_len = SAFE_SNPRINTF(query, query_size, len, " UNION SELECT contact_id "
2054                                         "FROM "CTS_TABLE_PHONE_LOOKUP" WHERE number LIKE '%%");
2055                         if (0 <= temp_len)
2056                                 len += temp_len;
2057
2058                         temp_len = SAFE_SNPRINTF(query, query_size, len, clean_number);
2059                         if (0 <= temp_len)
2060                                 len += temp_len;
2061
2062                         temp_len = SAFE_SNPRINTF(query, query_size, len, "%%' ");
2063                         if (0 <= temp_len)
2064                                 len += temp_len;
2065
2066
2067                         if (cc && cc[0] == '7' && clean_number[0] == '8') {   /* Russia */
2068                                 char normal_num[strlen(clean_number)+1+5];   /* for cc */
2069                                 int ret;
2070                                 ret = ctsvc_normalize_number(clean_number, normal_num, sizeof(normal_num), false);
2071                                 if (ret <= 0) {
2072                                         free(clean_number);
2073                                         break;
2074                                 }
2075                                 temp_len = SAFE_SNPRINTF(query, query_size, len, "OR number LIKE '%%");
2076                                 if (0 <= temp_len)
2077                                         len += temp_len;
2078
2079                                 temp_len = SAFE_SNPRINTF(query, query_size, len, normal_num);
2080                                 if (0 <= temp_len)
2081                                         len += temp_len;
2082
2083                                 temp_len = SAFE_SNPRINTF(query, query_size, len, "%%' ");
2084                                 if (0 <= temp_len)
2085                                         len += temp_len;
2086                         }
2087                         free(clean_number);
2088                 } while (0);
2089
2090         } else {
2091                 char *normalized_name = NULL;
2092                 int lang = CTSVC_LANG_OTHERS;
2093                 char *hiragana = NULL;
2094                 char *search_hiragana = NULL;
2095                 bool need_union = false;
2096
2097                 lang = ctsvc_normalize_str(half_keyword, &normalized_name);
2098
2099                 char *mod_keyword = NULL;
2100                 if (range & CONTACTS_SEARCH_RANGE_NUMBER) {
2101                         switch (lang) {
2102                         case CTSVC_LANG_JAPANESE:
2103                                 ctsvc_convert_japanese_to_hiragana(half_keyword, &hiragana);
2104                                 search_hiragana = __ctsvc_db_make_search_keyword(hiragana);
2105                                 mod_keyword = search_hiragana;
2106                                 break;
2107                         default:
2108                                 mod_keyword = search_keyword;
2109                                 break;
2110                         }
2111
2112                         temp_len = SAFE_SNPRINTF(query, query_size, len, "SELECT contact_id "
2113                                         "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" "
2114                                         "MATCH 'number:");
2115                         if (0 <= temp_len)
2116                                 len += temp_len;
2117
2118                         temp_len = SAFE_SNPRINTF(query, query_size, len, mod_keyword);
2119                         if (0 <= temp_len)
2120                                 len += temp_len;
2121
2122                         temp_len = SAFE_SNPRINTF(query, query_size, len, "' ");
2123                         if (0 <= temp_len)
2124                                 len += temp_len;
2125
2126                         need_union = true;
2127                 }
2128
2129                 if (range & CONTACTS_SEARCH_RANGE_DATA) {
2130                         int tmp_len = 0;
2131                         char *tmp_keyword = NULL;
2132                         switch (lang) {
2133                         case CTSVC_LANG_JAPANESE:
2134                                 mod_keyword = search_hiragana;
2135                                 break;
2136                         default:
2137                                 /* replace '-' -> '_' because FTS does not support search '-' */
2138                                 tmp_len = strlen(search_keyword);
2139                                 tmp_keyword = calloc(tmp_len + 1, sizeof(char));
2140                                 if (NULL == tmp_keyword) {
2141                                         /* LCOV_EXCL_START */
2142                                         ERR("calloc() Fail");
2143                                         mod_keyword = search_keyword;
2144                                         break;
2145                                         /* LCOV_EXCL_STOP */
2146                                 }
2147                                 for (i = 0; i < tmp_len; i++)
2148                                         tmp_keyword[i] = ('-' == search_keyword[i]) ? '_' : search_keyword[i];
2149                                 tmp_keyword[i] = '\0';
2150                                 mod_keyword = tmp_keyword;
2151                                 break;
2152                         }
2153                         if (need_union) {
2154                                 temp_len = SAFE_SNPRINTF(query, query_size, len, " UNION ");
2155                                 if (0 <= temp_len)
2156                                         len += temp_len;
2157                         }
2158                         temp_len = SAFE_SNPRINTF(query, query_size, len, "SELECT contact_id "
2159                                         "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" "
2160                                         "MATCH 'data:");
2161                         if (0 <= temp_len)
2162                                 len += temp_len;
2163
2164                         temp_len = SAFE_SNPRINTF(query, query_size, len, mod_keyword);
2165                         if (0 <= temp_len)
2166                                 len += temp_len;
2167
2168                         temp_len = SAFE_SNPRINTF(query, query_size, len, "' ");
2169                         if (0 <= temp_len)
2170                                 len += temp_len;
2171
2172                         free(tmp_keyword);
2173                         need_union = true;
2174                 }
2175
2176                 if (range & CONTACTS_SEARCH_RANGE_NAME) {
2177                         int len_keyword = strlen(half_keyword);
2178                         int len_chosung = 0;
2179                         char *chosung = NULL;
2180                         char *korean_pattern = NULL;
2181                         char *search_chosung = NULL;
2182                         char *search_normalized = NULL;
2183                         switch (lang) {
2184                         case CTSVC_LANG_KOREAN: /* search with chosung */
2185                                 /*
2186                                  * If try to find '홍길동' by 'ㄱ동'
2187                                  * search by 'ㄱㄷ' in search_index table
2188                                  * intersect
2189                                  * search by '*ㄱ*동*' in name_lookup table
2190                                  */
2191                                 chosung = calloc(len_keyword * 5, sizeof(char));
2192                                 len_chosung = ctsvc_get_chosung(half_keyword, chosung, len_keyword * 5);
2193                                 mod_keyword = __ctsvc_db_make_search_keyword(0 < len_chosung ? chosung : half_keyword);
2194                                 break;
2195                         case CTSVC_LANG_JAPANESE:
2196                                 mod_keyword = search_hiragana;
2197                                 break;
2198                         default:
2199                                 if (CONTACTS_ERROR_NONE <= lang) { /* normalized string search */
2200                                         search_normalized = __ctsvc_db_make_search_keyword(normalized_name);
2201                                         mod_keyword = search_normalized;
2202                                 } else {  /* original keyword search */
2203                                         mod_keyword = search_keyword;
2204                                 }
2205                                 break;
2206                         }
2207                         if (need_union) {
2208                                 temp_len = SAFE_SNPRINTF(query, query_size, len, " UNION ");
2209                                 if (0 <= temp_len)
2210                                         len += temp_len;
2211                         }
2212                         temp_len = SAFE_SNPRINTF(query, query_size, len, "SELECT contact_id "
2213                                         "FROM "CTS_TABLE_SEARCH_INDEX" WHERE "CTS_TABLE_SEARCH_INDEX" "
2214                                         "MATCH 'name:");
2215                         if (0 <= temp_len)
2216                                 len += temp_len;
2217
2218                         temp_len = SAFE_SNPRINTF(query, query_size, len, mod_keyword);
2219                         if (0 <= temp_len)
2220                                 len += temp_len;
2221
2222                         temp_len = SAFE_SNPRINTF(query, query_size, len, "' ");
2223                         if (0 <= temp_len)
2224                                 len += temp_len;
2225
2226                         switch (lang) {
2227                         case CTSVC_LANG_KOREAN:
2228                                 korean_pattern = calloc(len_keyword *5, sizeof(char));
2229                                 ctsvc_get_korean_search_pattern(half_keyword, korean_pattern, len_keyword * 5);
2230                                 temp_len = SAFE_SNPRINTF(query, query_size, len, " INTERSECT SELECT "
2231                                                 "contact_id FROM "CTS_TABLE_NAME_LOOKUP" WHERE name GLOB '*");
2232                                 if (0 <= temp_len)
2233                                         len += temp_len;
2234
2235                                 temp_len = SAFE_SNPRINTF(query, query_size, len, korean_pattern);
2236                                 if (0 <= temp_len)
2237                                         len += temp_len;
2238
2239                                 temp_len = SAFE_SNPRINTF(query, query_size, len, "*' ");
2240                                 if (0 <= temp_len)
2241                                         len += temp_len;
2242
2243                                 free(chosung);
2244                                 free(korean_pattern);
2245                                 free(search_chosung);
2246                                 break;
2247                         case CTSVC_LANG_JAPANESE:
2248                                 break;
2249                         default:
2250                                 free(search_normalized);
2251                                 break;
2252                         }
2253
2254                         int j = 0;
2255                         char temp_str[len_keyword * 2 + 1];
2256                         for (i = 0; i < len_keyword; i++) {
2257                                 if (half_keyword[i] == '\'' || half_keyword[i] == '_' ||
2258                                                 half_keyword[i] == '%' || half_keyword[i] == '\\') {
2259                                         temp_str[j++] = CTSVC_DB_ESCAPE_CHAR;
2260                                 }
2261                                 temp_str[j++] = half_keyword[i];
2262                         }
2263                         temp_str[j] = '\0';
2264                         temp_len = SAFE_SNPRINTF(query, query_size, len, " UNION SELECT contact_id "
2265                                         "FROM "CTS_TABLE_NAME_LOOKUP" WHERE name LIKE '");
2266                         if (0 <= temp_len)
2267                                 len += temp_len;
2268
2269                         temp_len = SAFE_SNPRINTF(query, query_size, len, temp_str);
2270                         if (0 <= temp_len)
2271                                 len += temp_len;
2272
2273                         /* CTSVC_DB_ESCAPE_CHAR */
2274                         temp_len = SAFE_SNPRINTF(query, query_size, len, "%%' ESCAPE '\\' ");
2275                         if (0 <= temp_len)
2276                                 len += temp_len;
2277                 }
2278
2279                 free(normalized_name);
2280                 free(hiragana);
2281                 free(search_hiragana);
2282         }
2283
2284         temp_len = SAFE_SNPRINTF(query, query_size, len, ") ");
2285         if (0 <= temp_len)
2286                 len += temp_len;
2287
2288         free(search_keyword);
2289
2290         if (use_replaced_keyword)
2291                 free(half_keyword);
2292
2293         return len;
2294 }
2295
2296 static int __ctsvc_db_search_records_character_count(const char *keyword)
2297 {
2298         int char_len = 0;
2299         int str_len = strlen(keyword);
2300         int i;
2301         int count = 0;
2302         bool after_space = true;
2303
2304         for (i = 0; i < str_len; i += char_len) {
2305                 char_len = ctsvc_check_utf8(keyword[i]);
2306                 if (char_len == 1 && keyword[i] == ' ') {
2307                         after_space = true;
2308                         continue;
2309                 }
2310                 if (after_space == true) {
2311                         count++;
2312                         after_space = false;
2313                 }
2314         }
2315         return count;
2316 }
2317
2318 static int __ctsvc_db_search_records_append_sort(const char *view_uri,
2319                 const char *sortkey, const char *keyword, int len, char **query, int *query_size)
2320 {
2321         int i;
2322         int temp_len;
2323
2324         if (ctsvc_get_primary_sort() == CTSVC_SORT_KOREAN) {
2325                 contacts_name_sorting_order_e order;
2326                 const char *field = NULL;
2327                 char *tmp_keyword = NULL;
2328                 ctsvc_setting_get_name_sorting_order(&order);
2329
2330                 if (CONTACTS_NAME_SORTING_ORDER_FIRSTLAST == order)
2331                         field = "display_name";
2332                 else
2333                         field = "reverse_display_name";
2334
2335                 if (ctsvc_has_chosung(keyword)) {
2336                         if (__ctsvc_db_search_records_character_count(keyword) == 1) {
2337                                 int j;
2338                                 int m;
2339                                 int char_len = 0;
2340                                 int keyword_len = strlen(keyword);
2341                                 bool first = true;
2342                                 char temp_str[((250*keyword_len) + 30) * SORT_CHECK_LEN + 50];
2343                                 int temp_str_len = 0;
2344
2345                                 temp_str_len = snprintf(temp_str + temp_str_len,
2346                                                 sizeof(temp_str) - temp_str_len, " ORDER BY CASE ");
2347
2348                                 for (j = 1; j <= SORT_CHECK_LEN; j++) {
2349                                         temp_str_len += snprintf(temp_str + temp_str_len,
2350                                                         sizeof(temp_str) - temp_str_len, " WHEN ");
2351                                         for (i = 0, m = 0; i < keyword_len; i += char_len, m++) {
2352                                                 char temp[10] = {0};
2353                                                 int k = -1;
2354                                                 char_len = ctsvc_check_utf8(keyword[i]);
2355                                                 if (char_len <= 0) {
2356                                                         char_len = 1;
2357                                                         continue;
2358                                                 }
2359                                                 memcpy(temp, &keyword[i], char_len);
2360                                                 temp[char_len] = '\0';
2361
2362                                                 if (char_len == 1 && temp[0] == ' ')
2363                                                         continue;
2364                                                 if (first == false && i != 0) {
2365                                                         temp_str_len += snprintf(temp_str + temp_str_len,
2366                                                                         sizeof(temp_str)-temp_str_len, " AND ");
2367                                                 }
2368                                                 if (ctsvc_is_chosung(temp)) {
2369                                                         for (k = 0; k < 19; k++) {
2370                                                                 if (STRING_EQUAL == strcmp(hangul_syllable[k][0], temp))
2371                                                                         break;
2372                                                         }
2373                                                 }
2374
2375                                                 if (0 <= k && k <= 18) {
2376                                                         temp_str_len += snprintf(temp_str+temp_str_len,
2377                                                                         sizeof(temp_str)-temp_str_len,
2378                                                                         " ((substr(%s, %d, 1) BETWEEN '%s' AND '%s') OR (substr(%s, %d, 1) = '%s'))  ",
2379                                                                         field,
2380                                                                         j+m,
2381                                                                         hangul_syllable[k][1],
2382                                                                         hangul_syllable[k][2],
2383                                                                         field,
2384                                                                         j+m,
2385                                                                         temp);
2386                                                 } else {
2387                                                         temp_str_len += snprintf(temp_str+temp_str_len,
2388                                                                         sizeof(temp_str)-temp_str_len,
2389                                                                         " (substr(%s, %d, 1) = '%s')  ",
2390                                                                         field,
2391                                                                         j+m,
2392                                                                         temp);
2393                                                 }
2394                                                 if (first)
2395                                                         first = false;
2396                                         }
2397                                         temp_str_len += snprintf(temp_str + temp_str_len,
2398                                                         sizeof(temp_str) - temp_str_len, " THEN %d ", j);
2399                                 }
2400                                 temp_str_len = snprintf(temp_str + temp_str_len,
2401                                                 sizeof(temp_str) - temp_str_len, " ELSE %d END ", j);
2402
2403                                 temp_len = SAFE_SNPRINTF(query, query_size, len, temp_str);
2404                                 if (0 <= temp_len)
2405                                         len += temp_len;
2406                         } else {
2407                                 temp_len = SAFE_SNPRINTF(query, query_size, len, " ORDER BY ");
2408                                 if (0 <= temp_len) len += temp_len;
2409                                 temp_len = SAFE_SNPRINTF(query, query_size, len, sortkey);
2410                                 if (0 <= temp_len) len += temp_len;
2411                         }
2412                 } else {
2413                         tmp_keyword = __db_get_str_with_escape((char*)keyword, strlen(keyword), true);
2414                         if (NULL == tmp_keyword) {
2415                                 /* LCOV_EXCL_START */
2416                                 ERR("__db_get_str_with_escape() Fail");
2417                                 /* LCOV_EXCL_STOP */
2418                                 return CONTACTS_ERROR_OUT_OF_MEMORY;
2419                         }
2420
2421                         char temp_str[CTS_SQL_MIN_LEN + (strlen(field) + strlen(tmp_keyword)) * SORT_CHECK_LEN];
2422                         snprintf(temp_str, sizeof(temp_str),
2423                                         " ORDER BY "
2424                                         "       CASE "
2425                                         "               WHEN %s LIKE '%s%%' THEN 1 "
2426                                         "               WHEN %s LIKE '_%s%%' THEN 2 "
2427                                         "               WHEN %s LIKE '__%s%%' THEN 3 "
2428                                         "               WHEN %s LIKE '___%s%%' THEN 4 "
2429                                         "               WHEN %s LIKE '____%s%%' THEN 5 "
2430                                         "               WHEN %s LIKE '_____%s%%' THEN 6 "
2431                                         "               WHEN %s LIKE '______%s%%' THEN 7 "
2432                                         "               ELSE 8 "
2433                                         "       END ",
2434                                         field, tmp_keyword, field, tmp_keyword,
2435                                         field, tmp_keyword, field, tmp_keyword,
2436                                         field, tmp_keyword, field, tmp_keyword,
2437                                         field, tmp_keyword);
2438                         temp_len = SAFE_SNPRINTF(query, query_size, len, temp_str);
2439                         if (0 <= temp_len) len += temp_len;
2440                         free(tmp_keyword);
2441                 }
2442                 temp_len = SAFE_SNPRINTF(query, query_size, len, ", ");
2443                 if (0 <= temp_len) len += temp_len;
2444                 temp_len = SAFE_SNPRINTF(query, query_size, len, sortkey);
2445                 if (0 <= temp_len) len += temp_len;
2446         } else {
2447                 temp_len = SAFE_SNPRINTF(query, query_size, len, " ORDER BY ");
2448                 if (0 <= temp_len) len += temp_len;
2449                 temp_len = SAFE_SNPRINTF(query, query_size, len, sortkey);
2450                 if (0 <= temp_len) len += temp_len;
2451         }
2452         return len;
2453 }
2454
2455 static int __ctsvc_db_search_records_exec(const char *view_uri,
2456                 int enum_uri,
2457                 const property_info_s *properties,
2458                 int ids_count,
2459                 const char *projection,
2460                 const char *keyword,
2461                 int offset,
2462                 int limit,
2463                 int range,
2464                 bool is_snippet,
2465                 const char *start_match,
2466                 const char *end_match,
2467                 int token_number,
2468                 contacts_list_h *out_list)
2469 {
2470         char *query = NULL;
2471         char temp_query[CTS_SQL_MAX_LEN];
2472         int query_size;
2473         int temp_len = 0;
2474         const char *table;
2475         int len = 0;
2476         int ret;
2477         int i;
2478         int type;
2479         cts_stmt stmt = NULL;
2480         contacts_list_h list = NULL;
2481         ctsvc_record_type_e r_type;
2482         const char *sortkey = NULL;
2483
2484         ret = ctsvc_db_get_table_name(view_uri, &table);
2485         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "Invalid view uri (%s)", view_uri);
2486
2487         query_size = CTS_SQL_MAX_LEN;
2488         query = calloc(1, query_size);
2489         if (NULL == query) {
2490                 /* LCOV_EXCL_START */
2491                 ERR("calloc() Fail");
2492                 return CONTACTS_ERROR_OUT_OF_MEMORY;
2493                 /* LCOV_EXCL_STOP */
2494         }
2495
2496         len = 0;
2497
2498         if (STRING_EQUAL == strcmp(keyword, "+"))
2499                 range &= ~CONTACTS_SEARCH_RANGE_NUMBER;
2500
2501         if (0 == range) {
2502                 /* LCOV_EXCL_START */
2503                 ERR("No range");
2504                 contacts_list_create(&list);
2505                 *out_list = list;
2506                 free(query);
2507                 return CONTACTS_ERROR_NONE;
2508                 /* LCOV_EXCL_STOP */
2509         }
2510
2511         switch (enum_uri) {
2512         case CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT:
2513         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP:
2514         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED:
2515         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED:
2516
2517                 if (range & CONTACTS_SEARCH_RANGE_EMAIL) {
2518                         free(query);
2519                         return CONTACTS_ERROR_INVALID_PARAMETER;
2520                 }
2521
2522                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT ");
2523                 if (0 <= temp_len)
2524                         len += temp_len;
2525                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
2526                 if (0 <= temp_len)
2527                         len += temp_len;
2528                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM ");
2529                 if (0 <= temp_len)
2530                         len += temp_len;
2531                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2532                 if (0 <= temp_len)
2533                         len += temp_len;
2534                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " as temp_contacts");
2535                 if (0 <= temp_len)
2536                         len += temp_len;
2537                 break;
2538
2539         case CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER:
2540                 {
2541                         bool need_or = false;
2542                         if (range & CONTACTS_SEARCH_RANGE_DATA || range & CONTACTS_SEARCH_RANGE_EMAIL) {
2543                                 /* LCOV_EXCL_START */
2544                                 ERR("Invalid range");
2545                                 free(query);
2546                                 return CONTACTS_ERROR_INVALID_PARAMETER;
2547                                 /* LCOV_EXCL_STOP */
2548                         }
2549                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT ");
2550                         if (0 <= temp_len) len += temp_len;
2551                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
2552                         if (0 <= temp_len) len += temp_len;
2553                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM (SELECT * FROM ");
2554                         if (0 <= temp_len) len += temp_len;
2555                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2556                         if (0 <= temp_len) len += temp_len;
2557
2558                         if ((range & CONTACTS_SEARCH_RANGE_NUMBER) && ctsvc_is_phonenumber(keyword)) {
2559                                 char clean_num[strlen(keyword)+1+5];   /* for cc */
2560
2561                                 /*
2562                                  * Original number can include '-' or special character. So search by cleaned_number
2563                                  * If contact has 010 1234 5678 (normalized number is +cc 10 1234 5678),
2564                                  *      then the contact should be searched by keyword +cc 10 1234 5678
2565                                  */
2566                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE ");
2567                                 if (0 <= temp_len)
2568                                         len += temp_len;
2569
2570                                 ret = ctsvc_clean_number(keyword, clean_num, sizeof(clean_num), false);
2571                                 if (0 < ret) {
2572                                         const char *cc = ctsvc_get_network_cc(false);
2573
2574                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "(normalized_number LIKE '%%");
2575                                         if (0 <= temp_len)
2576                                                 len += temp_len;
2577
2578                                         if (cc && cc[0] == '7' && clean_num[0] == '8') {   /* Russia */
2579                                                 char normal_num[strlen(clean_num)+1+5];   /* for cc */
2580                                                 ret = ctsvc_normalize_number(clean_num, normal_num,
2581                                                                 sizeof(normal_num), false);
2582                                                 if (0 < ret)
2583                                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, normal_num);
2584                                                 else
2585                                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, clean_num);
2586                                         } else {
2587                                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, clean_num);
2588                                         }
2589                                         if (0 <= temp_len)
2590                                                 len += temp_len;
2591
2592                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%' OR cleaned_number LIKE '%%");
2593                                         if (0 <= temp_len)
2594                                                 len += temp_len;
2595
2596                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, clean_num);
2597                                         if (0 <= temp_len)
2598                                                 len += temp_len;
2599
2600                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%')");
2601                                         if (0 <= temp_len)
2602                                                 len += temp_len;
2603                                 } else {
2604                                         char *temp_keyword = __db_get_str_with_escape((char*)keyword,
2605                                                         strlen(keyword), true);
2606                                         if (NULL == temp_keyword) {
2607                                                 /* LCOV_EXCL_START */
2608                                                 ERR("__db_get_str_with_escape() Fail");
2609                                                 free(query);
2610                                                 return CONTACTS_ERROR_OUT_OF_MEMORY;
2611                                                 /* LCOV_EXCL_STOP */
2612                                         }
2613
2614                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "(SELECT * FROM ");
2615                                         if (0 <= temp_len)
2616                                                 len += temp_len;
2617
2618                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2619                                         if (0 <= temp_len)
2620                                                 len += temp_len;
2621
2622                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE number LIKE '%%");
2623                                         if (0 <= temp_len)
2624                                                 len += temp_len;
2625
2626                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_keyword);
2627                                         if (0 <= temp_len)
2628                                                 len += temp_len;
2629
2630                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%' ESCAPE '\\')");
2631                                         if (0 <= temp_len)
2632                                                 len += temp_len;
2633                                         free(temp_keyword);
2634                                 }
2635                                 need_or = true;
2636                         }
2637
2638                         if (range & CONTACTS_SEARCH_RANGE_NAME) {
2639                                 if (need_or)
2640                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " OR ");
2641                                 else
2642                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE ");
2643
2644                                 if (0 <= temp_len)
2645                                         len += temp_len;
2646
2647                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "person_id IN (SELECT "
2648                                                 "person_id FROM "CTS_TABLE_CONTACTS" WHERE deleted = 0 AND contact_id IN");
2649                                 if (0 <= temp_len)
2650                                         len += temp_len;
2651
2652                                 /*
2653                                  * search contact from search_index table by name and join the results
2654                                  * FTS can support to serach with multiple words
2655                                  * If contact display_name is 'abc def', then the contact should be searched by 'def'
2656                                  */
2657                                 temp_len = __db_append_search_query(keyword, &query, &query_size, len,
2658                                                 CONTACTS_SEARCH_RANGE_NAME);
2659                                 if (0 <= temp_len)
2660                                         len = temp_len;
2661
2662                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
2663                                 if (0 <= temp_len)
2664                                         len += temp_len;
2665                         }
2666
2667                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") as temp_contacts");
2668                         if (0 <= temp_len)
2669                                 len += temp_len;
2670                 }
2671                 break;
2672
2673         case CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL:
2674                 {
2675                         bool need_or = false;
2676                         if (range & CONTACTS_SEARCH_RANGE_NUMBER || range & CONTACTS_SEARCH_RANGE_DATA) {
2677                                 free(query);
2678                                 return CONTACTS_ERROR_INVALID_PARAMETER;
2679                         }
2680
2681                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT ");
2682                         if (0 <= temp_len)
2683                                 len += temp_len;
2684
2685                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
2686                         if (0 <= temp_len)
2687                                 len += temp_len;
2688
2689                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM (");
2690                         if (0 <= temp_len)
2691                                 len += temp_len;
2692
2693                         if (range & CONTACTS_SEARCH_RANGE_EMAIL) {
2694                                 /* search contact which has email address started with keyword */
2695                                 char *temp_keyword = __db_get_str_with_escape((char*)keyword,
2696                                                 strlen(keyword), true);
2697                                 if (NULL == temp_keyword) {
2698                                         /* LCOV_EXCL_START */
2699                                         ERR("__db_get_str_with_escape() Fail");
2700                                         free(query);
2701                                         return CONTACTS_ERROR_OUT_OF_MEMORY;
2702                                         /* LCOV_EXCL_STOP */
2703                                 }
2704                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT * FROM ");
2705                                 if (0 <= temp_len)
2706                                         len += temp_len;
2707
2708                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2709                                 if (0 <= temp_len)
2710                                         len += temp_len;
2711
2712                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE (email LIKE '");
2713                                 if (0 <= temp_len)
2714                                         len += temp_len;
2715
2716                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_keyword);
2717                                 if (0 <= temp_len)
2718                                         len += temp_len;
2719
2720                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "%%' ESCAPE '\\')");
2721                                 if (0 <= temp_len)
2722                                         len += temp_len;
2723                                 free(temp_keyword);
2724                                 need_or = true;
2725                         }
2726
2727                         if (range & CONTACTS_SEARCH_RANGE_NAME) {
2728                                 if (need_or) {
2729                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " OR ");
2730                                         if (0 <= temp_len)
2731                                                 len += temp_len;
2732
2733                                 } else {
2734                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT * FROM ");
2735                                         if (0 <= temp_len)
2736                                                 len += temp_len;
2737                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2738                                         if (0 <= temp_len)
2739                                                 len += temp_len;
2740                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE ");
2741                                         if (0 <= temp_len)
2742                                                 len += temp_len;
2743                                 }
2744
2745                                 snprintf(temp_query, sizeof(temp_query), "person_id IN (SELECT person_id "
2746                                                 "FROM "CTS_TABLE_CONTACTS" WHERE deleted = 0 AND contact_id ");
2747                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_query);
2748                                 if (0 <= temp_len)
2749                                         len += temp_len;
2750                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " IN ");
2751                                 if (0 <= temp_len)
2752                                         len += temp_len;
2753                                 temp_len = __db_append_search_query(keyword, &query, &query_size, len,
2754                                                 CONTACTS_SEARCH_RANGE_NAME);
2755                                 if (0 <= temp_len)
2756                                         len = temp_len;
2757                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
2758                                 if (0 <= temp_len)
2759                                         len += temp_len;
2760                         }
2761
2762                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
2763                         if (0 <= temp_len)
2764                                 len += temp_len;
2765                 }
2766                 break;
2767
2768         case CTSVC_ENUM_URI_PERSON:
2769                 if (range & CONTACTS_SEARCH_RANGE_EMAIL) {
2770                         free(query);
2771                         return CONTACTS_ERROR_INVALID_PARAMETER;
2772                 }
2773                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT ");
2774                 if (0 <= temp_len)
2775                         len += temp_len;
2776
2777                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
2778                 if (0 <= temp_len)
2779                         len += temp_len;
2780
2781                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM ");
2782                 if (0 <= temp_len)
2783                         len += temp_len;
2784
2785                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2786                 if (0 <= temp_len)
2787                         len += temp_len;
2788         }
2789
2790         if (true == is_snippet) {
2791                 bool need_union = false;
2792                 char cond_data[CTS_SQL_MAX_LEN] = {0};
2793                 char clean_number[SAFE_STRLEN(keyword) + 1];
2794                 ctsvc_clean_number(keyword, clean_number, sizeof(clean_number), false);
2795                 temp_len = SAFE_SNPRINTF(&query, &query_size, len,
2796                         ", (SELECT contact_id temp_contact_id, "
2797                                 "datatype, _priority, _data FROM (");
2798                 if (0 <= temp_len)
2799                         len += temp_len;
2800
2801                 if (range & CONTACTS_SEARCH_RANGE_NAME) {
2802                         snprintf(cond_data, sizeof(cond_data), "SELECT contact_id, "
2803                                         "datatype, %d as _priority, 'a' as _data FROM "CTS_TABLE_DATA" "
2804                                         "WHERE contact_id IN (", CTSVC_SNIPPET_PRIO_NAME);
2805                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, cond_data);
2806                         if (0 <= temp_len)
2807                                 len += temp_len;
2808
2809                         char *query_name = _get_search_query_for_name(keyword);
2810                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, query_name);
2811                         if (0 <= temp_len)
2812                                 len += temp_len;
2813                         free(query_name);
2814
2815                         /* datatype=1 NAME */
2816                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") AND datatype=1 AND is_my_profile=0 ");
2817                         if (0 <= temp_len)
2818                                 len += temp_len;
2819
2820                         need_union = true;
2821                 }
2822                 if (range & CONTACTS_SEARCH_RANGE_NUMBER) {
2823                         if (true == need_union) {
2824                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " UNION ");
2825                                 if (0 <= temp_len)
2826                                         len += temp_len;
2827                         }
2828                         snprintf(cond_data, sizeof(cond_data), "SELECT contact_id, "
2829                                         "datatype, %d as _priority, data3 as _data FROM "CTS_TABLE_DATA" "
2830                                         "WHERE contact_id IN (", CTSVC_SNIPPET_PRIO_NUMBER);
2831                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, cond_data);
2832                         if (0 <= temp_len)
2833                                 len += temp_len;
2834
2835                         char *query_number = _get_search_query_for_number(keyword);
2836                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, query_number);
2837                         if (0 <= temp_len)
2838                                 len += temp_len;
2839                         free(query_number);
2840
2841                         /* datatype=8 NUMBER */
2842                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") AND datatype=8 AND is_my_profile=0 ");
2843                         if (0 <= temp_len)
2844                                 len += temp_len;
2845
2846                         need_union = true;
2847                 }
2848                 if (range & CONTACTS_SEARCH_RANGE_DATA) {
2849                         if (true == need_union) {
2850                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " UNION ");
2851                                 if (0 <= temp_len)
2852                                         len += temp_len;
2853                         }
2854                         snprintf(cond_data, sizeof(cond_data), "SELECT contact_id, datatype, "
2855                                         "(CASE "
2856                                         "WHEN datatype=%d THEN %d " /* POSTAL       */
2857                                         "WHEN datatype=%d THEN %d " /* COMPANY      */
2858                                         "WHEN datatype=%d THEN %d " /* NICKNAME     */
2859                                         "WHEN datatype=%d THEN %d " /* EMAIL        */
2860                                         "WHEN datatype=%d THEN %d " /* NOTE         */
2861                                         "END) as _priority, "
2862                                         "(CASE "
2863                                         "WHEN datatype=%d THEN (COALESCE(data5||' ','')||"
2864                                         "COALESCE(data6||' ','')||COALESCE(data7||' ','')||"
2865                                         "COALESCE(data8||' ','')||COALESCE(data9,'')) " /* POSTAL       */
2866                                         "WHEN datatype=%d THEN (COALESCE(data3||' ','')||"
2867                                         "COALESCE(data4||' ','')||COALESCE(data5||' ','')||"
2868                                         "COALESCE(data6||' ','')||COALESCE(data7||' ','')||"
2869                                         "COALESCE(data8||' ','')||COALESCE(data9||' ','')||"
2870                                         "COALESCE(data10||' ','')||COALESCE(data11||' ','')) " /* COMPANY      */
2871                                         "WHEN datatype=%d THEN data3 " /* NICKNAME     */
2872                                         "WHEN datatype=%d THEN data3 " /* EMAIL        */
2873                                         "WHEN datatype=%d THEN data3 " /* NOTE         */
2874                                         "END) as _data FROM "CTS_TABLE_DATA" WHERE is_my_profile=0 AND contact_id IN (",
2875                                 CONTACTS_DATA_TYPE_POSTAL, CTSVC_SNIPPET_PRIO_POSTAL,
2876                                 CONTACTS_DATA_TYPE_COMPANY, CTSVC_SNIPPET_PRIO_COMPANY,
2877                                 CONTACTS_DATA_TYPE_NICKNAME, CTSVC_SNIPPET_PRIO_NICKNAME,
2878                                 CONTACTS_DATA_TYPE_EMAIL, CTSVC_SNIPPET_PRIO_EMAIL,
2879                                 CONTACTS_DATA_TYPE_NOTE, CTSVC_SNIPPET_PRIO_NOTE,
2880                                 CONTACTS_DATA_TYPE_POSTAL,
2881                                 CONTACTS_DATA_TYPE_COMPANY,
2882                                 CONTACTS_DATA_TYPE_NICKNAME,
2883                                 CONTACTS_DATA_TYPE_EMAIL,
2884                                 CONTACTS_DATA_TYPE_NOTE);
2885                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, cond_data);
2886                         if (0 <= temp_len)
2887                                 len += temp_len;
2888
2889                         char *query_data = _get_search_query_for_data(keyword);
2890                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, query_data);
2891                         if (0 <= temp_len)
2892                                 len += temp_len;
2893                         free(query_data);
2894
2895                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") AND _data IS NOT NULL "
2896                                         "AND _data LIKE ('%%' || '");
2897                         if (0 <= temp_len)
2898                                 len += temp_len;
2899                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, keyword);
2900                         if (0 <= temp_len)
2901                                 len += temp_len;
2902                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "' || '%%') ");
2903                         if (0 <= temp_len)
2904                                 len += temp_len;
2905                 }
2906                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ")) as temp_data ON "
2907                                 "temp_data.temp_contact_id = temp_contacts.contact_id ");
2908                 if (0 <= temp_len)
2909                         len += temp_len;
2910         }
2911
2912         switch (enum_uri) {
2913         case CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT:
2914         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP:
2915         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED:
2916         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED:
2917                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE temp_contacts.contact_id IN ");
2918                 if (0 <= temp_len)
2919                         len += temp_len;
2920                 temp_len = __db_append_search_query(keyword, &query, &query_size, len, range);
2921                 if (0 <= temp_len)
2922                         len = temp_len;
2923                 break;
2924
2925         case CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER:
2926                 break;
2927         case CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL:
2928                 break;
2929         case CTSVC_ENUM_URI_PERSON:
2930                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ", (SELECT contact_id, person_id "
2931                                 "person_id_in_contact, addressbook_id FROM "CTS_TABLE_CONTACTS" "
2932                                 "WHERE deleted = 0 AND contact_id IN ");
2933                 if (0 <= temp_len)
2934                         len += temp_len;
2935
2936                 temp_len = __db_append_search_query(keyword, &query, &query_size, len, range);
2937                 if (0 <= temp_len)
2938                         len = temp_len;
2939
2940                 temp_len = SAFE_SNPRINTF(&query, &query_size, len,
2941                                 "GROUP BY person_id_in_contact) temp_contacts ON ");
2942                 if (0 <= temp_len)
2943                         len += temp_len;
2944
2945                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
2946                 if (0 <= temp_len)
2947                         len += temp_len;
2948
2949                 temp_len = SAFE_SNPRINTF(&query, &query_size, len,
2950                                 ".person_id = temp_contacts.person_id_in_contact");
2951                 if (0 <= temp_len)
2952                         len += temp_len;
2953                 break;
2954         }
2955
2956         if (__ctsvc_db_view_has_display_name(view_uri, properties, ids_count))
2957                 sortkey = ctsvc_get_sort_column();
2958
2959         if (true == is_snippet) {
2960                 switch (enum_uri) {
2961                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT:
2962                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP:
2963                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED:
2964                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED:
2965                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER:
2966                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL:
2967                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " GROUP BY temp_data.temp_contact_id");
2968                         break;
2969                 case CTSVC_ENUM_URI_PERSON:
2970                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " GROUP BY person_id");
2971                         break;
2972                 }
2973                 if (0 <= temp_len)
2974                         len += temp_len;
2975         }
2976
2977         if (sortkey) {
2978                 len = __ctsvc_db_search_records_append_sort(view_uri, sortkey, keyword, len,
2979                                 &query, &query_size);
2980         } else if (STRING_EQUAL == strcmp(view_uri, CTSVC_VIEW_URI_GROUP)) {
2981                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY group_prio");
2982                 if (0 <= temp_len) len += temp_len;
2983         }
2984
2985         do {
2986                 if (0 == limit)
2987                         break;
2988
2989                 snprintf(temp_query, sizeof(temp_query), " LIMIT %d", limit);
2990                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_query);
2991                 if (0 <= temp_len)
2992                         len += temp_len;
2993
2994                 if (offset <= 0)
2995                         break;
2996
2997                 snprintf(temp_query, sizeof(temp_query), " OFFSET %d", offset);
2998                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_query);
2999                 if (0 <= temp_len)
3000                         len += temp_len;
3001         } while (0);
3002
3003         ret = ctsvc_query_prepare(query, &stmt);
3004         free(query);
3005         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
3006
3007         r_type = ctsvc_view_get_record_type(view_uri);
3008
3009         contacts_list_create(&list);
3010         while ((ret = ctsvc_stmt_step(stmt))) {
3011                 contacts_record_h record;
3012                 if (1 != ret) {
3013                         /* LCOV_EXCL_START */
3014                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
3015                         ctsvc_stmt_finalize(stmt);
3016                         contacts_list_destroy(list, true);
3017                         return ret;
3018                         /* LCOV_EXCL_STOP */
3019                 }
3020
3021                 if (r_type == CTSVC_RECORD_PERSON) {
3022                         unsigned int *project = malloc(sizeof(unsigned int)*ids_count);
3023                         for (i = 0; i < ids_count; i++)
3024                                 project[i] = properties[i].property_id;
3025
3026                         int ret = ctsvc_db_person_create_record_from_stmt_with_projection(stmt, project,
3027                                         ids_count, is_snippet, keyword, start_match, end_match, token_number, &record);
3028
3029                         free(project);
3030
3031                         if (CONTACTS_ERROR_NONE != ret)
3032                                 /* LCOV_EXCL_START */
3033                                 ERR("make record Fail(%d)", ret);
3034                         /* LCOV_EXCL_STOP */
3035                 } else {
3036                         contacts_record_create(view_uri, &record);
3037
3038                         for (i = 0; i < ids_count; i++) {
3039                                 type = (properties[i].property_id & CTSVC_VIEW_DATA_TYPE_MASK);
3040                                 char *temp = NULL;
3041                                 switch (type) {
3042                                 case CTSVC_VIEW_DATA_TYPE_INT:
3043                                         ctsvc_record_set_int(record, properties[i].property_id,
3044                                                         ctsvc_stmt_get_int(stmt, i));
3045                                         break;
3046                                 case CTSVC_VIEW_DATA_TYPE_STR:
3047                                         temp = ctsvc_stmt_get_text(stmt, i);
3048                                         if (CTSVC_PROPERTY_PERSON_SNIPPET_STRING == properties[i].property_id) {
3049                                                 temp = ctsvc_utils_get_modified_str(temp, is_snippet, keyword,
3050                                                                 start_match, end_match, token_number);
3051                                         }
3052                                         ctsvc_record_set_str(record, properties[i].property_id, temp);
3053                                         break;
3054                                 case CTSVC_VIEW_DATA_TYPE_BOOL:
3055                                         ctsvc_record_set_bool(record, properties[i].property_id,
3056                                                         (ctsvc_stmt_get_int(stmt, i) ? true : false));
3057                                         break;
3058                                 case CTSVC_VIEW_DATA_TYPE_LLI:
3059                                         ctsvc_record_set_lli(record, properties[i].property_id,
3060                                                         ctsvc_stmt_get_int64(stmt, i));
3061                                         break;
3062                                 case CTSVC_VIEW_DATA_TYPE_DOUBLE:
3063                                         ctsvc_record_set_double(record, properties[i].property_id,
3064                                                         ctsvc_stmt_get_dbl(stmt, i));
3065                                         break;
3066                                 default:
3067                                         /* LCOV_EXCL_START */
3068                                         ERR("unknown type (%d)", type);
3069                                         break;
3070                                         /* LCOV_EXCL_STOP */
3071                                 }
3072                         }
3073                 }
3074
3075                 ctsvc_list_prepend(list, record);
3076         }
3077
3078         ctsvc_stmt_finalize(stmt);
3079         ctsvc_list_reverse(list);
3080         *out_list = list;
3081         return CONTACTS_ERROR_NONE;
3082 }
3083
3084 static int __ctsvc_db_search_records(const char *view_uri,
3085                 const char *keyword,
3086                 int offset,
3087                 int limit,
3088                 bool is_snippet,
3089                 const char *start_match,
3090                 const char *end_match,
3091                 int token_number,
3092                 contacts_list_h *out_list)
3093 {
3094         int ret;
3095         unsigned int count;
3096         char *projection;
3097         const property_info_s *p;
3098         bool can_keyword_search = false;
3099         int enum_uri = 0;
3100         int range = CONTACTS_SEARCH_RANGE_NAME | CONTACTS_SEARCH_RANGE_NUMBER | CONTACTS_SEARCH_RANGE_DATA;
3101
3102         RETV_IF(NULL == keyword, CONTACTS_ERROR_INVALID_PARAMETER);
3103
3104         can_keyword_search = __ctsvc_db_view_can_keyword_search(view_uri, &enum_uri);
3105         RETVM_IF(false == can_keyword_search, CONTACTS_ERROR_INVALID_PARAMETER,
3106                         "can not keyword search");
3107
3108         p = ctsvc_view_get_all_property_infos(view_uri, &count);
3109         ret = __db_create_projection(view_uri, p, count, NULL, 0, &projection, is_snippet);
3110         if (CONTACTS_ERROR_NONE != ret) {
3111                 /* LCOV_EXCL_START */
3112                 ERR("__db_create_projection() Fail(%d)", ret);
3113                 return ret;
3114                 /* LCOV_EXCL_STOP */
3115         }
3116
3117         ret = __ctsvc_db_search_records_exec(view_uri, enum_uri, p, count, projection,
3118                         keyword, offset, limit, range, is_snippet, start_match, end_match, token_number, out_list);
3119         free(projection);
3120
3121         return ret;
3122 }
3123
3124 static int __ctsvc_db_search_records_with_range(const char *view_uri,
3125                 const char *keyword,
3126                 int offset,
3127                 int limit,
3128                 int range,
3129                 bool is_snippet,
3130                 const char *start_match,
3131                 const char *end_match,
3132                 int token_number,
3133                 contacts_list_h *out_list)
3134 {
3135         int ret;
3136         unsigned int count;
3137         char *projection;
3138         const property_info_s *p;
3139         bool can_keyword_search = false;
3140         int eunm_uri = 0;
3141
3142         RETV_IF(NULL == keyword, CONTACTS_ERROR_INVALID_PARAMETER);
3143
3144         can_keyword_search = __ctsvc_db_view_can_keyword_search(view_uri, &eunm_uri);
3145         RETV_IF(false == can_keyword_search, CONTACTS_ERROR_INVALID_PARAMETER);
3146
3147         p = ctsvc_view_get_all_property_infos(view_uri, &count);
3148         ret = __db_create_projection(view_uri, p, count, NULL, 0, &projection, is_snippet);
3149         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "__db_create_projection() Fail(%d)", ret);
3150
3151         ret = __ctsvc_db_search_records_exec(view_uri, eunm_uri, p, count, projection,
3152                         keyword, offset, limit, range, is_snippet, start_match, end_match, token_number, out_list);
3153         free(projection);
3154
3155         return ret;
3156 }
3157
3158 static inline int __ctsvc_db_search_records_with_query_exec(ctsvc_query_s *s_query,
3159                 int enum_uri,
3160                 const char *projection,
3161                 const char *condition,
3162                 GSList *bind,
3163                 const char *keyword,
3164                 int offset,
3165                 int limit,
3166                 bool is_snippet,
3167                 const char *start_match,
3168                 const char *end_match,
3169                 int token_number,
3170                 contacts_list_h *out_list)
3171 {
3172         char *query = NULL;
3173         int query_size;
3174         int temp_len;
3175         int len;
3176         int ret;
3177         int i;
3178         int type;
3179         bool person_contact_query = false;
3180         GSList *cursor;
3181         cts_stmt stmt = NULL;
3182         contacts_list_h list = NULL;
3183         const char *table;
3184         const char *sortkey = NULL;
3185         int range = CONTACTS_SEARCH_RANGE_NAME | CONTACTS_SEARCH_RANGE_NUMBER | CONTACTS_SEARCH_RANGE_DATA;
3186
3187         RETV_IF(NULL == projection || '\0' == *projection, CONTACTS_ERROR_INVALID_PARAMETER);
3188
3189         ret = ctsvc_db_get_table_name(s_query->view_uri, &table);
3190         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "Invalid view uri(%s)", s_query->view_uri);
3191
3192         query_size = CTS_SQL_MAX_LEN;
3193         query = calloc(1, query_size);
3194         if (NULL == query) {
3195                 /* LCOV_EXCL_START */
3196                 ERR("calloc() Fail");
3197                 return CONTACTS_ERROR_OUT_OF_MEMORY;
3198                 /* LCOV_EXCL_STOP */
3199         }
3200
3201         len = 0;
3202
3203         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "SELECT ");
3204         if (0 <= temp_len)
3205                 len += temp_len;
3206
3207         if (s_query->distinct) {
3208                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, "DISTINCT ");
3209                 if (0 <= temp_len)
3210                         len += temp_len;
3211         }
3212         temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
3213         if (0 <= temp_len)
3214                 len += temp_len;
3215         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM ");
3216         if (0 <= temp_len)
3217                 len += temp_len;
3218         temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
3219         if (0 <= temp_len)
3220                 len += temp_len;
3221
3222         switch (enum_uri) {
3223         case CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT:
3224         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP:
3225         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED:
3226         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED:
3227         case CTSVC_ENUM_URI_PERSON:
3228                 break;
3229
3230         case CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER:
3231         case CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL:
3232         default:
3233                 free(query);
3234                 return CONTACTS_ERROR_INVALID_PARAMETER;
3235         }
3236
3237         if (true == is_snippet) {
3238                 bool need_union = false;
3239                 char cond_data[CTS_SQL_MAX_LEN] = {0};
3240                 char clean_number[SAFE_STRLEN(keyword) + 1];
3241                 ctsvc_clean_number(keyword, clean_number, sizeof(clean_number), false);
3242                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ", (SELECT contact_id temp_contact_id, "
3243                                 "datatype, _priority, _data FROM (");
3244                 if (0 <= temp_len)
3245                         len += temp_len;
3246
3247                 if (range & CONTACTS_SEARCH_RANGE_NAME) {
3248                         snprintf(cond_data, sizeof(cond_data), "SELECT contact_id, "
3249                                         "datatype, %d as _priority, 'a' as _data FROM "CTS_TABLE_DATA" "
3250                                         "WHERE contact_id IN (", CTSVC_SNIPPET_PRIO_NAME);
3251                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, cond_data);
3252                         if (0 <= temp_len)
3253                                 len += temp_len;
3254
3255                         char *query_name = _get_search_query_for_name(keyword);
3256                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, query_name);
3257                         if (0 <= temp_len)
3258                                 len += temp_len;
3259                         free(query_name);
3260
3261                         /* datatype=1 NAME */
3262                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") AND datatype=1 ");
3263                         if (0 <= temp_len)
3264                                 len += temp_len;
3265
3266                         need_union = true;
3267                 }
3268                 if (range & CONTACTS_SEARCH_RANGE_NUMBER) {
3269                         if (true == need_union) {
3270                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " UNION ");
3271                                 if (0 <= temp_len)
3272                                         len += temp_len;
3273                         }
3274                         snprintf(cond_data, sizeof(cond_data), "SELECT contact_id, "
3275                                         "datatype, %d as _priority, data3 as _data FROM "CTS_TABLE_DATA" "
3276                                         "WHERE contact_id IN (", CTSVC_SNIPPET_PRIO_NUMBER);
3277                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, cond_data);
3278                         if (0 <= temp_len)
3279                                 len += temp_len;
3280
3281                         char *query_number = _get_search_query_for_number(keyword);
3282                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, query_number);
3283                         if (0 <= temp_len)
3284                                 len += temp_len;
3285                         free(query_number);
3286
3287                         /* datatype=8 NUMBER */
3288                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") AND datatype=8 ");
3289                         if (0 <= temp_len)
3290                                 len += temp_len;
3291
3292                         need_union = true;
3293                 }
3294                 if (range & CONTACTS_SEARCH_RANGE_DATA) {
3295                         if (true == need_union) {
3296                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " UNION ");
3297                                 if (0 <= temp_len)
3298                                         len += temp_len;
3299                         }
3300                         snprintf(cond_data, sizeof(cond_data), "SELECT contact_id, datatype, "
3301                                         "(CASE "
3302                                         "WHEN datatype=%d THEN %d " /* POSTAL       */
3303                                         "WHEN datatype=%d THEN %d " /* COMPANY      */
3304                                         "WHEN datatype=%d THEN %d " /* NICKNAME     */
3305                                         "WHEN datatype=%d THEN %d " /* EMAIL        */
3306                                         "WHEN datatype=%d THEN %d " /* NOTE         */
3307                                         "END) as _priority, "
3308                                         "(CASE "
3309                                         "WHEN datatype=%d THEN (COALESCE(data5||' ','')||"
3310                                         "COALESCE(data6||' ','')||COALESCE(data7||' ','')||"
3311                                         "COALESCE(data8||' ','')||COALESCE(data9,'')) " /* POSTAL       */
3312                                         "WHEN datatype=%d THEN (COALESCE(data3||' ','')||"
3313                                         "COALESCE(data4||' ','')||COALESCE(data5||' ','')||"
3314                                         "COALESCE(data6||' ','')||COALESCE(data7||' ','')||"
3315                                         "COALESCE(data8||' ','')||COALESCE(data9||' ','')||"
3316                                         "COALESCE(data10||' ','')||COALESCE(data11||' ','')) " /* COMPANY      */
3317                                         "WHEN datatype=%d THEN data3 " /* NICKNAME     */
3318                                         "WHEN datatype=%d THEN data3 " /* EMAIL        */
3319                                         "WHEN datatype=%d THEN data3 " /* NOTE         */
3320                                         "END) as _data FROM "CTS_TABLE_DATA" WHERE contact_id IN (",
3321                                 CONTACTS_DATA_TYPE_POSTAL, CTSVC_SNIPPET_PRIO_POSTAL,
3322                                 CONTACTS_DATA_TYPE_COMPANY, CTSVC_SNIPPET_PRIO_COMPANY,
3323                                 CONTACTS_DATA_TYPE_NICKNAME, CTSVC_SNIPPET_PRIO_NICKNAME,
3324                                 CONTACTS_DATA_TYPE_EMAIL, CTSVC_SNIPPET_PRIO_EMAIL,
3325                                 CONTACTS_DATA_TYPE_NOTE, CTSVC_SNIPPET_PRIO_NOTE,
3326                                 CONTACTS_DATA_TYPE_POSTAL,
3327                                 CONTACTS_DATA_TYPE_COMPANY,
3328                                 CONTACTS_DATA_TYPE_NICKNAME,
3329                                 CONTACTS_DATA_TYPE_EMAIL,
3330                                 CONTACTS_DATA_TYPE_NOTE);
3331                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, cond_data);
3332                         if (0 <= temp_len)
3333                                 len += temp_len;
3334
3335                         char *query_data = _get_search_query_for_data(keyword);
3336                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, query_data);
3337                         if (0 <= temp_len)
3338                                 len += temp_len;
3339                         free(query_data);
3340
3341                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") AND _data IS NOT NULL "
3342                                         "AND _data LIKE ('%%' || '");
3343                         if (0 <= temp_len)
3344                                 len += temp_len;
3345                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, keyword);
3346                         if (0 <= temp_len)
3347                                 len += temp_len;
3348                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, "' || '%%') ");
3349                         if (0 <= temp_len)
3350                                 len += temp_len;
3351                 }
3352                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ")) as temp_data ON "
3353                                 "temp_data.temp_contact_id = contact_id ");
3354                 if (0 <= temp_len)
3355                         len += temp_len;
3356         }
3357
3358         switch (enum_uri) {
3359         case CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT:
3360         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP:
3361         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED:
3362         case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED:
3363                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE person_id IN (SELECT "
3364                                 "person_id FROM "CTS_TABLE_CONTACTS" WHERE deleted = 0 AND contact_id IN ");
3365                 if (0 <= temp_len)
3366                         len += temp_len;
3367
3368                 person_contact_query = true;
3369                 break;
3370
3371         case CTSVC_ENUM_URI_PERSON:
3372                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ", (SELECT contact_id, "
3373                                 "person_id person_id_in_contact, addressbook_id FROM "CTS_TABLE_CONTACTS" "
3374                                 "WHERE deleted = 0) temp_contacts ON ");
3375                 if (0 <= temp_len)
3376                         len += temp_len;
3377
3378                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
3379                 if (0 <= temp_len)
3380                         len += temp_len;
3381
3382                 temp_len = SAFE_SNPRINTF(&query, &query_size, len,
3383                                 ".person_id = temp_contacts.person_id_in_contact WHERE temp_contacts.contact_id IN ");
3384                 if (0 <= temp_len)
3385                         len += temp_len;
3386                 break;
3387
3388         case CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER:
3389         case CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL:
3390         default:
3391                 return CONTACTS_ERROR_INVALID_PARAMETER;
3392         }
3393
3394         temp_len = __db_append_search_query(keyword, &query, &query_size, len, range);
3395         if (0 <= temp_len)
3396                 len = temp_len;
3397
3398         if (person_contact_query) {
3399                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
3400                 if (0 <= temp_len)
3401                         len += temp_len;
3402         }
3403
3404         if (condition && *condition) {
3405                 if (person_contact_query) {
3406                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " AND contact_id IN "
3407                                         "(SELECT contact_id FROM ");
3408                         if (0 <= temp_len)
3409                                 len += temp_len;
3410                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, table);
3411                         if (0 <= temp_len)
3412                                 len += temp_len;
3413                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE ");
3414                         if (0 <= temp_len)
3415                                 len += temp_len;
3416                 } else {
3417                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " AND (");
3418                         if (0 <= temp_len)
3419                                 len += temp_len;
3420                 }
3421                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, condition);
3422                 if (0 <= temp_len)
3423                         len += temp_len;
3424                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ")");
3425                 if (0 <= temp_len)
3426                         len += temp_len;
3427         }
3428
3429         if (true == is_snippet) {
3430                 switch (enum_uri) {
3431                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_CONTACT:
3432                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP:
3433                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_ASSIGNED:
3434                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_GROUP_NOT_ASSIGNED:
3435                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_NUMBER:
3436                 case CTSVC_ENUM_URI_READ_ONLY_PERSON_EMAIL:
3437                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " GROUP BY temp_data.temp_contact_id");
3438                         break;
3439                 case CTSVC_ENUM_URI_PERSON:
3440                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " GROUP BY person_id");
3441                         break;
3442                 }
3443                 if (0 <= temp_len)
3444                         len += temp_len;
3445         }
3446
3447         if (__ctsvc_db_view_has_display_name(s_query->view_uri, s_query->properties, s_query->property_count))
3448                 sortkey = ctsvc_get_sort_column();
3449
3450         if (s_query->sort_property_id) {
3451                 const char *field_name;
3452
3453                 switch (s_query->sort_property_id) {
3454                 case CTSVC_PROPERTY_PERSON_DISPLAY_NAME:
3455                 case CTSVC_PROPERTY_CONTACT_DISPLAY_NAME:
3456                         if (sortkey) {
3457                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
3458                                 if (0 <= temp_len)
3459                                         len += temp_len;
3460                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, sortkey);
3461                                 if (0 <= temp_len)
3462                                         len += temp_len;
3463                                 if (false == s_query->sort_asc) {
3464                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " DESC ");
3465                                         if (0 <= temp_len)
3466                                                 len += temp_len;
3467                                 }
3468                         }
3469                         break;
3470                 default:
3471                         field_name = __ctsvc_db_get_property_field_name(s_query->properties,
3472                                         s_query->property_count, QUERY_SORTKEY, s_query->sort_property_id);
3473                         if (field_name) {
3474                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
3475                                 if (0 <= temp_len)
3476                                         len += temp_len;
3477                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, field_name);
3478                                 if (0 <= temp_len)
3479                                         len += temp_len;
3480                                 if (false == s_query->sort_asc) {
3481                                         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " DESC ");
3482                                         if (0 <= temp_len)
3483                                                 len += temp_len;
3484                                 }
3485                         } else if (sortkey) {
3486                                 len += snprintf(query+len, sizeof(query)-len, " ORDER BY %s", sortkey);
3487                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY ");
3488                                 if (0 <= temp_len)
3489                                         len += temp_len;
3490                                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, sortkey);
3491                                 if (0 <= temp_len)
3492                                         len += temp_len;
3493                         }
3494                         break;
3495                 }
3496         } else if (sortkey) {
3497                 len = __ctsvc_db_search_records_append_sort(s_query->view_uri, sortkey, keyword,
3498                                 len, &query, &query_size);
3499         } else if (STRING_EQUAL == strcmp(s_query->view_uri, CTSVC_VIEW_URI_GROUP)) {
3500                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " ORDER BY group_prio");
3501                 if (0 <= temp_len)
3502                         len += temp_len;
3503         }
3504
3505         do {
3506                 if (0 == limit)
3507                         break;
3508
3509                 char temp_str[20] = {0};
3510                 snprintf(temp_str, sizeof(temp_str), " LIMIT %d", limit);
3511                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_str);
3512                 if (0 <= temp_len)
3513                         len += temp_len;
3514
3515                 if (offset <= 0)
3516                         break;
3517
3518                 snprintf(temp_str, sizeof(temp_str), " OFFSET %d", offset);
3519                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, temp_str);
3520                 if (0 <= temp_len)
3521                         len += temp_len;
3522         } while (0);
3523
3524         ret = ctsvc_query_prepare(query, &stmt);
3525         if (NULL == stmt) {
3526                 /* LCOV_EXCL_START */
3527                 ERR("ctsvc_query_prepare() Fail(%d)[%s]", ret, query);
3528                 free(query);
3529                 return ret;
3530                 /* LCOV_EXCL_STOP */
3531         }
3532         free(query);
3533
3534         i = 1;
3535         len = g_slist_length(bind);
3536         for (cursor = bind; cursor; cursor = cursor->next, i++)
3537                 ctsvc_stmt_bind_text(stmt, i, cursor->data);
3538
3539         contacts_list_create(&list);
3540         while ((ret = ctsvc_stmt_step(stmt))) {
3541                 contacts_record_h record;
3542                 if (1 != ret) {
3543                         /* LCOV_EXCL_START */
3544                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
3545                         ctsvc_stmt_finalize(stmt);
3546                         contacts_list_destroy(list, true);
3547                         return ret;
3548                         /* LCOV_EXCL_STOP */
3549                 }
3550
3551                 if (ctsvc_view_get_record_type(s_query->view_uri) == CTSVC_RECORD_PERSON) {
3552                         unsigned int ids_count;
3553                         unsigned int *project;
3554                         if (0 == s_query->projection_count)
3555                                 ids_count = s_query->property_count;
3556                         else
3557                                 ids_count = s_query->projection_count;
3558
3559                         project = malloc(sizeof(unsigned int)*ids_count);
3560
3561                         for (i = 0; i < ids_count; i++) {
3562                                 if (0 == s_query->projection_count)
3563                                         project[i] = s_query->properties[i].property_id;
3564                                 else
3565                                         project[i] = s_query->projection[i];
3566                         }
3567
3568                         ret = ctsvc_db_person_create_record_from_stmt_with_projection(stmt, project,
3569                                         ids_count, is_snippet, keyword, start_match, end_match, token_number, &record);
3570                         if (CONTACTS_ERROR_NONE != ret)
3571                                 /* LCOV_EXCL_START */
3572                                 ERR("ctsvc_db_person_create_record_from_stmt_with_projection() Fail(%d)", ret);
3573                         /* LCOV_EXCL_STOP */
3574
3575                         free(project);
3576                 } else {
3577                         contacts_record_create(s_query->view_uri, (contacts_record_h*)&record);
3578                         int field_count;
3579                         if (0 == s_query->projection_count) {
3580                                 field_count = s_query->property_count;
3581                         } else {
3582                                 field_count = s_query->projection_count;
3583
3584                                 int err = ctsvc_record_set_projection_flags(record, s_query->projection,
3585                                                 s_query->projection_count, s_query->property_count);
3586                                 if (CONTACTS_ERROR_NONE != err)
3587                                         ASSERT_NOT_REACHED("To set projection is Fail");
3588                         }
3589
3590                         for (i = 0; i < field_count; i++) {
3591                                 int property_id;
3592
3593                                 if (0 == s_query->projection_count)
3594                                         property_id = s_query->properties[i].property_id;
3595                                 else
3596                                         property_id = s_query->projection[i];
3597
3598                                 type = __ctsvc_db_get_property_type(s_query->properties,
3599                                                 s_query->property_count, property_id);
3600
3601                                 char *temp = NULL;
3602                                 switch (type) {
3603                                 case CTSVC_VIEW_DATA_TYPE_INT:
3604                                         ctsvc_record_set_int(record, property_id, ctsvc_stmt_get_int(stmt, i));
3605                                         break;
3606                                 case CTSVC_VIEW_DATA_TYPE_STR:
3607                                         temp = ctsvc_stmt_get_text(stmt, i);
3608                                         if (CTSVC_PROPERTY_PERSON_SNIPPET_STRING == property_id) {
3609                                                 temp = ctsvc_utils_get_modified_str(temp, is_snippet, keyword,
3610                                                                 start_match, end_match, token_number);
3611                                         }
3612                                         ctsvc_record_set_str(record, property_id, temp);
3613                                         break;
3614                                 case CTSVC_VIEW_DATA_TYPE_BOOL:
3615                                         ctsvc_record_set_bool(record, property_id, (ctsvc_stmt_get_int(stmt, i) ? true : false));
3616                                         break;
3617                                 case CTSVC_VIEW_DATA_TYPE_LLI:
3618                                         ctsvc_record_set_lli(record, property_id, ctsvc_stmt_get_int64(stmt, i));
3619                                         break;
3620                                 case CTSVC_VIEW_DATA_TYPE_DOUBLE:
3621                                         ctsvc_record_set_double(record, property_id, ctsvc_stmt_get_dbl(stmt, i));
3622                                         break;
3623                                 default:
3624                                         /* LCOV_EXCL_START */
3625                                         ERR("unknown type (%d)", type);
3626                                         break;
3627                                         /* LCOV_EXCL_STOP */
3628                                 }
3629                         }
3630                 }
3631                 ctsvc_list_prepend(list, record);
3632         }
3633         ctsvc_stmt_finalize(stmt);
3634         ctsvc_list_reverse(list);
3635
3636         *out_list = list;
3637         return CONTACTS_ERROR_NONE;
3638 }
3639
3640 static int __ctsvc_db_search_records_with_query(contacts_query_h query,
3641                 const char *keyword,
3642                 int offset,
3643                 int limit,
3644                 bool is_snippet,
3645                 const char *start_match,
3646                 const char *end_match,
3647                 int token_number,
3648                 contacts_list_h *out_list)
3649 {
3650         int ret;
3651         char *condition = NULL;
3652         char *projection;
3653         ctsvc_query_s *s_query = (ctsvc_query_s*)query;
3654         GSList *bind_text = NULL;
3655         GSList *cursor = NULL;
3656         bool can_keyword_search;
3657         int enum_uri = 0;
3658
3659         RETV_IF(NULL == query, CONTACTS_ERROR_INVALID_PARAMETER);
3660         RETV_IF(NULL == keyword, CONTACTS_ERROR_INVALID_PARAMETER);
3661
3662         can_keyword_search = __ctsvc_db_view_can_keyword_search(s_query->view_uri, &enum_uri);
3663         RETV_IF(false == can_keyword_search, CONTACTS_ERROR_INVALID_PARAMETER);
3664
3665         ret = __db_create_projection(s_query->view_uri, s_query->properties,
3666                         s_query->property_count, s_query->projection, s_query->projection_count,
3667                         &projection, is_snippet);
3668         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "__db_create_projection Fail(%d)", ret);
3669
3670         if (s_query->filter) {
3671                 ret = __db_create_composite_condition(s_query->filter, &condition,
3672                                 &bind_text);
3673                 if (CONTACTS_ERROR_NONE != ret) {
3674                         /* LCOV_EXCL_START */
3675                         ERR("__db_create_composite_condition Fail(%d)", ret);
3676                         free(projection);
3677                         return ret;
3678                         /* LCOV_EXCL_STOP */
3679                 }
3680         }
3681
3682         ret = __ctsvc_db_search_records_with_query_exec(s_query, enum_uri, projection, condition,
3683                         bind_text, keyword, offset, limit, is_snippet, start_match, end_match, token_number, out_list);
3684         if (CONTACTS_ERROR_NONE != ret) {
3685                 /* LCOV_EXCL_START */
3686                 ERR("__ctsvc_db_search_records_with_query_exec Fail(%d)", ret);
3687                 for (cursor = bind_text; cursor; cursor = cursor->next)
3688                         free(cursor->data);
3689                 g_slist_free(bind_text);
3690
3691                 free(condition);
3692                 free(projection);
3693                 return ret;
3694                 /* LCOV_EXCL_STOP */
3695         }
3696
3697         for (cursor = bind_text; cursor; cursor = cursor->next)
3698                 free(cursor->data);
3699         g_slist_free(bind_text);
3700
3701         free(condition);
3702         free(projection);
3703
3704         return CONTACTS_ERROR_NONE;
3705 }
3706
3707 typedef struct {
3708         contacts_list_h list;
3709         int *ids;
3710         int count;
3711         unsigned int index;
3712         const char *view_uri;
3713         void *cb;
3714         void *user_data;
3715 } ctsvc_bulk_info_s;
3716
3717 static int __ctsvc_db_insert_records(contacts_list_h list, int **ids)
3718 {
3719         int ret;
3720         int index;
3721         int *id = NULL;
3722         int count;
3723         contacts_record_h record = NULL;
3724
3725         ret = contacts_list_get_count(list, &count);
3726         if (CONTACTS_ERROR_NONE != ret) {
3727                 /* LCOV_EXCL_START */
3728                 ERR("contacts_list_get_count() Fail(%d)", ret);
3729                 return ret;
3730                 /* LCOV_EXCL_STOP */
3731         }
3732
3733         ret = ctsvc_begin_trans();
3734         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "ctsvc_begin_trans Fail(%d)", ret);
3735
3736         id = calloc(count, sizeof(int));
3737
3738         contacts_list_first(list);
3739         index = 0;
3740         /* for performance. phone_number_get_normalized_number() can be called repeatedly below */
3741         phone_number_connect();
3742         do {
3743                 ret = contacts_list_get_current_record_p(list, &record);
3744                 if (CONTACTS_ERROR_NONE != ret) {
3745                         /* LCOV_EXCL_START */
3746                         ERR("contacts_list_get_current_record_p() Fail(%d)", ret);
3747                         ctsvc_end_trans(false);
3748                         free(id);
3749                         phone_number_disconnect();
3750                         return ret;
3751                         /* LCOV_EXCL_STOP */
3752                 }
3753
3754                 ret = ctsvc_db_insert_record(record, &id[index++]);
3755                 if (ret != CONTACTS_ERROR_NONE) {
3756                         /* LCOV_EXCL_START */
3757                         ERR("contacts_db_insert_record() Fail(%d)", ret);
3758                         ctsvc_end_trans(false);
3759                         free(id);
3760                         phone_number_disconnect();
3761                         return ret;
3762                         /* LCOV_EXCL_STOP */
3763                 }
3764         } while (CONTACTS_ERROR_NONE  == contacts_list_next(list));
3765         phone_number_disconnect();
3766         ret = ctsvc_end_trans(true);
3767         if (ret < CONTACTS_ERROR_NONE) {
3768                 /* LCOV_EXCL_START */
3769                 ERR("ctsvc_end_trans() Fail(%d)", ret);
3770                 free(id);
3771                 return ret;
3772                 /* LCOV_EXCL_STOP */
3773         }
3774
3775         if (ids)
3776                 *ids = id;
3777         else
3778                 free(id);
3779         return CONTACTS_ERROR_NONE;
3780 }
3781
3782 static int __ctsvc_db_delete_records(const char *view_uri, int ids[], int count)
3783 {
3784         int ret = CONTACTS_ERROR_NONE;
3785         int index;
3786
3787         ret = ctsvc_begin_trans();
3788         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "ctsvc_begin_trans Fail(%d)", ret);
3789
3790         index = 0;
3791         do {
3792                 ret = ctsvc_db_delete_record(view_uri, ids[index++]);
3793                 if (CONTACTS_ERROR_NO_DATA == ret) {
3794                         DBG("the record is not exist : %d", ret);
3795                         continue;
3796                 } else if (ret != CONTACTS_ERROR_NONE) {
3797                         /* LCOV_EXCL_START */
3798                         ERR("contacts_db_delete_record() Fail(%d)", ret);
3799                         ctsvc_end_trans(false);
3800                         return ret;
3801                         /* LCOV_EXCL_STOP */
3802                 }
3803         } while (index < count);
3804
3805         ret = ctsvc_end_trans(true);
3806         if (ret < CONTACTS_ERROR_NONE) {
3807                 /* LCOV_EXCL_START */
3808                 ERR("ctsvc_end_trans() Fail(%d)", ret);
3809                 return ret;
3810                 /* LCOV_EXCL_STOP */
3811         }
3812
3813         return CONTACTS_ERROR_NONE;
3814 }
3815
3816 static int __ctsvc_db_update_records(contacts_list_h list)
3817 {
3818         int ret = CONTACTS_ERROR_NONE;
3819         int count;
3820         contacts_record_h record;
3821
3822         ret = contacts_list_get_count(list, &count);
3823         RETVM_IF(ret != CONTACTS_ERROR_NONE, ret, "contacts_list_get_count() Fail(%d)", ret);
3824         RETVM_IF(count <= 0, CONTACTS_ERROR_INVALID_PARAMETER, "Invalid count(%d)", count);
3825
3826         ret = ctsvc_begin_trans();
3827         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "ctsvc_begin_trans() Fail(%d)", ret);
3828
3829         contacts_list_first(list);
3830         /* for performance. phone_number_get_normalized_number() can be called repeatedly below */
3831         phone_number_connect();
3832         do {
3833                 ret = contacts_list_get_current_record_p(list, &record);
3834                 if (CONTACTS_ERROR_NONE != ret) {
3835                         /* LCOV_EXCL_START */
3836                         ERR("contacts_list_get_current_record_p() Fail(%d)", ret);
3837                         ctsvc_end_trans(false);
3838                         phone_number_disconnect();
3839                         return ret;
3840                         /* LCOV_EXCL_STOP */
3841                 }
3842
3843                 ret = ctsvc_db_update_record(record);
3844                 if (ret != CONTACTS_ERROR_NONE) {
3845                         /* LCOV_EXCL_START */
3846                         ERR("contacts_db_update_record() Fail(%d)", ret);
3847                         ctsvc_end_trans(false);
3848                         phone_number_disconnect();
3849                         return ret;
3850                         /* LCOV_EXCL_STOP */
3851                 }
3852         } while (CONTACTS_ERROR_NONE == contacts_list_next(list));
3853         phone_number_disconnect();
3854         ret = ctsvc_end_trans(true);
3855         if (ret < CONTACTS_ERROR_NONE) {
3856                 /* LCOV_EXCL_START */
3857                 ERR("ctsvc_end_trans() Fail(%d)", ret);
3858                 return ret;
3859                 /* LCOV_EXCL_STOP */
3860         }
3861
3862         return CONTACTS_ERROR_NONE;
3863 }
3864
3865 static int __ctsvc_db_get_count_exec(const char *view_uri, const property_info_s *properties, int ids_count,
3866                 const char *projection, int *out_count)
3867 {
3868         char query[CTS_SQL_MAX_LEN] = {0};
3869         const char *table;
3870         int len;
3871         int ret;
3872
3873         ret = ctsvc_db_get_table_name(view_uri, &table);
3874         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "Invalid view uri(%s)", view_uri);
3875
3876         len = snprintf(query, sizeof(query), "SELECT COUNT(*) FROM (SELECT %s FROM ", projection);
3877
3878         len += snprintf(query+len, sizeof(query)-len, "  (%s)", table);
3879
3880         len += snprintf(query+len, sizeof(query)-len, ") ");
3881
3882         ret = ctsvc_query_get_first_int_result(query, out_count);
3883         if (CONTACTS_ERROR_NONE != ret) {
3884                 /* LCOV_EXCL_START */
3885                 ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
3886                 return ret;
3887                 /* LCOV_EXCL_STOP */
3888         }
3889
3890         return ret;
3891 }
3892
3893 static int __ctsvc_db_get_count(const char *view_uri, int *out_count)
3894 {
3895         int ret;
3896         unsigned int count;
3897         char *projection;
3898
3899         const property_info_s *p = ctsvc_view_get_all_property_infos(view_uri, &count);
3900         ret = __db_create_projection(view_uri, p, count, NULL, 0, &projection, false);
3901         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "__db_create_projection Fail(%d)", ret);
3902
3903         __ctsvc_db_get_count_exec(view_uri, p, count, projection, out_count);
3904         free(projection);
3905
3906         return CONTACTS_ERROR_NONE;
3907 }
3908
3909 static int __db_get_count_with_query_exec(ctsvc_query_s *s_query, const char *projection,
3910                 const char *condition, GSList *bind_text, int *out_count)
3911 {
3912         char *query = NULL;
3913         int query_size;
3914         int temp_len;
3915         int len;
3916         int ret;
3917         const char *table;
3918
3919         RETV_IF(NULL == projection || '\0' == *projection, CONTACTS_ERROR_INVALID_PARAMETER);
3920
3921         ret = ctsvc_db_get_table_name(s_query->view_uri, &table);
3922         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "Invalid view uri(%s)", s_query->view_uri);
3923
3924         query_size = CTS_SQL_MAX_LEN;
3925         query = calloc(1, query_size);
3926         if (NULL == query) {
3927                 /* LCOV_EXCL_START */
3928                 ERR("calloc() Fail");
3929                 return CONTACTS_ERROR_OUT_OF_MEMORY;
3930                 /* LCOV_EXCL_STOP */
3931         }
3932
3933         len = 0;
3934         if (s_query->distinct) {
3935                 temp_len = SAFE_SNPRINTF(&query, &query_size, len,
3936                                 "SELECT COUNT(*) FROM (SELECT DISTINCT ");
3937         } else {
3938                 temp_len = SAFE_SNPRINTF(&query, &query_size, len,
3939                                 "SELECT COUNT(*) FROM (SELECT ");
3940         }
3941         if (0 <= temp_len)
3942                 len += temp_len;
3943
3944         temp_len = SAFE_SNPRINTF(&query, &query_size, len, projection);
3945         if (0 <= temp_len)
3946                 len += temp_len;
3947
3948         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " FROM ");
3949         if (0 <= temp_len)
3950                 len += temp_len;
3951
3952         temp_len = SAFE_SNPRINTF(&query, &query_size, len, " (");
3953         if (0 <= temp_len)
3954                 len += temp_len;
3955
3956         temp_len = SAFE_SNPRINTF(&query, &query_size, len,  table);
3957         if (0 <= temp_len)
3958                 len += temp_len;
3959
3960         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
3961         if (0 <= temp_len)
3962                 len += temp_len;
3963
3964         if (condition && *condition) {
3965                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, " WHERE (");
3966                 if (0 <= temp_len)
3967                         len += temp_len;
3968
3969                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, condition);
3970                 if (0 <= temp_len)
3971                         len += temp_len;
3972
3973                 temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
3974                 if (0 <= temp_len)
3975                         len += temp_len;
3976         }
3977
3978         temp_len = SAFE_SNPRINTF(&query, &query_size, len, ") ");
3979         if (0 <= temp_len) len += temp_len;
3980
3981         if (bind_text) {
3982                 cts_stmt stmt;
3983                 GSList *cursor;
3984                 int i;
3985                 ret = ctsvc_query_prepare(query, &stmt);
3986                 free(query);
3987                 RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
3988
3989                 for (cursor = bind_text, i = 1; cursor; cursor = cursor->next, i++)
3990                         ctsvc_stmt_bind_copy_text(stmt, i, cursor->data, strlen(cursor->data));
3991                 ret = ctsvc_stmt_get_first_int_result(stmt, out_count);
3992                 if (CONTACTS_ERROR_NONE != ret) {
3993                         /* LCOV_EXCL_START */
3994                         ERR("ctsvc_stmt_get_first_int_result() Fail(%d)", ret);
3995                         return ret;
3996                         /* LCOV_EXCL_STOP */
3997                 }
3998         } else {
3999                 ret = ctsvc_query_get_first_int_result(query, out_count);
4000                 free(query);
4001                 if (CONTACTS_ERROR_NONE != ret) {
4002                         /* LCOV_EXCL_START */
4003                         ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
4004                         return ret;
4005                         /* LCOV_EXCL_STOP */
4006                 }
4007         }
4008         return ret;
4009 }
4010
4011 static int __ctsvc_db_get_count_with_query(contacts_query_h query, int *out_count)
4012 {
4013         int ret;
4014         char *condition = NULL;
4015         char *projection = NULL;
4016         ctsvc_query_s *s_query;
4017         GSList *bind_text = NULL;
4018         GSList *cursor;
4019
4020         RETV_IF(NULL == query, CONTACTS_ERROR_INVALID_PARAMETER);
4021         s_query = (ctsvc_query_s*)query;
4022
4023         if (s_query->filter) {
4024                 ret = __db_create_composite_condition(s_query->filter, &condition, &bind_text);
4025                 RETVM_IF(CONTACTS_ERROR_NONE != ret, ret,
4026                                 "__db_create_composite_condition() Fail(%d)", ret);
4027         }
4028
4029         ret = __db_create_projection(s_query->view_uri, s_query->properties,
4030                         s_query->property_count, s_query->projection, s_query->projection_count,
4031                         &projection, false);
4032         if (CONTACTS_ERROR_NONE != ret) {
4033                 /* LCOV_EXCL_START */
4034                 ERR("__db_create_projection() Fail(%d)", ret);
4035                 for (cursor = bind_text; cursor; cursor = cursor->next)
4036                         free(cursor->data);
4037                 g_slist_free(bind_text);
4038                 free(condition);
4039                 return ret;
4040                 /* LCOV_EXCL_STOP */
4041         }
4042
4043         ret = __db_get_count_with_query_exec(s_query, projection, condition, bind_text,
4044                         out_count);
4045         for (cursor = bind_text; cursor; cursor = cursor->next)
4046                 free(cursor->data);
4047         g_slist_free(bind_text);
4048
4049         free(condition);
4050         free(projection);
4051
4052         return ret;
4053 }
4054
4055 int ctsvc_db_get_records_with_query(contacts_query_h query, int offset, int limit,
4056                 contacts_list_h *out_list)
4057 {
4058         int ret = CONTACTS_ERROR_NONE;
4059         ctsvc_query_s *s_query = (ctsvc_query_s*)query;
4060         ctsvc_db_plugin_info_s *plugin_info = NULL;
4061
4062         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4063         *out_list = NULL;
4064
4065         RETV_IF(NULL == query, CONTACTS_ERROR_INVALID_PARAMETER);
4066
4067         plugin_info = ctsvc_db_get_plugin_info(ctsvc_view_get_record_type(s_query->view_uri));
4068         if (plugin_info) {
4069                 if (plugin_info->get_records_with_query) {
4070                         ret = plugin_info->get_records_with_query(query, offset, limit, out_list);
4071                         return ret;
4072                 }
4073         }
4074
4075         return __ctsvc_db_get_records_with_query_exec(s_query, offset, limit, out_list);
4076 }
4077
4078 static int __ctsvc_db_get_contact_changes(const char *view_uri, int addressbook_id,
4079                 int version, contacts_list_h *out_list, int *out_current_version)
4080 {
4081         int ret;
4082         char query[CTS_SQL_MAX_LEN] = {0};
4083         contacts_list_h list;
4084         cts_stmt stmt;
4085
4086         if (0 <= addressbook_id) {
4087                 snprintf(query, sizeof(query),
4088                                 "SELECT %d, contact_id, changed_ver, created_ver, addressbook_id, image_changed_ver "
4089                                 "FROM "CTS_TABLE_CONTACTS" "
4090                                 "WHERE changed_ver > %d AND addressbook_id = %d AND deleted = 0 "
4091                                 "UNION "
4092                                 "SELECT %d, contact_id, deleted_ver, -1, addressbook_id, 0 "
4093                                 "FROM "CTS_TABLE_DELETEDS" "
4094                                 "WHERE deleted_ver > %d AND created_ver <= %d AND addressbook_id = %d "
4095                                 "UNION "
4096                                 "SELECT %d, contact_id, changed_ver, -1, addressbook_id, 0 "
4097                                 "FROM "CTS_TABLE_CONTACTS" "
4098                                 "WHERE changed_ver > %d AND created_ver <= %d AND addressbook_id = %d AND deleted = 1 "
4099                                 "AND addressbook_id = (SELECT addressbook_id FROM "CTS_TABLE_ADDRESSBOOKS" WHERE addressbook_id = %d)",
4100                                 CONTACTS_CHANGE_UPDATED, version, addressbook_id,
4101                                 CONTACTS_CHANGE_DELETED, version, version, addressbook_id,
4102                                 CONTACTS_CHANGE_DELETED, version, version, addressbook_id, addressbook_id);
4103         } else {
4104                 snprintf(query, sizeof(query),
4105                                 "SELECT %d, contact_id, changed_ver, created_ver, addressbook_id, image_changed_ver "
4106                                 "FROM "CTS_TABLE_CONTACTS" "
4107                                 "WHERE changed_ver > %d AND deleted = 0 "
4108                                 "UNION "
4109                                 "SELECT %d, contact_id, deleted_ver, -1, addressbook_id, 0 "
4110                                 "FROM "CTS_TABLE_DELETEDS" "
4111                                 "WHERE deleted_ver > %d AND created_ver <= %d "
4112                                 "UNION "
4113                                 "SELECT %d, contact_id, changed_ver, -1, "CTS_TABLE_CONTACTS".addressbook_id, 0 "
4114                                 "FROM "CTS_TABLE_CONTACTS",  "CTS_TABLE_ADDRESSBOOKS" "
4115                                 "WHERE changed_ver > %d AND created_ver <= %d AND deleted = 1 "
4116                                 "AND "CTS_TABLE_CONTACTS".addressbook_id = "CTS_TABLE_ADDRESSBOOKS".addressbook_id",
4117                                 CONTACTS_CHANGE_UPDATED, version,
4118                                 CONTACTS_CHANGE_DELETED, version, version,
4119                                 CONTACTS_CHANGE_DELETED, version, version);
4120         }
4121
4122         ret = ctsvc_query_prepare(query, &stmt);
4123         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
4124
4125         contacts_list_create(&list);
4126         while ((ret = ctsvc_stmt_step(stmt))) {
4127                 contacts_record_h record;
4128                 ctsvc_updated_info_s *update_info;
4129
4130                 if (1 != ret) {
4131                         /* LCOV_EXCL_START */
4132                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
4133                         ctsvc_stmt_finalize(stmt);
4134                         contacts_list_destroy(list, true);
4135                         return ret;
4136                         /* LCOV_EXCL_STOP */
4137                 }
4138
4139                 ret = contacts_record_create(_contacts_contact_updated_info._uri, &record);
4140                 update_info = (ctsvc_updated_info_s*)record;
4141                 update_info->changed_type = ctsvc_stmt_get_int(stmt, 0);
4142                 update_info->id = ctsvc_stmt_get_int(stmt, 1);
4143                 update_info->changed_ver = ctsvc_stmt_get_int(stmt, 2);
4144
4145                 if (ctsvc_stmt_get_int(stmt, 3) == update_info->changed_ver
4146                                 || version < ctsvc_stmt_get_int(stmt, 3)) {
4147                         update_info->changed_type = CONTACTS_CHANGE_INSERTED;
4148                 }
4149
4150                 update_info->addressbook_id = ctsvc_stmt_get_int(stmt, 4);
4151
4152                 if (version < ctsvc_stmt_get_int(stmt, 5))
4153                         update_info->image_changed = true;
4154
4155                 ctsvc_list_prepend(list, record);
4156         }
4157         ctsvc_stmt_finalize(stmt);
4158         ctsvc_list_reverse(list);
4159
4160         snprintf(query, sizeof(query), "SELECT ver FROM "CTS_TABLE_VERSION);
4161         ret = ctsvc_query_get_first_int_result(query, out_current_version);
4162         if (CONTACTS_ERROR_NONE != ret) {
4163                 /* LCOV_EXCL_START */
4164                 ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
4165                 contacts_list_destroy(list, true);
4166                 return ret;
4167                 /* LCOV_EXCL_STOP */
4168         }
4169         *out_list = list;
4170
4171         return CONTACTS_ERROR_NONE;
4172 }
4173
4174 static int __ctsvc_db_get_group_changes(const char *view_uri, int addressbook_id,
4175                 int version, contacts_list_h *out_list, int *out_current_version)
4176 {
4177         int ret;
4178         char query[CTS_SQL_MAX_LEN] = {0};
4179         contacts_list_h list;
4180         cts_stmt stmt;
4181
4182         if (0 <= addressbook_id) {
4183                 snprintf(query, sizeof(query),
4184                                 "SELECT %d, group_id, changed_ver, created_ver, addressbook_id FROM %s "
4185                                 "WHERE changed_ver > %d AND addressbook_id = %d "
4186                                 "UNION "
4187                                 "SELECT %d, group_id, deleted_ver, -1, addressbook_id FROM %s "
4188                                 "WHERE deleted_ver > %d AND created_ver <= %d AND addressbook_id = %d",
4189                                 CONTACTS_CHANGE_UPDATED, CTS_TABLE_GROUPS, version, addressbook_id,
4190                                 CONTACTS_CHANGE_DELETED, CTS_TABLE_GROUP_DELETEDS, version, version, addressbook_id);
4191         } else {
4192                 snprintf(query, sizeof(query),
4193                                 "SELECT %d, group_id, changed_ver, created_ver, addressbook_id FROM %s "
4194                                 "WHERE changed_ver > %d "
4195                                 "UNION "
4196                                 "SELECT %d, group_id, deleted_ver, -1, addressbook_id FROM %s "
4197                                 "WHERE deleted_ver > %d AND created_ver <= %d",
4198                                 CONTACTS_CHANGE_UPDATED, CTS_TABLE_GROUPS, version,
4199                                 CONTACTS_CHANGE_DELETED, CTS_TABLE_GROUP_DELETEDS, version, version);
4200         }
4201
4202         ret = ctsvc_query_prepare(query, &stmt);
4203         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
4204
4205         contacts_list_create(&list);
4206         while ((ret = ctsvc_stmt_step(stmt))) {
4207                 contacts_record_h record;
4208                 ctsvc_updated_info_s *update_info;
4209
4210                 if (1 != ret) {
4211                         /* LCOV_EXCL_START */
4212                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
4213                         ctsvc_stmt_finalize(stmt);
4214                         contacts_list_destroy(list, true);
4215                         return ret;
4216                         /* LCOV_EXCL_STOP */
4217                 }
4218
4219                 ret = contacts_record_create(_contacts_group_updated_info._uri, &record);
4220                 update_info = (ctsvc_updated_info_s*)record;
4221                 update_info->changed_type = ctsvc_stmt_get_int(stmt, 0);
4222                 update_info->id = ctsvc_stmt_get_int(stmt, 1);
4223                 update_info->changed_ver = ctsvc_stmt_get_int(stmt, 2);
4224
4225                 if (ctsvc_stmt_get_int(stmt, 3) == update_info->changed_ver
4226                                 || version < ctsvc_stmt_get_int(stmt, 3)) {
4227                         update_info->changed_type = CONTACTS_CHANGE_INSERTED;
4228                 }
4229
4230                 update_info->addressbook_id = ctsvc_stmt_get_int(stmt, 4);
4231
4232                 ctsvc_list_prepend(list, record);
4233         }
4234         ctsvc_stmt_finalize(stmt);
4235         ctsvc_list_reverse(list);
4236
4237         snprintf(query, sizeof(query), "SELECT ver FROM "CTS_TABLE_VERSION);
4238         ret = ctsvc_query_get_first_int_result(query, out_current_version);
4239         if (CONTACTS_ERROR_NONE != ret) {
4240                 /* LCOV_EXCL_START */
4241                 ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
4242                 contacts_list_destroy(list, true);
4243                 return ret;
4244                 /* LCOV_EXCL_STOP */
4245         }
4246         *out_list = list;
4247
4248         return CONTACTS_ERROR_NONE;
4249 }
4250
4251 static int __ctsvc_db_get_group_relations_changes(const char *view_uri,
4252                 int addressbook_id,
4253                 int version,
4254                 contacts_list_h *out_list,
4255                 int *out_current_version)
4256 {
4257         int len;
4258         int ret;
4259         char query[CTS_SQL_MAX_LEN] = {0};
4260         char temp_query[CTS_SQL_MAX_LEN] = {0};
4261         contacts_list_h list;
4262         cts_stmt stmt;
4263
4264         len = snprintf(temp_query, sizeof(temp_query),
4265                         "SELECT %d, group_id, contact_id, addressbook_id, ver "
4266                         "FROM "CTS_TABLE_GROUP_RELATIONS", "CTS_TABLE_GROUPS" USING (group_id) "
4267                         "WHERE ver > %d AND deleted = 0 "
4268                         "UNION SELECT %d, group_id, contact_id, addressbook_id, ver "
4269                         "FROM "CTS_TABLE_GROUP_RELATIONS", "CTS_TABLE_GROUPS" USING (group_id) "
4270                         "WHERE ver > %d AND deleted = 1 ",
4271                         CONTACTS_CHANGE_INSERTED, version,
4272                         CONTACTS_CHANGE_DELETED, version);
4273
4274         if (0 <= addressbook_id) {
4275                 len += snprintf(query, sizeof(query),
4276                                 "SELECT * FROM (%s) WHERE addressbook_id = %d ", temp_query, addressbook_id);
4277         }
4278
4279         ret = ctsvc_query_prepare(query, &stmt);
4280         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
4281
4282         contacts_list_create(&list);
4283         while ((ret = ctsvc_stmt_step(stmt))) {
4284                 contacts_record_h record;
4285                 if (1 != ret) {
4286                         /* LCOV_EXCL_START */
4287                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
4288                         ctsvc_stmt_finalize(stmt);
4289                         contacts_list_destroy(list, true);
4290                         return ret;
4291                         /* LCOV_EXCL_STOP */
4292                 }
4293
4294                 ret = contacts_record_create(view_uri, &record);
4295                 ctsvc_record_set_int(record, _contacts_grouprel_updated_info.type,
4296                                 ctsvc_stmt_get_int(stmt, 0));
4297                 ctsvc_record_set_int(record, _contacts_grouprel_updated_info.group_id,
4298                                 ctsvc_stmt_get_int(stmt, 1));
4299                 ctsvc_record_set_int(record, _contacts_grouprel_updated_info.contact_id,
4300                                 ctsvc_stmt_get_int(stmt, 2));
4301                 ctsvc_record_set_int(record, _contacts_grouprel_updated_info.address_book_id,
4302                                 ctsvc_stmt_get_int(stmt, 3));
4303                 ctsvc_record_set_int(record, _contacts_grouprel_updated_info.version,
4304                                 ctsvc_stmt_get_int(stmt, 4));
4305
4306                 ctsvc_list_prepend(list, record);
4307         }
4308         ctsvc_stmt_finalize(stmt);
4309         ctsvc_list_reverse(list);
4310
4311         snprintf(query, sizeof(query), "SELECT ver FROM "CTS_TABLE_VERSION);
4312         ret = ctsvc_query_get_first_int_result(query, out_current_version);
4313         if (CONTACTS_ERROR_NONE != ret) {
4314                 /* LCOV_EXCL_START */
4315                 ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
4316                 contacts_list_destroy(list, true);
4317                 return ret;
4318                 /* LCOV_EXCL_STOP */
4319         }
4320
4321         *out_list = list;
4322
4323         return CONTACTS_ERROR_NONE;
4324 }
4325
4326 static int __ctsvc_db_get_group_member_changes(const char *view_uri, int addressbook_id,
4327                 int version, contacts_list_h *out_list, int *out_current_version)
4328 {
4329         int len;
4330         int ret;
4331         char query[CTS_SQL_MAX_LEN] = {0};
4332         contacts_list_h list;
4333         cts_stmt stmt;
4334
4335         len = snprintf(query, sizeof(query),
4336                         "SELECT group_id, member_changed_ver, addressbook_id "
4337                         "FROM "CTS_TABLE_GROUPS" WHERE member_changed_ver > %d", version);
4338
4339         if (0 <= addressbook_id) {
4340                 len += snprintf(query+len, sizeof(query)-len, " AND addressbook_id = %d ",
4341                                 addressbook_id);
4342         }
4343
4344         ret = ctsvc_query_prepare(query, &stmt);
4345         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
4346
4347         contacts_list_create(&list);
4348         while ((ret = ctsvc_stmt_step(stmt))) {
4349                 contacts_record_h record;
4350                 if (1 != ret) {
4351                         /* LCOV_EXCL_START */
4352                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
4353                         ctsvc_stmt_finalize(stmt);
4354                         contacts_list_destroy(list, true);
4355                         return ret;
4356                         /* LCOV_EXCL_STOP */
4357                 }
4358
4359                 ret = contacts_record_create(view_uri, &record);
4360                 ctsvc_record_set_int(record, _contacts_group_member_updated_info.group_id,
4361                                 ctsvc_stmt_get_int(stmt, 0));
4362                 ctsvc_record_set_int(record, _contacts_group_member_updated_info.version,
4363                                 ctsvc_stmt_get_int(stmt, 1));
4364                 ctsvc_record_set_int(record, _contacts_group_member_updated_info.address_book_id,
4365                                 ctsvc_stmt_get_int(stmt, 2));
4366
4367                 ctsvc_list_prepend(list, record);
4368         }
4369         ctsvc_stmt_finalize(stmt);
4370         ctsvc_list_reverse(list);
4371
4372         snprintf(query, sizeof(query), "SELECT ver FROM "CTS_TABLE_VERSION);
4373         ret = ctsvc_query_get_first_int_result(query, out_current_version);
4374         if (CONTACTS_ERROR_NONE != ret) {
4375                 /* LCOV_EXCL_START */
4376                 ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
4377                 contacts_list_destroy(list, true);
4378                 return ret;
4379                 /* LCOV_EXCL_STOP */
4380         }
4381
4382         *out_list = list;
4383
4384         return CONTACTS_ERROR_NONE;
4385 }
4386
4387 static int __ctsvc_db_get_my_profile_changes(const char *view_uri, int addressbook_id,
4388                 int version, contacts_list_h *out_list, int *out_current_version)
4389 {
4390         int ret;
4391         char query[CTS_SQL_MAX_LEN] = {0};
4392         contacts_list_h list;
4393         cts_stmt stmt;
4394
4395         if (0 <= addressbook_id) {
4396                 snprintf(query, sizeof(query),
4397                                 "SELECT changed_ver, addressbook_id, %d FROM %s "
4398                                 "WHERE changed_ver > %d AND changed_ver == created_ver AND deleted = 0 AND addressbook_id = %d "
4399                                 "UNION "
4400                                 "SELECT changed_ver, addressbook_id, %d FROM %s "
4401                                 "WHERE changed_ver > %d AND changed_ver != created_ver AND deleted = 0 AND addressbook_id = %d "
4402                                 "UNION "
4403                                 "SELECT changed_ver, addressbook_id, %d FROM %s "
4404                                 "WHERE changed_ver > %d AND deleted = 1 AND addressbook_id = %d",
4405                                 CONTACTS_CHANGE_INSERTED, CTS_TABLE_MY_PROFILES, version, addressbook_id,
4406                                 CONTACTS_CHANGE_UPDATED, CTS_TABLE_MY_PROFILES, version, addressbook_id,
4407                                 CONTACTS_CHANGE_DELETED, CTS_TABLE_MY_PROFILES, version, addressbook_id);
4408         } else {
4409                 snprintf(query, sizeof(query),
4410                                 "SELECT changed_ver, addressbook_id, %d FROM %s "
4411                                 "WHERE changed_ver > %d AND changed_ver == created_ver AND deleted = 0 "
4412                                 "UNION "
4413                                 "SELECT changed_ver, addressbook_id, %d FROM %s "
4414                                 "WHERE changed_ver > %d AND changed_ver != created_ver AND deleted = 0 "
4415                                 "UNION "
4416                                 "SELECT changed_ver, addressbook_id, %d FROM %s "
4417                                 "WHERE changed_ver > %d AND deleted = 1",
4418                                 CONTACTS_CHANGE_INSERTED, CTS_TABLE_MY_PROFILES, version,
4419                                 CONTACTS_CHANGE_UPDATED, CTS_TABLE_MY_PROFILES, version,
4420                                 CONTACTS_CHANGE_DELETED, CTS_TABLE_MY_PROFILES, version);
4421         }
4422
4423         ret = ctsvc_query_prepare(query, &stmt);
4424         RETVM_IF(NULL == stmt, ret, "ctsvc_query_prepare() Fail(%d)", ret);
4425
4426         contacts_list_create(&list);
4427         while ((ret = ctsvc_stmt_step(stmt))) {
4428                 contacts_record_h record;
4429                 if (1 != ret) {
4430                         /* LCOV_EXCL_START */
4431                         ERR("ctsvc_stmt_step() Fail(%d)", ret);
4432                         ctsvc_stmt_finalize(stmt);
4433                         contacts_list_destroy(list, true);
4434                         return ret;
4435                         /* LCOV_EXCL_STOP */
4436                 }
4437
4438                 ret = contacts_record_create(view_uri, &record);
4439                 ctsvc_record_set_int(record, _contacts_my_profile_updated_info.version,
4440                                 ctsvc_stmt_get_int(stmt, 0));
4441                 ctsvc_record_set_int(record, _contacts_my_profile_updated_info.address_book_id,
4442                                 ctsvc_stmt_get_int(stmt, 1));
4443                 ctsvc_record_set_int(record, _contacts_my_profile_updated_info.last_changed_type,
4444                                 ctsvc_stmt_get_int(stmt, 2));
4445                 ctsvc_list_prepend(list, record);
4446         }
4447         ctsvc_stmt_finalize(stmt);
4448         ctsvc_list_reverse(list);
4449
4450         snprintf(query, sizeof(query), "SELECT ver FROM "CTS_TABLE_VERSION);
4451         ret = ctsvc_query_get_first_int_result(query, out_current_version);
4452         if (CONTACTS_ERROR_NONE != ret) {
4453                 /* LCOV_EXCL_START */
4454                 ERR("ctsvc_query_get_first_int_result() Fail(%d)", ret);
4455                 contacts_list_destroy(list, true);
4456                 return ret;
4457                 /* LCOV_EXCL_STOP */
4458         }
4459
4460         *out_list = list;
4461
4462         return CONTACTS_ERROR_NONE;
4463 }
4464
4465 int ctsvc_db_get_changes_by_version(const char *view_uri, int addressbook_id,
4466                 int version, contacts_list_h *out_list, int *out_current_version)
4467 {
4468         int ret;
4469         RETV_IF(version < 0, CONTACTS_ERROR_INVALID_PARAMETER);
4470         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4471         *out_list = NULL;
4472         RETV_IF(NULL == out_current_version, CONTACTS_ERROR_INVALID_PARAMETER);
4473         *out_current_version = 0;
4474
4475         if (STRING_EQUAL == strcmp(view_uri, _contacts_contact_updated_info._uri)) {
4476                 ret = __ctsvc_db_get_contact_changes(view_uri, addressbook_id,
4477                                 version, out_list, out_current_version);
4478                 return ret;
4479         } else if (STRING_EQUAL == strcmp(view_uri, _contacts_group_updated_info._uri)) {
4480                 ret = __ctsvc_db_get_group_changes(view_uri, addressbook_id,
4481                                 version, out_list, out_current_version);
4482                 return ret;
4483         } else if (STRING_EQUAL == strcmp(view_uri, _contacts_group_member_updated_info._uri)) {
4484                 ret = __ctsvc_db_get_group_member_changes(view_uri, addressbook_id,
4485                                 version, out_list, out_current_version);
4486                 return ret;
4487         } else if (STRING_EQUAL == strcmp(view_uri, _contacts_grouprel_updated_info._uri)) {
4488                 ret = __ctsvc_db_get_group_relations_changes(view_uri, addressbook_id,
4489                                 version, out_list, out_current_version);
4490                 return ret;
4491         } else if (STRING_EQUAL == strcmp(view_uri, _contacts_my_profile_updated_info._uri)) {
4492                 ret = __ctsvc_db_get_my_profile_changes(view_uri, addressbook_id,
4493                                 version, out_list, out_current_version);
4494                 return ret;
4495         }
4496
4497         /* LCOV_EXCL_START */
4498         ERR("this API does not support uri(%s)", view_uri);
4499         return CONTACTS_ERROR_INVALID_PARAMETER;
4500         /* LCOV_EXCL_STOP */
4501 }
4502
4503 int ctsvc_db_get_current_version(int *out_current_version)
4504 {
4505         RETV_IF(NULL == out_current_version, CONTACTS_ERROR_INVALID_PARAMETER);
4506
4507         return ctsvc_get_current_version(out_current_version);
4508 }
4509
4510 int ctsvc_db_search_records(const char *view_uri, const char *keyword,
4511                 int offset, int limit, contacts_list_h *out_list)
4512 {
4513         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4514         *out_list = NULL;
4515         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4516
4517         return __ctsvc_db_search_records(view_uri, keyword, offset, limit, false, NULL,
4518                         NULL, -1, out_list);
4519 }
4520
4521 int ctsvc_db_search_records_with_range(const char *view_uri, const char *keyword,
4522                 int offset, int limit, int range, contacts_list_h *out_list)
4523 {
4524         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4525         *out_list = NULL;
4526         RETV_IF(range == 0, CONTACTS_ERROR_INVALID_PARAMETER);
4527         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4528
4529         return __ctsvc_db_search_records_with_range(view_uri, keyword, offset, limit, range,
4530                         false, NULL, NULL, -1, out_list);
4531 }
4532
4533 int ctsvc_db_search_records_with_query(contacts_query_h query, const char *keyword,
4534                 int offset, int limit, contacts_list_h *out_list)
4535 {
4536         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4537         *out_list = NULL;
4538         RETV_IF(NULL == query, CONTACTS_ERROR_INVALID_PARAMETER);
4539
4540         return __ctsvc_db_search_records_with_query(query, keyword, offset, limit, false,
4541                         NULL, NULL, -1, out_list);
4542 }
4543
4544 int ctsvc_db_search_records_for_snippet(const char *view_uri, const char *keyword,
4545                 int offset,
4546                 int limit,
4547                 const char *start_match,
4548                 const char *end_match,
4549                 int token_number,
4550                 contacts_list_h *out_list)
4551 {
4552         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4553         *out_list = NULL;
4554         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4555
4556         return __ctsvc_db_search_records(view_uri, keyword, offset, limit, true, start_match,
4557                         end_match, token_number, out_list);
4558 }
4559
4560 int ctsvc_db_search_records_with_range_for_snippet(const char *view_uri,
4561                 const char *keyword,
4562                 int offset,
4563                 int limit,
4564                 int range,
4565                 const char *start_match,
4566                 const char *end_match,
4567                 int token_number,
4568                 contacts_list_h *out_list)
4569 {
4570         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4571         *out_list = NULL;
4572         RETV_IF(range == 0, CONTACTS_ERROR_INVALID_PARAMETER);
4573         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4574
4575         return __ctsvc_db_search_records_with_range(view_uri, keyword, offset, limit, range,
4576                         true, start_match, end_match, token_number, out_list);
4577 }
4578
4579 int ctsvc_db_search_records_with_query_for_snippet(contacts_query_h query,
4580                 const char *keyword,
4581                 int offset,
4582                 int limit,
4583                 const char *start_match,
4584                 const char *end_match,
4585                 int token_number,
4586                 contacts_list_h *out_list)
4587 {
4588         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4589         *out_list = NULL;
4590         RETV_IF(NULL == query, CONTACTS_ERROR_INVALID_PARAMETER);
4591
4592         return __ctsvc_db_search_records_with_query(query, keyword, offset, limit, true,
4593                         start_match, end_match, token_number, out_list);
4594 }
4595
4596 int ctsvc_db_get_count(const char *view_uri, int *out_count)
4597 {
4598         int ret;
4599         ctsvc_db_plugin_info_s *plugin_info = NULL;
4600
4601         RETV_IF(NULL == out_count, CONTACTS_ERROR_INVALID_PARAMETER);
4602         *out_count = 0;
4603         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4604
4605         if ((plugin_info = ctsvc_db_get_plugin_info(ctsvc_view_get_record_type(view_uri)))) {
4606                 if (plugin_info->get_count) {
4607                         ret = plugin_info->get_count(out_count);
4608                         return ret;
4609                 }
4610         }
4611
4612         return __ctsvc_db_get_count(view_uri, out_count);
4613 }
4614
4615 int ctsvc_db_get_count_with_query(contacts_query_h query, int *out_count)
4616 {
4617         int ret = CONTACTS_ERROR_NONE;
4618         ctsvc_record_type_e type = CTSVC_RECORD_INVALID;
4619         ctsvc_db_plugin_info_s *plugin_info = NULL;
4620         ctsvc_query_s *s_query = (ctsvc_query_s*)query;
4621
4622         RETV_IF(NULL == out_count, CONTACTS_ERROR_INVALID_PARAMETER);
4623         *out_count = 0;
4624         RETV_IF(NULL == query, CONTACTS_ERROR_INVALID_PARAMETER);
4625
4626         type = ctsvc_view_get_record_type(s_query->view_uri);
4627         plugin_info = ctsvc_db_get_plugin_info(type);
4628
4629         if (plugin_info) {
4630                 if (plugin_info->get_count_with_query) {
4631                         ret = plugin_info->get_count_with_query(query, out_count);
4632                         return ret;
4633                 }
4634         }
4635
4636         return __ctsvc_db_get_count_with_query(query, out_count);
4637 }
4638
4639 int ctsvc_db_insert_record(contacts_record_h record, int *id)
4640 {
4641         ctsvc_db_plugin_info_s *plugin_info = NULL;
4642
4643         if (id)
4644                 *id = 0;
4645
4646         RETV_IF(NULL == record, CONTACTS_ERROR_INVALID_PARAMETER);
4647
4648         plugin_info = ctsvc_db_get_plugin_info(((ctsvc_record_s*)record)->r_type);
4649         RETV_IF(NULL == plugin_info, CONTACTS_ERROR_INVALID_PARAMETER);
4650         RETV_IF(NULL == plugin_info->insert_record, CONTACTS_ERROR_INVALID_PARAMETER);
4651
4652         return plugin_info->insert_record(record, id);
4653 }
4654
4655 int ctsvc_db_update_record(contacts_record_h record)
4656 {
4657         ctsvc_db_plugin_info_s *plugin_info = NULL;
4658
4659         RETV_IF(NULL == record, CONTACTS_ERROR_INVALID_PARAMETER);
4660
4661         plugin_info = ctsvc_db_get_plugin_info(((ctsvc_record_s*)record)->r_type);
4662         RETV_IF(NULL == plugin_info, CONTACTS_ERROR_INVALID_PARAMETER);
4663         RETV_IF(NULL == plugin_info->update_record, CONTACTS_ERROR_INVALID_PARAMETER);
4664
4665         return plugin_info->update_record(record);
4666 }
4667
4668 int ctsvc_db_delete_record(const char *view_uri, int id)
4669 {
4670         ctsvc_record_type_e type = CTSVC_RECORD_INVALID;
4671         ctsvc_db_plugin_info_s *plugin_info = NULL;
4672
4673         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4674
4675         type = ctsvc_view_get_record_type(view_uri);
4676         plugin_info = ctsvc_db_get_plugin_info(type);
4677         RETV_IF(NULL == plugin_info, CONTACTS_ERROR_INVALID_PARAMETER);
4678         RETV_IF(NULL == plugin_info->delete_record, CONTACTS_ERROR_INVALID_PARAMETER);
4679
4680         return plugin_info->delete_record(id);
4681 }
4682
4683 int ctsvc_db_get_record(const char *view_uri, int id, contacts_record_h *out_record)
4684 {
4685         ctsvc_record_type_e type = CTSVC_RECORD_INVALID;
4686         ctsvc_db_plugin_info_s *plugin_info = NULL;
4687
4688         RETV_IF(NULL == out_record, CONTACTS_ERROR_INVALID_PARAMETER);
4689         *out_record = NULL;
4690         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4691
4692         type = ctsvc_view_get_record_type(view_uri);
4693         plugin_info = ctsvc_db_get_plugin_info(type);
4694
4695         RETV_IF(NULL == plugin_info, CONTACTS_ERROR_INVALID_PARAMETER);
4696         RETV_IF(NULL == plugin_info->get_record, CONTACTS_ERROR_INVALID_PARAMETER);
4697
4698         return plugin_info->get_record(id, out_record);
4699 }
4700
4701 int ctsvc_db_replace_record(contacts_record_h record, int id)
4702 {
4703         ctsvc_db_plugin_info_s *plugin_info = NULL;
4704
4705         RETV_IF(NULL == record, CONTACTS_ERROR_INVALID_PARAMETER);
4706
4707         plugin_info = ctsvc_db_get_plugin_info(((ctsvc_record_s*)record)->r_type);
4708         RETV_IF(NULL == plugin_info, CONTACTS_ERROR_INVALID_PARAMETER);
4709         RETV_IF(NULL == plugin_info->replace_record, CONTACTS_ERROR_INVALID_PARAMETER);
4710
4711         return plugin_info->replace_record(record, id);
4712 }
4713
4714 int ctsvc_db_get_all_records(const char *view_uri, int offset, int limit,
4715                 contacts_list_h *out_list)
4716 {
4717         int ret = CONTACTS_ERROR_NONE;
4718         ctsvc_record_type_e type = CTSVC_RECORD_INVALID;
4719         ctsvc_db_plugin_info_s *plugin_info = NULL;
4720
4721         RETV_IF(NULL == out_list, CONTACTS_ERROR_INVALID_PARAMETER);
4722         *out_list = NULL;
4723         RETV_IF(NULL == view_uri, CONTACTS_ERROR_INVALID_PARAMETER);
4724
4725         type = ctsvc_view_get_record_type(view_uri);
4726         plugin_info = ctsvc_db_get_plugin_info(type);
4727
4728         if (plugin_info) {
4729                 if (plugin_info->get_all_records) {
4730                         ret = plugin_info->get_all_records(offset, limit, out_list);
4731                         return ret;
4732                 }
4733         }
4734
4735         return __ctsvc_db_get_all_records(view_uri, offset, limit, out_list);
4736 }
4737
4738 int ctsvc_db_insert_records(contacts_list_h list, int **ids, int *count)
4739 {
4740         int ret = CONTACTS_ERROR_NONE;
4741         ctsvc_db_plugin_info_s *plugin_info = NULL;
4742
4743         RETV_IF(NULL == list, CONTACTS_ERROR_INVALID_PARAMETER);
4744
4745         if (count)
4746                 contacts_list_get_count(list, count);
4747         if ((plugin_info = ctsvc_db_get_plugin_info(((ctsvc_list_s*)list)->l_type))) {
4748                 if (plugin_info->insert_records) {
4749                         ret = plugin_info->insert_records(list, ids);
4750                         return ret;
4751                 }
4752         }
4753
4754         return __ctsvc_db_insert_records(list, ids);
4755 }
4756
4757 int ctsvc_db_update_records(contacts_list_h list)
4758 {
4759         int ret = CONTACTS_ERROR_NONE;
4760         ctsvc_db_plugin_info_s *plugin_info = NULL;
4761
4762         RETV_IF(NULL == list, CONTACTS_ERROR_INVALID_PARAMETER);
4763
4764         if ((plugin_info = ctsvc_db_get_plugin_info(((ctsvc_list_s*)list)->l_type))) {
4765                 if (plugin_info->update_records) {
4766                         ret = plugin_info->update_records(list);
4767                         return ret;
4768                 }
4769         }
4770
4771         return __ctsvc_db_update_records(list);
4772 }
4773
4774 int ctsvc_db_delete_records(const char *view_uri, int *ids, int count)
4775 {
4776         int ret = CONTACTS_ERROR_NONE;
4777         ctsvc_db_plugin_info_s *plugin_info = NULL;
4778
4779         RETV_IF(NULL == ids, CONTACTS_ERROR_INVALID_PARAMETER);
4780
4781         if ((plugin_info = ctsvc_db_get_plugin_info(ctsvc_view_get_record_type(view_uri)))) {
4782                 if (plugin_info->delete_records) {
4783                         ret = plugin_info->delete_records(ids, count);
4784                         return ret;
4785                 }
4786         }
4787
4788         return __ctsvc_db_delete_records(view_uri, ids, count);
4789 }
4790
4791 static int __ctsvc_db_replace_records(contacts_list_h list, int ids[], int count)
4792 {
4793         int ret = CONTACTS_ERROR_NONE;
4794         int record_count;
4795         contacts_record_h record;
4796         int i;
4797
4798         ret = contacts_list_get_count(list, &record_count);
4799         RETVM_IF(ret != CONTACTS_ERROR_NONE, ret, "contacts_list_get_count() Fail(%d)", ret);
4800         RETVM_IF(record_count <= 0, CONTACTS_ERROR_INVALID_PARAMETER, "Invalid count(%d)",
4801                         record_count);
4802         RETVM_IF(record_count != count, CONTACTS_ERROR_INVALID_PARAMETER,
4803                         "record_count(%d) and count(%d) are not matched", record_count, count);
4804
4805         ret = ctsvc_begin_trans();
4806         RETVM_IF(CONTACTS_ERROR_NONE != ret, ret, "ctsvc_begin_trans Fail(%d)", ret);
4807
4808         contacts_list_first(list);
4809         i = 0;
4810         /* for performance. phone_number_get_normalized_number() can be called repeatedly below */
4811         phone_number_connect();
4812         do {
4813                 ret = contacts_list_get_current_record_p(list, &record);
4814                 if (CONTACTS_ERROR_NONE != ret) {
4815                         /* LCOV_EXCL_START */
4816                         ERR("contacts_list_get_current_record_p() Fail(%d)", ret);
4817                         ctsvc_end_trans(false);
4818                         phone_number_disconnect();
4819                         return ret;
4820                         /* LCOV_EXCL_STOP */
4821                 }
4822
4823                 ret = ctsvc_db_replace_record(record, ids[i++]);
4824                 if (ret != CONTACTS_ERROR_NONE) {
4825                         /* LCOV_EXCL_START */
4826                         ERR("contacts_db_replace_record() Fail(%d)", ret);
4827                         ctsvc_end_trans(false);
4828                         phone_number_disconnect();
4829                         return ret;
4830                         /* LCOV_EXCL_STOP */
4831                 }
4832         } while (CONTACTS_ERROR_NONE == contacts_list_next(list));
4833         phone_number_disconnect();
4834         ret = ctsvc_end_trans(true);
4835
4836         return ret;
4837 }
4838
4839
4840 int ctsvc_db_replace_records(contacts_list_h list, int ids[], int count)
4841 {
4842         int ret = CONTACTS_ERROR_NONE;
4843         ctsvc_db_plugin_info_s *plugin_info = NULL;
4844
4845         RETV_IF(NULL == list, CONTACTS_ERROR_INVALID_PARAMETER);
4846         RETV_IF(NULL == ids, CONTACTS_ERROR_INVALID_PARAMETER);
4847
4848         if ((plugin_info = ctsvc_db_get_plugin_info(((ctsvc_list_s*)list)->l_type))) {
4849                 if (plugin_info->replace_records) {
4850                         ret = plugin_info->replace_records(list, ids, count);
4851                         return ret;
4852                 }
4853         }
4854
4855         return __ctsvc_db_replace_records(list, ids, count);
4856 }
4857
4858 int ctsvc_db_get_status(contacts_db_status_e *status)
4859 {
4860         *status = __db_status;
4861         return CONTACTS_ERROR_NONE;
4862 }
4863
4864 void ctsvc_db_set_status(contacts_db_status_e status)
4865 {
4866         __db_status = status;
4867
4868         ctsvc_change_subject_publish_status(status);
4869         return;
4870 }
4871