public PostgreSQLRepository(PostgreSQLTypeLookup typeLookup, PostgreSQLConstants <T> sqlConstants, EntityUtils entityUtils,
ExpressionUtils expressionUtils, PostgreSQLExecutor sqlExecutor, List <string> updateableFields) : base(typeLookup, sqlConstants, entityUtils,
expressionUtils, sqlExecutor, updateableFields)
{
var sb = new StringBuilder();
for (var i = 0; i < Fields.Length; i++)
{
var field = Fields[i];
sb.Append($"{PostgreSQLConstants.PARAMETER_PRESIGN}{SQLConstants.PARAMETER_PREFIX}{field.Name.GetDelimitedName()},");
}
var lastComma = sb.ToString().LastIndexOf(',');
sb.Remove(lastComma, 1);
QueryInsert = $"{SQLConstants.SELECT} * {SQLConstants.FROM} {_sqlConstants.ProcedureNameInsert} (:{SQLConstants.UPDATED_BY_PARAMETER_NAME},:{SQLConstants.UID_PARAMETER_NAME},:{SQLConstants.NAME_PARAMETER_NAME},{sb});";
QueryUpdate = $"{PostgreSQLConstants.CALL} {_sqlConstants.ProcedureNameUpdate} (:{SQLConstants.UPDATED_BY_PARAMETER_NAME},:{_sqlConstants.IdParameterName},:{SQLConstants.NAME_PARAMETER_NAME},{sb});";
QueryDelete = $"{PostgreSQLConstants.CALL} {_sqlConstants.ProcedureNameDelete} (:{SQLConstants.UPDATED_BY_PARAMETER_NAME},:{_sqlConstants.IdParameterName});";
QueryUndoDelete = $"{PostgreSQLConstants.CALL} {_sqlConstants.ProcedureNameUndoDelete} (:{SQLConstants.UPDATED_BY_PARAMETER_NAME},:{_sqlConstants.IdParameterName});";
QueryHardDelete = $"{PostgreSQLConstants.CALL} {_sqlConstants.ProcedureNameHardDelete} (:{SQLConstants.UPDATED_BY_PARAMETER_NAME},:{_sqlConstants.IdParameterName});";
QuerySelectById = $"{SQLConstants.SELECT} * {SQLConstants.FROM} {_sqlConstants.ProcedureNameSelectById} (:{_sqlConstants.IdParameterName});";
QuerySelectRevisions = $"{SQLConstants.SELECT} * {SQLConstants.FROM} {_sqlConstants.ProcedureNameSelectRevisions} (:{_sqlConstants.IdParameterName});";
QueryRestoreRevision = $"{PostgreSQLConstants.CALL} {_sqlConstants.ProcedureNameRestoreRevision} (:{SQLConstants.UPDATED_BY_PARAMETER_NAME},:{_sqlConstants.IdParameterName},:{SQLConstants.REVISION_PARAMETER_NAME}, null);";
}
public (PostgreSQLTypeLookup, EntityUtils, ConnectionSettings, PostgreSQLExecutor) Generate()
{
var connectionSettings = new ConnectionSettings();
connectionSettings.DbName = ConfigurationManager.AppSettings["DbName"];
connectionSettings.DbHost = ConfigurationManager.AppSettings["DbHost"];
connectionSettings.DbUser = ConfigurationManager.AppSettings["DbUser"];
connectionSettings.DbPassword = ConfigurationManager.AppSettings["DbPass"];
connectionSettings.DbPort = ConfigurationManager.AppSettings["DbPort"];
var typeLookup = new PostgreSQLTypeLookup();
var entityUtils = new EntityUtils(typeLookup, Assembly.GetExecutingAssembly());
var masterConnectionString = PostgreSQLConnectionFactory.GetConnectionString(connectionSettings.DbHost, connectionSettings.DbNameMaster, connectionSettings.DbUser, connectionSettings.DbPassword, connectionSettings.DbPort);
var masterConnectionFactory = new PostgreSQLConnectionFactory(masterConnectionString);
var sqlExecutorMaster = new PostgreSQLExecutor(masterConnectionFactory, entityUtils);
var connectionString = PostgreSQLConnectionFactory.GetConnectionString(connectionSettings);
var connectionFactory = new PostgreSQLConnectionFactory(connectionString);
var sqlExecutor = new PostgreSQLExecutor(connectionFactory, entityUtils);
var dbGenerator = new PostgreSQLDbGenerator(typeLookup, entityUtils, sqlExecutorMaster, sqlExecutor);
if (!dbGenerator.IsDbExistsDb(connectionSettings.DbName))
{
dbGenerator.CreateDb(connectionSettings.DbName);
dbGenerator.Generate().Wait();
}
return(typeLookup, entityUtils, connectionSettings, sqlExecutor);
}
public void PostgreSQLDbGenerator_Generate()
{
var connectionSettings = GetConnectionSettings();
connectionSettings.DbName += "_" + Guid.NewGuid().ToString("N");
var typeLookup = new PostgreSQLTypeLookup();
var entityUtils = new EntityUtils(typeLookup, typeof(Project).Assembly);
var masterConnectionString = PostgreSQLConnectionFactory.GetConnectionString(connectionSettings.DbHost, connectionSettings.DbNameMaster, connectionSettings.DbUser,
connectionSettings.DbPassword, connectionSettings.DbPort);
var masterConnectionFactory = new PostgreSQLConnectionFactory(masterConnectionString);
var sqlExecutorMaster = new PostgreSQLExecutor(masterConnectionFactory, entityUtils);
var connectionString = PostgreSQLConnectionFactory.GetConnectionString(connectionSettings);
var connectionFactory = new PostgreSQLConnectionFactory(connectionString);
var sqlExecutor = new PostgreSQLExecutor(connectionFactory, entityUtils);
var dbGenerator = new PostgreSQLDbGenerator(typeLookup, entityUtils, sqlExecutorMaster, sqlExecutor);
dbGenerator.CreateDb(connectionSettings.DbName);
dbGenerator.Generate().Wait();
Assert.True(dbGenerator.IsDbExistsDb(connectionSettings.DbName));
sqlExecutorMaster.ExecuteSql($@"SELECT Pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '{connectionSettings.DbName}'
AND pid <> Pg_backend_pid();
DROP DATABASE {connectionSettings.DbName};").Wait();
}
private PostgreSQLExecutor GetSQLExecutor(string dbName)
{
var typeLookup = new PostgreSQLTypeLookup();
var entityUtils = new EntityUtils(typeLookup, GetAssemblyOfEntities());
var sqlExecutor = new PostgreSQLExecutor(new PostgreSQLConnectionFactory(GetConnectionSettings(dbName)), entityUtils);
return(sqlExecutor);
}
public PostgreSQLExecutor GetSQLExecutor(string dbName)
{
var typeLookup = new PostgreSQLTypeLookup();
var entityUtils = new EntityUtils(typeLookup, Assembly.GetExecutingAssembly());
var sqlExecutor = new PostgreSQLExecutor(new PostgreSQLConnectionFactory(GetConnectionSettings(dbName)), entityUtils);
return(sqlExecutor);
}
private async Task <PostgreSQLExecutor> GetSQLExecutor(string dbName)
{
var typeLookup = new PostgreSQLTypeLookup();
var entityUtils = new EntityUtils(typeLookup, Assembly.GetExecutingAssembly());
var connectionSettings = await GetConnectionSettings(dbName);
var sqlExecutor = new PostgreSQLExecutor(new PostgreSQLConnectionFactory(connectionSettings), entityUtils);
return(sqlExecutor);
}
public void Install(IWindsorContainer container, IConfigurationStore store)
{
container.Register(Component.For <CryptoHelper>());
var connectionSettings = new ConnectionSettings();
connectionSettings.DbName = ConfigurationManager.AppSettings[ConstantHelper.KEY_DB_NAME];
connectionSettings.DbHost = ConfigurationManager.AppSettings[ConstantHelper.KEY_DB_HOST];
connectionSettings.DbUser = ConfigurationManager.AppSettings[ConstantHelper.KEY_DB_USER];
connectionSettings.DbPassword = ConfigurationManager.AppSettings[ConstantHelper.KEY_DB_PASS];
connectionSettings.DbPort = ConfigurationManager.AppSettings[ConstantHelper.KEY_DB_PORT];
container.Register(Component.For <ConnectionSettings>().Instance(connectionSettings));
var adminSettings = new AdminSettings();
adminSettings.AdminEmail = ConfigurationManager.AppSettings[ConstantHelper.KEY_SUPER_ADMIN_EMAIL];
adminSettings.AdminFirstName = ConfigurationManager.AppSettings[ConstantHelper.KEY_SUPER_ADMIN_FIRST_NAME];
adminSettings.AdminLastName = ConfigurationManager.AppSettings[ConstantHelper.KEY_SUPER_ADMIN_LAST_NAME];
adminSettings.AdminPassword = ConfigurationManager.AppSettings[ConstantHelper.KEY_SUPER_ADMIN_PASS];
container.Register(Component.For <AdminSettings>().Instance(adminSettings));
container.Register(Component.For(typeof(PostgreSQLConstants <>)));
var expressionUtils = new PostgreSQLExpressionUtils();
container.Register(Component.For <ExpressionUtils>().Instance(expressionUtils));
var typeLookup = new PostgreSQLTypeLookup();
container.Register(Component.For <PostgreSQLTypeLookup>().Instance(typeLookup));
var entityAssemblies = new[] { typeof(Organization).Assembly };
var entityUtils = new EntityUtils(typeLookup, entityAssemblies);
container.Register(Component.For <EntityUtils>().Instance(entityUtils));
container.Register(Component.For <PostgreSQLConnectionFactory>());
container.Register(Component.For <NpgsqlConnection>()
.DependsOn(Dependency.OnValue("connectionString", PostgreSQLConnectionFactory.GetConnectionString(connectionSettings)))
.LifestyleTransient());
container.Register(Component.For <PostgreSQLExecutor>().LifestyleTransient());
container.Register(Component.For <PostgreSQLTransactionalExecutor>().LifestyleTransient());
}
private void EnsureDbGenerated(string dbName)
{
var masterExecutor = GetSQLExecutor(POSTGRES_DB_NAME);
var isDbExist = masterExecutor.ExecuteSqlReturningValue <bool>($"SELECT true FROM pg_database WHERE datname = '{dbName}';").Result;
if (!isDbExist)
{
masterExecutor.ExecuteSql($"CREATE DATABASE {dbName};").Wait();
}
Sleep();
if (!isDbExist)
{
var typeLookup = new PostgreSQLTypeLookup();
var entityUtils = new EntityUtils(typeLookup, GetAssemblyOfEntities());
var executor = GetSQLExecutor(dbName);
var dbGenerator = new PostgreSQLDbGenerator(typeLookup, entityUtils, masterExecutor, executor);
dbGenerator.Generate().Wait();
}
}
public OrganizationRepository(PostgreSQLTypeLookup typeLookup, PostgreSQLConstants <Organization> sqlConstants, EntityUtils entityUtils, ExpressionUtils expressionUtils, PostgreSQLExecutor sqlExecutor, List <string> updateableFields) : base(typeLookup, sqlConstants, entityUtils, expressionUtils, sqlExecutor, updateableFields)
{
}
public ProjectRepository(PostgreSQLTypeLookup typeLookup, PostgreSQLConstants <Project> sqlConstants, EntityUtils entityUtils,
ExpressionUtils expressionUtils, PostgreSQLExecutor sqlExecutor) : base(typeLookup, sqlConstants, entityUtils,
expressionUtils, sqlExecutor, GetUpdateableFieldsList())
{
}
public void PostgreSQLTypeLookup_HasDbType(Type type)
{
var typeLookup = new PostgreSQLTypeLookup();
Assert.IsTrue(typeLookup.HasDbType(type));
}
