From d091407b77b0ad7c6b0462fc11f3dd62ae60e211 Mon Sep 17 00:00:00 2001 From: Abhay agarwal Date: Fri, 6 Dec 2019 09:37:56 +0530 Subject: [PATCH] Add database APIs to update device service payload info Signed-off-by: Abhay agarwal --- ua-daemon/data/ua_db.sql | 2 + ua-daemon/include/ua-manager-database.h | 7 + ua-daemon/src/ua-manager-core.c | 14 +- ua-daemon/src/ua-manager-db.c | 2 + ua-daemon/src/ua-manager-device-db.c | 2 +- ua-daemon/src/ua-manager-device-service-db.c | 82 +++++++++++ ua-daemon/src/ua-manager-payload-db.c | 203 +++++++++++++++------------ ua-daemon/src/ua-manager-service-db.c | 2 +- 8 files changed, 214 insertions(+), 100 deletions(-) diff --git a/ua-daemon/data/ua_db.sql b/ua-daemon/data/ua_db.sql index dac1447..c83282d 100644 --- a/ua-daemon/data/ua_db.sql +++ b/ua-daemon/data/ua_db.sql @@ -63,10 +63,12 @@ CREATE TABLE IF NOT EXISTS services ( CREATE TABLE IF NOT EXISTS device_services ( device_number INTEGER, service_number INTEGER, + payload_number INTEGER, discriminant INTEGER, last_seen LONG, FOREIGN KEY(device_number) REFERENCES devices(device_number), FOREIGN KEY(service_number) REFERENCES services(service_number), + FOREIGN KEY(payload_number) REFERENCES payloads(payload_number), PRIMARY KEY(device_number, service_number) ); diff --git a/ua-daemon/include/ua-manager-database.h b/ua-daemon/include/ua-manager-database.h index aa46e34..d514af7 100644 --- a/ua-daemon/include/ua-manager-database.h +++ b/ua-daemon/include/ua-manager-database.h @@ -222,6 +222,8 @@ int _uam_db_update_device_service_discriminant(const char *device_id, int _uam_db_update_device_service_last_seen(const char *device_id, int tech_type, const char *address, const char *service_name, unsigned long long last_seen); +int _uam_db_update_device_service_payload_info( + uam_ble_payload_s *payload, char *service_name); /* IBEACON_ADV QUERIES */ @@ -262,6 +264,11 @@ int _uam_db_insert_payload_info( /* delete operations */ int _uam_payload_db_clear(void); +/* select operations */ +int _uam_db_get_payload_device_number(uam_ble_payload_s *payload, + int *device_number); +int _uam_db_get_payload_number(uam_ble_payload_s *payload, + int *payload_number); #ifdef __cplusplus } diff --git a/ua-daemon/src/ua-manager-core.c b/ua-daemon/src/ua-manager-core.c index c25cc79..8b18cb2 100644 --- a/ua-daemon/src/ua-manager-core.c +++ b/ua-daemon/src/ua-manager-core.c @@ -3907,21 +3907,21 @@ int _uam_core_service_add_payload(uam_ble_payload_s *payload, { FUNC_ENTRY; int ret = UAM_ERROR_NONE; -// uam_db_service_info_t *service; -// GSList *l; + uam_db_service_info_t *service; + GSList *l; retv_if(NULL == payload, UAM_ERROR_INVALID_PARAMETER); retv_if(NULL == svc_name, UAM_ERROR_INVALID_PARAMETER); /* Retrieve service from list */ -// l = g_slist_find_custom(services, svc_name, __compare_svc_name); -// retv_if(NULL == l, UAM_ERROR_INVALID_PARAMETER); -// service = l->data; + l = g_slist_find_custom(services, svc_name, __compare_svc_name); + retv_if(NULL == l, UAM_ERROR_INVALID_PARAMETER); + service = l->data; /*** Add payload to database ***/ -// ret = _uam_db_insert_payload_service_info(payload, service); + ret = _uam_db_update_device_service_payload_info(payload, service->name); if (UAM_ERROR_NONE != ret) { - UAM_ERR("_uam_db_insert_payload_service_info failed"); + UAM_ERR("_uam_db_update_device_service_payload_info failed"); return ret; } diff --git a/ua-daemon/src/ua-manager-db.c b/ua-daemon/src/ua-manager-db.c index f4abfcc..de1a01d 100644 --- a/ua-daemon/src/ua-manager-db.c +++ b/ua-daemon/src/ua-manager-db.c @@ -77,10 +77,12 @@ #define CREATE_DEVICE_SERVICES_TABLE "CREATE TABLE IF NOT EXISTS device_services ( " \ "device_number INTEGER, " \ "service_number INTEGER, " \ + "payload_number INTEGER, " \ "discriminant INTEGER, " \ "last_seen LONG, " \ "FOREIGN KEY(device_number) REFERENCES devices(device_number), " \ "FOREIGN KEY(service_number) REFERENCES services(service_number), " \ + "FOREIGN KEY(payload_number) REFERENCES payloads(payload_number), " \ "PRIMARY KEY(device_number, service_number) " \ "); " diff --git a/ua-daemon/src/ua-manager-device-db.c b/ua-daemon/src/ua-manager-device-db.c index 8615a96..9ee9ddf 100644 --- a/ua-daemon/src/ua-manager-device-db.c +++ b/ua-daemon/src/ua-manager-device-db.c @@ -555,7 +555,7 @@ int _uam_device_db_get_device(char *device_id, int tech_type, char *address, retv_if(NULL == info, UAM_ERROR_INVALID_PARAMETER); - UAM_INFO("UserId: %s", device_id); + UAM_INFO("device id: %s", device_id); DB_ACTION(sqlite3_bind_text(stmt, 1, device_id, -1, SQLITE_STATIC), error_code, handle_error); diff --git a/ua-daemon/src/ua-manager-device-service-db.c b/ua-daemon/src/ua-manager-device-service-db.c index 37bff4a..024e50c 100644 --- a/ua-daemon/src/ua-manager-device-service-db.c +++ b/ua-daemon/src/ua-manager-device-service-db.c @@ -56,6 +56,9 @@ #define UPDATE_DEVICE_SERVICE_LAST_SEEN "UPDATE device_services " \ "SET last_seen = ? WHERE device_number = ? AND service_number = ?" +#define UPDATE_DEVICE_SERVICE_PAYLOAD "UPDATE device_services " \ + "SET payload_number = ? WHERE device_number = ? AND service_number = ?" + /* DELETE statements */ static sqlite3_stmt *delete_all_device_services; static sqlite3_stmt *delete_service; @@ -73,6 +76,7 @@ static sqlite3_stmt *insert_device_service; /* UPDATE statements */ static sqlite3_stmt *update_device_service_discriminant; static sqlite3_stmt *update_device_service_last_seen; +static sqlite3_stmt *update_device_service_payload; extern sqlite3 *database_handle; @@ -114,6 +118,7 @@ static void __uam_device_service_finalize_update(void) FINALIZE(update_device_service_discriminant); FINALIZE(update_device_service_last_seen); + FINALIZE(update_device_service_payload); FUNC_EXIT; } @@ -181,6 +186,8 @@ static int __uam_device_service_prepare_update(sqlite3 *db) UPDATE_DEVICE_SERVICE_DISCRIMINANT, __uam_device_service_finalize_update); PREPARE_QUERY(rc, db, update_device_service_last_seen, UPDATE_DEVICE_SERVICE_LAST_SEEN, __uam_device_service_finalize_update); + PREPARE_QUERY(rc, db, update_device_service_payload, + UPDATE_DEVICE_SERVICE_PAYLOAD, __uam_device_service_finalize_update); initialized = 1; FUNC_EXIT; @@ -806,3 +813,78 @@ GSList *_uam_db_get_service_devices_info() FUNC_EXIT; return svc_dev_list; } + +static int __uam_db_update_dev_svc_payload(int device_number, + int service_number, int payload_number) +{ + FUNC_ENTRY; + int error_code = UAM_ERROR_NONE; + sqlite3_stmt *stmt = update_device_service_payload; + int sql_ret = SQLITE_OK; + + UAM_INFO("service: %d device: %d payload: %d", + device_number, service_number, payload_number); + + DB_ACTION(sqlite3_bind_int(stmt, 1, payload_number), error_code, handle_error); + DB_ACTION(sqlite3_bind_int(stmt, 2, device_number), error_code, handle_error); + DB_ACTION(sqlite3_bind_int(stmt, 3, service_number), error_code, handle_error); + + sql_ret = sqlite3_step(stmt); + if (sql_ret != SQLITE_DONE) { + UAM_ERR("Failed to update device-service payload [%d:%s]", + sql_ret, sqlite3_errmsg(database_handle)); + error_code = UAM_ERROR_DB_FAILED; + goto handle_error; + } + + UAM_DBG("Device-service payload info updated [%d] [%d] [%d] ", + device_number, service_number, payload_number); + +handle_error: + sqlite3_reset(stmt); + FUNC_EXIT; + return error_code; +} + +int _uam_db_update_device_service_payload_info( + uam_ble_payload_s *payload, char *service_name) +{ + FUNC_ENTRY; + int ret = UAM_ERROR_NONE; + db_service_info_t info; + int service_number = 0; + int device_number = 0; + int payload_number = 0; + + retv_if(NULL == payload, UAM_ERROR_INVALID_PARAMETER); + retv_if(NULL == service_name, UAM_ERROR_INVALID_PARAMETER); + + ret = _uam_db_get_service_info(service_name, &info); + if (UAM_ERROR_NONE != ret) + goto handle_error; + service_number = info.service_number; + + ret = _uam_db_get_payload_device_number(payload, &device_number); + if (UAM_ERROR_NONE != ret) + goto handle_error; + + ret = _uam_db_get_payload_number(payload, &payload_number); + if (UAM_ERROR_NONE != ret) + goto handle_error; + + UAM_INFO("service: %d-%s device: %d payload: %d", + service_number, service_name, + device_number, payload_number); + + ret = __uam_db_update_dev_svc_payload(device_number, + service_number, payload_number); + if (UAM_ERROR_NONE != ret) + goto handle_error; + + UAM_DBG("payload-service info inserted [%d] [%d]", device_number, + service_number); + +handle_error: + FUNC_EXIT; + return ret; +} diff --git a/ua-daemon/src/ua-manager-payload-db.c b/ua-daemon/src/ua-manager-payload-db.c index f69af2a..3457469 100644 --- a/ua-daemon/src/ua-manager-payload-db.c +++ b/ua-daemon/src/ua-manager-payload-db.c @@ -33,6 +33,11 @@ "where payload_name = ?" */ +#define SELECT_PAYLOAD_DEVICE "SELECT device_number FROM payloads " \ + "where primary_key = ? AND secondary_key = ? AND device_uid = ?" +#define SELECT_PAYLOAD_NUMBER "SELECT payload_number FROM payloads " \ + "where primary_key = ? AND secondary_key = ? AND device_uid = ?" + #define INSERT_PAYLOAD "insert into payloads (primary_key, " \ "secondary_key, device_uid, device_icon, device_number)" \ "values (?, ?, ?, ?, ?)" @@ -55,6 +60,8 @@ static sqlite3_stmt *delete_all_payloads; /* SELECT statements */ //static sqlite3_stmt *select_all_payloads; //static sqlite3_stmt *select_payload; +static sqlite3_stmt *select_payload_device; +static sqlite3_stmt *select_payload_number; /* UPDATE statements */ //static sqlite3_stmt *update_cycle; @@ -81,6 +88,8 @@ static void __uam_payload_finalize_select(void) // FINALIZE(select_all_payloads); // FINALIZE(select_payload); + FINALIZE(select_payload_device); + FINALIZE(select_payload_number); FUNC_EXIT; } @@ -139,6 +148,10 @@ static int __uam_payload_prepare_select(sqlite3 *db) // SELECT_ALL_PAYLOADS, __uam_payload_finalize_select); // PREPARE_QUERY(rc, db, select_payload, // SELECT_PAYLOAD, __uam_payload_finalize_select); + PREPARE_QUERY(rc, db, select_payload_device, + SELECT_PAYLOAD_DEVICE, __uam_payload_finalize_select); + PREPARE_QUERY(rc, db, select_payload_number, + SELECT_PAYLOAD_NUMBER, __uam_payload_finalize_select); initialized = 1; FUNC_EXIT; @@ -291,35 +304,40 @@ handle_error: return error_code; } -/* -int _uam_db_update_payload_info(uam_db_payload_info_t *svc) +int _uam_db_get_payload_device_number(uam_ble_payload_s *payload, + int *device_number) { FUNC_ENTRY; int error_code = UAM_ERROR_NONE; - sqlite3_stmt *stmt = update_payload_info; + sqlite3_stmt *stmt = select_payload_device; int sql_ret = SQLITE_OK; - retv_if(NULL == svc->name, UAM_ERROR_INVALID_PARAMETER); - - UAM_INFO("%s-%d-%d-%d", svc->name, svc->cycle, svc->presence_threshold, - svc->absence_threshold); + retv_if(NULL == payload, UAM_ERROR_INVALID_PARAMETER); - DB_ACTION(sqlite3_bind_int(stmt, 1, svc->presence_threshold), + DB_ACTION(sqlite3_bind_text(stmt, 1, &(payload->primary_key), 1, SQLITE_TRANSIENT), error_code, handle_error); - DB_ACTION(sqlite3_bind_int(stmt, 2, svc->absence_threshold), + DB_ACTION(sqlite3_bind_text(stmt, 2, &(payload->secondary_key), 1, SQLITE_TRANSIENT), error_code, handle_error); - DB_ACTION(sqlite3_bind_text(stmt, 3, svc->name, -1, SQLITE_TRANSIENT), + DB_ACTION(sqlite3_bind_text(stmt, 3, payload->device_uid, UAM_BLE_PAYLOAD_DEVICE_UID_LEN + 1, SQLITE_TRANSIENT), error_code, handle_error); - sql_ret = sqlite3_step(stmt); - if (sql_ret != SQLITE_DONE) { - UAM_ERR("Failed to update payload info [%d:%s]", - sql_ret, sqlite3_errmsg(database_handle)); - error_code = UAM_ERROR_DB_FAILED; - goto handle_error; - } + do { + sql_ret = sqlite3_step(stmt); - UAM_DBG("Payload info updated"); + switch (sql_ret) { + case SQLITE_DONE: + break; + case SQLITE_ROW: + UAM_DBG("Device number info found"); + *device_number = sqlite3_column_int(stmt, 0); + UAM_INFO("device_number %d", *device_number); + break; + case SQLITE_ERROR: + default: + UAM_ERR("Failed to select payload device number [%d:%s]", + sql_ret, sqlite3_errmsg(database_handle)); + } + } while (sql_ret == SQLITE_ROW); handle_error: sqlite3_reset(stmt); @@ -327,104 +345,129 @@ handle_error: return error_code; } -int _uam_db_delete_payload_info(const char *payload_name) +int _uam_db_get_payload_number(uam_ble_payload_s *payload, + int *payload_number) { FUNC_ENTRY; int error_code = UAM_ERROR_NONE; - sqlite3_stmt *stmt = delete_payload_info; + sqlite3_stmt *stmt = select_payload_number; int sql_ret = SQLITE_OK; - error_code = _uam_db_delete_device_payload(payload_name); - if (UAM_ERROR_NONE != error_code) { - UAM_ERR("_uam_db_delete_device_payload failed"); - return error_code; - } + retv_if(NULL == payload, UAM_ERROR_INVALID_PARAMETER); - DB_ACTION(sqlite3_bind_text(stmt, 1, payload_name, -1, SQLITE_STATIC), + DB_ACTION(sqlite3_bind_text(stmt, 1, &(payload->primary_key), 1, SQLITE_TRANSIENT), error_code, handle_error); + DB_ACTION(sqlite3_bind_text(stmt, 2, &(payload->secondary_key), 1, SQLITE_TRANSIENT), + error_code, handle_error); + DB_ACTION(sqlite3_bind_text(stmt, 3, payload->device_uid, UAM_BLE_PAYLOAD_DEVICE_UID_LEN + 1, SQLITE_TRANSIENT), + error_code, handle_error); + + do { + sql_ret = sqlite3_step(stmt); + + switch (sql_ret) { + case SQLITE_DONE: + break; + case SQLITE_ROW: + UAM_DBG("Payload number info found"); + *payload_number = sqlite3_column_int(stmt, 0); + UAM_INFO("payload number: %d", *payload_number); + break; + case SQLITE_ERROR: + default: + UAM_ERR("Failed to select payload device number [%d:%s]", + sql_ret, sqlite3_errmsg(database_handle)); + } + } while (sql_ret == SQLITE_ROW); + +handle_error: + sqlite3_reset(stmt); + FUNC_EXIT; + return error_code; +} + + +int _uam_payload_db_clear(void) +{ + int error_code = UAM_ERROR_NONE; + sqlite3_stmt *stmt = delete_all_payloads; + int sql_ret = SQLITE_OK; sql_ret = sqlite3_step(stmt); if (sql_ret != SQLITE_DONE) { - UAM_ERR("Failed to delete payload info [%d:%s]", + UAM_ERR("Failed to delete payload data [%d:%s]", sql_ret, sqlite3_errmsg(database_handle)); error_code = UAM_ERROR_DB_FAILED; - } else - UAM_DBG("Payload info deleted"); + goto handle_error; + } + + UAM_DBG("Payload data deleted "); handle_error: + sqlite3_reset(stmt); - FUNC_EXIT; return error_code; } -int _uam_db_update_payload_cycle(const char *payload_name, int cycle) +/* +int _uam_db_update_payload_info(uam_db_payload_info_t *svc) { + FUNC_ENTRY; int error_code = UAM_ERROR_NONE; - sqlite3_stmt *stmt = update_cycle; + sqlite3_stmt *stmt = update_payload_info; int sql_ret = SQLITE_OK; - retv_if(NULL == payload_name, UAM_ERROR_INVALID_PARAMETER); + retv_if(NULL == svc->name, UAM_ERROR_INVALID_PARAMETER); - DB_ACTION(sqlite3_bind_int64(stmt, 1, cycle), + UAM_INFO("%s-%d-%d-%d", svc->name, svc->cycle, svc->presence_threshold, + svc->absence_threshold); + + DB_ACTION(sqlite3_bind_int(stmt, 1, svc->presence_threshold), error_code, handle_error); - DB_ACTION(sqlite3_bind_text(stmt, 2, payload_name, -1, SQLITE_TRANSIENT), + DB_ACTION(sqlite3_bind_int(stmt, 2, svc->absence_threshold), + error_code, handle_error); + DB_ACTION(sqlite3_bind_text(stmt, 3, svc->name, -1, SQLITE_TRANSIENT), error_code, handle_error); sql_ret = sqlite3_step(stmt); if (sql_ret != SQLITE_DONE) { - UAM_ERR("Failed to update payload cycle [%d:%s]", + UAM_ERR("Failed to update payload info [%d:%s]", sql_ret, sqlite3_errmsg(database_handle)); - error_code = UAM_ERROR_DB_FAILED; goto handle_error; } - UAM_DBG("Payload cycle updated [%d]", cycle); + UAM_DBG("Payload info updated"); handle_error: sqlite3_reset(stmt); + FUNC_EXIT; return error_code; } -int _uam_db_get_payload_info(const char *payload_name, db_payload_info_t *info) +int _uam_db_delete_payload_info(const char *payload_name) { FUNC_ENTRY; - int error_code = UAM_ERROR_NOT_FOUND; - sqlite3_stmt *stmt = select_payload; + int error_code = UAM_ERROR_NONE; + sqlite3_stmt *stmt = delete_payload_info; int sql_ret = SQLITE_OK; - retv_if(!payload_name, UAM_ERROR_INVALID_PARAMETER); - retv_if(!info, UAM_ERROR_INVALID_PARAMETER); - - UAM_INFO("Payload Name: %s", payload_name); + error_code = _uam_db_delete_device_payload(payload_name); + if (UAM_ERROR_NONE != error_code) { + UAM_ERR("_uam_db_delete_device_payload failed"); + return error_code; + } DB_ACTION(sqlite3_bind_text(stmt, 1, payload_name, -1, SQLITE_STATIC), error_code, handle_error); - do { - sql_ret = sqlite3_step(stmt); - switch (sql_ret) { - case SQLITE_DONE: - break; - case SQLITE_ROW: - UAM_DBG("Payload info found"); - info->payload_number = sqlite3_column_int(stmt, 0); - g_strlcpy(info->payload_name, (char *)sqlite3_column_text(stmt, 1), - UAM_PAYLOAD_MAX_STRING_LEN); - info->cycle = sqlite3_column_int(stmt, 2); - info->presence_threshold = sqlite3_column_int(stmt, 3); - info->absence_threshold = sqlite3_column_int(stmt, 4); - - UAM_INFO("%d-%s-%d", info->payload_number, info->payload_name, info->cycle); - error_code = UAM_ERROR_NONE; - break; - case SQLITE_ERROR: - default: - UAM_ERR("Failed to enumerate device info [%d:%s]", - sql_ret, sqlite3_errmsg(database_handle)); - error_code = UAM_ERROR_DB_FAILED; - } - } while (sql_ret == SQLITE_ROW); + sql_ret = sqlite3_step(stmt); + if (sql_ret != SQLITE_DONE) { + UAM_ERR("Failed to delete payload info [%d:%s]", + sql_ret, sqlite3_errmsg(database_handle)); + error_code = UAM_ERROR_DB_FAILED; + } else + UAM_DBG("Payload info deleted"); handle_error: sqlite3_reset(stmt); @@ -471,25 +514,3 @@ GSList *_uam_payload_db_get_all_payloads(void) return payload_list; } */ - -int _uam_payload_db_clear(void) -{ - int error_code = UAM_ERROR_NONE; - sqlite3_stmt *stmt = delete_all_payloads; - int sql_ret = SQLITE_OK; - - sql_ret = sqlite3_step(stmt); - if (sql_ret != SQLITE_DONE) { - UAM_ERR("Failed to delete payload data [%d:%s]", - sql_ret, sqlite3_errmsg(database_handle)); - error_code = UAM_ERROR_DB_FAILED; - goto handle_error; - } - - UAM_DBG("Payload data deleted "); - -handle_error: - - sqlite3_reset(stmt); - return error_code; -} diff --git a/ua-daemon/src/ua-manager-service-db.c b/ua-daemon/src/ua-manager-service-db.c index 892b816..d68576a 100644 --- a/ua-daemon/src/ua-manager-service-db.c +++ b/ua-daemon/src/ua-manager-service-db.c @@ -429,7 +429,7 @@ int _uam_db_get_service_info(const char *service_name, db_service_info_t *info) break; case SQLITE_ERROR: default: - UAM_ERR("Failed to enumerate device info [%d:%s]", + UAM_ERR("Failed to enumerate service info [%d:%s]", sql_ret, sqlite3_errmsg(database_handle)); error_code = UAM_ERROR_DB_FAILED; } -- 2.7.4