geofence-server/src/geofence_server_db.c

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