geofence-server/src/geofence_server_db.c

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