ua-daemon/src/ua-manager-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 #include <stdio.h>
  18 #include <stdlib.h>
  19 #include <string.h>
  20 #include <stdbool.h>
  21
  22 #include <sqlite3.h>
  23 #include <dlfcn.h>
  24
  25 #include "ua-manager-common.h"
  26 #include "ua-manager-database.h"
  27
  28 #define UAM_DB_USERDATA_TABLE "userdata" /**< User data DB table name */
  29 #define UAM_DB_DEVICES_TABLE "devices" /**< Device DB table name */
  30 #define UAM_DB_SERVICES_TABLE "services" /**< Service DB table name */
  31 #define UAM_DB_DEVICE_SERVICES_TABLE "device_services" /**< Device services DB table name */
  32 #define UAM_DB_IBEACON_ADV_TABLE "ibeacon_adv" /**< iBeacon adv DB table name */
  33
  34 #define CREATE_USERDATA_TABLE "CREATE TABLE IF NOT EXISTS userdata ( " \
  35         "name TEXT, " \
  36         "user_id INTEGER PRIMARY KEY AUTOINCREMENT, " \
  37         "account TEXT, " \
  38         "UNIQUE (account) " \
  39         "); "
  40
  41 #define CREATE_DEVICES_TABLE "CREATE TABLE IF NOT EXISTS devices ( " \
  42         "device_number INTEGER PRIMARY KEY AUTOINCREMENT, " \
  43         "device_id TEXT, " \
  44         "user_id INTEGER, " \
  45         "tech_type INTEGER, " \
  46         "address TEXT, " \
  47         "ip_address TEXT, " \
  48         "last_seen LONG, " \
  49         "presence_state INTEGER, " \
  50         "os_type INTEGER, " \
  51         "discriminant INTEGER, " \
  52         "service_id TEXT, " \
  53         "purpose TEXT, " \
  54         "duid TEXT, " \
  55         "device_icon TEXT, " \
  56         "FOREIGN KEY(user_id) REFERENCES userdata(user_id), " \
  57         "UNIQUE (device_id, tech_type) " \
  58         "); "
  59
  60 #define CREATE_SERVICES_TABLE "CREATE TABLE IF NOT EXISTS services ( " \
  61         "service_number INTEGER PRIMARY KEY AUTOINCREMENT, " \
  62         "service_name TEXT, " \
  63         "cycle INTEGER, " \
  64         "presence_threshold INTEGER, " \
  65         "absence_threshold INTEGER, " \
  66         "UNIQUE (service_name) " \
  67         "); "
  68
  69 #define CREATE_DEVICE_SERVICES_TABLE "CREATE TABLE IF NOT EXISTS device_services ( " \
  70         "device_number INTEGER, " \
  71         "service_number INTEGER, " \
  72         "discriminant INTEGER, " \
  73         "last_seen LONG, " \
  74         "FOREIGN KEY(device_number) REFERENCES devices(device_number), " \
  75         "FOREIGN KEY(service_number) REFERENCES services(service_number), " \
  76         "PRIMARY KEY(device_number, service_number) " \
  77         "); "
  78
  79 #define CREATE_IBEACON_ADV_TABLE "CREATE TABLE IF NOT EXISTS ibeacon_adv ( " \
  80         "ibeacon_id INTEGER PRIMARY KEY AUTOINCREMENT, " \
  81         "ibeacon_adv TEXT, " \
  82         "adv_len INTEGER, " \
  83         "UNIQUE (ibeacon_adv) " \
  84         "); "
  85
  86 #define VERSION 1
  87
  88 sqlite3 *database_handle;
  89 static sqlite3_stmt *select_version;
  90 static int transaction_cnt;
  91
  92 static int __uam_db_exec_sql(char *sql, void *cb)
  93 {
  94         FUNC_ENTRY;
  95         int sql_ret;
  96         char *error = NULL;
  97
  98         if (NULL == database_handle) {
  99                 UAM_ERR("database_handle is NULL");
 100                 return UAM_ERROR_DB_FAILED;
 101         }
 102
 103         if (NULL == sql) {
 104                 UAM_ERR("sql is NULL");
 105                 return UAM_ERROR_DB_FAILED;
 106         }
 107
 108         sql_ret = sqlite3_exec(database_handle, sql, cb, NULL, &error);
 109         if (SQLITE_OK != sql_ret) {
 110                 UAM_ERR("Failed to execute sql: (%d) %s", sql_ret, error);
 111                 sqlite3_free(error);
 112                 sqlite3_close(database_handle);
 113                 database_handle = NULL;
 114                 return UAM_ERROR_DB_FAILED;
 115         }
 116
 117         return UAM_ERROR_NONE;
 118 }
 119
 120 static int __uam_db_open(void)
 121 {
 122         FUNC_ENTRY;
 123         int res = 0;
 124
 125         res = sqlite3_open_v2(DATABASE_FULL_PATH, &(database_handle),
 126                                 SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
 127         if (SQLITE_OK != res) {
 128                 UAM_ERR("sqlite3_open_v2 failed, (%d)", res);
 129                 FUNC_EXIT;
 130                 return UAM_ERROR_DB_FAILED;
 131         }
 132         UAM_DBG("Successfully opened database");
 133
 134         FUNC_EXIT;
 135         return res;
 136 }
 137
 138 static int __uam_db_busy(void *data, int attempt)
 139 {
 140         FUNC_ENTRY;
 141         int max_attempts = SQLITE_MAX_RETRY;
 142
 143         if (attempt < max_attempts) {
 144                 UAM_WARN("DB locked, attempt number %d, Attempt Remaining %d",
 145                         attempt, max_attempts - attempt);
 146                 usleep(SQLITE_BUSY_TIMEOUT); /* wait for a half second*/
 147                 return 1;
 148         }
 149
 150         UAM_ERR("DB locked by another process, attempts tried %d, Exit",
 151                         attempt);
 152         FUNC_EXIT;
 153         return 0;
 154 }
 155
 156 static int __uam_db_get_version(unsigned int *ver)
 157 {
 158         FUNC_ENTRY;
 159         int error_code = UAM_ERROR_NONE;
 160         int sql_ret;
 161         char *sql = NULL;
 162         sql = sqlite3_mprintf("PRAGMA user_version");
 163         sqlite3_stmt *stmt = select_version;
 164
 165         sql_ret = sqlite3_prepare_v2(database_handle, sql, -1, &stmt, NULL);
 166         if (sql_ret != SQLITE_OK) {
 167                 stmt = NULL;
 168                 FINALIZE(select_version);
 169                 UAM_ERR("Failed to prepare \"%s\" query", sql);
 170                 sqlite3_free(sql);
 171                 return sql_ret;
 172         }
 173
 174         do {
 175                 sql_ret = sqlite3_step(stmt);
 176
 177                 switch (sql_ret) {
 178                 case SQLITE_DONE:
 179                         break;
 180                 case SQLITE_ROW:
 181                         *ver = sqlite3_column_int(stmt, 0);
 182                         UAM_INFO("database version %d", *ver);
 183                         break;
 184                 case SQLITE_ERROR:
 185                 default:
 186                         UAM_ERR("Failed to select database version info [%d:%s]",
 187                                 sql_ret, sqlite3_errmsg(database_handle));
 188                 }
 189         } while (sql_ret == SQLITE_ROW);
 190
 191         FINALIZE(select_version);
 192         sqlite3_reset(stmt);
 193         sqlite3_finalize(stmt);
 194         sqlite3_free(sql);
 195         FUNC_EXIT;
 196         return error_code;
 197 }
 198
 199 static int __uam_db_update_version(unsigned int version)
 200 {
 201         FUNC_ENTRY;
 202         char *sql = NULL;
 203         int ret = UAM_ERROR_NONE;
 204
 205         sql = sqlite3_mprintf("PRAGMA user_version = '%d';", version);
 206
 207         ret = __uam_db_exec_sql(sql, NULL);
 208         if (UAM_ERROR_NONE != ret)
 209                 UAM_DBG("failed to updated database version to %d", version);
 210         else
 211                 UAM_DBG("Successfully updated database version to %d", version);
 212
 213         sqlite3_free(sql);
 214
 215         FUNC_EXIT;
 216         return ret;
 217 }
 218
 219 static int __uam_db_upgrade(unsigned int version)
 220 {
 221         FUNC_ENTRY;
 222         /*
 223         This code should handle all the database upgrade after initial version
 224         */
 225
 226         /* update database version */
 227         retv_if(UAM_ERROR_NONE != __uam_db_update_version(version), UAM_ERROR_DB_FAILED);
 228         UAM_DBG("Successfully upgraded database version to %d", version);
 229
 230         FUNC_EXIT;
 231         return UAM_ERROR_NONE;
 232 }
 233
 234 static int __uam_db_upgrade_version(unsigned int old_ver, unsigned int new_ver)
 235 {
 236         FUNC_ENTRY;
 237         int ret = UAM_ERROR_NONE;
 238         unsigned int i;
 239
 240         for (i = old_ver + 1; i <= new_ver; i++) {
 241                 ret = __uam_db_upgrade(i);
 242                 if (UAM_ERROR_NONE != ret) {
 243                         UAM_ERR("Faild to __uam_db_upgrade(%d)", i);
 244                         ret = UAM_ERROR_DB_FAILED;
 245                         break;
 246                 }
 247         }
 248
 249         FUNC_EXIT;
 250         return ret;
 251 }
 252
 253 int _uam_db_check_version(void)
 254 {
 255         FUNC_ENTRY;
 256         unsigned int curr_ver = VERSION;
 257         unsigned int old_ver = 0;
 258
 259         /* check database version */
 260         retv_if(UAM_ERROR_NONE != __uam_db_get_version(&old_ver), UAM_ERROR_DB_FAILED);
 261
 262         if (0 == old_ver) {
 263                 retv_if(UAM_ERROR_NONE != __uam_db_update_version(curr_ver), UAM_ERROR_DB_FAILED);
 264         } else if (old_ver != curr_ver) {
 265                 UAM_DBG("Database version changed, needs upgrade");
 266                 /* upgrade database to new version */
 267                 retv_if(UAM_ERROR_NONE != __uam_db_upgrade_version(old_ver, curr_ver), UAM_ERROR_DB_FAILED);
 268         }
 269
 270         FUNC_EXIT;
 271         return UAM_ERROR_NONE;
 272 }
 273
 274 static bool __uam_db_is_table_existing(const char *table)
 275 {
 276         char *sql;
 277         int count;
 278         bool result = false;
 279         int sql_ret = SQLITE_OK;
 280
 281         sql = sqlite3_mprintf(
 282                 "SELECT count(*) FROM sqlite_master WHERE type='table' AND name ='%s';", table);
 283         if (NULL == sql) {
 284                 UAM_ERR("sqlite3_mprintf failed");
 285                 return false;
 286         }
 287         sqlite3_stmt *stmt = NULL;
 288
 289         sql_ret = sqlite3_prepare_v2(database_handle, sql, strlen(sql), &stmt, NULL);
 290         if (SQLITE_OK != sql_ret) {
 291                 UAM_ERR("sqlite3_prepare_v2 failed, [%d:%s]", sql_ret, sqlite3_errmsg(database_handle));
 292                 result = false;
 293                 goto is_table_existing_done;
 294         }
 295
 296         sql_ret = sqlite3_step(stmt);
 297         if (sql_ret != SQLITE_ROW) {
 298                 UAM_ERR("sqlite3_step failed, [%d:%s]", sql_ret, sqlite3_errmsg(database_handle));
 299                 result = false;
 300                 goto is_table_existing_done;
 301         }
 302
 303         count = sqlite3_column_int(stmt, 0);
 304         if (count > 0)
 305                 result = true;
 306         else
 307                 result = false;
 308
 309 is_table_existing_done:
 310         sqlite3_finalize(stmt);
 311         sqlite3_free(sql);
 312
 313         return result;
 314 }
 315
 316 static int __uam_db_check_integrity_cb(
 317         void *err, int count, char **data, char **columns)
 318 {
 319         FUNC_ENTRY;
 320
 321         UAM_DBG("%s = %s\n", columns[0], data[0] ? data[0] : "NULL");
 322         if (!g_strcmp0(columns[0], "integrity_check") && !g_strcmp0(data[0], "ok"))
 323                 return SQLITE_OK;
 324
 325         return SQLITE_ERROR;
 326 }
 327
 328 static int __uam_db_check_integrity(void)
 329 {
 330         FUNC_ENTRY;
 331         int ret = UAM_ERROR_NONE;
 332         char *sql = NULL;
 333
 334         sql = sqlite3_mprintf("PRAGMA integrity_check");
 335
 336         ret = __uam_db_exec_sql(sql, __uam_db_check_integrity_cb);
 337         if (UAM_ERROR_NONE != ret)
 338                 UAM_ERR("Faild to __uam_db_exec_sql()");
 339         else
 340                 UAM_DBG("Successfully verified database integrity");
 341
 342         sqlite3_free(sql);
 343
 344         FUNC_EXIT;
 345         return ret;
 346 }
 347
 348 static int __uam_db_set_locking_mode(void)
 349 {
 350         char *sql = NULL;
 351         int ret = UAM_ERROR_NONE;
 352
 353         sql = sqlite3_mprintf("PRAGMA locking_mode = NORMAL");
 354         ret = __uam_db_exec_sql(sql, NULL);
 355         if (UAM_ERROR_NONE != ret)
 356                 ret = UAM_ERROR_DB_FAILED;
 357
 358         sqlite3_free(sql);
 359
 360         return ret;
 361 }
 362
 363 static int __uam_db_create_table(const char *table_name)
 364 {
 365         char *sql = NULL;
 366         int ret = UAM_ERROR_NONE;
 367
 368         sql = sqlite3_mprintf(table_name);
 369         ret = __uam_db_exec_sql(sql, NULL);
 370
 371         if (UAM_ERROR_NONE != ret)
 372                 UAM_ERR("Faild to __uam_db_exec_sql()");
 373         else
 374                 UAM_DBG("Successfully created table %s", table_name);
 375
 376         sqlite3_free(sql);
 377
 378         return ret;
 379 }
 380
 381 static int __uam_db_check_table_creation(void)
 382 {
 383         FUNC_ENTRY;
 384         int error_code = UAM_ERROR_DB_FAILED;
 385
 386         if (!__uam_db_is_table_existing(UAM_DB_USERDATA_TABLE))
 387                 retv_if(UAM_ERROR_NONE != __uam_db_create_table(CREATE_USERDATA_TABLE), error_code);
 388
 389         if (!__uam_db_is_table_existing(UAM_DB_DEVICES_TABLE))
 390                 retv_if(UAM_ERROR_NONE != __uam_db_create_table(CREATE_DEVICES_TABLE), error_code);
 391
 392         if (!__uam_db_is_table_existing(UAM_DB_SERVICES_TABLE))
 393                 retv_if(UAM_ERROR_NONE != __uam_db_create_table(CREATE_SERVICES_TABLE), error_code);
 394
 395         if (!__uam_db_is_table_existing(UAM_DB_DEVICE_SERVICES_TABLE))
 396                 retv_if(UAM_ERROR_NONE != __uam_db_create_table(CREATE_DEVICE_SERVICES_TABLE), error_code);
 397
 398         if (!__uam_db_is_table_existing(UAM_DB_IBEACON_ADV_TABLE))
 399                 retv_if(UAM_ERROR_NONE != __uam_db_create_table(CREATE_IBEACON_ADV_TABLE), error_code);
 400
 401         UAM_DBG("Successfully verified table creation");
 402         FUNC_EXIT;
 403         return UAM_ERROR_NONE;
 404 }
 405
 406 static int __uam_db_verify()
 407 {
 408         FUNC_ENTRY;
 409
 410         /* check table existance*/
 411         retv_if(UAM_ERROR_NONE != __uam_db_check_table_creation(), UAM_ERROR_DB_FAILED);
 412         /* check db size */
 413         _uam_get_file_size(DATABASE_FULL_PATH);
 414         /* check db integrity */
 415         retv_if(UAM_ERROR_NONE != __uam_db_check_integrity(), UAM_ERROR_DB_FAILED);
 416         /* set locking mode */
 417         retv_if(UAM_ERROR_NONE != __uam_db_set_locking_mode(), UAM_ERROR_DB_FAILED);
 418
 419         UAM_DBG("Successfully verified database");
 420
 421         return UAM_ERROR_NONE;
 422 }
 423
 424 int _uam_db_initialize_once(void)
 425 {
 426         FUNC_ENTRY;
 427         char *sql;
 428         int ret = UAM_ERROR_NONE;
 429         int max_retries = 2;
 430
 431         do {
 432                 /* open database */
 433                 retv_if(SQLITE_OK != __uam_db_open(), UAM_ERROR_DB_FAILED);
 434
 435                 if (UAM_ERROR_NONE != __uam_db_verify()) {
 436                         UAM_ERR("Failed to verify database");
 437                         sqlite3_close(database_handle);
 438                         unlink(DATABASE_FULL_PATH);
 439                         database_handle = NULL;
 440                         retv_if(0 == max_retries, UAM_ERROR_DB_FAILED);
 441                 } else {
 442                         break;
 443                 }
 444         } while (max_retries--);
 445
 446         /* Enable persist journal mode */
 447         sql = sqlite3_mprintf("PRAGMA journal_mode = PERSIST");
 448         ret = __uam_db_exec_sql(sql, NULL);
 449         sqlite3_free(sql);
 450         if (UAM_ERROR_NONE != ret) {
 451                 UAM_ERR("Faile to __uam_db_exec_sql()");
 452                 return UAM_ERROR_DB_FAILED;
 453         }
 454
 455         if (NULL == database_handle) {
 456                 unlink(DATABASE_FULL_PATH);
 457                 return UAM_ERROR_DB_FAILED;
 458         }
 459
 460         /* Set how many times we'll repeat our attempts for sqlite_step */
 461         if (SQLITE_OK != sqlite3_busy_handler(database_handle, __uam_db_busy, NULL)) {
 462                 UAM_ERR("Couldn't set busy handler!");
 463                 return UAM_ERROR_DB_FAILED;
 464         }
 465
 466         FUNC_EXIT;
 467         return ret;
 468 }
 469
 470 int _uam_db_initialize(void)
 471 {
 472         FUNC_ENTRY;
 473         database_handle = NULL;
 474
 475         /* initialize database */
 476         EXEC(UAM_ERROR_NONE, _uam_db_initialize_once(), handle_error);
 477
 478         /* check database version */
 479         EXEC(UAM_ERROR_NONE, _uam_db_check_version(), handle_error);
 480
 481         /* initialize tables */
 482         EXEC(UAM_ERROR_NONE, _uam_user_db_initialize(), handle_error);
 483         EXEC(UAM_ERROR_NONE, _uam_device_db_initialize(), handle_error);
 484         EXEC(UAM_ERROR_NONE, _uam_service_db_initialize(), handle_error);
 485         EXEC(UAM_ERROR_NONE, _uam_device_service_db_initialize(), handle_error);
 486         EXEC(UAM_ERROR_NONE, _uam_adv_db_initialize(), handle_error);
 487
 488         transaction_cnt = 0;
 489         FUNC_EXIT;
 490         return UAM_ERROR_NONE;
 491
 492 handle_error:
 493         _uam_db_deinitialize();
 494         FUNC_EXIT;
 495         return UAM_ERROR_DB_FAILED;
 496 }
 497
 498 int _uam_db_deinitialize(void)
 499 {
 500         FUNC_ENTRY;
 501
 502         retv_if(NULL == database_handle, UAM_ERROR_NONE);
 503
 504         _uam_user_db_deinitialize();
 505         _uam_device_db_deinitialize();
 506         _uam_service_db_deinitialize();
 507         _uam_device_service_db_deinitialize();
 508         _uam_adv_db_deinitialize();
 509
 510         FUNC_EXIT;
 511         return UAM_ERROR_NONE;
 512 }
 513
 514 int _uam_db_clear(void)
 515 {
 516         int error_code = UAM_ERROR_DB_FAILED;
 517         retv_if(NULL == database_handle, UAM_ERROR_DB_FAILED);
 518
 519         EXEC(UAM_ERROR_NONE, _uam_user_db_clear(), handle_error);
 520         EXEC(UAM_ERROR_NONE, _uam_device_db_clear(), handle_error);
 521         EXEC(UAM_ERROR_NONE, _uam_service_db_clear(), handle_error);
 522         EXEC(UAM_ERROR_NONE, _uam_device_service_db_clear(), handle_error);
 523         EXEC(UAM_ERROR_NONE, _uam_adv_db_clear(), handle_error);
 524         UAM_DBG("Table data deleted ");
 525
 526         error_code = UAM_ERROR_NONE;
 527
 528 handle_error:
 529         return error_code;
 530 }
 531
 532 int __uam_db_begin_transaction(void)
 533 {
 534         FUNC_ENTRY;
 535         char *sql = NULL;
 536         int ret = UAM_ERROR_NONE;
 537
 538         if (transaction_cnt <= 0) {
 539                 transaction_cnt = 0;
 540                 sql = sqlite3_mprintf("BEGIN IMMEDIATE TRANSACTION");
 541
 542                 ret = __uam_db_exec_sql(sql, NULL);
 543                 if (UAM_ERROR_NONE != ret)
 544                         UAM_DBG("failed to begin transaction");
 545                 else
 546                         UAM_DBG("Successful to begin transaction");
 547
 548                 sqlite3_free(sql);
 549         }
 550         transaction_cnt++;
 551         UAM_DBG("transaction_cnt: %d", transaction_cnt);
 552
 553         FUNC_EXIT;
 554         return ret;
 555 }
 556
 557 static int __uam_db_rollback_transaction(void)
 558 {
 559         FUNC_ENTRY;
 560         char *sql = NULL;
 561         int ret = UAM_ERROR_NONE;
 562
 563         sql = sqlite3_mprintf("ROLLBACK TRANSACTION");
 564
 565         ret = __uam_db_exec_sql(sql, NULL);
 566         if (UAM_ERROR_NONE != ret)
 567                 UAM_DBG("failed to rollback transaction");
 568         else
 569                 UAM_DBG("Successful to rollback transaction");
 570
 571         sqlite3_free(sql);
 572
 573         FUNC_EXIT;
 574         return ret;
 575 }
 576
 577 int __uam_db_end_transaction(gboolean is_success)
 578 {
 579         FUNC_ENTRY;
 580         char *sql = NULL;
 581         int ret = UAM_ERROR_NONE;
 582
 583         transaction_cnt--;
 584
 585         if (0 != transaction_cnt) {
 586                 UAM_DBG("transaction_cnt: %d", transaction_cnt);
 587                 return ret;
 588         }
 589
 590         if (false == is_success) {
 591                 ret = __uam_db_rollback_transaction();
 592                 return ret;
 593         }
 594
 595         sql = sqlite3_mprintf("COMMIT TRANSACTION");
 596
 597         ret = __uam_db_exec_sql(sql, NULL);
 598         if (UAM_ERROR_NONE != ret) {
 599                 UAM_DBG("failed to commit transaction");
 600                 ret = __uam_db_rollback_transaction();
 601         } else
 602                 UAM_DBG("Successful to commit transaction");
 603
 604         sqlite3_free(sql);
 605
 606         FUNC_EXIT;
 607         return ret;
 608 }