public async Task <StatesEnums?> GetLastInitializationState() { IDbBase db = null; StatesEnums?statesEnums = null; try { db = _dbFactory.GetDbBase(); await db.ConnectAsync(); using (var reader = await db.ExecuteReaderFunctionAsync(GET_LAST_INITIALIZATION_STATE)) { if (reader.HasRows && reader.Read()) { statesEnums = (StatesEnums)Convert.ToInt16(reader["o_state_id"]); } } return(statesEnums); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { await db?.DisconnectAsync(); } }
public async Task <List <User> > GetUsersByRole(RolesEnum rolesEnum) { IDbBase dbBase = null; List <User> users = null; try { var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = (short)rolesEnum, ParameterName = _dataManagersHelpers.CreateParameterName(ConstantsTs.ROLE_ID), NpgsqlDbType = NpgsqlDbType.Smallint } }; dbBase = _dbFactory.GetDbBase(); await dbBase.ConnectAsync(); using (var reader = await dbBase.ExecuteReaderFunctionAsync(GET_USERS_BY_ROLE_FNC_NAME, parameters)) { if (reader.HasRows) { users = new List <User>(); while (reader.Read()) { var email = reader[_dataManagersHelpers.CreateSelectPropertyName(ConstantsTs.EMAIL)]; var firstName = reader[_dataManagersHelpers.CreateSelectPropertyName(ConstantsTs.FIRST_NAME)]; var lastName = reader[_dataManagersHelpers.CreateSelectPropertyName(ConstantsTs.LAST_NAME)]; users.Add( new User { UserId = Convert.ToInt64(_dataManagersHelpers.CreateSelectPropertyName(ConstantsTs.USER_ID)), Email = email is DBNull ? null : email.ToString(), FirstName = firstName is DBNull ? null : firstName.ToString(), LastName = lastName is DBNull ? null : lastName.ToString() } ); } } } return(users); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { await dbBase?.DisconnectAsync(); } }
public async Task ServerInitizalizationStateSet(InitializationStatesEnums databaseState) { IDbBase dbBase = null; try { var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = (short)databaseState, ParameterName = STATE_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Smallint } }; dbBase = _dbFactory.GetDbBase(); await dbBase.ConnectAsync(); await dbBase.ExecuteNonQueryFunctionAsync(SET_INITIALIZATION_STATE_FNC_NAME, parameters); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new OutputException( ex, StatusCodes.Status500InternalServerError, MnemoxStatusCodes.CANNOT_SET_STATE_PARAMETER); } finally { await dbBase?.DisconnectAsync(); } }
public async Task <long> AddResource(ResourceBaseModel resource) { IDbBase dbBase = null; try { if (string.IsNullOrWhiteSpace(resource.Name)) { throw new OutputException( new Exception(RESOURCE_NAME_IS_MANDATORY), StatusCodes.Status400BadRequest, MnemoxStatusCodes.INVALID_MODEL); } dbBase = _dbFactory.GetDbBase(); var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = resource.Name, ParameterName = RESOURCE_NAME_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar }, new TimescaleParameter { NpgsqlValue = resource.Description, ParameterName = RESOURCE_DESCRIPTION_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar }, new TimescaleParameter { NpgsqlValue = resource.Type, ParameterName = RESOURCE_TYPE_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Smallint } }; await dbBase.ConnectAsync(); var resourceId = (long)await dbBase.ExecuteScalarAsync(ADD_RESOURCE_FUNCTION_NAME, parameters); return(resourceId); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { if (dbBase != null) { await dbBase.DisconnectAsync(); } } }
public async Task <Tokens> GetTokenDetailsFromDataStorgeAsync(string token) { IDbBase dbBase = null; Tokens tokenResponse = null; try { dbBase = _dbFactory.GetDbBase(); var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = token, ParameterName = TOKEN_PARAMTER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar } }; await dbBase.ConnectAsync(); using (var reader = await dbBase.ExecuteReaderFunctionAsync(GET_TOKEN_DETAILS_FUNCTION_NAME, parameters)) { if (reader.HasRows && reader.Read()) { tokenResponse = new Tokens { TokenId = _dataManagersHelpers.GetLong(reader, OUTPUT_TOKEN_ID_PARAMTER_NAME).Value, Token = _dataManagersHelpers.GetString(reader, OUTPUT_TOKEN_PARAMTER_NAME), OwnerId = _dataManagersHelpers.GetLong(reader, OUTPUT_TOKEN_OWNER_ID_PARAMTER_NAME).Value, MnemoxAccessObjectsType = (MnemoxAccessObjectsTypesEnum)_dataManagersHelpers.GetInt(reader, OUTPUT_TOKEN_OWNER_TYPE_ID_PARAMTER_NAME).Value, ValidUntilDateTimeUtc = _dataManagersHelpers.GetDateTime(reader, OUTPUT_TOKEN_VALID_UNTIL_PARAMTER_NAME).Value }; } } return(tokenResponse); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { if (dbBase != null) { await dbBase.DisconnectAsync(); } } }
public async Task <long> SetSignedInUserIntoStorage(User user, string signInToken, DateTime tokenValidUntilUtc) { IDbBase dbBase = null; try { var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = signInToken, ParameterName = TOKEN_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar }, new TimescaleParameter { NpgsqlValue = user.UserId, ParameterName = OWNER_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Bigint }, new TimescaleParameter { NpgsqlValue = (int)MnemoxAccessObjectsTypesEnum.USER, ParameterName = OWNER_TYPE_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Integer }, new TimescaleParameter { NpgsqlValue = tokenValidUntilUtc, ParameterName = VALID_UNTIL_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Timestamp } }; dbBase = _dbFactory.GetDbBase(); await dbBase.ConnectAsync(); var tokenId = (long)await dbBase.ExecuteScalarAsync(STORE_TOKEN_FUNCTION_NAME, parameters); return(tokenId); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { if (dbBase != null) { await dbBase.DisconnectAsync(); } } }
public async Task <List <ServerModel> > GetServersListByStateOrAllAsync(StatesEnums?state = null) { IDbBase db = null; try { List <ServerModel> servers = null; var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = state != null ? (short)state : DBNull.Value, ParameterName = STATE_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Smallint } }; db = _dbFactory.GetDbBase(); await db.ConnectAsync(); using (var reader = await db.ExecuteReaderFunctionAsync(GET_SERVERS_LIST_FNC_NAME, parameters)) { if (reader.HasRows) { servers = new List <ServerModel>(); while (reader.Read()) { servers.Add(new ServerModel { ServerId = Convert.ToInt64(reader["o_server_id"]), ServerName = reader["o_server_name"].ToString(), ServerState = (StatesEnums)Convert.ToInt16(reader["o_state_id"]) }); } } } return(servers); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { await db?.DisconnectAsync(); } }
public async Task <ServerModel> GetServerDetailsById(long serverId) { IDbBase db = null; try { ServerModel server = null; var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = serverId, ParameterName = SERVER_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Bigint } }; db = _dbFactory.GetDbBase(); await db.ConnectAsync(); using (var reader = await db.ExecuteReaderFunctionAsync(GET_SERVER_DETAILS_BY_ID_FNC_NAME, parameters)) { if (reader.HasRows && reader.Read()) { server = new ServerModel { ServerId = Convert.ToInt64(reader["o_server_id"]), ServerName = reader["o_server_name"].ToString(), ServerState = (StatesEnums)Convert.ToInt16(reader["o_state_id"]) }; } } return(server); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { await db?.DisconnectAsync(); } }
public async Task <int> Add(ServerModel serverModel) { IDbBase db = null; try { var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = serverModel.ServerName, ParameterName = SERVER_NAME_PARAMTER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar }, new TimescaleParameter { NpgsqlValue = (short)serverModel.ServerState, ParameterName = STATE_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Smallint } }; db = _dbFactory.GetDbBase(); await db.ConnectAsync(); var serverId = (int)await db.ExecuteScalarAsync(ADD_SERVER_FNC_NAME, parameters); return(serverId); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { await db?.DisconnectAsync(); } }
public async Task StoreHeartBeat(HeartBeatRequest heartBeatRequest) { IDbBase dbBase = null; try { dbBase = _dbFactory.GetDbBase(); var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = heartBeatRequest.InstanceId, ParameterName = INSTANCE_ID_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Bigint } }; await dbBase.ConnectAsync(); await dbBase.ExecuteNonQueryFunctionAsync(ADD_HEART_BEAT_FUNCTION_NAME, parameters); } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { if (dbBase != null) { await dbBase.DisconnectAsync(); } } }
public async Task <InitializationStatesEnums> InitializeDataStorage(dynamic settings) { IDbBase db = null; try { InfrastructureSettings infrastructureSettings = settings; db = _dbFactory.GetDbBase(infrastructureSettings.ConnectonString); await db.ConnectAsync(); var databaseInitializationState = await _timescaleInfrastructureHelpers.InitializationState(db); if (databaseInitializationState == InitializationStatesEnums.DATABASE_INITIALIZED) { _timescaleInfrastructureHelpers.ReInitConnectionString(infrastructureSettings); return(databaseInitializationState); } await _timescaleInfrastructureHelpers.CreateSchema(db, "server"); await _timescaleInfrastructureHelpers.CreateSchema(db, "monitoring"); await _timescaleInfrastructureHelpers.CreateExtension(db, "timescaledb", "monitoring"); await _timescaleInfrastructureHelpers.CreateSchema(db, "resources"); await _timescaleInfrastructureHelpers.CreateSchema(db, "tenants"); await _timescaleInfrastructureHelpers.DropSchema(db, "public"); await _timescaleInfrastructureHelpers.CreateExtension(db, "pgcrypto", "tenants"); var pathToInitializationFile = Path.Combine( _settingsManager.FullSettings.ServerSettings.BasePath, PATH_TO_DATABASE_INITIALIZATION_FILES_FOLDER, TABLES_AND_FUNCTIONS_FILE); var tablesAndFunctionsInitialization = _timescaleInfrastructureHelpers.LoadFile( pathToInitializationFile ); await _timescaleInfrastructureHelpers.RunNonQuery(db, tablesAndFunctionsInitialization); await _settingsManager.ReloadSettingsAsync(); _timescaleInfrastructureHelpers.ReInitConnectionString(infrastructureSettings); await _serversManager.ServerInitizalizationStateSet(InitializationStatesEnums.DATABASE_INITIALIZED); return(InitializationStatesEnums.DATABASE_INITIALIZED); } catch (OutputException) { throw; } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new OutputException(ex, StatusCodes.Status500InternalServerError, MnemoxStatusCodes.INTERNAL_SERVER_ERROR); } finally { await db?.DisconnectAsync(); } }
public async Task <User> GetUserByUsernameAndPassword(string username, string password) { IDbBase dbBase = null; User user = null; try { if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password)) { throw new OutputException( new Exception(ALL_FIELDS_ARE_MANDATORY), StatusCodes.Status400BadRequest, MnemoxStatusCodes.INVALID_MODEL ); } var parameters = new List <TimescaleParameter> { new TimescaleParameter { NpgsqlValue = username, ParameterName = USERNAME_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar }, new TimescaleParameter { NpgsqlValue = password, ParameterName = PASSWORD_PARAMETER_NAME, NpgsqlDbType = NpgsqlDbType.Varchar } }; dbBase = _dbFactory.GetDbBase(); await dbBase.ConnectAsync(); using (var userReader = await dbBase.ExecuteReaderFunctionAsync(SIGN_IN_USER_FUNCTION_NAME, parameters)) { if (!userReader.HasRows) { return(null); } userReader.Read(); user = new User { UserId = Convert.ToInt64(userReader[USER_ID_READER_FIELD_NAME]), FirstName = _dataManagersHelpers.GetString(userReader, FIRST_NAME_READER_FIELD_NAME), LastName = _dataManagersHelpers.GetString(userReader, LAST_NAME_READER_FIELD_NAME) }; } return(user); } catch (OutputException) { throw; } catch (Exception ex) { await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource()); throw new HandledException(ex); } finally { if (dbBase != null) { await dbBase.DisconnectAsync(); } } }