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