geofence-server/src/geofence_server_db.c

   1 /* Copyright 2014 Samsung Electronics Co., Ltd All Rights Reserved
   2  *
   3  * Licensed under the Apache License, Version 2.0 (the "License");
   4  * you may not use this file except in compliance with the License.
   5  * You may obtain a copy of the License at
   6  *
   7  * http://www.apache.org/licenses/LICENSE-2.0
   8  *
   9  * Unless required by applicable law or agreed to in writing, software
  10  * distributed under the License is distributed on an "AS IS" BASIS,
  11  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  12  * See the License for the specific language governing permissions and
  13  * limitations under the License.
  14  */
  15
  16 #include <sqlite3.h>
  17 #include <sys/time.h>
  18 #include <db-util.h>
  19 #include <gio/gio.h>
  20 #include <sys/stat.h>
  21 #include <string.h>
  22 #include <bluetooth.h>
  23 #include <wifi.h>
  24 #include <tzplatform_config.h>
  25 #include <sys/types.h>
  26 #include <fcntl.h>
  27
  28 #include "debug_util.h"
  29 #include "geofence_server.h"
  30 #include "geofence_server_db.h"
  31 #include "geofence_server_private.h"
  32
  33 /* dbspace path for Tizen 3.0 was changed.
  34 #define GEOFENCE_SERVER_DB_FILE                ".geofence-server.db"
  35 #define GEOFENCE_SERVER_DB_PATH                "/opt/dbspace/"GEOFENCE_SERVER_DB_FILE
  36 */
  37
  38 #define GEOFENCE_DB_NAME        ".geofence-server.db"
  39 #define GEOFENCE_DB_FILE        tzplatform_mkpath(TZ_USER_DB, GEOFENCE_DB_NAME)
  40
  41 #define MAX_DATA_NAME           20
  42 #define DATA_LEN                        20
  43
  44 #define GEOFENCE_INVALID        0
  45
  46 char *menu_table[4] = { "GeoFence", "FenceGeocoordinate", "FenceGeopointWifi", "FenceBssid" };
  47
  48 const char *group_id = NULL;
  49
  50 #ifdef SUPPORT_ENCRYPTION
  51 static char *password = "k1s2c3w4k5a6";
  52 const char *col_latitude = "la";
  53 const char *col_longitude = "lo";
  54 const char *col_radius = "r";
  55 #endif
  56
  57 typedef enum {
  58     FENCE_MAIN_TABLE = 0,       /*GeoFence */
  59     FENCE_GEOCOORDINATE_TAB,    /*FenceGeocoordinate */
  60     FENCE_GEOPOINT_WIFI_TABLE,  /*FenceCurrentLocation */
  61     FENCE_BSSID_TABLE   /*FenceBluetoothBssid */
  62 } fence_table_type_e;
  63
  64 static struct {
  65         sqlite3 *handle;
  66 } db_info_s = {
  67         .handle = NULL,
  68 };
  69
  70 #define SQLITE3_RETURN(ret, msg, state) \
  71         if (ret != SQLITE_OK) { \
  72                 LOGI_GEOFENCE("sqlite3 Error[%d] : %s", ret, msg); \
  73                 sqlite3_reset(state); \
  74                 sqlite3_clear_bindings(state); \
  75                 sqlite3_finalize(state); \
  76                 return FENCE_ERR_SQLITE_FAIL; \
  77         }
  78
  79 /*
  80  * \note
  81  * DB Table schema
  82  *
  83  * GeoFence
  84  * +----------+-------+-------+------------+-------+-------+-----------+---------+
  85  * | fence_id | name     | app_id  | geofence_type |direction |enable    |smart_assist_id|time_stamp
  86  * +-------+-------+-------+-------+
  87  * |   -   |   -   |   -   |   -   |
  88  * +-------+-------+-------+-------+
  89  * CREATE TABLE GeoFence ( fence_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, app_id TEXT NOT NULL, geofence_type INTEGER," \
  90                 "direction INTEGER,  enable INTEGER,  smart_assist_id INTEGER, time_stamp INTEGER)";
  91  *
  92  *
  93  * FenceGeocoordinate
  94  * +----------+---------+--------+------+
  95  * | fence_id | latitude     | longitude | radius
  96  * +-------+---------+-----+---------+
  97  * |   -   |    -    |  -  |    -    |     -    |    -    |
  98  * +-------+---------+-----+---------+
  99  *  CREATE TABLE FenceGeocoordinate ( fence_id INTEGER , latitude DOUBLE, longitude DOUBLE, radius DOUBLE, FOREIGN KEY(fence_id) REFERENCES GeoFence(fence_id) ON DELETE CASCADE)";
 100  *
 101  *
 102  * FenceCurrentLocation
 103  * +-----+-------+------
 104  * |bssid 1|fence_id1 |...
 105  * +-----+-------+------
 106  * |bssid 2|fence_id1|...
 107  * +-----+-------+------
 108  * |bssid 3|fence_id1|...
 109  * +-----+-------+------
 110  * |bssid 1|fence_id2|...
 111  * +-----+-------+------
 112  * |bssid 2|fence_id2|...
 113  * +-------+---------+-----+---------+
 114  * |   -   |    -    |  -  |    -    |     -    |    -    |
 115  * +-------+---------+-----+---------+
 116 *CREATE TABLE FenceCurrentLocation ( fence_id INTEGER,  bssid TEXT,  FOREIGN KEY(fence_id) REFERENCES GeoFence(fence_id) ON DELETE CASCADE)";
 117 */
 118
 119 static inline int begin_transaction(void)
 120 {
 121         FUNC_ENTRANCE_SERVER;
 122         sqlite3_stmt *stmt;
 123         int ret;
 124
 125         ret = sqlite3_prepare_v2(db_info_s.handle, "BEGIN TRANSACTION", -1, &stmt, NULL);
 126
 127         if (ret != SQLITE_OK) {
 128                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 129                 return FENCE_ERR_SQLITE_FAIL;
 130         }
 131
 132         if (sqlite3_step(stmt) != SQLITE_DONE) {
 133                 LOGI_GEOFENCE("Failed to do update (%s)", sqlite3_errmsg(db_info_s.handle));
 134                 sqlite3_finalize(stmt);
 135                 return FENCE_ERR_SQLITE_FAIL;
 136         }
 137
 138         sqlite3_finalize(stmt);
 139         return FENCE_ERR_NONE;
 140 }
 141
 142 static inline int rollback_transaction(void)
 143 {
 144         FUNC_ENTRANCE_SERVER;
 145         int ret;
 146         sqlite3_stmt *stmt;
 147
 148         ret = sqlite3_prepare_v2(db_info_s.handle, "ROLLBACK TRANSACTION", -1, &stmt, NULL);
 149         if (ret != SQLITE_OK) {
 150                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 151                 return FENCE_ERR_SQLITE_FAIL;
 152         }
 153
 154         if (sqlite3_step(stmt) != SQLITE_DONE) {
 155                 LOGI_GEOFENCE("Failed to do update (%s)", sqlite3_errmsg(db_info_s.handle));
 156                 sqlite3_finalize(stmt);
 157                 return FENCE_ERR_SQLITE_FAIL;
 158         }
 159
 160         sqlite3_finalize(stmt);
 161         return FENCE_ERR_NONE;
 162 }
 163
 164 static inline int commit_transaction(void)
 165 {
 166         FUNC_ENTRANCE_SERVER;
 167         sqlite3_stmt *stmt;
 168         int ret;
 169
 170         ret = sqlite3_prepare_v2(db_info_s.handle, "COMMIT TRANSACTION", -1, &stmt, NULL);
 171         if (ret != SQLITE_OK) {
 172                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 173                 return FENCE_ERR_SQLITE_FAIL;
 174         }
 175
 176         if (sqlite3_step(stmt) != SQLITE_DONE) {
 177                 LOGI_GEOFENCE("Failed to do update (%s)", sqlite3_errmsg(db_info_s.handle));
 178                 sqlite3_finalize(stmt);
 179                 return FENCE_ERR_SQLITE_FAIL;
 180         }
 181
 182         sqlite3_finalize(stmt);
 183         return FENCE_ERR_NONE;
 184 }
 185
 186 static inline int __geofence_manager_db_create_places_table(void)
 187 {
 188         FUNC_ENTRANCE_SERVER;
 189         char *err = NULL;
 190         char *ddl;
 191
 192         ddl = sqlite3_mprintf("CREATE TABLE Places ( place_id INTEGER PRIMARY KEY AUTOINCREMENT, access_type INTEGER, place_name TEXT NOT NULL, app_id TEXT NOT NULL)");
 193         if (sqlite3_exec(db_info_s.handle, ddl, NULL, NULL, &err) != SQLITE_OK) {
 194                 LOGI_GEOFENCE("Failed to execute the DDL (%s)", err);
 195                 sqlite3_free(ddl);
 196                 return FENCE_ERR_SQLITE_FAIL;
 197         }
 198
 199         if (sqlite3_changes(db_info_s.handle) == 0) {
 200                 LOGI_GEOFENCE("No changes  to DB");
 201         }
 202         sqlite3_free(ddl);
 203         return FENCE_ERR_NONE;
 204 }
 205
 206 static inline int __geofence_manager_db_create_geofence_table(void)
 207 {
 208         FUNC_ENTRANCE_SERVER;
 209         char *err = NULL;
 210         char *ddl;
 211
 212         ddl = sqlite3_mprintf("CREATE TABLE GeoFence ( fence_id INTEGER PRIMARY KEY AUTOINCREMENT, place_id INTEGER, enable INTEGER, app_id TEXT NOT NULL, geofence_type INTEGER, access_type INTEGER, running_status INTEGER, FOREIGN KEY(place_id) REFERENCES Places(place_id) ON DELETE CASCADE)");
 213
 214         if (sqlite3_exec(db_info_s.handle, ddl, NULL, NULL, &err) != SQLITE_OK) {
 215                 LOGI_GEOFENCE("Failed to execute the DDL (%s)", err);
 216                 sqlite3_free(ddl);
 217                 return FENCE_ERR_SQLITE_FAIL;
 218         }
 219
 220         if (sqlite3_changes(db_info_s.handle) == 0) {
 221                 LOGI_GEOFENCE("No changes  to DB");
 222         }
 223         sqlite3_free(ddl);
 224         return FENCE_ERR_NONE;
 225 }
 226
 227 static inline int __geofence_manager_db_create_geocoordinate_table(void)
 228 {
 229         FUNC_ENTRANCE_SERVER;
 230         char *err = NULL;
 231         char *ddl;
 232
 233         ddl = sqlite3_mprintf("CREATE TABLE FenceGeocoordinate ( fence_id INTEGER PRIMARY KEY, latitude TEXT NOT NULL, longitude TEXT NOT NULL, radius TEXT NOT NULL, address TEXT, FOREIGN KEY(fence_id) REFERENCES GeoFence(fence_id) ON DELETE CASCADE ON UPDATE CASCADE)");
 234
 235         if (sqlite3_exec(db_info_s.handle, ddl, NULL, NULL, &err) != SQLITE_OK) {
 236                 LOGI_GEOFENCE("Failed to execute the DDL (%s)", err);
 237                 sqlite3_free(ddl);
 238                 return FENCE_ERR_SQLITE_FAIL;
 239         }
 240
 241         if (sqlite3_changes(db_info_s.handle) == 0) {
 242                 LOGI_GEOFENCE("No changes to DB");
 243         }
 244         sqlite3_free(ddl);
 245         return FENCE_ERR_NONE;
 246 }
 247
 248 static inline int __geofence_manager_db_create_wifi_data_table(void)
 249 {
 250         FUNC_ENTRANCE_SERVER;
 251         char *err = NULL;
 252         char *ddl;
 253
 254         ddl = sqlite3_mprintf("CREATE TABLE FenceGeopointWifi (fence_id INTEGER, bssid TEXT, FOREIGN KEY(fence_id) REFERENCES GeoFence(fence_id) ON DELETE CASCADE ON UPDATE CASCADE)");
 255
 256         if (sqlite3_exec(db_info_s.handle, ddl, NULL, NULL, &err) != SQLITE_OK) {
 257                 LOGI_GEOFENCE("Failed to execute the DDL (%s)", err);
 258                 sqlite3_free(ddl);
 259                 return FENCE_ERR_SQLITE_FAIL;
 260         }
 261
 262         if (sqlite3_changes(db_info_s.handle) == 0) {
 263                 LOGI_GEOFENCE("No changes to DB");
 264         }
 265         sqlite3_free(ddl);
 266         return FENCE_ERR_NONE;
 267 }
 268
 269 /* DB table for save the pair of fence id and bluetooth bssid */
 270 static inline int __geofence_manager_db_create_bssid_table(void)
 271 {
 272         FUNC_ENTRANCE_SERVER
 273         char *err = NULL;
 274         char *ddl;
 275
 276         ddl = sqlite3_mprintf("CREATE TABLE FenceBssid (fence_id INTEGER PRIMARY KEY, bssid TEXT, ssid TEXT, FOREIGN KEY(fence_id) REFERENCES GeoFence(fence_id) ON DELETE CASCADE ON UPDATE CASCADE)");
 277
 278         if (sqlite3_exec(db_info_s.handle, ddl, NULL, NULL, &err) != SQLITE_OK) {
 279                 LOGI_GEOFENCE("Failed to execute the DDL (%s)", err);
 280                 sqlite3_free(ddl);
 281                 return FENCE_ERR_SQLITE_FAIL;
 282         }
 283
 284         if (sqlite3_changes(db_info_s.handle) == 0)
 285                 LOGI_GEOFENCE("No changes to DB");
 286         sqlite3_free(ddl);
 287         return FENCE_ERR_NONE;
 288 }
 289
 290 static int __geofence_manager_open_db_handle(const int open_flag)
 291 {
 292         LOGI_GEOFENCE("enter");
 293         int ret = SQLITE_OK;
 294
 295         ret = db_util_open_with_options(GEOFENCE_DB_FILE, &db_info_s.handle, open_flag, NULL);
 296         if (ret != SQLITE_OK) {
 297                 LOGI_GEOFENCE("sqlite3_open_v2 Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 298                 return FENCE_ERR_SQLITE_FAIL;
 299         }
 300
 301         return FENCE_ERR_NONE;
 302 }
 303
 304 static int __geofence_manager_db_get_count_by_fence_id_and_bssid(int fence_id, char *bssid, fence_table_type_e table_type, int *count)
 305 {
 306         FUNC_ENTRANCE_SERVER;
 307         g_return_val_if_fail(bssid, FENCE_ERR_INVALID_PARAMETER);
 308         sqlite3_stmt *state = NULL;
 309         int ret = SQLITE_OK;
 310         const char *tail = NULL;
 311
 312         char *query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM %Q where fence_id = %d AND bssid = %Q;", menu_table[table_type], fence_id, bssid);
 313
 314         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 315         if (ret != SQLITE_OK) {
 316                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 317                 sqlite3_free(query);
 318                 return FENCE_ERR_PREPARE;
 319         }
 320
 321         ret = sqlite3_step(state);
 322         if (ret != SQLITE_ROW) {
 323                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 324                 sqlite3_finalize(state);
 325                 sqlite3_free(query);
 326                 return FENCE_ERR_SQLITE_FAIL;
 327         }
 328         *count = sqlite3_column_int(state, 0);
 329         sqlite3_reset(state);
 330         sqlite3_finalize(state);
 331         sqlite3_free(query);
 332
 333         return FENCE_ERR_NONE;
 334 }
 335
 336 static int __geofence_manager_db_insert_bssid_info(const int fence_id, const char *bssid_info, const char *ssid)
 337 {
 338         FUNC_ENTRANCE_SERVER;
 339         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
 340         g_return_val_if_fail(bssid_info, FENCE_ERR_INVALID_PARAMETER);
 341         sqlite3_stmt *state = NULL;
 342         int ret = SQLITE_OK;
 343         int index = 0;
 344         int count = -1;
 345         const char *tail;
 346         char *bssid = NULL;
 347
 348         char *query = sqlite3_mprintf("INSERT INTO %Q(fence_id, bssid, ssid) VALUES (?, ?, ?)", menu_table[FENCE_BSSID_TABLE]);
 349         bssid = (char *)g_malloc0(sizeof(char) * WLAN_BSSID_LEN);
 350         g_strlcpy(bssid, bssid_info, WLAN_BSSID_LEN);
 351         LOGI_GEOFENCE("fence_id[%d], bssid[%s], ssid[%s]", fence_id, bssid, ssid);
 352
 353         ret = __geofence_manager_db_get_count_by_fence_id_and_bssid(fence_id, bssid, FENCE_BSSID_TABLE, &count);
 354         if (ret != FENCE_ERR_NONE) {
 355                 LOGI_GEOFENCE("__geofence_manager_db_get_count_by_fence_id_and_bssid() failed. ERROR(%d)", ret);
 356                 sqlite3_free(query);
 357                 return ret;
 358         }
 359         if (count > 0) {
 360                 LOGI_GEOFENCE("count = %d", count);
 361                 return FENCE_ERR_NONE;
 362         }
 363
 364         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 365         if (ret != SQLITE_OK) {
 366                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 367                 sqlite3_free(query);
 368                 return FENCE_ERR_PREPARE;
 369         }
 370
 371         ret = sqlite3_bind_int(state, ++index, fence_id);
 372         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 373
 374         ret = sqlite3_bind_text(state, ++index, bssid, -1, SQLITE_STATIC);
 375         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 376
 377         ret = sqlite3_bind_text(state, ++index, ssid, -1, SQLITE_STATIC);
 378         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 379
 380         ret = sqlite3_step(state);
 381         if (ret != SQLITE_DONE) {
 382                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 383                 sqlite3_finalize(state);
 384                 g_free(bssid);
 385                 sqlite3_free(query);
 386                 return FENCE_ERR_SQLITE_FAIL;
 387         }
 388
 389         sqlite3_reset(state);
 390         sqlite3_clear_bindings(state);
 391         sqlite3_finalize(state);
 392         g_free(bssid);
 393         sqlite3_free(query);
 394         LOGI_GEOFENCE("fence_id[%d], bssid[%s], ssid[%s] inserted db table [%s] successfully.", fence_id, bssid_info, ssid, menu_table[FENCE_BSSID_TABLE]);
 395
 396         return FENCE_ERR_NONE;
 397 }
 398
 399 static int __geofence_manager_db_insert_wifi_data_info(gpointer data, gpointer user_data)
 400 {
 401         FUNC_ENTRANCE_SERVER;
 402         g_return_val_if_fail(data, FENCE_ERR_INVALID_PARAMETER);
 403         g_return_val_if_fail(user_data, FENCE_ERR_INVALID_PARAMETER);
 404         int *fence_id = (int *) user_data;
 405         sqlite3_stmt *state = NULL;
 406         wifi_info_s *wifi_info = NULL;
 407         int ret = SQLITE_OK;
 408         int index = 0;
 409         int count = -1;
 410         const char *tail;
 411         char *bssid = NULL;
 412         wifi_info = (wifi_info_s *) data;
 413         bssid = (char *)g_malloc0(sizeof(char) * WLAN_BSSID_LEN);
 414         g_strlcpy(bssid, wifi_info->bssid, WLAN_BSSID_LEN);
 415         LOGI_GEOFENCE("fence_id[%d] bssid[%s]", *fence_id, wifi_info->bssid);
 416
 417         char *query = sqlite3_mprintf("INSERT INTO FenceGeopointWifi(fence_id, bssid) VALUES (?, ?)");
 418
 419         ret = __geofence_manager_db_get_count_by_fence_id_and_bssid(*fence_id, bssid, FENCE_GEOPOINT_WIFI_TABLE, &count);
 420         if (count > 0) {
 421                 LOGI_GEOFENCE("count = %d", count);
 422                 sqlite3_free(query);
 423                 return ret;
 424         }
 425
 426         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 427         if (ret != SQLITE_OK) {
 428                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 429                 sqlite3_free(query);
 430                 return FENCE_ERR_PREPARE;
 431         }
 432
 433         ret = sqlite3_bind_int(state, ++index, *fence_id);
 434         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 435
 436         ret = sqlite3_bind_text(state, ++index, bssid, -1, SQLITE_STATIC);
 437         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 438
 439         ret = sqlite3_step(state);
 440         if (ret != SQLITE_DONE) {
 441                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 442                 sqlite3_finalize(state);
 443                 g_free(bssid);
 444                 sqlite3_free(query);
 445                 return FENCE_ERR_SQLITE_FAIL;
 446         }
 447
 448         sqlite3_reset(state);
 449         sqlite3_clear_bindings(state);
 450         sqlite3_finalize(state);
 451         g_free(bssid);
 452         sqlite3_free(query);
 453
 454         return FENCE_ERR_NONE;
 455 }
 456
 457 static int __geofence_manager_delete_table(int fence_id, fence_table_type_e table_type)
 458 {
 459         FUNC_ENTRANCE_SERVER;
 460         sqlite3_stmt *state = NULL;
 461         int ret = SQLITE_OK;
 462
 463         char *query = sqlite3_mprintf("DELETE from %Q where fence_id = %d;", menu_table[table_type], fence_id);
 464         LOGI_GEOFENCE("current fence id is [%d]", fence_id);
 465         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, NULL);
 466         if (SQLITE_OK != ret) {
 467                 LOGI_GEOFENCE("Fail to connect to table. Error[%s]", sqlite3_errmsg(db_info_s.handle));
 468                 sqlite3_free(query);
 469                 return FENCE_ERR_SQLITE_FAIL;
 470         }
 471
 472         ret = sqlite3_step(state);
 473         if (SQLITE_DONE != ret) {
 474                 LOGI_GEOFENCE("Fail to step. Error[%d]", ret);
 475                 sqlite3_finalize(state);
 476                 sqlite3_free(query);
 477                 return FENCE_ERR_SQLITE_FAIL;
 478         }
 479         sqlite3_finalize(state);
 480         LOGI_GEOFENCE("fence_id[%d], deleted from db table [%s] successfully.", fence_id, menu_table[table_type]);
 481         sqlite3_free(query);
 482         return FENCE_ERR_NONE;
 483 }
 484
 485 static int __geofence_manager_delete_place_table(int place_id)
 486 {
 487         FUNC_ENTRANCE_SERVER;
 488         sqlite3_stmt *state = NULL;
 489         int ret = SQLITE_OK;
 490
 491         char *query = sqlite3_mprintf("DELETE from Places where place_id = %d;", place_id);
 492
 493         LOGI_GEOFENCE("current place id is [%d]", place_id);
 494         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, NULL);
 495         if (SQLITE_OK != ret) {
 496                 LOGI_GEOFENCE("Fail to connect to table. Error[%s]", sqlite3_errmsg(db_info_s.handle));
 497                 sqlite3_free(query);
 498                 return FENCE_ERR_SQLITE_FAIL;
 499         }
 500
 501         ret = sqlite3_step(state);
 502         if (SQLITE_DONE != ret) {
 503                 LOGI_GEOFENCE("Fail to step. Error[%d]", ret);
 504                 sqlite3_finalize(state);
 505                 sqlite3_free(query);
 506                 return FENCE_ERR_SQLITE_FAIL;
 507         }
 508         sqlite3_finalize(state);
 509         LOGI_GEOFENCE("place_id[%d], deleted place from db table Places successfully.", place_id);
 510         sqlite3_free(query);
 511         return FENCE_ERR_NONE;
 512 }
 513
 514 static inline void __geofence_manager_db_create_table(void)
 515 {
 516         FUNC_ENTRANCE_SERVER;
 517         int ret;
 518         begin_transaction();
 519
 520         ret = __geofence_manager_db_create_places_table();
 521         if (ret < 0) {
 522                 rollback_transaction();
 523                 return;
 524         }
 525
 526         ret = __geofence_manager_db_create_geofence_table();
 527         if (ret < 0) {
 528                 rollback_transaction();
 529                 return;
 530         }
 531
 532         ret = __geofence_manager_db_create_geocoordinate_table();
 533         if (ret < 0) {
 534                 rollback_transaction();
 535                 return;
 536         }
 537
 538         ret = __geofence_manager_db_create_wifi_data_table();
 539         if (ret < 0) {
 540                 rollback_transaction();
 541                 return;
 542         }
 543
 544         ret = __geofence_manager_db_create_bssid_table();
 545         if (ret < 0) {
 546                 rollback_transaction();
 547                 return;
 548         }
 549
 550         commit_transaction();
 551 }
 552
 553 /* Get fence id count in certain table, such as GeoFence/FenceGeocoordinate/FenceCurrentLocation */
 554 static int __geofence_manager_db_get_count_of_fence_id(int fence_id, fence_table_type_e table_type, int *count)
 555 {
 556         sqlite3_stmt *state = NULL;
 557         int ret = SQLITE_OK;
 558         const char *tail = NULL;
 559
 560         char *query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM %Q where fence_id = %d;", menu_table[table_type], fence_id);
 561
 562         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 563         if (ret != SQLITE_OK) {
 564                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 565                 sqlite3_free(query);
 566                 return FENCE_ERR_PREPARE;
 567         }
 568
 569         ret = sqlite3_step(state);
 570         if (ret != SQLITE_ROW) {
 571                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 572                 sqlite3_finalize(state);
 573                 sqlite3_free(query);
 574                 return FENCE_ERR_SQLITE_FAIL;
 575         }
 576         *count = sqlite3_column_int(state, 0);
 577         sqlite3_reset(state);
 578         sqlite3_finalize(state);
 579         sqlite3_free(query);
 580         return FENCE_ERR_NONE;
 581 }
 582
 583 static int __geofence_manager_db_enable_foreign_keys(void)
 584 {
 585         sqlite3_stmt *state = NULL;
 586         int ret = FENCE_ERR_NONE;
 587         char *query = sqlite3_mprintf("PRAGMA foreign_keys = ON;");
 588
 589         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, NULL);
 590         if (SQLITE_OK != ret) {
 591                 LOGI_GEOFENCE("Fail to connect to table. Error[%s]", sqlite3_errmsg(db_info_s.handle));
 592                 sqlite3_free(query);
 593                 return FENCE_ERR_SQLITE_FAIL;
 594         }
 595
 596         ret = sqlite3_step(state);
 597         if (SQLITE_DONE != ret) {
 598                 LOGI_GEOFENCE("Fail to step. Error[%d]", ret);
 599                 sqlite3_finalize(state);
 600                 sqlite3_free(query);
 601                 return FENCE_ERR_SQLITE_FAIL;
 602         }
 603         sqlite3_reset(state);
 604         sqlite3_finalize(state);
 605         sqlite3_free(query);
 606         return FENCE_ERR_NONE;
 607 }
 608
 609 #ifdef SUPPORT_ENCRYPTION
 610 void replaceChar(char *src, char oldChar, char newChar)
 611 {
 612         while (*src) {
 613                 if (*src == oldChar)
 614                         *src = newChar;
 615                 src++;
 616         }
 617 }
 618
 619 void __geofence_manager_generate_password(char *password)
 620 {
 621         char *bt_address = NULL;
 622         char *wifi_address = NULL;
 623         char *token = NULL, *save_token = NULL;
 624         int bt_temp[6] = {0}, wifi_temp[6] = {0};
 625         int i = 0, fkey[6], lkey[6];
 626         char s1[100], s2[100], result[200];
 627         char keyword[6] = { 'b', 'w', 'd', 's', 'j', 'f' };
 628         int ret = 0;
 629
 630         ret = bt_adapter_get_address(&bt_address);
 631         if (ret != BT_ERROR_NONE) {
 632                 LOGD_GEOFENCE("bt address get fail %d", ret);
 633         }
 634
 635         ret = wifi_get_mac_address(&wifi_address);
 636         if (ret != WIFI_ERROR_NONE) {
 637                 LOGD_GEOFENCE("wifi address get fail %d", ret);
 638         }
 639
 640         if (bt_address) {
 641                 token = strtok_r(bt_address, ":", &save_token);
 642                 i = 0;
 643                 while (token) {
 644                         bt_temp[i++] = atoi(token);
 645                         token = strtok_r(NULL, ":", &save_token);
 646                         if (i >= 6)
 647                                 break;
 648                 }
 649         }
 650
 651         if (wifi_address) {
 652                 token = strtok_r(wifi_address, ":", &save_token);
 653                 i = 0;
 654                 while (token) {
 655                         wifi_temp[i++] = atoi(token);
 656                         token = strtok_r(NULL, ":", &save_token);
 657                         if (i >= 6)
 658                                 break;
 659                 }
 660         }
 661
 662         memset((void *) s1, 0, sizeof(s1));
 663         memset((void *) s2, 0, sizeof(s2));
 664         memset((void *) result, 0, sizeof(result));
 665
 666         for (i = 0; i < 6; i++) {
 667                 fkey[i] = bt_temp[i] * wifi_temp[i];
 668                 lkey[i] = bt_temp[i] + wifi_temp[i];
 669         }
 670
 671         for (i = 0; i < 6; i++) {
 672                 sprintf(s1, "%s%x", s1, fkey[i]);
 673                 sprintf(s2, "%s%x", s2, lkey[i]);
 674                 replaceChar(s1, 0x30 + ((i * 2) % 10), keyword[i]);
 675                 replaceChar(s2, 0x30 + ((i * 2 + 1) % 10), keyword[i]);
 676                 LOGD_GEOFENCE("s1 %s", s1);
 677                 LOGD_GEOFENCE("s2 %s", s2);
 678         }
 679
 680         sprintf(result, "%s%s", s1, s2);
 681         LOGD_GEOFENCE("result : %s", result);
 682
 683         password = result;
 684
 685         if (bt_address != NULL)
 686                 free(bt_address);
 687         if (wifi_address != NULL)
 688                 free(wifi_address);
 689 }
 690 #endif
 691
 692 static int __check_db_file()
 693 {
 694         int fd = -1;
 695
 696     fd = open(GEOFENCE_DB_FILE, O_RDONLY);
 697     if (fd < 0) {
 698              LOGW_GEOFENCE("DB file(%s) is not exist.", GEOFENCE_DB_FILE);
 699              return -1;
 700      }
 701      close(fd);
 702      return 0;
 703 }
 704
 705 /**
 706  * This function in DB and create  GeoFence/FenceGeocoordinate /FenceCurrentLocation four table on DB if necessary.
 707  *
 708  * @param[in]    struct of  fence_point_info_s
 709  * @return         FENCE_ERR_NONE on success, negative values for errors
 710  */
 711 int geofence_manager_db_init(void)
 712 {
 713         FUNC_ENTRANCE_SERVER;
 714         struct stat stat;
 715         int open_flag = 0;
 716
 717         if (__check_db_file()) {
 718                 LOGW_GEOFENCE("db(%s) file doesn't exist.", GEOFENCE_DB_FILE);
 719                 open_flag = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE| SQLITE_OPEN_FULLMUTEX;
 720         } else {
 721                 if (lstat(GEOFENCE_DB_FILE, &stat) < 0) {
 722                         LOGE_GEOFENCE("Can't get db(%s) information.", GEOFENCE_DB_FILE);
 723                         return FENCE_ERR_SQLITE_FAIL;
 724                 }
 725                 open_flag = SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX;
 726         }
 727
 728         if (__geofence_manager_open_db_handle(open_flag) != FENCE_ERR_NONE) {
 729                 LOGI_GEOFENCE("Fail to create db file(%s).", GEOFENCE_DB_FILE);
 730                 return FENCE_ERR_SQLITE_FAIL;
 731         }
 732
 733         if (open_flag & SQLITE_OPEN_CREATE)
 734                 __geofence_manager_db_create_table();
 735
 736         return FENCE_ERR_NONE;
 737 }
 738
 739 int geofence_manager_db_reset(void)
 740 {
 741         FUNC_ENTRANCE_SERVER;
 742         sqlite3_stmt *state = NULL;
 743         int idx = 0;
 744         int ret = SQLITE_OK;
 745         char *query = NULL;
 746
 747         for (idx = 0; idx < 4; idx++) {
 748                 query = sqlite3_mprintf("DELETE from %Q;", menu_table[idx]);
 749
 750                 ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, NULL);
 751                 if (SQLITE_OK != ret) {
 752                         LOGI_GEOFENCE("Fail to connect to table. Error[%s]", sqlite3_errmsg(db_info_s.handle));
 753                         sqlite3_free(query);
 754                         return FENCE_ERR_SQLITE_FAIL;
 755                 }
 756
 757                 ret = sqlite3_step(state);
 758                 if (SQLITE_DONE != ret) {
 759                         LOGI_GEOFENCE("Fail to step. Error[%d]", ret);
 760                         sqlite3_finalize(state);
 761                         sqlite3_free(query);
 762                         return FENCE_ERR_SQLITE_FAIL;
 763                 }
 764                 sqlite3_finalize(state);
 765                 sqlite3_free(query);
 766         }
 767         return FENCE_ERR_NONE;
 768 }
 769
 770 int geofence_manager_set_place_info(place_info_s *place_info, int *place_id)
 771 {
 772         FUNC_ENTRANCE_SERVER;
 773         g_return_val_if_fail(place_info, FENCE_ERR_INVALID_PARAMETER);
 774         g_return_val_if_fail(place_id, FENCE_ERR_INVALID_PARAMETER);
 775         sqlite3_stmt *state = NULL;
 776         int ret = SQLITE_OK;
 777         int index = 0;
 778         const char *tail;
 779         char *appid = NULL;
 780         char *place_name = NULL;
 781         char *query = sqlite3_mprintf("INSERT INTO Places (access_type, place_name, app_id) VALUES (?, ?, ?)");
 782
 783         place_name = (char *)g_malloc0(sizeof(char) * PLACE_NAME_LEN);
 784         g_strlcpy(place_name, place_info->place_name, PLACE_NAME_LEN);
 785         appid = (char *)g_malloc0(sizeof(char) * APP_ID_LEN);
 786         g_strlcpy(appid, place_info->appid, APP_ID_LEN);
 787
 788         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 789         if (ret != SQLITE_OK) {
 790                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 791                 sqlite3_free(query);
 792                 return FENCE_ERR_PREPARE;
 793         }
 794         LOGD_GEOFENCE("appid[%s] access_type[%d] place_name[%s]", appid, place_info->access_type, place_info->place_name);
 795
 796         ret = sqlite3_bind_int(state, ++index, place_info->access_type);
 797         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 798
 799         ret = sqlite3_bind_text(state, ++index, place_name, -1, SQLITE_STATIC);
 800         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 801
 802         ret = sqlite3_bind_text(state, ++index, appid, -1, SQLITE_STATIC);
 803         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 804
 805         ret = sqlite3_step(state);
 806         if (ret != SQLITE_DONE) {
 807                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 808                 sqlite3_finalize(state);
 809                 g_free(place_name);
 810                 g_free(appid);
 811                 sqlite3_free(query);
 812                 return FENCE_ERR_SQLITE_FAIL;
 813         }
 814         *place_id = sqlite3_last_insert_rowid(db_info_s.handle);
 815         LOGI_GEOFENCE(" auto-genarated place_id[%d]", *place_id);
 816         sqlite3_reset(state);
 817         sqlite3_clear_bindings(state);
 818         sqlite3_finalize(state);
 819         g_free(place_name);
 820         g_free(appid);
 821         sqlite3_free(query);
 822
 823         if (*place_id < 1) {
 824                 LOGI_GEOFENCE("TMP Invalid fence_id");
 825                 *place_id = 0;
 826         }
 827
 828         return FENCE_ERR_NONE;
 829 }
 830
 831 int geofence_manager_set_common_info(fence_common_info_s *fence_info, int *fence_id)
 832 {
 833         FUNC_ENTRANCE_SERVER;
 834         g_return_val_if_fail(fence_info, FENCE_ERR_INVALID_PARAMETER);
 835         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
 836         sqlite3_stmt *state = NULL;
 837         int ret = SQLITE_OK;
 838         int index = 0;
 839         const char *tail;
 840         char *appid = NULL;
 841         char *query = sqlite3_mprintf("INSERT INTO GeoFence (place_id, enable, app_id, geofence_type, access_type, running_status) VALUES (?, ?, ?, ?, ?, ?)");
 842         appid = (char *)g_malloc0(sizeof(char) * APP_ID_LEN);
 843         g_strlcpy(appid, fence_info->appid, APP_ID_LEN);
 844
 845         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 846         if (ret != SQLITE_OK) {
 847                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 848                 sqlite3_free(query);
 849                 return FENCE_ERR_PREPARE;
 850         }
 851
 852         LOGD_GEOFENCE("place_id[%d], enable[%d], appid[%s] geofence_type[%d] access_type[%d] running_status[%d]", fence_info->place_id, fence_info->enable, appid, fence_info->running_status, fence_info->type, fence_info->access_type, fence_info->place_id);
 853
 854         ret = sqlite3_bind_int(state, ++index, fence_info->place_id);
 855         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 856
 857         ret = sqlite3_bind_int(state, ++index, fence_info->enable);
 858         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 859
 860         ret = sqlite3_bind_text(state, ++index, appid, -1, SQLITE_STATIC);
 861         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 862
 863         ret = sqlite3_bind_int(state, ++index, fence_info->type);
 864         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 865
 866         ret = sqlite3_bind_int(state, ++index, fence_info->access_type);
 867         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 868
 869         ret = sqlite3_bind_int(state, ++index, fence_info->running_status);
 870         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
 871
 872         ret = sqlite3_step(state);
 873         if (ret != SQLITE_DONE) {
 874                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
 875                 sqlite3_finalize(state);
 876                 g_free(appid);
 877                 sqlite3_free(query);
 878                 return FENCE_ERR_SQLITE_FAIL;
 879         }
 880         *fence_id = sqlite3_last_insert_rowid(db_info_s.handle);
 881         LOGI_GEOFENCE(" auto-genarated fence_id[%d]", *fence_id);
 882         sqlite3_reset(state);
 883         sqlite3_clear_bindings(state);
 884         sqlite3_finalize(state);
 885         g_free(appid);
 886         sqlite3_free(query);
 887
 888         if (*fence_id < 1) {
 889                 LOGI_GEOFENCE("TMP Invalid fence_id");
 890                 *fence_id = 0;
 891         }
 892
 893         return FENCE_ERR_NONE;
 894 }
 895
 896 int geofence_manager_get_place_list_from_db(int *number_of_places, GList **places)
 897 {
 898         FUNC_ENTRANCE_SERVER;
 899         sqlite3_stmt *state = NULL;
 900         int ret = SQLITE_OK;
 901         const char *tail = NULL;
 902         char *query = NULL;
 903         int count = 0;
 904
 905         query = sqlite3_mprintf("SELECT place_id, place_name, access_type, app_id FROM Places");
 906
 907         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 908         if (ret != SQLITE_OK) {
 909                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 910                 sqlite3_free(query);
 911                 return FENCE_ERR_PREPARE;
 912         }
 913         GList *place_list = NULL;
 914         int column_index = 0;
 915         do {
 916                 ret = sqlite3_step(state);
 917
 918                 if (ret != SQLITE_ROW) {
 919                         LOGI_GEOFENCE("DONE...!!! : %d", ret);
 920                         break;
 921                 }
 922                 column_index = 0;
 923                 place_info_s *place = g_slice_new0(place_info_s);
 924
 925                 if (place == NULL)
 926                         continue;
 927
 928                 place->place_id = sqlite3_column_int(state, column_index++);
 929                 g_strlcpy(place->place_name, (char *) sqlite3_column_text(state, column_index++), PLACE_NAME_LEN);
 930                 place->access_type = sqlite3_column_int(state, column_index++);
 931                 g_strlcpy(place->appid, (char *) sqlite3_column_text(state, column_index++), APP_ID_LEN);
 932                 place_list = g_list_append(place_list, place);
 933                 count++;
 934         } while (ret != SQLITE_DONE);
 935
 936         *places = place_list;
 937         *number_of_places = count;
 938
 939         sqlite3_reset(state);
 940         sqlite3_finalize(state);
 941         sqlite3_free(query);
 942         return FENCE_ERR_NONE;
 943 }
 944
 945 int geofence_manager_get_fence_list_from_db(int *number_of_fences, GList **fences, int place_id)
 946 {
 947         FUNC_ENTRANCE_SERVER;
 948
 949         sqlite3_stmt *state = NULL;
 950         int ret = SQLITE_OK;
 951         const char *tail = NULL;
 952         char *query = NULL;
 953         int count = 0;
 954
 955         if (place_id == -1)
 956                 query = sqlite3_mprintf("SELECT DISTINCT A.fence_id, A.app_id, A.geofence_type, A.access_type, A.place_id, B.latitude, B.longitude, B.radius, B.address, C.bssid, C.ssid FROM GeoFence A LEFT JOIN FenceGeocoordinate B ON A.fence_id = B.fence_id LEFT JOIN FenceBssid C ON A.fence_id = C.fence_id GROUP BY A.fence_id");
 957         else
 958                 query = sqlite3_mprintf("SELECT DISTINCT A.fence_id, A.app_id, A.geofence_type, A.access_type, A.place_id, B.latitude, B.longitude, B.radius, B.address, C.bssid, C.ssid FROM GeoFence A LEFT JOIN FenceGeocoordinate B ON A.fence_id = B.fence_id LEFT JOIN FenceBssid C ON A.fence_id = C.fence_id WHERE A.place_id = %d GROUP BY A.fence_id", place_id);
 959
 960         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
 961         if (ret != SQLITE_OK) {
 962                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
 963                 sqlite3_free(query);
 964                 return FENCE_ERR_PREPARE;
 965         }
 966         GList *fence_list = NULL;
 967         do {
 968                 ret = sqlite3_step(state);
 969
 970                 if (ret != SQLITE_ROW) {
 971                         LOGI_GEOFENCE("DONE...!!! : %d", ret);
 972                         break;
 973                 }
 974                 int column_index = 0;
 975
 976                 geofence_info_s *fence = g_slice_new0(geofence_info_s);
 977
 978                 if (fence == NULL)
 979                         continue;
 980
 981                 fence->fence_id = sqlite3_column_int(state, column_index++);
 982                 g_strlcpy(fence->app_id, (char *) sqlite3_column_text(state, column_index++), APP_ID_LEN);
 983                 fence->param.type = sqlite3_column_int(state, column_index++);
 984                 fence->access_type = sqlite3_column_int(state, column_index++);
 985                 fence->param.place_id = sqlite3_column_int(state, column_index++);
 986                 char *data_name = NULL;
 987
 988                 data_name = (char *) sqlite3_column_text(state, column_index++);
 989                 if (!data_name || !strlen(data_name))
 990                         LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
 991                 else
 992                         fence->param.latitude = atof(data_name);
 993
 994                 data_name = (char *) sqlite3_column_text(state, column_index++);
 995                 if (!data_name || !strlen(data_name))
 996                         LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
 997                 else
 998                         fence->param.longitude = atof(data_name);
 999
1000                 data_name = (char *) sqlite3_column_text(state, column_index++);
1001                 if (!data_name || !strlen(data_name))
1002                         LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
1003                 else
1004                         fence->param.radius = atof(data_name);
1005
1006                 g_strlcpy(fence->param.address, (char *) sqlite3_column_text(state, column_index++), ADDRESS_LEN);
1007                 g_strlcpy(fence->param.bssid, (char *) sqlite3_column_text(state, column_index++), WLAN_BSSID_LEN);
1008                 g_strlcpy(fence->param.ssid, (char *) sqlite3_column_text(state, column_index++), WLAN_BSSID_LEN);
1009                 LOGI_GEOFENCE("radius = %d, bssid = %s", fence->param.radius, fence->param.bssid);
1010                 fence_list = g_list_append(fence_list, fence);
1011                 count++;
1012         } while (ret != SQLITE_DONE);
1013
1014         *fences = fence_list;
1015         *number_of_fences = count;
1016
1017         sqlite3_reset(state);
1018         sqlite3_finalize(state);
1019         sqlite3_free(query);
1020         return FENCE_ERR_NONE;
1021 }
1022
1023 int geofence_manager_get_fenceid_list_from_db(int *number_of_fences, GList **fences, int place_id)
1024 {
1025         FUNC_ENTRANCE_SERVER;
1026         sqlite3_stmt *state = NULL;
1027         int ret = SQLITE_OK;
1028         const char *tail = NULL;
1029         char *query = NULL;
1030         int count = 0;
1031         query = sqlite3_mprintf("SELECT fence_id FROM GeoFence WHERE place_id = %d", place_id);
1032
1033         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1034         if (ret != SQLITE_OK) {
1035                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1036                 sqlite3_free(query);
1037                 return FENCE_ERR_PREPARE;
1038         }
1039         GList *fence_list = NULL;
1040         int column_index = 0;
1041         int fence_id = 0;
1042         do {
1043                 ret = sqlite3_step(state);
1044                 if (ret != SQLITE_ROW) {
1045                         LOGI_GEOFENCE("DONE...!!! : %d", ret);
1046                         break;
1047                 }
1048                 fence_id = 0;
1049                 fence_id = sqlite3_column_int(state, column_index);
1050                 fence_list = g_list_append(fence_list, GINT_TO_POINTER(fence_id));
1051                 count++;
1052         } while (ret != SQLITE_DONE);
1053         *fences = fence_list;
1054         *number_of_fences = count;
1055
1056         sqlite3_reset(state);
1057         sqlite3_finalize(state);
1058         sqlite3_free(query);
1059         return FENCE_ERR_NONE;
1060 }
1061
1062 int geofence_manager_update_geocoordinate_info(int fence_id, geocoordinate_info_s *geocoordinate_info)
1063 {
1064         FUNC_ENTRANCE_SERVER;
1065         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
1066         g_return_val_if_fail(geocoordinate_info, FENCE_ERR_INVALID_PARAMETER);
1067         sqlite3_stmt *state = NULL;
1068         const char *tail;
1069         int ret = SQLITE_OK;
1070
1071         char *query = sqlite3_mprintf("UPDATE FenceGeocoordinate SET latitude = %lf, longitude = %lf, radius = %lf where fence_id = %d;", geocoordinate_info->latitude, geocoordinate_info->longitude, geocoordinate_info->radius, fence_id);
1072
1073         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1074         if (ret != SQLITE_OK) {
1075                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1076                 sqlite3_free(query);
1077                 return FENCE_ERR_PREPARE;
1078         }
1079
1080         ret = sqlite3_step(state);
1081         if (ret != SQLITE_DONE) {
1082                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1083                 sqlite3_finalize(state);
1084                 sqlite3_free(query);
1085                 return FENCE_ERR_SQLITE_FAIL;
1086         }
1087
1088         sqlite3_finalize(state);
1089         sqlite3_free(query);
1090         LOGI_GEOFENCE("fence_id: %d has been successfully updated.", fence_id);
1091         return FENCE_ERR_NONE;
1092 }
1093
1094 int geofence_manager_update_place_info(int place_id, const char *place_info_name)
1095 {
1096         FUNC_ENTRANCE_SERVER;
1097         g_return_val_if_fail(place_id, FENCE_ERR_INVALID_PARAMETER);
1098         sqlite3_stmt *state = NULL;
1099         const char *tail;
1100         int ret = SQLITE_OK;
1101         char *place_name = NULL;
1102
1103         place_name = (char *)g_malloc0(sizeof(char) * PLACE_NAME_LEN);
1104         g_strlcpy(place_name, place_info_name, PLACE_NAME_LEN);
1105
1106         char *query = sqlite3_mprintf("UPDATE Places SET place_name = %Q where place_id = %d", place_name, place_id);
1107
1108         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1109         if (ret != SQLITE_OK) {
1110                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1111                 sqlite3_free(query);
1112                 g_free(place_name);
1113                 return FENCE_ERR_PREPARE;
1114         }
1115
1116         ret = sqlite3_step(state);
1117         if (ret != SQLITE_DONE) {
1118                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1119                 sqlite3_finalize(state);
1120                 g_free(place_name);
1121                 sqlite3_free(query);
1122                 return FENCE_ERR_SQLITE_FAIL;
1123         }
1124
1125         sqlite3_finalize(state);
1126         g_free(place_name);
1127         sqlite3_free(query);
1128         LOGI_GEOFENCE("place_id: %d has been successfully updated.", place_id);
1129         return FENCE_ERR_NONE;
1130 }
1131
1132 /**
1133  * This function set geocoordinate info  in DB.
1134  *
1135  * @param[in]           fence_id
1136  * @param[out]          struct of geocoordinate_info_s
1137  * @return              FENCE_ERR_NONE on success, negative values for errors
1138  */
1139 int geofence_manager_set_geocoordinate_info(int fence_id, geocoordinate_info_s *geocoordinate_info)
1140 {
1141         FUNC_ENTRANCE_SERVER;
1142         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
1143         g_return_val_if_fail(geocoordinate_info, FENCE_ERR_INVALID_PARAMETER);
1144         sqlite3_stmt *state = NULL;
1145         int ret = SQLITE_OK;
1146         int index = 0;
1147         const char *tail;
1148         int count = -1;
1149         char data_name_lat[MAX_DATA_NAME] = { 0 };
1150         char data_name_lon[MAX_DATA_NAME] = { 0 };
1151         char data_name_rad[MAX_DATA_NAME] = { 0 };
1152         char *query = sqlite3_mprintf("INSERT INTO FenceGeocoordinate(fence_id, latitude, longitude, radius, address) VALUES (?, ?, ?, ?, ?)");
1153
1154         ret = __geofence_manager_db_get_count_of_fence_id(fence_id, FENCE_GEOCOORDINATE_TAB, &count);
1155         if (ret != FENCE_ERR_NONE) {
1156                 LOGI_GEOFENCE("Fail to get geofence_manager_db_get_count_of_fence_id [%d]", ret);
1157                 sqlite3_free(query);
1158                 return ret;
1159         } else if (count) {     /* fence id has been in FenceGeocoordinate table */
1160                 sqlite3_free(query);
1161                 return FENCE_ERR_FENCE_ID;
1162         }
1163
1164         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1165         if (ret != SQLITE_OK) {
1166                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1167                 sqlite3_free(query);
1168                 return FENCE_ERR_PREPARE;
1169         }
1170
1171         ret = sqlite3_bind_int(state, ++index, fence_id);
1172         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
1173
1174 #ifdef SUPPORT_ENCRYPTION
1175         if (password == NULL)
1176                 __geofence_manager_generate_password(password);
1177 #endif
1178
1179         ret = snprintf(data_name_lat, MAX_DATA_NAME, "%lf", geocoordinate_info->latitude);
1180
1181         ret = sqlite3_bind_text(state, ++index, data_name_lat, -1, SQLITE_STATIC);
1182
1183         /*ret = sqlite3_bind_double (state, ++index, geocoordinate_info->latitude);*/
1184         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
1185
1186         ret = snprintf(data_name_lon, MAX_DATA_NAME, "%lf", geocoordinate_info->longitude);
1187         if (ret < 0) {
1188                 LOGD_GEOFENCE("ERROR: String will be truncated");
1189                 return FENCE_ERR_STRING_TRUNCATED;
1190         }
1191
1192         ret = sqlite3_bind_text(state, ++index, data_name_lon, -1, SQLITE_STATIC);
1193         /*ret = sqlite3_bind_double (state, ++index, geocoordinate_info->longitude);*/
1194         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
1195
1196         ret = snprintf(data_name_rad, MAX_DATA_NAME, "%lf", geocoordinate_info->radius);
1197         if (ret < 0) {
1198                 LOGD_GEOFENCE("ERROR: String will be truncated");
1199                 return FENCE_ERR_STRING_TRUNCATED;
1200         }
1201
1202         ret = sqlite3_bind_text(state, ++index, data_name_rad, -1, SQLITE_STATIC);
1203         /*ret = sqlite3_bind_double (state, ++index, geocoordinate_info->radius);*/
1204         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
1205
1206         ret = sqlite3_bind_text(state, ++index, geocoordinate_info->address, -1, SQLITE_STATIC);
1207         SQLITE3_RETURN(ret, sqlite3_errmsg(db_info_s.handle), state);
1208
1209         ret = sqlite3_step(state);
1210         if (ret != SQLITE_DONE) {
1211                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1212                 sqlite3_finalize(state);
1213                 return FENCE_ERR_SQLITE_FAIL;
1214         }
1215
1216         sqlite3_reset(state);
1217         sqlite3_clear_bindings(state);
1218         sqlite3_finalize(state);
1219         sqlite3_free(query);
1220
1221         return FENCE_ERR_NONE;
1222 }
1223
1224 /**
1225  * This function get geocoordinate info from DB.
1226  *
1227  * @param[in]   fence_id
1228  * @param[out]  struct of geocoordinate_info_s
1229  * @return      FENCE_ERR_NONE on success, negative values for errors
1230  */
1231 int geofence_manager_get_geocoordinate_info(int fence_id, geocoordinate_info_s **geocoordinate_info)
1232 {
1233         FUNC_ENTRANCE_SERVER;
1234         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
1235         sqlite3_stmt *state = NULL;
1236         int ret = SQLITE_OK;
1237         const char *tail = NULL;
1238         int index = 0;
1239         char *data_name = NULL;
1240         char *query = sqlite3_mprintf("SELECT * FROM FenceGeocoordinate where fence_id = %d;", fence_id);
1241
1242         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1243         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1244         if (ret != SQLITE_OK) {
1245                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1246                 sqlite3_free(query);
1247                 return FENCE_ERR_PREPARE;
1248         }
1249
1250         ret = sqlite3_step(state);
1251         if (ret != SQLITE_ROW) {
1252                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1253                 sqlite3_finalize(state);
1254                 sqlite3_free(query);
1255                 return FENCE_ERR_SQLITE_FAIL;
1256         }
1257
1258         *geocoordinate_info = (geocoordinate_info_s *)g_malloc0(sizeof(geocoordinate_info_s));
1259         g_return_val_if_fail(*geocoordinate_info, FENCE_ERR_INVALID_PARAMETER);
1260
1261 #ifdef SUPPORT_ENCRYPTION
1262         if (password == NULL)
1263                 __geofence_manager_generate_password(password);
1264 #endif
1265
1266         data_name = (char *) sqlite3_column_text(state, ++index);
1267
1268         if (!data_name || !strlen(data_name)) {
1269                 LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
1270         } else {
1271                 (*geocoordinate_info)->latitude = atof(data_name);
1272         }
1273
1274         data_name = (char *) sqlite3_column_text(state, ++index);
1275         if (!data_name || !strlen(data_name)) {
1276                 LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
1277         } else {
1278                 (*geocoordinate_info)->longitude = atof(data_name);
1279         }
1280
1281         data_name = (char *) sqlite3_column_text(state, ++index);
1282         if (!data_name || !strlen(data_name)) {
1283                 LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
1284         } else {
1285                 (*geocoordinate_info)->radius = atof(data_name);
1286         }
1287
1288         g_strlcpy((*geocoordinate_info)->address, (char *) sqlite3_column_text(state, ++index), ADDRESS_LEN);
1289
1290         sqlite3_finalize(state);
1291         sqlite3_free(query);
1292
1293         return FENCE_ERR_NONE;
1294 }
1295
1296 /**
1297  * This function get ap list  from DB.
1298  *
1299  * @param[in]   fence_id
1300  * @param[out]  ap_list
1301  * @return      FENCE_ERR_NONE on success, negative values for errors
1302  */
1303 int geofence_manager_get_ap_info(const int fence_id, GList **ap_list)
1304 {
1305         FUNC_ENTRANCE_SERVER;
1306         sqlite3_stmt *state = NULL;
1307         int ret = SQLITE_OK;
1308         const char *tail = NULL;
1309         int count = -1;
1310         int i = 0;
1311         wifi_info_s *wifi_info = NULL;
1312         const char *bssid = NULL;
1313
1314         char *query1 = sqlite3_mprintf("SELECT COUNT(bssid) FROM FenceGeopointWifi where fence_id = %d;", fence_id);
1315
1316         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1317         ret = sqlite3_prepare_v2(db_info_s.handle, query1, -1, &state, &tail);
1318         if (ret != SQLITE_OK) {
1319                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1320                 sqlite3_free(query1);
1321                 return FENCE_ERR_PREPARE;
1322         }
1323
1324         ret = sqlite3_step(state);
1325         if (ret != SQLITE_ROW) {
1326                 LOGD_GEOFENCE("Fail to get count sqlite3_step");
1327                 sqlite3_finalize(state);
1328                 sqlite3_free(query1);
1329                 return FENCE_ERR_SQLITE_FAIL;
1330         }
1331
1332         count = sqlite3_column_int(state, 0);
1333         sqlite3_reset(state);
1334         sqlite3_finalize(state);
1335         sqlite3_free(query1);
1336         if (count <= 0) {
1337                 LOGI_GEOFENCE("ERROR: count = %d", count);
1338                 return FENCE_ERR_COUNT;
1339         } else {
1340                 LOGD_GEOFENCE("count[%d]", count);
1341         }
1342
1343         char *query2 = sqlite3_mprintf("SELECT * FROM FenceGeopointWifi where fence_id = %d;", fence_id);
1344
1345         ret = sqlite3_prepare_v2(db_info_s.handle, query2, -1, &state, &tail);
1346         if (ret != SQLITE_OK) {
1347                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1348                 sqlite3_free(query2);
1349                 return FENCE_ERR_PREPARE;
1350         }
1351
1352         for (i = 0; i < count; i++) {
1353                 ret = sqlite3_step(state);
1354                 if (ret != SQLITE_ROW) {
1355                         LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1356                         break;
1357                 }
1358                 wifi_info = g_slice_new0(wifi_info_s);
1359                 g_return_val_if_fail(wifi_info, -1);
1360                 if (wifi_info) {
1361                         bssid = (const char *) sqlite3_column_text(state, 1);
1362                         g_strlcpy(wifi_info->bssid, bssid, WLAN_BSSID_LEN);
1363                         *ap_list = g_list_append(*ap_list, (gpointer) wifi_info);
1364                 }
1365         }
1366
1367         sqlite3_finalize(state);
1368         sqlite3_free(query2);
1369         return FENCE_ERR_NONE;
1370 }
1371
1372 /*This function get place info from DB.
1373  *
1374  * @param[in]   place_id
1375  * @param[out]  struct of place_info_s
1376  * @return      FENCE_ERR_NONE on success, negative values for errors
1377  */
1378 int geofence_manager_get_place_info(int place_id, place_info_s **place_info)
1379 {
1380         FUNC_ENTRANCE_SERVER;
1381         g_return_val_if_fail(place_id, FENCE_ERR_INVALID_PARAMETER);
1382         sqlite3_stmt *state = NULL;
1383         int ret = SQLITE_OK;
1384         const char *tail = NULL;
1385         int index = 0;
1386         char *data_name = NULL;
1387         char *query = sqlite3_mprintf("SELECT * FROM Places where place_id = %d;", place_id);
1388
1389         LOGD_GEOFENCE("current place id is [%d]", place_id);
1390         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1391         if (ret != SQLITE_OK) {
1392                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1393                 sqlite3_free(query);
1394                 return FENCE_ERR_PREPARE;
1395         }
1396         ret = sqlite3_step(state);
1397         if (ret != SQLITE_ROW) {
1398                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1399                 sqlite3_finalize(state);
1400                 sqlite3_free(query);
1401                 return FENCE_ERR_SQLITE_FAIL;
1402         }
1403         *place_info = (place_info_s *)g_malloc0(sizeof(place_info_s));
1404         g_return_val_if_fail(*place_info, FENCE_ERR_INVALID_PARAMETER);
1405
1406         data_name = (char *)sqlite3_column_text(state, ++index);
1407         if (!data_name || !strlen(data_name)) {
1408                 LOGI_GEOFENCE("ERROR: data_name is NULL!!!");
1409         } else {
1410                 (*place_info)->access_type = atof(data_name);
1411         }
1412
1413         g_strlcpy((*place_info)->place_name, (char *)sqlite3_column_text(state, ++index), PLACE_NAME_LEN);
1414         g_strlcpy((*place_info)->appid, (char *)sqlite3_column_text(state, ++index), APP_ID_LEN);
1415         sqlite3_finalize(state);
1416         sqlite3_free(query);
1417
1418         return FENCE_ERR_NONE;
1419 }
1420
1421 /**
1422  * This function insert ap list  in DB.
1423  *
1424  * @param[in]   fence_id
1425  * @param[out]  ap_list
1426  * @return      FENCE_ERR_NONE on success, negative values for errors
1427  */
1428 int geofence_manager_set_ap_info(int fence_id, GList *ap_list)
1429 {
1430         FUNC_ENTRANCE_SERVER;
1431         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
1432         g_return_val_if_fail(ap_list, FENCE_ERR_INVALID_PARAMETER);
1433         int ret = FENCE_ERR_NONE;
1434         int count = -1;
1435
1436         ret = __geofence_manager_db_get_count_of_fence_id(fence_id, FENCE_GEOPOINT_WIFI_TABLE, &count);
1437         if (ret != FENCE_ERR_NONE) {
1438                 LOGI_GEOFENCE("Fail to get geofence_manager_db_get_count_of_fence_id [%d]", ret);
1439                 return ret;
1440         } else {
1441                 if (count) {    /* fence id has been in FenceCurrentLocation table */
1442                         LOGI_GEOFENCE("count is [%d]", count);
1443                         return FENCE_ERR_FENCE_ID;
1444                 }
1445         }
1446
1447         g_list_foreach(ap_list, (GFunc) __geofence_manager_db_insert_wifi_data_info, &fence_id);
1448
1449         return FENCE_ERR_NONE;
1450 }
1451
1452 /**
1453  * This function get bluetooth info from DB.
1454  *
1455  * @param[in]   fence_id
1456  * @param[out]  bt_info which contained bssid of bluetooth and correspond of fence_id.
1457  * @return      FENCE_ERR_NONE on success, negative values for errors
1458  */
1459 int geofence_manager_get_bssid_info(const int fence_id, bssid_info_s **bssid_info)
1460 {
1461         FUNC_ENTRANCE_SERVER;
1462         sqlite3_stmt *state = NULL;
1463         int ret = SQLITE_OK;
1464         const char *tail = NULL;
1465         int count = -1;
1466         int i = 0;
1467         bssid_info_s *bssid_info_from_db = NULL;
1468         const char *bssid = NULL;
1469         const char *ssid = NULL;
1470
1471         char *query1 = sqlite3_mprintf("SELECT COUNT(bssid) FROM %s where fence_id = %d;", menu_table[FENCE_BSSID_TABLE], fence_id);
1472
1473         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1474         ret = sqlite3_prepare_v2(db_info_s.handle, query1, -1, &state, &tail);
1475         if (ret != SQLITE_OK) {
1476                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1477                 sqlite3_free(query1);
1478                 return FENCE_ERR_PREPARE;
1479         }
1480
1481         ret = sqlite3_step(state);
1482         if (ret != SQLITE_ROW) {
1483                 LOGD_GEOFENCE("Fail to get count sqlite3_step");
1484                 sqlite3_finalize(state);
1485                 sqlite3_free(query1);
1486                 return FENCE_ERR_SQLITE_FAIL;
1487         }
1488
1489         count = sqlite3_column_int(state, 0);
1490         sqlite3_reset(state);
1491         sqlite3_finalize(state);
1492         sqlite3_free(query1);
1493         if (count <= 0) {
1494                 LOGI_GEOFENCE("ERROR: count = %d", count);
1495                 return FENCE_ERR_COUNT;
1496         } else {
1497                 LOGD_GEOFENCE("count[%d]", count);
1498         }
1499
1500         char *query2 = sqlite3_mprintf("SELECT * FROM %s where fence_id = %d;", menu_table[FENCE_BSSID_TABLE], fence_id);
1501
1502         ret = sqlite3_prepare_v2(db_info_s.handle, query2, -1, &state, &tail);
1503         if (ret != SQLITE_OK) {
1504                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1505                 sqlite3_free(query2);
1506                 return FENCE_ERR_PREPARE;
1507         }
1508
1509         /*'count' should be 1. because bluetooth bssid and fence_id matched one by one.*/
1510         for (i = 0; i < count; i++) {
1511                 ret = sqlite3_step(state);
1512                 if (ret != SQLITE_ROW) {
1513                         LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1514                         break;
1515                 }
1516                 bssid_info_from_db = g_slice_new0(bssid_info_s);
1517                 g_return_val_if_fail(bssid_info_from_db, -1);
1518                 if (bssid_info_from_db) {
1519                         bssid = (const char *)sqlite3_column_text(state, 1);
1520                         ssid = (const char *)sqlite3_column_text(state, 2);
1521                         g_strlcpy(bssid_info_from_db->bssid, bssid, WLAN_BSSID_LEN);
1522                         g_strlcpy(bssid_info_from_db->ssid, ssid, WLAN_BSSID_LEN);
1523                         *bssid_info = bssid_info_from_db;
1524                 }
1525         }
1526
1527         sqlite3_finalize(state);
1528         sqlite3_free(query2);
1529         return FENCE_ERR_NONE;
1530 }
1531
1532 int geofence_manager_update_bssid_info(const int fence_id, bssid_info_s *bssid_info)
1533 {
1534         FUNC_ENTRANCE_SERVER
1535         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
1536         g_return_val_if_fail(bssid_info, FENCE_ERR_INVALID_PARAMETER);
1537         sqlite3_stmt *state = NULL;
1538         int ret = SQLITE_OK;
1539         const char *tail;
1540         char *query = sqlite3_mprintf("UPDATE %Q SET bssid = %Q where fence_id = %d;", menu_table[FENCE_BSSID_TABLE], bssid_info->bssid, fence_id);
1541
1542         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1543         if (ret != SQLITE_OK) {
1544                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1545                 sqlite3_free(query);
1546                 return FENCE_ERR_PREPARE;
1547         }
1548
1549         ret = sqlite3_step(state);
1550         if (ret != SQLITE_DONE) {
1551                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1552                 sqlite3_finalize(state);
1553                 sqlite3_free(query);
1554                 return FENCE_ERR_SQLITE_FAIL;
1555         }
1556
1557         sqlite3_finalize(state);
1558         sqlite3_free(query);
1559         LOGI_GEOFENCE("Fence_id: %d has been successfully updated.", fence_id);
1560         return FENCE_ERR_NONE;
1561 }
1562
1563 /**
1564  * This function insert bssid information in DB.
1565  *
1566  * @param[in]   fence_id
1567  * @param[in]   bssid_info which contained bssid of wifi or bluetooth for geofence.
1568  * @return      FENCE_ERR_NONE on success, negative values for errors
1569  */
1570 int geofence_manager_set_bssid_info(int fence_id, bssid_info_s *bssid_info)
1571 {
1572         FUNC_ENTRANCE_SERVER
1573         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
1574         g_return_val_if_fail(bssid_info, FENCE_ERR_INVALID_PARAMETER);
1575         int ret = FENCE_ERR_NONE;
1576         int count = -1;
1577
1578         ret = __geofence_manager_db_get_count_of_fence_id(fence_id, FENCE_BSSID_TABLE, &count);
1579         if (ret != FENCE_ERR_NONE) {
1580                 LOGI_GEOFENCE("Fail to get geofence_manager_db_get_count_of_fence_id [%d]", ret);
1581                 return ret;
1582         } else {
1583                 if (count) {    /* fence id has been in FenceBssid table */
1584                         LOGI_GEOFENCE("count is [%d]", count);
1585                         return FENCE_ERR_FENCE_ID;
1586                 }
1587         }
1588
1589         ret = __geofence_manager_db_insert_bssid_info(fence_id, bssid_info->bssid, bssid_info->ssid);
1590         if (ret != FENCE_ERR_NONE) {
1591                 LOGI_GEOFENCE("Fail to insert the bssid info");
1592                 return ret;
1593         }
1594         return FENCE_ERR_NONE;
1595 }
1596
1597 /**
1598  * This function get enable status  from DB.
1599  *
1600  * @param[in]   fence_id
1601  * @param[in]   status: 1 enbale, 0 disable.
1602  * @return      FENCE_ERR_NONE on success, negative values for errors
1603  */
1604 int geofence_manager_get_enable_status(const int fence_id, int *status)
1605 {
1606         FUNC_ENTRANCE_SERVER;
1607         sqlite3_stmt *state = NULL;
1608         int ret = SQLITE_OK;
1609         const char *tail = NULL;
1610
1611         char *query = sqlite3_mprintf("SELECT enable FROM GeoFence where fence_id = %d;", fence_id);
1612
1613         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1614         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1615         if (ret != SQLITE_OK) {
1616                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1617                 sqlite3_free(query);
1618                 return FENCE_ERR_PREPARE;
1619         }
1620
1621         ret = sqlite3_step(state);
1622         if (ret != SQLITE_ROW) {
1623                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1624                 sqlite3_finalize(state);
1625                 sqlite3_free(query);
1626                 return FENCE_ERR_SQLITE_FAIL;
1627         }
1628
1629         *status = sqlite3_column_int(state, 0);
1630
1631         sqlite3_finalize(state);
1632         sqlite3_free(query);
1633         return FENCE_ERR_NONE;
1634 }
1635
1636 /**
1637  * This function set  enable  on DB.
1638  *
1639  * @param[in]   fence_id
1640  * @param[in]   status: 1 enbale, 0 disable.
1641  * @return      FENCE_ERR_NONE on success, negative values for errors
1642  */
1643 int geofence_manager_set_enable_status(int fence_id, int status)
1644 {
1645         FUNC_ENTRANCE_SERVER;
1646         sqlite3_stmt *state;
1647         int ret = SQLITE_OK;
1648         const char *tail;
1649
1650         char *query = sqlite3_mprintf("UPDATE GeoFence SET enable = %d where fence_id = %d;", status, fence_id);
1651
1652         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1653         if (ret != SQLITE_OK) {
1654                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1655                 sqlite3_free(query);
1656                 return FENCE_ERR_PREPARE;
1657         }
1658
1659         ret = sqlite3_step(state);
1660         if (ret != SQLITE_DONE) {
1661                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1662                 sqlite3_finalize(state);
1663                 sqlite3_free(query);
1664                 return FENCE_ERR_SQLITE_FAIL;
1665         }
1666
1667         sqlite3_finalize(state);
1668         sqlite3_free(query);
1669         return FENCE_ERR_NONE;
1670 }
1671
1672 /**
1673  * This function get name from DB.
1674  *
1675  * @param[in]   fence_id
1676  * @param[out]  name
1677  * @return      FENCE_ERR_NONE on success, negative values for errors
1678  */
1679 int geofence_manager_get_place_name(int place_id, char **name)
1680 {
1681         FUNC_ENTRANCE_SERVER;
1682         sqlite3_stmt *state = NULL;
1683         int ret = SQLITE_OK;
1684         const char *tail = NULL;
1685         char *tmp = NULL;
1686
1687         char *query = sqlite3_mprintf("SELECT place_name FROM Places where place_id = %d;", place_id);
1688
1689         LOGD_GEOFENCE("current place id is [%d]", place_id);
1690         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1691         if (ret != SQLITE_OK) {
1692                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1693                 sqlite3_free(query);
1694                 return FENCE_ERR_PREPARE;
1695         }
1696
1697         ret = sqlite3_step(state);
1698         if (ret != SQLITE_ROW) {
1699                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1700                 sqlite3_finalize(state);
1701                 sqlite3_free(query);
1702                 return FENCE_ERR_SQLITE_FAIL;
1703         }
1704
1705         tmp = (char *) sqlite3_column_text(state, 0);
1706         if (!tmp || !strlen(tmp)) {
1707                 LOGI_GEOFENCE("ERROR: name is NULL!!!");
1708         } else {
1709                 *name = g_strdup(tmp);
1710         }
1711
1712         sqlite3_finalize(state);
1713         sqlite3_free(query);
1714         return FENCE_ERR_NONE;
1715 }
1716
1717 /**
1718  * This function set name on DB.
1719  *
1720  * @param[in]   fence_id
1721  * @param[in]   name
1722  * @return      FENCE_ERR_NONE on success, negative values for errors
1723  */
1724 int geofence_manager_set_place_name(int place_id, const char *name)
1725 {
1726         FUNC_ENTRANCE_SERVER;
1727         sqlite3_stmt *state;
1728         int ret = SQLITE_OK;
1729         const char *tail;
1730
1731         char *query = sqlite3_mprintf("UPDATE Places SET place_name = %Q where place_id = %d;", name, place_id);
1732
1733         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1734         if (ret != SQLITE_OK) {
1735                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1736                 sqlite3_free(query);
1737                 return FENCE_ERR_PREPARE;
1738         }
1739
1740         ret = sqlite3_step(state);
1741         if (ret != SQLITE_DONE) {
1742                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1743                 sqlite3_finalize(state);
1744                 sqlite3_free(query);
1745                 return FENCE_ERR_SQLITE_FAIL;
1746         }
1747
1748         sqlite3_finalize(state);
1749         sqlite3_free(query);
1750         return FENCE_ERR_NONE;
1751 }
1752
1753 /**
1754  * This function get appid from DB.
1755  *
1756  * @param[in]  place_id
1757  * @param[in]  appid
1758  * @return     FENCE_ERR_NONE on success, negative values for errors
1759  */
1760 int geofence_manager_get_appid_from_places(int place_id, char **appid)
1761 {
1762         FUNC_ENTRANCE_SERVER;
1763         sqlite3_stmt *state = NULL;
1764         int ret = SQLITE_OK;
1765         const char *tail = NULL;
1766         char *id = NULL;
1767
1768         char *query = sqlite3_mprintf("SELECT app_id FROM Places where place_id = %d;", place_id);
1769
1770         LOGD_GEOFENCE("current place id is [%d]", place_id);
1771         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1772         if (ret != SQLITE_OK) {
1773                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1774                 sqlite3_free(query);
1775                 return FENCE_ERR_PREPARE;
1776         }
1777
1778         ret = sqlite3_step(state);
1779         if (ret != SQLITE_ROW) {
1780                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1781                 sqlite3_finalize(state);
1782                 sqlite3_free(query);
1783                 return FENCE_ERR_SQLITE_FAIL;
1784         }
1785
1786         id = (char *) sqlite3_column_text(state, 0);
1787         if (!id || !strlen(id)) {
1788                 LOGI_GEOFENCE("ERROR: appid is NULL!!!");
1789         } else {
1790                 *appid = g_strdup(id);
1791         }
1792
1793         sqlite3_finalize(state);
1794         sqlite3_free(query);
1795         return FENCE_ERR_NONE;
1796 }
1797
1798 /**
1799  * This function set appid on DB.
1800  *
1801  * @param[in]   place_id
1802  * @param[in]   appid.
1803  * @return         FENCE_ERR_NONE on success, negative values for errors
1804  */
1805 int geofence_manager_set_appid_to_places(int place_id, char *appid)
1806 {
1807         FUNC_ENTRANCE_SERVER;
1808         sqlite3_stmt *state;
1809         int ret = SQLITE_OK;
1810         const char *tail;
1811
1812         char *query = sqlite3_mprintf("UPDATE Places SET app_id = %Q where place_id = %d;", appid, place_id);
1813         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1814         if (ret != SQLITE_OK) {
1815                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1816                 sqlite3_free(query);
1817                 return FENCE_ERR_PREPARE;
1818         }
1819
1820         ret = sqlite3_step(state);
1821         if (ret != SQLITE_DONE) {
1822                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1823                 sqlite3_finalize(state);
1824                 sqlite3_free(query);
1825                 return FENCE_ERR_SQLITE_FAIL;
1826         }
1827
1828         sqlite3_finalize(state);
1829         sqlite3_free(query);
1830         return FENCE_ERR_NONE;
1831 }
1832
1833 /**
1834  * This function get appid from DB.
1835  *
1836  * @param[in]   fence_id
1837  * @param[in]   appid
1838  * @return      FENCE_ERR_NONE on success, negative values for errors
1839  */
1840 int geofence_manager_get_appid_from_geofence(int fence_id, char **appid)
1841 {
1842         FUNC_ENTRANCE_SERVER;
1843         sqlite3_stmt *state = NULL;
1844         int ret = SQLITE_OK;
1845         const char *tail = NULL;
1846         char *id = NULL;
1847
1848         char *query = sqlite3_mprintf("SELECT app_id FROM GeoFence where fence_id = %d;", fence_id);
1849         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1850         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1851         if (ret != SQLITE_OK) {
1852                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1853                 sqlite3_free(query);
1854                 return FENCE_ERR_PREPARE;
1855         }
1856
1857         ret = sqlite3_step(state);
1858         if (ret != SQLITE_ROW) {
1859                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1860                 sqlite3_finalize(state);
1861                 sqlite3_free(query);
1862                 return FENCE_ERR_SQLITE_FAIL;
1863         }
1864
1865         id = (char *) sqlite3_column_text(state, 0);
1866         if (!id || !strlen(id)) {
1867                 LOGI_GEOFENCE("ERROR: appid is NULL!!!");
1868         } else {
1869                 *appid = g_strdup(id);
1870         }
1871
1872         sqlite3_finalize(state);
1873         sqlite3_free(query);
1874         return FENCE_ERR_NONE;
1875 }
1876
1877 /**
1878  * This function set appid on DB.
1879  *
1880  * @param[in]   fence_id
1881  * @param[in]   appid.
1882  * @return      FENCE_ERR_NONE on success, negative values for errors
1883  */
1884 int geofence_manager_set_appid_to_geofence(int fence_id, char *appid)
1885 {
1886         FUNC_ENTRANCE_SERVER;
1887         sqlite3_stmt *state;
1888         int ret = SQLITE_OK;
1889         const char *tail;
1890
1891         char *query = sqlite3_mprintf("UPDATE GeoFence SET app_id = %Q where fence_id = %d;", appid, fence_id);
1892
1893         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1894         if (ret != SQLITE_OK) {
1895                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1896                 sqlite3_free(query);
1897                 return FENCE_ERR_PREPARE;
1898         }
1899
1900         ret = sqlite3_step(state);
1901         if (ret != SQLITE_DONE) {
1902                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1903                 sqlite3_finalize(state);
1904                 sqlite3_free(query);
1905                 return FENCE_ERR_SQLITE_FAIL;
1906         }
1907
1908         sqlite3_finalize(state);
1909         sqlite3_free(query);
1910         return FENCE_ERR_NONE;
1911 }
1912
1913 /**
1914  * This function get geofence type from DB.
1915  *
1916  * @param[in]   fence_id
1917  * @param[in]   geofence_type_e.
1918  * @return      FENCE_ERR_NONE on success, negative values for errors
1919  */
1920 int geofence_manager_get_geofence_type(int fence_id, geofence_type_e *fence_type)
1921 {
1922         FUNC_ENTRANCE_SERVER;
1923         sqlite3_stmt *state = NULL;
1924         int ret = SQLITE_OK;
1925         const char *tail = NULL;
1926
1927         char *query = sqlite3_mprintf("SELECT geofence_type FROM GeoFence where fence_id = %d;", fence_id);
1928
1929         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1930         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1931         if (ret != SQLITE_OK) {
1932                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1933                 sqlite3_free(query);
1934                 return FENCE_ERR_PREPARE;
1935         }
1936
1937         ret = sqlite3_step(state);
1938         if (ret != SQLITE_ROW) {
1939                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1940                 sqlite3_finalize(state);
1941                 sqlite3_free(query);
1942                 return FENCE_ERR_SQLITE_FAIL;
1943         }
1944
1945         *fence_type = sqlite3_column_int(state, 0);
1946
1947         sqlite3_reset(state);
1948         sqlite3_finalize(state);
1949         sqlite3_free(query);
1950
1951         return FENCE_ERR_NONE;
1952 }
1953
1954 int geofence_manager_get_place_id(int fence_id, int *place_id)
1955 {
1956         FUNC_ENTRANCE_SERVER;
1957         sqlite3_stmt *state = NULL;
1958         int ret = SQLITE_OK;
1959         const char *tail = NULL;
1960
1961         char *query = sqlite3_mprintf("SELECT place_id FROM GeoFence where fence_id = %d;", fence_id);
1962
1963         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
1964         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
1965         if (ret != SQLITE_OK) {
1966                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
1967                 sqlite3_free(query);
1968                 return FENCE_ERR_PREPARE;
1969         }
1970
1971         ret = sqlite3_step(state);
1972         if (ret != SQLITE_ROW) {
1973                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
1974                 sqlite3_finalize(state);
1975                 sqlite3_free(query);
1976                 return FENCE_ERR_SQLITE_FAIL;
1977         }
1978
1979         *place_id = sqlite3_column_int(state, 0);
1980
1981         sqlite3_reset(state);
1982         sqlite3_finalize(state);
1983         sqlite3_free(query);
1984
1985         return FENCE_ERR_NONE;
1986 }
1987
1988 /**
1989  * This function get geofence/place access type from DB.
1990  *
1991  * @param[in]   fence_id/place_id
1992  * @param[in]   access_type_e.
1993  * @return      FENCE_ERR_NONE on success, negative values for errors
1994  */
1995 int geofence_manager_get_access_type(int fence_id, int place_id, access_type_e *fence_type)
1996 {
1997         FUNC_ENTRANCE_SERVER;
1998         sqlite3_stmt *state = NULL;
1999         int ret = SQLITE_OK;
2000         const char *tail = NULL;
2001         char *query = NULL;
2002
2003         if (place_id == -1)
2004                 query = sqlite3_mprintf("SELECT access_type FROM GeoFence WHERE fence_id = %d;", fence_id);
2005         else if (fence_id == -1)
2006                 query = sqlite3_mprintf("SELECT access_type FROM Places WHERE place_id = %d", place_id);
2007
2008         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
2009         LOGD_GEOFENCE("current place id is [%d]", place_id);
2010         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2011         if (ret != SQLITE_OK) {
2012                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2013                 sqlite3_free(query);
2014                 return FENCE_ERR_PREPARE;
2015         }
2016
2017         ret = sqlite3_step(state);
2018         if (ret != SQLITE_ROW) {
2019                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2020                 sqlite3_finalize(state);
2021                 sqlite3_free(query);
2022                 return FENCE_ERR_SQLITE_FAIL;
2023         }
2024
2025         *fence_type = sqlite3_column_int(state, 0);
2026
2027         sqlite3_reset(state);
2028         sqlite3_finalize(state);
2029         sqlite3_free(query);
2030
2031         return FENCE_ERR_NONE;
2032 }
2033
2034 /**
2035  * This function set geofence type on DB.
2036  *
2037  * @param[in]   fence_id
2038  * @param[in]   fence_type.
2039  * @return      FENCE_ERR_NONE on success, negative values for errors
2040  */
2041 int geofence_manager_set_geofence_type(int fence_id, geofence_type_e fence_type)
2042 {
2043         FUNC_ENTRANCE_SERVER;
2044         sqlite3_stmt *state;
2045         int ret = SQLITE_OK;
2046         const char *tail;
2047
2048         char *query = sqlite3_mprintf("UPDATE GeoFence SET geofence_type = %d where fence_id = %d;", fence_type, fence_id);
2049
2050         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2051         if (ret != SQLITE_OK) {
2052                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2053                 sqlite3_free(query);
2054                 return FENCE_ERR_PREPARE;
2055         }
2056
2057         ret = sqlite3_step(state);
2058         if (ret != SQLITE_DONE) {
2059                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2060                 sqlite3_finalize(state);
2061                 sqlite3_free(query);
2062                 return FENCE_ERR_SQLITE_FAIL;
2063         }
2064
2065         sqlite3_finalize(state);
2066         sqlite3_free(query);
2067         return FENCE_ERR_NONE;
2068 }
2069
2070 /**
2071  * This function get geofence place_id from DB.
2072  *
2073  * @param[in]   fence_id
2074  * @param[in]   place_id
2075  * @return      FENCE_ERR_NONE on success, negative values for errors
2076  */
2077 int geofence_manager_get_placeid_from_geofence(int fence_id, int *place_id)
2078 {
2079         FUNC_ENTRANCE_SERVER;
2080         sqlite3_stmt *state = NULL;
2081         int ret = SQLITE_OK;
2082         const char *tail = NULL;
2083
2084         char *query = sqlite3_mprintf("SELECT place_id FROM GeoFence where fence_id = %d;", fence_id);
2085         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
2086         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2087         if (ret != SQLITE_OK) {
2088                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2089                 sqlite3_free(query);
2090                 return FENCE_ERR_PREPARE;
2091         }
2092
2093         ret = sqlite3_step(state);
2094         if (ret != SQLITE_ROW) {
2095                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2096                 sqlite3_finalize(state);
2097                 sqlite3_free(query);
2098                 return FENCE_ERR_SQLITE_FAIL;
2099         }
2100
2101         *place_id = sqlite3_column_int(state, 0);
2102
2103         sqlite3_reset(state);
2104         sqlite3_finalize(state);
2105         sqlite3_free(query);
2106
2107         return FENCE_ERR_NONE;
2108 }
2109
2110 /**
2111  * This function get running status from DB.
2112  *
2113  * @param[in]   fence_id
2114  * @param[in]   int
2115  * @return      FENCE_ERR_NONE on success, negative values for errors
2116  */
2117 int geofence_manager_get_running_status(int fence_id, int *running_status)
2118 {
2119         FUNC_ENTRANCE_SERVER;
2120         sqlite3_stmt *state = NULL;
2121         int ret = SQLITE_OK;
2122         const char *tail = NULL;
2123
2124         char *query = sqlite3_mprintf("SELECT running_status FROM GeoFence where fence_id = %d;", fence_id);
2125
2126         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
2127         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2128         if (ret != SQLITE_OK) {
2129                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2130                 sqlite3_free(query);
2131                 return FENCE_ERR_PREPARE;
2132         }
2133
2134         ret = sqlite3_step(state);
2135         if (ret != SQLITE_ROW) {
2136                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2137                 sqlite3_finalize(state);
2138                 sqlite3_free(query);
2139                 return FENCE_ERR_SQLITE_FAIL;
2140         }
2141
2142         *running_status = sqlite3_column_int(state, 0);
2143
2144         sqlite3_reset(state);
2145         sqlite3_finalize(state);
2146         sqlite3_free(query);
2147
2148         return FENCE_ERR_NONE;
2149 }
2150
2151 /**
2152  * This function set running state on DB.
2153  *
2154  * @param[in]   fence_id
2155  * @param[in]   state
2156  * @return      FENCE_ERR_NONE on success, negative values for errors
2157  */
2158 int geofence_manager_set_running_status(int fence_id, int running_status)
2159 {
2160         FUNC_ENTRANCE_SERVER;
2161         sqlite3_stmt *state;
2162         int ret = SQLITE_OK;
2163         const char *tail;
2164
2165         char *query = sqlite3_mprintf("UPDATE GeoFence SET running_status = %d where fence_id = %d;", running_status, fence_id);
2166
2167         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2168         if (ret != SQLITE_OK) {
2169                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2170                 sqlite3_free(query);
2171                 return FENCE_ERR_PREPARE;
2172         }
2173
2174         ret = sqlite3_step(state);
2175         if (ret != SQLITE_DONE) {
2176                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2177                 sqlite3_finalize(state);
2178                 sqlite3_free(query);
2179                 return FENCE_ERR_SQLITE_FAIL;
2180         }
2181
2182         sqlite3_finalize(state);
2183         sqlite3_free(query);
2184         return FENCE_ERR_NONE;
2185 }
2186
2187 /**
2188  * This function get direction type from DB.
2189  *
2190  * @param[in]   fence_id
2191  * @param[in]   direction
2192  * @return      FENCE_ERR_NONE on success, negative values for errors
2193  */
2194 int geofence_manager_get_direction(int fence_id, geofence_direction_e *direction)
2195 {
2196         FUNC_ENTRANCE_SERVER;
2197         sqlite3_stmt *state = NULL;
2198         int ret = SQLITE_OK;
2199         const char *tail = NULL;
2200
2201         char *query = sqlite3_mprintf("SELECT direction FROM GeoFence where fence_id = %d;", fence_id);
2202         LOGD_GEOFENCE("current fence id is [%d]", fence_id);
2203         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2204         if (ret != SQLITE_OK) {
2205                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2206                 sqlite3_free(query);
2207                 return FENCE_ERR_PREPARE;
2208         }
2209
2210         ret = sqlite3_step(state);
2211         if (ret != SQLITE_ROW) {
2212                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2213                 sqlite3_finalize(state);
2214                 sqlite3_free(query);
2215                 return FENCE_ERR_SQLITE_FAIL;
2216         }
2217
2218         *direction = sqlite3_column_int(state, 0);
2219
2220         sqlite3_finalize(state);
2221         sqlite3_free(query);
2222
2223         return FENCE_ERR_NONE;
2224 }
2225
2226 /**
2227  * This function set direction type on DB.
2228  *
2229  * @param[in]   fence_id
2230  * @param[in]   direction
2231  * @return      FENCE_ERR_NONE on success, negative values for errors
2232  */
2233 int geofence_manager_set_direction(int fence_id, geofence_direction_e direction)
2234 {
2235         FUNC_ENTRANCE_SERVER;
2236         sqlite3_stmt *state;
2237         int ret = SQLITE_OK;
2238         const char *tail;
2239
2240         char *query = sqlite3_mprintf("UPDATE GeoFence SET direction = %d where fence_id = %d;", direction, fence_id);
2241
2242         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2243         if (ret != SQLITE_OK) {
2244                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2245                 sqlite3_free(query);
2246                 return FENCE_ERR_PREPARE;
2247         }
2248
2249         ret = sqlite3_step(state);
2250         if (ret != SQLITE_DONE) {
2251                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2252                 sqlite3_finalize(state);
2253                 sqlite3_free(query);
2254                 return FENCE_ERR_SQLITE_FAIL;
2255         }
2256
2257         sqlite3_finalize(state);
2258         sqlite3_free(query);
2259
2260         return FENCE_ERR_NONE;
2261 }
2262
2263 /**
2264  * This function remove fence from DB.
2265  *
2266  * @param[in]    fence_id
2267  * @return       FENCE_ERR_NONE on success, negative values for errors
2268  */
2269 int geofence_manager_delete_fence_info(int fence_id)
2270 {
2271         FUNC_ENTRANCE_SERVER;
2272         g_return_val_if_fail(fence_id, FENCE_ERR_INVALID_PARAMETER);
2273         int ret = FENCE_ERR_NONE;
2274         geofence_type_e fence_type = GEOFENCE_INVALID;
2275
2276         ret = geofence_manager_get_geofence_type(fence_id, &fence_type);
2277         if (FENCE_ERR_NONE != ret) {
2278                 LOGI_GEOFENCE("Fail to geofence_manager_delete_fence_point_info");
2279                 return ret;
2280         }
2281
2282         ret = __geofence_manager_db_enable_foreign_keys();
2283         if (FENCE_ERR_NONE != ret) {
2284                 LOGI_GEOFENCE("Fail to geofence_manager_db_enable_foreign_keys");
2285                 return ret;
2286         }
2287
2288         ret = __geofence_manager_delete_table(fence_id, FENCE_MAIN_TABLE);
2289         if (FENCE_ERR_NONE != ret) {
2290                 LOGI_GEOFENCE("Fail to geofence_manager_delete_fence_point_info");
2291                 return ret;
2292         }
2293
2294         return ret;
2295 }
2296
2297 /**
2298  * This function remove place from DB.
2299  *
2300  * @param[in]      place_id
2301  * @return         FENCE_ERR_NONE on success, negative values for errors
2302  */
2303 int geofence_manager_delete_place_info(int place_id)
2304 {
2305         FUNC_ENTRANCE_SERVER;
2306         g_return_val_if_fail(place_id, FENCE_ERR_INVALID_PARAMETER);
2307         int ret = FENCE_ERR_NONE;
2308
2309         ret = __geofence_manager_db_enable_foreign_keys();
2310         if (FENCE_ERR_NONE != ret) {
2311                 LOGI_GEOFENCE("Fail to geofence_manager_db_enable_foreign_keys");
2312                 return ret;
2313         }
2314
2315         ret = __geofence_manager_delete_place_table(place_id);
2316         if (FENCE_ERR_NONE != ret) {
2317                 LOGI_GEOFENCE("Fail to geofence_manager_delete_place_info");
2318                 return ret;
2319         }
2320
2321         return ret;
2322 }
2323
2324 /**
2325  * This function close  DB handle.
2326  *
2327  * @param[in]    fence_id
2328  * @return         FENCE_ERR_NONE on success, negative values for errors
2329  */
2330 int geofence_manager_close_db(void)
2331 {
2332         FUNC_ENTRANCE_SERVER;
2333         int ret = SQLITE_OK;
2334
2335         if (db_info_s.handle == NULL) {
2336                 return FENCE_ERR_NONE;
2337         }
2338
2339         ret = db_util_close(db_info_s.handle);
2340         if (ret != SQLITE_OK) {
2341                 LOGI_GEOFENCE("Close DB ERROR!!!");
2342                 return FENCE_ERR_SQLITE_FAIL;
2343         }
2344
2345         return FENCE_ERR_NONE;
2346 }
2347
2348 /**
2349  * This function deletes all data on db.
2350  *
2351  * @return         FENCE_ERR_NONE on success, negative values for errors
2352  */
2353 int geofence_manager_reset(void)
2354 {
2355         FUNC_ENTRANCE_SERVER;
2356         sqlite3_stmt *state = NULL;
2357         int ret = SQLITE_OK;
2358
2359         ret = __geofence_manager_db_enable_foreign_keys();
2360         if (FENCE_ERR_NONE != ret) {
2361                 LOGI_GEOFENCE("Fail to geofence_manager_db_enable_foreign_keys");
2362                 return ret;
2363         }
2364
2365         char *query_two = sqlite3_mprintf("DELETE from %Q;", menu_table[FENCE_MAIN_TABLE]);
2366
2367         ret = sqlite3_prepare_v2(db_info_s.handle, query_two, -1, &state, NULL);
2368         if (SQLITE_OK != ret) {
2369                 LOGI_GEOFENCE("Fail to connect to table. Error[%s]", sqlite3_errmsg(db_info_s.handle));
2370                 sqlite3_free(query_two);
2371                 return FENCE_ERR_SQLITE_FAIL;
2372         }
2373
2374         ret = sqlite3_step(state);
2375         if (SQLITE_DONE != ret) {
2376                 LOGI_GEOFENCE("Fail to step. Error[%d]", ret);
2377                 sqlite3_finalize(state);
2378                 sqlite3_free(query_two);
2379                 return FENCE_ERR_SQLITE_FAIL;
2380         }
2381         sqlite3_reset(state);
2382         sqlite3_free(query_two);
2383
2384         char *query_three = sqlite3_mprintf("UPDATE sqlite_sequence SET seq = 0 where name = %Q;", menu_table[FENCE_MAIN_TABLE]);
2385
2386         ret = sqlite3_prepare_v2(db_info_s.handle, query_three, -1, &state, NULL);
2387         if (SQLITE_OK != ret) {
2388                 LOGI_GEOFENCE("Fail to connect to table. Error[%s]", sqlite3_errmsg(db_info_s.handle));
2389                 sqlite3_free(query_three);
2390                 return FENCE_ERR_SQLITE_FAIL;
2391         }
2392
2393         ret = sqlite3_step(state);
2394         if (SQLITE_DONE != ret) {
2395                 LOGI_GEOFENCE("Fail to step. Error[%d]", ret);
2396                 sqlite3_finalize(state);
2397                 sqlite3_free(query_three);
2398                 return FENCE_ERR_SQLITE_FAIL;
2399         }
2400
2401         sqlite3_reset(state);
2402         sqlite3_finalize(state);
2403         sqlite3_free(query_three);
2404         return FENCE_ERR_NONE;
2405 }
2406
2407 /**
2408  * This function copy source wifi info to dest wifi info.
2409  *
2410  * @param[in]    src_wifi
2411  * @param[out]  dest_wifi
2412  * @return         FENCE_ERR_NONE on success, negative values for errors
2413  */
2414 int geofence_manager_copy_wifi_info(wifi_info_s *src_wifi, wifi_info_s **dest_wifi)
2415 {
2416         FUNC_ENTRANCE_SERVER;
2417         g_return_val_if_fail(src_wifi, FENCE_ERR_INVALID_PARAMETER);
2418
2419         *dest_wifi = (wifi_info_s *)g_malloc0(sizeof(wifi_info_s));
2420         g_return_val_if_fail(*dest_wifi, -1);
2421
2422         g_strlcpy((*dest_wifi)->bssid, src_wifi->bssid, WLAN_BSSID_LEN);
2423
2424         return FENCE_ERR_NONE;
2425 }
2426
2427 /**
2428 * This function create a wifi infor .
2429 *
2430 * @param[in]    fence_id
2431 * @param[in]    bssid
2432 * @param[out]   wifi info
2433 * @return       FENCE_ERR_NONE on success, negative values for errors
2434 */
2435 int geofence_manager_create_wifi_info(int fence_id, char *bssid, wifi_info_s **new_wifi)
2436 {
2437         FUNC_ENTRANCE_SERVER;
2438         g_return_val_if_fail(fence_id >= 0, FENCE_ERR_INVALID_PARAMETER);
2439         g_return_val_if_fail(bssid, FENCE_ERR_INVALID_PARAMETER);
2440
2441         *new_wifi = (wifi_info_s *)g_malloc0(sizeof(wifi_info_s));
2442         g_strlcpy((*new_wifi)->bssid, bssid, WLAN_BSSID_LEN);
2443
2444         return FENCE_ERR_NONE;
2445 }
2446
2447 /**
2448 * This function get fence id count  by params such as app id and fence type and enable status .
2449 *
2450 * @param[in]    app_id : if app_id == NULL: ALL
2451 * @param[in]    fence_type:if GEOFENCE_TYPE_INVALID == NULL: ALL fence type
2452 * @param[in]    enable_status
2453 * @param[out]  fence id count
2454 * @return           FENCE_ERR_NONE on success, negative values for errors
2455 */
2456 int geofence_manager_get_count_by_params(const char *app_id, geofence_type_e fence_type, int *count)
2457 {
2458         FUNC_ENTRANCE_SERVER;
2459         sqlite3_stmt *state = NULL;
2460         int ret = SQLITE_OK;
2461         const char *tail = NULL;
2462         char *query = NULL;
2463
2464         if (NULL == app_id) {
2465                 if (GEOFENCE_INVALID != fence_type) {   /* app_id == NULL : All  and  GEOFENCE_TYPE_INVALID != fence_type */
2466                         query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM GeoFence where geofence_type = %d ;", fence_type);
2467                 } else {
2468                         query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM GeoFence ;");
2469                 }
2470         } else {                        /*app_id not NULL */
2471                 if (GEOFENCE_INVALID != fence_type) {   /* app_id not NULL   and  GEOFENCE_TYPE_INVALID != fence_type */
2472                         query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM GeoFence where app_id = %Q AND geofence_type = %d ;", app_id, fence_type);
2473                 } else {
2474                         query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM GeoFence where app_id = %Q ;", app_id);
2475                 }
2476         }
2477
2478         LOGI_GEOFENCE("app_id[%s] fence_type[%d] ", app_id, fence_type);
2479         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2480         if (ret != SQLITE_OK) {
2481                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2482                 sqlite3_free(query);
2483                 return FENCE_ERR_PREPARE;
2484         }
2485
2486         ret = sqlite3_step(state);
2487         if (ret != SQLITE_ROW) {
2488                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2489                 sqlite3_finalize(state);
2490                 sqlite3_free(query);
2491                 return FENCE_ERR_SQLITE_FAIL;
2492         }
2493
2494         *count = sqlite3_column_int(state, 0);
2495
2496         if (*count <= 0) {
2497                 LOGI_GEOFENCE("ERROR: count = %d", *count);
2498                 return FENCE_ERR_COUNT;
2499         } else {
2500                 LOGI_GEOFENCE("count[%d]", *count);
2501         }
2502
2503         sqlite3_reset(state);
2504         sqlite3_finalize(state);
2505         sqlite3_free(query);
2506         return FENCE_ERR_NONE;
2507 }
2508
2509 /*
2510         app_id == NULL : All, geofence_type_e : INVALID - all, IN enable_status : enable, disable or both. Output : a list of geofence_id
2511 */
2512 int geofence_manager_get_fences(const char *app_id, geofence_type_e fence_type, GList **fences)
2513 {
2514         FUNC_ENTRANCE_SERVER;
2515         sqlite3_stmt *state = NULL;
2516         int ret = SQLITE_OK;
2517         const char *tail = NULL;
2518         char *query = NULL;
2519         int i = 0;
2520         int fence_id = 0;
2521         int count = -1;
2522
2523         ret = geofence_manager_get_count_by_params(app_id, fence_type, &count);
2524         if (ret != FENCE_ERR_NONE) {
2525                 LOGI_GEOFENCE("ERROR: geofence_manager_get_count_of_fences_by_app.");
2526                 return ret;
2527         }
2528
2529         if (NULL == app_id) {
2530                 if (GEOFENCE_INVALID != fence_type) {   /* app_id == NULL : All  and  GEOFENCE_TYPE_INVALID != fence_type */
2531                         query = sqlite3_mprintf("SELECT fence_id FROM GeoFence where geofence_type = %d;", fence_type);
2532                 } else {
2533                         query = sqlite3_mprintf("SELECT fence_id FROM GeoFence;");
2534                 }
2535         } else {                        /*app_id not NULL */
2536                 if (GEOFENCE_INVALID != fence_type) {   /* app_id not NULL   and  GEOFENCE_TYPE_INVALID != fence_type */
2537                         query = sqlite3_mprintf("SELECT fence_id FROM GeoFence where app_id = %Q AND geofence_type = %d ;", app_id, fence_type);
2538                 } else {
2539                         query = sqlite3_mprintf("SELECT fence_id FROM GeoFence where app_id = %Q;", app_id);
2540                 }
2541         }
2542
2543         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2544         if (ret != SQLITE_OK) {
2545                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2546                 sqlite3_free(query);
2547                 return FENCE_ERR_PREPARE;
2548         }
2549
2550         for (i = 0; i < count; i++) {
2551                 ret = sqlite3_step(state);
2552                 if (ret != SQLITE_ROW) {
2553                         LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2554                         break;
2555                 }
2556                 fence_id = sqlite3_column_int(state, 0);
2557                 LOGI_GEOFENCE("fence id is [%d]", fence_id);
2558                 *fences = g_list_append(*fences, (gpointer) GINT_TO_POINTER(fence_id));
2559         }
2560
2561         sqlite3_reset(state);
2562         sqlite3_finalize(state);
2563         sqlite3_free(query);
2564         return FENCE_ERR_NONE;
2565 }
2566
2567 int geofence_manager_get_count_of_fences(int *count)
2568 {
2569         FUNC_ENTRANCE_SERVER;
2570         sqlite3_stmt *state = NULL;
2571         int ret = SQLITE_OK;
2572         const char *tail = NULL;
2573         char *query = sqlite3_mprintf("SELECT COUNT(fence_id) FROM GeoFence;");
2574
2575         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2576         if (ret != SQLITE_OK) {
2577                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2578                 sqlite3_free(query);
2579                 return FENCE_ERR_PREPARE;
2580         }
2581
2582         ret = sqlite3_step(state);
2583         if (ret != SQLITE_ROW) {
2584                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2585                 sqlite3_finalize(state);
2586                 sqlite3_free(query);
2587                 return FENCE_ERR_SQLITE_FAIL;
2588         }
2589
2590         *count = sqlite3_column_int(state, 0);
2591
2592         if (*count < 0) {
2593                 LOGI_GEOFENCE("ERROR: count = %d", *count);
2594                 return FENCE_ERR_COUNT;
2595         } else {
2596                 LOGI_GEOFENCE("count[%d]", *count);
2597         }
2598
2599         sqlite3_reset(state);
2600         sqlite3_finalize(state);
2601         sqlite3_free(query);
2602         return FENCE_ERR_NONE;
2603 }
2604
2605 int geofence_manager_get_place_count_by_placeid(int place_id, int *count)
2606 {
2607         FUNC_ENTRANCE_SERVER;
2608         sqlite3_stmt *state = NULL;
2609         int ret = SQLITE_OK;
2610         const char *tail = NULL;
2611         char *query = sqlite3_mprintf("SELECT COUNT(place_id) FROM Places WHERE place_id=%d;", place_id);
2612
2613         ret = sqlite3_prepare_v2(db_info_s.handle, query, -1, &state, &tail);
2614         if (ret != SQLITE_OK) {
2615                 LOGI_GEOFENCE("Error: %s", sqlite3_errmsg(db_info_s.handle));
2616                 sqlite3_free(query);
2617                 return FENCE_ERR_PREPARE;
2618         }
2619
2620         ret = sqlite3_step(state);
2621         if (ret != SQLITE_ROW) {
2622                 LOGI_GEOFENCE("sqlite3_step Error[%d] : %s", ret, sqlite3_errmsg(db_info_s.handle));
2623                 sqlite3_finalize(state);
2624                 sqlite3_free(query);
2625                 return FENCE_ERR_SQLITE_FAIL;
2626         }
2627
2628         *count = sqlite3_column_int(state, 0);
2629
2630         if (*count < 0) {
2631                 LOGI_GEOFENCE("ERROR: place count = %d", *count);
2632                 return FENCE_ERR_COUNT;
2633         } else {
2634                 LOGI_GEOFENCE("place count[%d]", *count);
2635         }
2636
2637         sqlite3_reset(state);
2638         sqlite3_finalize(state);
2639         sqlite3_free(query);
2640         return FENCE_ERR_NONE;
2641 }