ua-daemon/src/ua-manager-device-db.c

   1 /*
   2  * Copyright (c) 2018 Samsung Electronics Co., Ltd. All rights reserved.
   3  *
   4  * Licensed under the Apache License, Version 2.0 (the "License")
   5  * you may not use this file except in compliance with the License.
   6  * You may obtain a copy of the License at
   7  *
   8  * http://www.apache.org/licenses/LICENSE-2.0
   9  *
  10  * Unless required by applicable law or agreed to in writing, software
  11  * distributed under the License is distributed on an "AS IS" BASIS,
  12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13  * See the License for the specific language governing permissions and
  14  * limitations under the License.
  15  *
  16  */
  17
  18 #include <sqlite3.h>
  19 #include <stdio.h>
  20 #include <stdlib.h>
  21 #include <string.h>
  22 #include <dlfcn.h>
  23
  24 #include "ua-manager-common.h"
  25 #include "ua-manager-database.h"
  26
  27 #define SELECT_ALL_DEVICES "SELECT device_id, tech_type, address, " \
  28         "ip_address, last_seen, presence_state, os_type, " \
  29         "user_id, discriminant, service_id, purpose, duid, device_icon FROM devices"
  30
  31 #define SELECT_DEVICE "SELECT device_id, tech_type, address, " \
  32         "ip_address, last_seen, presence_state, os_type, " \
  33         "user_id, discriminant, service_id, purpose, duid, device_icon FROM devices " \
  34         "WHERE device_id = ? AND tech_type = ? AND address = ?"
  35
  36 #define SELECT_DEVICE_NUMBER "SELECT device_number FROM devices " \
  37         "WHERE device_id = ? AND tech_type = ? AND address = ?"
  38
  39 #define INSERT_DEVICE "insert into devices " \
  40         "(device_id, tech_type, address, ip_address, last_seen, " \
  41         "presence_state, os_type, user_id, discriminant, service_id, " \
  42         "purpose, duid, device_icon)" \
  43         "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
  44
  45 #define UPDATE_LAST_SEEN "UPDATE devices " \
  46         "SET last_seen = ? WHERE device_id = ? AND tech_type = ? AND address = ?"
  47
  48 #define UPDATE_PRESENCE "UPDATE devices " \
  49         "SET presence_state = ? WHERE device_id = ? AND tech_type = ? AND address = ?"
  50
  51 #define UPDATE_IP_ADDRESS "UPDATE devices " \
  52         "SET ip_address = ? WHERE device_id = ? AND tech_type = ?"
  53
  54 #define UPDATE_MAC_ADDRESS "UPDATE devices " \
  55         "SET address = ? WHERE device_id = ? AND tech_type = ?"
  56
  57 #define UPDATE_DEVICE "UPDATE devices " \
  58         "SET (os_type, discriminant, ip_address, service_id, " \
  59         "purpose, duid, device_icon) = (?, ?, ?, ?, ?, ?, ?) " \
  60         "WHERE device_id = ? AND tech_type = ? AND address = ?"
  61
  62 #define DELETE_ALL_DEVICES "delete from devices "
  63
  64 #define DELETE_DEVICE_INFO "delete from devices " \
  65         "WHERE device_id = ? AND tech_type = ? AND address = ?"
  66
  67 static void __uam_device_finalize_delete(void);
  68
  69 /* DELETE statements */
  70 static sqlite3_stmt *delete_all_devices;
  71 static sqlite3_stmt *delete_device_info;
  72
  73 /* SELECT statements */
  74 static sqlite3_stmt *select_all_devices;
  75 static sqlite3_stmt *select_device;
  76 static sqlite3_stmt *select_device_number;
  77
  78 /* UPDATE statements */
  79 static sqlite3_stmt *update_last_seen;
  80 static sqlite3_stmt *update_presence;
  81 static sqlite3_stmt *update_ip_address;
  82 static sqlite3_stmt *update_mac_address;
  83 static sqlite3_stmt *update_device;
  84
  85 /* INSERT statements */
  86 static sqlite3_stmt *insert_device_info;
  87
  88 extern sqlite3 *database_handle;
  89
  90 static void __uam_device_finalize_delete(void)
  91 {
  92         FUNC_ENTRY;
  93
  94         FINALIZE(delete_all_devices);
  95         FINALIZE(delete_device_info);
  96
  97         FUNC_EXIT;
  98 }
  99
 100 static void __uam_device_finalize_select(void)
 101 {
 102         FUNC_ENTRY;
 103
 104         FINALIZE(select_all_devices);
 105         FINALIZE(select_device);
 106         FINALIZE(select_device_number);
 107
 108         FUNC_EXIT;
 109 }
 110
 111 static void __uam_device_finalize_insert(void)
 112 {
 113         FUNC_ENTRY;
 114
 115         FINALIZE(insert_device_info);
 116
 117         FUNC_EXIT;
 118 }
 119
 120 static void __uam_device_finalize_update(void)
 121 {
 122         FUNC_ENTRY;
 123
 124         FINALIZE(update_last_seen);
 125         FINALIZE(update_presence);
 126         FINALIZE(update_ip_address);
 127         FINALIZE(update_mac_address);
 128         FINALIZE(update_device);
 129
 130         FUNC_EXIT;
 131 }
 132
 133 static int __uam_device_prepare_delete(sqlite3 *db)
 134 {
 135         FUNC_ENTRY;
 136         int rc;
 137         static int initialized;
 138
 139         if (initialized) {
 140                 FUNC_EXIT;
 141                 return SQLITE_OK;
 142         }
 143
 144         PREPARE_QUERY(rc, db, delete_all_devices,
 145                 DELETE_ALL_DEVICES,     __uam_device_finalize_delete);
 146         PREPARE_QUERY(rc, db, delete_device_info,
 147                 DELETE_DEVICE_INFO, __uam_device_finalize_delete);
 148
 149         initialized = 1;
 150         FUNC_EXIT;
 151         return rc;
 152 }
 153
 154 static int __uam_device_prepare_select(sqlite3 *db)
 155 {
 156         FUNC_ENTRY;
 157         int rc;
 158         static int initialized;
 159
 160         if (initialized) {
 161                 FUNC_EXIT;
 162                 return SQLITE_OK;
 163         }
 164
 165         PREPARE_QUERY(rc, db, select_all_devices,
 166                 SELECT_ALL_DEVICES, __uam_device_finalize_select);
 167         PREPARE_QUERY(rc, db, select_device,
 168                 SELECT_DEVICE, __uam_device_finalize_select);
 169         PREPARE_QUERY(rc, db, select_device_number,
 170                 SELECT_DEVICE_NUMBER, __uam_device_finalize_select);
 171
 172         initialized = 1;
 173         FUNC_EXIT;
 174         return rc;
 175 }
 176
 177 static int __uam_device_prepare_update(sqlite3 *db)
 178 {
 179         FUNC_ENTRY;
 180         int rc;
 181         static int initialized;
 182
 183         if (initialized) {
 184                 FUNC_EXIT;
 185                 return SQLITE_OK;
 186         }
 187
 188         PREPARE_QUERY(rc, db, update_last_seen,
 189                 UPDATE_LAST_SEEN, __uam_device_finalize_update);
 190         PREPARE_QUERY(rc, db, update_presence,
 191                 UPDATE_PRESENCE, __uam_device_finalize_update);
 192         PREPARE_QUERY(rc, db, update_ip_address,
 193                 UPDATE_IP_ADDRESS, __uam_device_finalize_update);
 194                 PREPARE_QUERY(rc, db, update_mac_address,
 195                 UPDATE_MAC_ADDRESS, __uam_device_finalize_update);
 196         PREPARE_QUERY(rc, db, update_device,
 197                 UPDATE_DEVICE, __uam_device_finalize_update);
 198
 199         initialized = 1;
 200         FUNC_EXIT;
 201         return rc;
 202 }
 203
 204 static int __uam_device_prepare_insert(sqlite3 *db)
 205 {
 206         FUNC_ENTRY;
 207         int rc;
 208         static int initialized;
 209
 210         if (initialized) {
 211                 FUNC_EXIT;
 212                 return SQLITE_OK;
 213         }
 214
 215         PREPARE_QUERY(rc, db, insert_device_info,
 216                 INSERT_DEVICE, __uam_device_finalize_insert);
 217
 218         initialized = 1;
 219         FUNC_EXIT;
 220         return rc;
 221 }
 222
 223 static int __uam_device_table_devicesinfo_prepare(sqlite3 *db)
 224 {
 225         FUNC_ENTRY;
 226
 227         int error_code = UAM_ERROR_NONE;
 228
 229         if (db == NULL) {
 230                 FUNC_EXIT;
 231                 return UAM_ERROR_DB_FAILED;
 232         }
 233
 234         DB_ACTION(__uam_device_prepare_delete(db), error_code, handle_error);
 235         DB_ACTION(__uam_device_prepare_select(db), error_code, handle_error);
 236         DB_ACTION(__uam_device_prepare_update(db), error_code, handle_error);
 237         DB_ACTION(__uam_device_prepare_insert(db), error_code, handle_error);
 238
 239 handle_error:
 240         FUNC_EXIT;
 241         return error_code;
 242 }
 243
 244 static void __uam_device_table_devicesinfo_finalize(void)
 245 {
 246         FUNC_ENTRY;
 247         __uam_device_finalize_delete();
 248         __uam_device_finalize_select();
 249         __uam_device_finalize_update();
 250         __uam_device_finalize_insert();
 251         FUNC_EXIT;
 252 }
 253
 254 int _uam_device_db_deinitialize(void)
 255 {
 256         FUNC_ENTRY;
 257
 258         retv_if(NULL == database_handle, UAM_ERROR_NONE);
 259
 260         __uam_device_table_devicesinfo_finalize();
 261         sqlite3_close(database_handle);
 262
 263         FUNC_EXIT;
 264         return UAM_ERROR_NONE;
 265 }
 266
 267 int _uam_device_db_initialize(void)
 268 {
 269         FUNC_ENTRY;
 270
 271         EXEC(UAM_ERROR_NONE, __uam_device_table_devicesinfo_prepare(database_handle), handle_error);
 272
 273         FUNC_EXIT;
 274         return UAM_ERROR_NONE;
 275
 276 handle_error:
 277         _uam_device_db_deinitialize();
 278         FUNC_EXIT;
 279         return UAM_ERROR_DB_FAILED;
 280 }
 281
 282 int _uam_device_db_update_device_ip_address(char *device_id, int tech_type,
 283         char *ip_address)
 284 {
 285         int error_code = UAM_ERROR_NONE;
 286         sqlite3_stmt *stmt = update_ip_address;
 287         int sql_ret = SQLITE_OK;
 288
 289         retv_if(NULL == device_id, UAM_ERROR_INVALID_PARAMETER);
 290         retv_if(NULL == ip_address, UAM_ERROR_INVALID_PARAMETER);
 291
 292         DB_ACTION(sqlite3_bind_text(stmt, 1, ip_address, -1, SQLITE_TRANSIENT),
 293                 error_code, handle_error);
 294         DB_ACTION(sqlite3_bind_text(stmt, 2, device_id, -1, SQLITE_TRANSIENT),
 295                 error_code, handle_error);
 296         DB_ACTION(sqlite3_bind_int(stmt, 3, tech_type),
 297                 error_code, handle_error);
 298
 299         sql_ret = sqlite3_step(stmt);
 300         if (sql_ret != SQLITE_DONE) {
 301                 UAM_ERR("Failed to update IP address [%d:%s]",
 302                         sql_ret, sqlite3_errmsg(database_handle));
 303
 304                 error_code = UAM_ERROR_DB_FAILED;
 305                 goto handle_error;
 306         }
 307
 308         UAM_DBG("[%d] IP address updated [%s]", tech_type, ip_address);
 309
 310 handle_error:
 311         sqlite3_reset(stmt);
 312         return error_code;
 313 }
 314
 315 int _uam_device_db_update_device_mac_address(char *device_id, int tech_type,
 316         char *address)
 317 {
 318         int error_code = UAM_ERROR_NONE;
 319         sqlite3_stmt *stmt = update_mac_address;
 320         int sql_ret = SQLITE_OK;
 321
 322         retv_if(NULL == device_id, UAM_ERROR_INVALID_PARAMETER);
 323         retv_if(NULL == address, UAM_ERROR_INVALID_PARAMETER);
 324
 325         DB_ACTION(sqlite3_bind_text(stmt, 1, address, -1, SQLITE_TRANSIENT),
 326                 error_code, handle_error);
 327         DB_ACTION(sqlite3_bind_text(stmt, 2, device_id, -1, SQLITE_TRANSIENT),
 328                 error_code, handle_error);
 329         DB_ACTION(sqlite3_bind_int(stmt, 3, tech_type),
 330                 error_code, handle_error);
 331
 332         sql_ret = sqlite3_step(stmt);
 333         if (sql_ret != SQLITE_DONE) {
 334                 UAM_ERR("Failed to update MAC address [%d:%s]",
 335                         sql_ret, sqlite3_errmsg(database_handle));
 336
 337                 error_code = UAM_ERROR_DB_FAILED;
 338                 goto handle_error;
 339         }
 340
 341         UAM_DBG("[%d] MAC address updated [%s]", tech_type, address);
 342
 343 handle_error:
 344         sqlite3_reset(stmt);
 345         return error_code;
 346 }
 347
 348 int _uam_device_db_update_device_presence(char *device_id, int tech_type,
 349         char *address, int presence_state)
 350 {
 351         int error_code = UAM_ERROR_NONE;
 352         sqlite3_stmt *stmt = update_presence;
 353         int sql_ret = SQLITE_OK;
 354
 355         retv_if(NULL == device_id, UAM_ERROR_INVALID_PARAMETER);
 356         retv_if(NULL == address, UAM_ERROR_INVALID_PARAMETER);
 357
 358         DB_ACTION(sqlite3_bind_int(stmt, 1, presence_state),
 359                 error_code, handle_error);
 360         DB_ACTION(sqlite3_bind_text(stmt, 2, device_id, -1, SQLITE_TRANSIENT),
 361                 error_code, handle_error);
 362         DB_ACTION(sqlite3_bind_int(stmt, 3, tech_type),
 363                 error_code, handle_error);
 364         DB_ACTION(sqlite3_bind_text(stmt, 4, address, -1, SQLITE_TRANSIENT),
 365                 error_code, handle_error);
 366
 367         sql_ret = sqlite3_step(stmt);
 368         if (sql_ret != SQLITE_DONE) {
 369                 UAM_ERR("Failed to update presence [%d:%s]",
 370                         sql_ret, sqlite3_errmsg(database_handle));
 371
 372                 error_code = UAM_ERROR_DB_FAILED;
 373                 goto handle_error;
 374         }
 375
 376         UAM_DBG("presence state updated [%d]", presence_state);
 377
 378 handle_error:
 379         sqlite3_reset(stmt);
 380         return error_code;
 381 }
 382
 383 int _uam_device_db_update_device_last_seen(char *device_id, int tech_type,
 384                                           char *address, unsigned long long last_seen)
 385 {
 386         int error_code = UAM_ERROR_NONE;
 387         sqlite3_stmt *stmt = update_last_seen;
 388         int sql_ret = SQLITE_OK;
 389
 390         retv_if(NULL == device_id, UAM_ERROR_INVALID_PARAMETER);
 391         retv_if(NULL == address, UAM_ERROR_INVALID_PARAMETER);
 392
 393         DB_ACTION(sqlite3_bind_int64(stmt, 1, last_seen),
 394                 error_code, handle_error);
 395         DB_ACTION(sqlite3_bind_text(stmt, 2, device_id, -1, SQLITE_TRANSIENT),
 396                 error_code, handle_error);
 397         DB_ACTION(sqlite3_bind_int(stmt, 3, tech_type),
 398                 error_code, handle_error);
 399         DB_ACTION(sqlite3_bind_text(stmt, 4, address, -1, SQLITE_TRANSIENT),
 400                 error_code, handle_error);
 401
 402         sql_ret = sqlite3_step(stmt);
 403         if (sql_ret != SQLITE_DONE) {
 404                 UAM_ERR("Failed to update device last_seen [%d:%s]",
 405                         sql_ret, sqlite3_errmsg(database_handle));
 406
 407                 error_code = UAM_ERROR_DB_FAILED;
 408                 goto handle_error;
 409         }
 410
 411         UAM_DBG("Device last_seen updated [%llu]", last_seen);
 412
 413 handle_error:
 414         sqlite3_reset(stmt);
 415         return error_code;
 416 }
 417
 418 int _uam_device_db_update_device_device(char *device_id, int tech_type,
 419         char *address, char *ip, char os_type, char discriminant, uam_ble_payload_s payload)
 420 {
 421         int error_code = UAM_ERROR_NONE;
 422         sqlite3_stmt *stmt = update_device;
 423         int sql_ret = SQLITE_OK;
 424
 425         retv_if(NULL == device_id, UAM_ERROR_INVALID_PARAMETER);
 426         retv_if(NULL == address, UAM_ERROR_INVALID_PARAMETER);
 427
 428         DB_ACTION(sqlite3_bind_int(stmt, 1, os_type),
 429                 error_code, handle_error);
 430         DB_ACTION(sqlite3_bind_int(stmt, 2, discriminant),
 431                 error_code, handle_error);
 432         DB_ACTION(sqlite3_bind_text(stmt, 3, ip, -1, SQLITE_TRANSIENT),
 433                 error_code, handle_error);
 434         DB_ACTION(sqlite3_bind_text(stmt, 4, &(payload.service_id), 1, SQLITE_TRANSIENT),
 435                 error_code, handle_error);
 436         DB_ACTION(sqlite3_bind_text(stmt, 5, &(payload.purpose), 1, SQLITE_TRANSIENT),
 437                 error_code, handle_error);
 438         DB_ACTION(sqlite3_bind_text(stmt, 6, payload.duid, UAM_BLE_PAYLOAD_DUID_LEN + 1, SQLITE_TRANSIENT),
 439                 error_code, handle_error);
 440         DB_ACTION(sqlite3_bind_text(stmt, 7, &(payload.device_icon), 1, SQLITE_TRANSIENT),
 441                 error_code, handle_error);
 442         DB_ACTION(sqlite3_bind_text(stmt, 8, device_id, -1, SQLITE_TRANSIENT),
 443                 error_code, handle_error);
 444         DB_ACTION(sqlite3_bind_int(stmt, 9, tech_type),
 445                 error_code, handle_error);
 446         DB_ACTION(sqlite3_bind_text(stmt, 10, address, -1, SQLITE_TRANSIENT),
 447                 error_code, handle_error);
 448
 449         sql_ret = sqlite3_step(stmt);
 450         if (sql_ret != SQLITE_DONE) {
 451                 UAM_ERR("Failed to update device discriminant [%d:%s]",
 452                         sql_ret, sqlite3_errmsg(database_handle));
 453
 454                 error_code = UAM_ERROR_DB_FAILED;
 455                 goto handle_error;
 456         }
 457
 458         UAM_DBG("Device discriminant updated [%s]", discriminant ? "true" : "false");
 459
 460 handle_error:
 461         sqlite3_reset(stmt);
 462         return error_code;
 463 }
 464
 465 int _uam_device_db_insert_device_info(int user_id,
 466         const uam_device_info_s *dev_info, int presence_state, unsigned long long last_seen)
 467 {
 468         FUNC_ENTRY;
 469         int error_code = UAM_ERROR_NONE;
 470         sqlite3_stmt *stmt = insert_device_info;
 471         int sql_ret = SQLITE_OK;
 472
 473         retv_if(NULL == dev_info, UAM_ERROR_INVALID_PARAMETER);
 474
 475         UAM_INFO("%s-%d-%s-%s-%llu-%d-%d-%d-%d-0x%2.2X", dev_info->device_id,
 476                         dev_info->type, dev_info->mac, dev_info->ipv4_addr,
 477                         last_seen, presence_state, dev_info->operating_system, dev_info->discriminant,
 478                         user_id, dev_info->payload.service_id);
 479
 480         DB_ACTION(sqlite3_bind_text(stmt, 1, dev_info->device_id, -1, SQLITE_TRANSIENT),
 481                 error_code, handle_error);
 482         DB_ACTION(sqlite3_bind_int(stmt, 2, dev_info->type),
 483                 error_code, handle_error);
 484         DB_ACTION(sqlite3_bind_text(stmt, 3, dev_info->mac, -1, SQLITE_TRANSIENT),
 485                 error_code, handle_error);
 486         DB_ACTION(sqlite3_bind_text(stmt, 4, dev_info->ipv4_addr, -1, SQLITE_TRANSIENT),
 487                 error_code, handle_error);
 488         DB_ACTION(sqlite3_bind_int64(stmt, 5, last_seen),
 489                 error_code, handle_error);
 490         DB_ACTION(sqlite3_bind_int(stmt, 6, presence_state),
 491                 error_code, handle_error);
 492         DB_ACTION(sqlite3_bind_int(stmt, 7, dev_info->operating_system),
 493                 error_code, handle_error);
 494         DB_ACTION(sqlite3_bind_int(stmt, 8, user_id),
 495                 error_code, handle_error);
 496         DB_ACTION(sqlite3_bind_int(stmt, 9, dev_info->discriminant),
 497                 error_code, handle_error);
 498         DB_ACTION(sqlite3_bind_text(stmt, 10, &(dev_info->payload.service_id), 1, SQLITE_TRANSIENT),
 499                 error_code, handle_error);
 500         DB_ACTION(sqlite3_bind_text(stmt, 11, &(dev_info->payload.purpose), 1, SQLITE_TRANSIENT),
 501                 error_code, handle_error);
 502         DB_ACTION(sqlite3_bind_text(stmt, 12, dev_info->payload.duid, UAM_BLE_PAYLOAD_DUID_LEN + 1, SQLITE_TRANSIENT),
 503                 error_code, handle_error);
 504         DB_ACTION(sqlite3_bind_text(stmt, 13, &(dev_info->payload.device_icon), 1, SQLITE_TRANSIENT),
 505                 error_code, handle_error);
 506
 507         sql_ret = sqlite3_step(stmt);
 508         if (sql_ret != SQLITE_DONE) {
 509                 UAM_ERR("Failed to insert device info [%d:%s]",
 510                         sql_ret, sqlite3_errmsg(database_handle));
 511                 error_code = UAM_ERROR_DB_FAILED;
 512                 goto handle_error;
 513         }
 514
 515         UAM_DBG("Device info inserted device_id [%s] ", dev_info->device_id);
 516
 517 handle_error:
 518         sqlite3_reset(stmt);
 519         FUNC_EXIT;
 520         return error_code;
 521 }
 522
 523 int _uam_device_db_delete_device_info(const char *device_id, int tech_type,
 524         const char *address)
 525 {
 526         FUNC_ENTRY;
 527         int error_code = UAM_ERROR_NONE;
 528         sqlite3_stmt *stmt = delete_device_info;
 529         int sql_ret = SQLITE_OK;
 530
 531         UAM_INFO("DeviceId: %s", device_id);
 532
 533         DB_ACTION(sqlite3_bind_text(stmt, 1, device_id, -1, SQLITE_STATIC),
 534                 error_code, handle_error);
 535         DB_ACTION(sqlite3_bind_int(stmt, 2, tech_type),
 536                 error_code, handle_error);
 537         DB_ACTION(sqlite3_bind_text(stmt, 3, address, -1, SQLITE_STATIC),
 538                 error_code, handle_error);
 539
 540         retv_if(UAM_ERROR_NONE != __uam_db_begin_transaction(), UAM_ERROR_DB_FAILED);
 541         error_code = _uam_db_delete_device_service_mapping(device_id, tech_type, address);
 542         if (UAM_ERROR_NONE != error_code) {
 543                 UAM_ERR("Failed to delete device service mapping");
 544                 __uam_db_end_transaction(0);
 545                 goto handle_error;
 546         }
 547
 548         sql_ret = sqlite3_step(stmt);
 549         if (sql_ret != SQLITE_DONE) {
 550                 UAM_ERR("Failed to delete device info [%d:%s]",
 551                         sql_ret, sqlite3_errmsg(database_handle));
 552                 error_code = UAM_ERROR_DB_FAILED;
 553                 __uam_db_end_transaction(0);
 554                 goto handle_error;
 555         } else
 556                 UAM_DBG("Device info deleted");
 557
 558         __uam_db_end_transaction(1);
 559
 560 handle_error:
 561         sqlite3_reset(stmt);
 562         FUNC_EXIT;
 563         return error_code;
 564 }
 565
 566 int _uam_device_db_get_device(char *device_id, int tech_type, char *address,
 567                 db_device_info_t *info)
 568 {
 569         FUNC_ENTRY;
 570         int error_code = UAM_ERROR_NONE;
 571         sqlite3_stmt *stmt = select_device;
 572         int sql_ret = SQLITE_OK;
 573         char *buf = NULL;
 574
 575         retv_if(NULL == info, UAM_ERROR_INVALID_PARAMETER);
 576
 577         UAM_INFO("UserId: %s", device_id);
 578
 579         DB_ACTION(sqlite3_bind_text(stmt, 1, device_id, -1, SQLITE_STATIC),
 580                 error_code, handle_error);
 581         DB_ACTION(sqlite3_bind_int(stmt, 2, tech_type),
 582                 error_code, handle_error);
 583         DB_ACTION(sqlite3_bind_text(stmt, 3, address, -1, SQLITE_STATIC),
 584                 error_code, handle_error);
 585
 586         sql_ret = sqlite3_step(stmt);
 587         if (sql_ret != SQLITE_DONE) {
 588                 UAM_ERR("Failed to select device info [%d:%s]",
 589                         sql_ret, sqlite3_errmsg(database_handle));
 590                 error_code = UAM_ERROR_DB_FAILED;
 591         } else {
 592                 UAM_DBG("Device info found");
 593                 info = g_new0(db_device_info_t, 1);
 594                 g_strlcpy(info->dev_info.device_id, (char *)sqlite3_column_text(stmt, 0),
 595                         UAM_DEVICE_ID_MAX_STRING_LEN);
 596                 info->dev_info.type = sqlite3_column_int(stmt, 1);
 597                 g_strlcpy(info->dev_info.mac, (char *)sqlite3_column_text(stmt, 2),
 598                         UAM_MAC_ADDRESS_STRING_LEN);
 599                 g_strlcpy(info->dev_info.ipv4_addr, (char *)sqlite3_column_text(stmt, 3),
 600                         UAM_IP_ADDRESS_MAX_STRING_LEN);
 601                 info->last_seen = sqlite3_column_int64(stmt, 4);
 602                 info->presence_state = sqlite3_column_int(stmt, 5);
 603                 info->dev_info.operating_system = sqlite3_column_int(stmt, 6);
 604                 info->user_id = sqlite3_column_int(stmt, 7);
 605                 info->dev_info.discriminant = sqlite3_column_int(stmt, 8);
 606                 memset(info->dev_info.payload.duid, 0, UAM_BLE_PAYLOAD_DUID_LEN + 1);
 607                 buf = (char *)sqlite3_column_text(stmt, 9);
 608                 info->dev_info.payload.service_id = buf ? *buf : 13;
 609                 buf = (char *)sqlite3_column_text(stmt, 10);
 610                 info->dev_info.payload.purpose = buf ? *buf : 1;
 611                 if ((char*)sqlite3_column_text(stmt, 11))
 612                         memcpy(info->dev_info.payload.duid, (char *)sqlite3_column_text(stmt, 11),
 613                                 UAM_BLE_PAYLOAD_DUID_LEN);
 614                 buf = (char *)sqlite3_column_text(stmt, 12);
 615                 info->dev_info.payload.device_icon = buf ? *buf : 1;
 616
 617                 UAM_INFO("%s-%d-%s-%s-%llu-%d-%d-%d-%d-0x%2.2X",
 618                         info->dev_info.device_id,
 619                         info->dev_info.type,
 620                         info->dev_info.mac,
 621                         info->dev_info.ipv4_addr,
 622                         info->last_seen,
 623                         info->presence_state,
 624                         info->dev_info.operating_system,
 625                         info->user_id,
 626                         info->dev_info.discriminant,
 627                         info->dev_info.payload.service_id);
 628         }
 629
 630 handle_error:
 631         sqlite3_reset(stmt);
 632         FUNC_EXIT;
 633         return error_code;
 634 }
 635
 636 int _uam_db_get_device_number(const char *device_id, int tech_type,
 637         const char *address, int *device_number)
 638 {
 639         FUNC_ENTRY;
 640         int error_code = UAM_ERROR_NONE;
 641         sqlite3_stmt *stmt = select_device_number;
 642         int sql_ret = SQLITE_OK;
 643
 644         retv_if(NULL == device_id, UAM_ERROR_INVALID_PARAMETER);
 645         retv_if(NULL == address, UAM_ERROR_INVALID_PARAMETER);
 646
 647         UAM_INFO("UserId: %s", device_id);
 648
 649         DB_ACTION(sqlite3_bind_text(stmt, 1, device_id, -1, SQLITE_STATIC),
 650                 error_code, handle_error);
 651         DB_ACTION(sqlite3_bind_int(stmt, 2, tech_type),
 652                 error_code, handle_error);
 653         DB_ACTION(sqlite3_bind_text(stmt, 3, address, -1, SQLITE_STATIC),
 654                 error_code, handle_error);
 655
 656         do {
 657                 sql_ret = sqlite3_step(stmt);
 658
 659                 switch (sql_ret) {
 660                 case SQLITE_DONE:
 661                         break;
 662                 case SQLITE_ROW:
 663                         UAM_DBG("Device number info found");
 664                         *device_number = sqlite3_column_int(stmt, 0);
 665                         UAM_INFO("device_number %d", *device_number);
 666                         break;
 667                 case SQLITE_ERROR:
 668                 default:
 669                         UAM_ERR("Failed to enumerate device info [%d:%s]",
 670                                 sql_ret, sqlite3_errmsg(database_handle));
 671                 }
 672         } while (sql_ret == SQLITE_ROW);
 673
 674 handle_error:
 675         sqlite3_reset(stmt);
 676         FUNC_EXIT;
 677         return error_code;
 678 }
 679
 680 GSList *_uam_device_db_get_all_devices(void)
 681 {
 682         FUNC_ENTRY;
 683         sqlite3_stmt *stmt = select_all_devices;
 684         GSList *device_list = NULL;
 685         db_device_info_t *info = NULL;
 686         int sql_ret = SQLITE_OK;
 687         char *buf = NULL;
 688
 689         do {
 690                 sql_ret = sqlite3_step(stmt);
 691
 692                 switch (sql_ret) {
 693                 case SQLITE_DONE:
 694                         break;
 695                 case SQLITE_ROW:
 696                         info = g_new0(db_device_info_t, 1);
 697                         g_strlcpy(info->dev_info.device_id, (char *)sqlite3_column_text(stmt, 0),
 698                                 UAM_DEVICE_ID_MAX_STRING_LEN);
 699                         info->dev_info.type = sqlite3_column_int(stmt, 1);
 700                         g_strlcpy(info->dev_info.mac, (char *)sqlite3_column_text(stmt, 2),
 701                                 UAM_MAC_ADDRESS_STRING_LEN);
 702                         g_strlcpy(info->dev_info.ipv4_addr, (char *)sqlite3_column_text(stmt, 3),
 703                                 UAM_IP_ADDRESS_MAX_STRING_LEN);
 704                         info->last_seen = sqlite3_column_int64(stmt, 4);
 705                         info->presence_state = sqlite3_column_int(stmt, 5);
 706                         info->dev_info.operating_system = sqlite3_column_int(stmt, 6);
 707                         info->user_id = sqlite3_column_int(stmt, 7);
 708                         info->dev_info.discriminant = sqlite3_column_int(stmt, 8);
 709                         memset(info->dev_info.payload.duid, 0, UAM_BLE_PAYLOAD_DUID_LEN + 1);
 710                         buf = (char *)sqlite3_column_text(stmt, 9);
 711                         info->dev_info.payload.service_id = buf ? *buf : 13;
 712                         buf = (char *)sqlite3_column_text(stmt, 10);
 713                         info->dev_info.payload.purpose = buf ? *buf : 1;
 714                         if ((char*)sqlite3_column_text(stmt, 11))
 715                                 memcpy(info->dev_info.payload.duid, (char *)sqlite3_column_text(stmt, 11),
 716                                         UAM_BLE_PAYLOAD_DUID_LEN);
 717                         buf = (char *)sqlite3_column_text(stmt, 12);
 718                         info->dev_info.payload.device_icon = buf ? *buf : 1;
 719
 720                         UAM_INFO("%s-%d-%s-%s-%llu-%d-%d-%d-%d-0x%2.2X",
 721                                 info->dev_info.device_id,
 722                                 info->dev_info.type,
 723                                 info->dev_info.mac,
 724                                 info->dev_info.ipv4_addr,
 725                                 info->last_seen,
 726                                 info->presence_state,
 727                                 info->dev_info.operating_system,
 728                                 info->user_id,
 729                                 info->dev_info.discriminant,
 730                                 info->dev_info.payload.service_id);
 731
 732                         device_list = g_slist_append(device_list, info);
 733                         break;
 734                 case SQLITE_ERROR:
 735                 default:
 736                         UAM_ERR("Failed to enumerate device info [%d:%s]",
 737                                 sql_ret, sqlite3_errmsg(database_handle));
 738                 }
 739         } while (sql_ret == SQLITE_ROW);
 740
 741         sqlite3_reset(stmt);
 742         FUNC_EXIT;
 743         return device_list;
 744 }
 745
 746 int _uam_device_db_clear(void)
 747 {
 748         int error_code = UAM_ERROR_NONE;
 749         sqlite3_stmt *stmt = delete_all_devices;
 750         int sql_ret = SQLITE_OK;
 751
 752         sql_ret = sqlite3_step(stmt);
 753         if (sql_ret != SQLITE_DONE) {
 754                 UAM_ERR("Failed to delete device data [%d:%s]",
 755                         sql_ret, sqlite3_errmsg(database_handle));
 756                 error_code = UAM_ERROR_DB_FAILED;
 757                 goto handle_error;
 758         }
 759         UAM_DBG("Device data deleted ");
 760
 761 handle_error:
 762         sqlite3_reset(stmt);
 763         return error_code;
 764 }