public async Task should_create_new_database_and_allow_exec_query_2()
{
// PREPARE
var dbName = "test_db_2";
var cmdBuilder = new StringBuilder();
cmdBuilder.AppendLine($"CREATE DATABASE {dbName}");
cmdBuilder.AppendLine("CONNECTION LIMIT = -1");
// RUN
using (var conn = new Npgsql.NpgsqlConnection(_fixture.ConnectionString))
{
await conn.OpenAsync();
var cmd = new NpgsqlCommand(cmdBuilder.ToString(), conn);
cmd.ExecuteNonQuery();
// ASSERT
var dbExists = await new NpgsqlCommand($"SELECT datname FROM pg_catalog.pg_database WHERE datname = '{dbName}'", conn)
.ExecuteScalarAsync();
Assert.NotNull(dbExists);
}
}
private async Task<string> Create()
{
string sql;
var assembly = typeof(TestDatabase).GetTypeInfo().Assembly;
using (var reader = new StreamReader(assembly.GetManifestResourceStream(_sqlFile)))
{
sql = await reader.ReadToEndAsync();
}
var dbName = await CreateDatabase();
var connectionString = $"Server=localhost;Port=5432;Database={_databaseName};User Id=postgres;Password=s1mpl3;";
using (var connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
foreach (
var statement in
Regex.Split(sql, @"^\s*GO\s*$", RegexOptions.Multiline)
.Where(s => !string.IsNullOrWhiteSpace(s)))
{
//_logger.LogDebug(sql);
command.CommandText = statement;
await command.ExecuteNonQueryAsync();
}
}
}
return _connectionString = connectionString;
}
public static IServiceCollection AddSqlConnectionBuilder(this IServiceCollection collection,
string configuration)
{
var connection = new NpgsqlConnection(configuration);
connection.OpenAsync();
collection.AddTransient(provider => connection);
return collection;
}
/// <summary>
/// Gets a fresh, open and ready-to-use connection wrapper
/// </summary>
public async Task<PostgresConnection> GetConnection()
{
var connection = new NpgsqlConnection(_connectionString);
await connection.OpenAsync();
var currentTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
return new PostgresConnection(connection, currentTransaction);
}
public static async Task<bool> CheckConnection(string connectionString)
{
if (string.IsNullOrWhiteSpace(connectionString))
throw new ArgumentException("The argument may not be null or empty", nameof(connectionString));
using (var connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
return connection.State == ConnectionState.Open;
}
}
public async Task<DbConnection> OpenConnectionAsync()
{
if (string.IsNullOrWhiteSpace(_connectionString))
{
throw new InvalidOperationException("Connection to PostgreSQL MicroGarden Settings storage is not configured yet. Consider using UseMicroGardenSettingsPostgreSQLStore in Configure");
}
var connection = new NpgsqlConnection(_connectionString);
await connection.OpenAsync();
return connection;
}
public static async Task<EventStream> GetLatestStreamCreated()
{
using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
{
await connection
.OpenAsync()
.ConfigureAwait(false);
var query = await connection
.QueryAsync<EventStream>(SelectLatestStreamSql)
.ConfigureAwait(false);
return query.Any()
? query.Single()
: default(EventStream);
}
}
// For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc(options =>
{
var logger = Factory.CreateLogger("Web.Exception");
options.InputFormatters.Clear();
options.OutputFormatters.Clear();
options.Filters.Add(new ExceptionFilter(logger, new JsonExceptionPage()));
options.InputFormatters.Add(new JsonInputFormatter());
options.OutputFormatters.Add(new JsonStandardMediaTypeFormatter());
});
services.AddTransient<IEmailService, EmailService>(
provider => new EmailService(Configuration["Email:Host"], Configuration["Email:UserName"],
Configuration["Email:Password"], Configuration["Email:From"],
Int32.Parse(Configuration["Email:Port"])));
var connection = new NpgsqlConnection(Configuration["Data:Npgsql:ConnectionString"]);
connection.OpenAsync();
services.AddTransient<DbConnection>(provider => connection);
services.AddBasicAuthentication();
var repositoryConfig = new DefaultDapperRepositoryOptions<SigmaUser>(connection,
new SigmaPropertyStore(connection));
services
.StartAuthenticationConfiguration<SigmaUser>()
.AddRepositories(repositoryConfig)
.AddEmailProvider(Configuration["Email:Host"], Configuration["Email:UserName"],
Configuration["Email:Password"], Configuration["Email:From"],
int.Parse(Configuration["Email:Port"]))
.AddLoggers(Factory)
.ConfigureLoginOptions(new LoginManagerOptions<SigmaUser>
{
ClaimsProvider =
new ClaimsProvider<SigmaUser>(repositoryConfig.UserStore, repositoryConfig.UserRoleStore,
new ClaimTypesOptions())
})
.BuildManagers();
}
private static async Task TestPostgres()
{
try
{
using (var cn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secretsquirrel"))
{
await cn.OpenAsync();
using (var cmd = new NpgsqlCommand("CREATE DATABASE simple", cn))
{
await cmd.ExecuteNonQueryAsync();
}
System.Console.WriteLine("Created database");
}
} catch {}
try
{
using (var cn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secretsquirrel;Database=simple"))
{
await cn.OpenAsync();
using (var cmd = new NpgsqlCommand("CREATE TABLE Starships (Id integer not null, Name varchar(100) not null)", cn))
{
await cmd.ExecuteNonQueryAsync();
}
using (var cmd = new NpgsqlCommand("INSERT INTO Starships VALUES (42, 'Heart of Gold')", cn))
{
await cmd.ExecuteNonQueryAsync();
}
System.Console.WriteLine("Created table");
}
} catch {}
var db = new SimpleData().Open(@"Host=localhost;Username=postgres;Password=secretsquirrel;Database=simple",
typeof(PostgresAdapter));
var starship = await db.Starships.GetById(42);
Console.WriteLine(starship.Name);
}
public async Task <DbConnection> OpenConnection()
{
int maxRetries = 10;
int retries = maxRetries;
retry:
var conn = new Npgsql.NpgsqlConnection(connectionString);
try
{
await conn.OpenAsync();
}
catch (PostgresException ex) when(ex.IsTransient && retries > 0)
{
retries--;
await conn.DisposeAsync();
await Task.Delay((maxRetries - retries) * 100);
goto retry;
}
return(conn);
}
private async Task<string> CreateDatabase()
{
_databaseName = $"simple_{Guid.NewGuid():N}".ToLowerInvariant();
var connectionString = $"Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=s1mpl3;";
using (var connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = $"CREATE DATABASE {_databaseName} WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;";
_logger.LogDebug(cmd.CommandText);
try
{
await cmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
_logger.LogError(42999, ex, "Failed to CREATE DATABASE");
}
}
}
return _databaseName;
}
public static async Task CreateEventStream(Guid streamId, string streamName)
{
using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
{
await connection
.OpenAsync()
.ConfigureAwait(false);
using (var command = connection.CreateCommand())
{
command.CommandText = CreateEventStreamSql;
command.Parameters.AddWithValue("@streamId", streamId);
command.Parameters.AddWithValue("@streamName", streamName);
command.Parameters.AddWithValue("@createdOn", DateTime.UtcNow);
await command
.ExecuteNonQueryAsync()
.ConfigureAwait(false);
}
}
}
public void ConnectTimeoutAsync()
{
var unknownIp = Environment.GetEnvironmentVariable("NPGSQL_UNKNOWN_IP");
if (unknownIp == null)
TestUtil.IgnoreExceptOnBuildServer("NPGSQL_UNKNOWN_IP isn't defined and is required for connection timeout tests");
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
Host = unknownIp,
Pooling = false,
Timeout = 2
};
using (var conn = new NpgsqlConnection(csb))
{
Assert.That(async () => await conn.OpenAsync(), Throws.Exception.TypeOf<TimeoutException>());
Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));
}
}
private async Task<NpgsqlConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
var con = new NpgsqlConnection(ConnectionString);
await con.OpenAsync(cancellationToken).ConfigureAwait(false);
//TODO: Research server settings.
return con;
}
private async Task<NpgsqlConnection> OpenConnection()
{
var conn = new NpgsqlConnection(_connectionStringBuilder);
await conn
.OpenAsync()
.ConfigureAwait(false);
return conn;
}
public void ConnectionRefusedAsync(bool pooled)
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { Port = 44444, Pooling = pooled };
using (var conn = new NpgsqlConnection(csb))
{
Assert.That(async () => await conn.OpenAsync(), Throws.Exception
.TypeOf<SocketException>()
.With.Property(nameof(SocketException.SocketErrorCode)).EqualTo(SocketError.ConnectionRefused)
);
Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Closed));
}
}
public static async Task CreateEvents(Guid streamId, IEnumerable<StoredEvent> storedEvents)
{
using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
{
await connection
.OpenAsync()
.ConfigureAwait(false);
foreach (var storedEvent in storedEvents)
{
using (var command = connection.CreateCommand())
{
command.CommandText = CreateEventSql;
command.Parameters.AddWithValue("@streamId", streamId);
command.Parameters.AddWithValue("@eventId", storedEvent.EventId);
command.Parameters.AddWithValue("@typeName", storedEvent.TypeName);
command.Parameters.AddWithValue("@occurredOn", storedEvent.OccurredOn);
command.Parameters.AddWithValue("@batchId", storedEvent.BatchId);
command.Parameters.AddWithValue("@body", NpgsqlDbType.Json, storedEvent.EventBody);
await command
.ExecuteNonQueryAsync()
.ConfigureAwait(false);
}
}
}
}
public void ConnectTimeoutCancel()
{
var unknownIp = Environment.GetEnvironmentVariable("NPGSQL_UNKNOWN_IP");
if (unknownIp == null)
TestUtil.IgnoreExceptOnBuildServer("NPGSQL_UNKNOWN_IP isn't defined and is required for connection cancellation tests");
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
Host = unknownIp,
Pooling = false,
Timeout = 30
};
using (var conn = new NpgsqlConnection(csb))
{
var cts = new CancellationTokenSource();
cts.CancelAfter(1000);
Assert.That(async () => await conn.OpenAsync(cts.Token), Throws.Exception.TypeOf<TaskCanceledException>());
Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));
}
}
public static async Task<IEnumerable<StoredEvent>> GetStreamEvents(Guid streamId)
{
using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
{
await connection
.OpenAsync()
.ConfigureAwait(false);
var query = await connection
.QueryAsync<StoredEvent>(SelectStreamEventsSql)
.ConfigureAwait(false);
return query.ToList();
}
}
