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 <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 <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 DropSchema(IDbBase dbBase, string schema)
{
try
{
await dbBase.ExecuteNonQueryAsync($"drop schema if exists {schema};");
}
catch (Exception ex)
{
await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource());
throw new OutputException(
new Exception($"Cannot drop schema {schema}"),
StatusCodes.Status500InternalServerError,
MnemoxStatusCodes.CANNOT_DROP_SCHEMA);
}
}
public async Task RunNonQuery(IDbBase dbBase, string query)
{
try
{
await dbBase.ExecuteNonQueryAsync(query);
}
catch (Exception ex)
{
await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource());
throw new OutputException(
new Exception($"Cannot run query {query}"),
StatusCodes.Status500InternalServerError,
MnemoxStatusCodes.CANNOT_CREATE_EXTENSION);
}
}
public async Task CreateSchema(IDbBase dbBase, string schemaName)
{
try
{
await dbBase.ExecuteNonQueryAsync($"create schema if not exists {schemaName};");
}
catch (Exception ex)
{
await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource());
throw new OutputException(
new Exception($"Cannot create schema {schemaName}"),
StatusCodes.Status500InternalServerError,
MnemoxStatusCodes.CANNOT_CREATE_SCHEMA);
}
}
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 <InitializationStatesEnums> InitializationState(IDbBase dbBase)
{
try
{
using (var reader = await dbBase.ExecuteReaderQueryAsync(SELECT_SERVER_SCHEMA_INFO))
{
if (!reader.HasRows)
{
return(InitializationStatesEnums.DATABASE_NOT_INITIALIZED);
}
}
using (var reader = await dbBase.ExecuteReaderQueryAsync(SELECT_DATABASE_STATE_TABLE_INFO))
{
if (!reader.HasRows)
{
return(InitializationStatesEnums.DATABASE_NOT_INITIALIZED);
}
}
var state = await dbBase.ExecuteScalarAsync(GET_INITIALIZATION_STATE_FNC_NAME);
if (state is DBNull)
{
return(InitializationStatesEnums.DATABASE_NOT_INITIALIZED);
}
return((InitializationStatesEnums)(short)state);
}
catch (Exception ex)
{
await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource());
throw new OutputException(
ex,
StatusCodes.Status500InternalServerError,
MnemoxStatusCodes.CANNOT_CREATE_EXTENSION);
}
}
public void SetProvider(MyDBType mydbtype)
{
Provider = new DbBase(mydbtype);
switch (mydbtype)
{
case MyDBType.Access:
connstr = DBConfig.CmsAccessConString;
break;
case MyDBType.Sql:
connstr = DBConfig.CmsSqlConString;
break;
case MyDBType.Oracle:
connstr = DBConfig.CmsOracleConString;
break;
case MyDBType.Other:
connstr = DBConfig.CmsConString;
break;
}
}
public async Task CreateExtension(IDbBase dbBase, string extensionName, string schema)
{
try
{
await dbBase.ExecuteNonQueryAsync($"create extension if not exists {extensionName} schema {schema};");
}
catch (Exception ex)
{
if (ex.Message.Contains("has already been loaded with another version"))
{
throw new OutputException(
new Exception($"Cannot create extension {extensionName} in schema {schema}, PostgreSQL service had to be restarted"),
StatusCodes.Status500InternalServerError,
MnemoxStatusCodes.CANNOT_CREATE_EXTENSION);
}
await _logsManager.ErrorAsync(new ErrorLogStructure(ex).WithErrorSource());
throw new OutputException(
new Exception($"Cannot create extension {extensionName} in schema {schema}"),
StatusCodes.Status500InternalServerError,
MnemoxStatusCodes.CANNOT_CREATE_EXTENSION);
}
}
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 MSSqlHelper(string connectionNodeName)
{
MyDatabase = DBhelper.CreateMsSql(DBhelper.GetConnectionStr(connectionNodeName));
}
public MSSqlHelper(string connection, bool isDirectConn)
{
MyDatabase = DBhelper.CreateMsSql(connection);
}
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();
}
}
}
public static void SetProvider(MyDBType mydbtype)
{
Provider = new DbBase(mydbtype);
}
