public async Task TestSelect1(String connectionConfigFileLocation)
{
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
var selectResult = await pool.UseConnectionAsync(async conn => { return(await conn.GetFirstOrDefaultAsync <Int32>("SELECT 1")); });
Assert.AreEqual(1, selectResult);
}
public async Task TestSelectMultipleValues(String connectionConfigFileLocation)
{
var first = 1;
var second = 2;
var third = 3;
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
var tuple = await pool.UseConnectionAsync(async conn =>
{
var iArgs = conn.PrepareStatementForExecution($"SELECT * FROM( VALUES( {first} ), ( {second} ), ( {third} ) ) AS tmp");
Assert.IsTrue(await iArgs.MoveNextAsync());
var seenFirst = await iArgs.GetDataRow().GetValueAsync <Int32>(0);
Assert.IsTrue(await iArgs.MoveNextAsync());
var seenSecond = await iArgs.GetDataRow().GetValueAsync <Int32>(0);
Assert.IsTrue(await iArgs.MoveNextAsync());
var seenThird = await iArgs.GetDataRow().GetValueAsync <Int32>(0);
Assert.IsFalse(await iArgs.MoveNextAsync());
return(seenFirst, seenSecond, seenThird);
});
Assert.AreEqual(first, tuple.Item1);
Assert.AreEqual(second, tuple.Item2);
Assert.AreEqual(third, tuple.Item3);
}
public async Task TestNotificationCheck(String connectionConfigFileLocation)
{
const String NOTIFICATION_NAME = "testing";
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
await pool.UseConnectionAsync(async conn =>
{
NotificationEventArgs notificationArgs = null;
conn.NotificationEvent += (sender, nArgs) => notificationArgs = nArgs;
// Check that notification check will not stuck
await conn.CheckNotificationsAsync();
Assert.IsNull(notificationArgs);
// Start listening
await conn.ExecuteNonQueryAsync("LISTEN " + NOTIFICATION_NAME);
// Use another connection pool to issue notify
await pool.UseConnectionAsync(async conn2 => await conn2.ExecuteNonQueryAsync("NOTIFY " + NOTIFICATION_NAME));
// Make sure that we have received it
await conn.CheckNotificationsAsync();
Assert.IsNotNull(notificationArgs);
Assert.AreEqual(notificationArgs.Name, NOTIFICATION_NAME);
Assert.AreNotEqual(notificationArgs.ProcessID, conn.BackendProcessID);
Assert.AreEqual(notificationArgs.Payload.Length, 0);
});
}
public async Task TestPreparedStatement_Strings(String connectionConfigFileLocation)
{
const String FIRST = "first";
const String SECOND = "second";
const String THIRD = "third";
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
var tuple = await pool.UseConnectionAsync(async conn =>
{
var stmt = conn.CreateStatementBuilder("SELECT * FROM ( VALUES( ? ), ( ? ), ( ? ) ) AS tmp");
stmt.SetParameterString(0, FIRST);
stmt.SetParameterString(1, SECOND);
stmt.SetParameterString(2, THIRD);
var iArgs = conn.PrepareStatementForExecution(stmt);
Assert.IsTrue(await iArgs.MoveNextAsync());
var seenFirst = await iArgs.GetDataRow().GetValueAsync <String>(0);
Assert.IsTrue(await iArgs.MoveNextAsync());
var seenSecond = await iArgs.GetDataRow().GetValueAsync <String>(0);
Assert.IsTrue(await iArgs.MoveNextAsync());
var seenThird = await iArgs.GetDataRow().GetValueAsync <String>(0);
Assert.IsFalse(await iArgs.MoveNextAsync());
await AssertThatConnectionIsStillUseable(conn);
return(seenFirst, seenSecond, seenThird);
});
Assert.AreEqual(FIRST, tuple.Item1);
Assert.AreEqual(SECOND, tuple.Item2);
Assert.AreEqual(THIRD, tuple.Item3);
}
public async Task TestSimpleSSL(String connectionConfigFileLocation)
{
// No other proper way to ensure SSL actually works
var creationInfo = GetConnectionCreationInfo(connectionConfigFileLocation);
// Since server needs to be configured for SSL mode as well, a separate config file is most generic option (in case SSL-enabled server is in different end-point than normal server used in tests)
Assert.AreEqual(creationInfo.CreationData.ConnectionSSLMode, ConnectionSSLMode.Required);
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(creationInfo);
var selectResult = await pool.UseConnectionAsync(async conn => { return(await conn.GetFirstOrDefaultAsync <Int32>("SELECT 1")); });
Assert.AreEqual(1, selectResult);
}
public async Task TestArrays(
String connectionConfigFileLocation,
Type arrayGenerator
)
{
var generator = (SimpleArrayDataGenerator)Activator.CreateInstance(arrayGenerator);
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
await pool.UseConnectionAsync(async conn =>
{
foreach (var arrayInfo in generator.GenerateArrays())
{
ValidateArrays(arrayInfo.Array, await conn.GetFirstOrDefaultAsync <Array>("SELECT " + arrayInfo.ArraySpec + " AS test_column"));
}
});
}
protected static async Task TestWithAndWithoutBinaryReceive(
String connectionConfigFileLocation,
Func <PgSQLConnection, Task> performTest
)
{
var data = GetConnectionCreationInfoData(connectionConfigFileLocation);
var enabled = data.CreateCopy();
enabled.DisableBinaryProtocolReceive = false;
var disabled = data.CreateCopy();
disabled.DisableBinaryProtocolReceive = true;
await Task.WhenAll(
PgSQLConnectionPool.CreateOneTimeUseConnectionPool( new PgSQLConnectionCreationInfo(enabled)).UseConnectionAsync(performTest),
PgSQLConnectionPool.CreateOneTimeUseConnectionPool( new PgSQLConnectionCreationInfo(disabled)).UseConnectionAsync(performTest)
);
}
public async Task TestBatchedStatementWithoutParameters(String connectionConfigFileLocation)
{
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
await pool.UseConnectionAsync(async conn =>
{
await conn.ExecuteNonQueryAsync("CREATE TEMPORARY TABLE batch_test( id SERIAL, PRIMARY KEY (id) )");
var batchStmt = conn.CreateStatementBuilder("INSERT INTO batch_test DEFAULT VALUES");
batchStmt.AddBatch();
batchStmt.AddBatch();
batchStmt.AddBatch();
await conn.ExecuteNonQueryAsync(batchStmt);
await AssertThatQueryProducesSameResults_IgnoreOrder(conn, "SELECT id FROM batch_test", 1, 2, 3);
});
}
public async Task TestJSONReading(
String connectionConfigFileLocation,
Type jsonGenerator
)
{
var generator = (SimpleJSONDataGenerator)Activator.CreateInstance(jsonGenerator);
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
pool.EnableJSONSupport();
await pool.UseConnectionAsync(async conn =>
{
foreach (var jsonInfo in generator.GenerateJSONValues())
{
ValidateJSON(jsonInfo.Object, await conn.GetFirstOrDefaultAsync <JToken>("SELECT '" + jsonInfo.JSONSpec + "'::json"));
ValidateJSON(jsonInfo.Object, await conn.GetFirstOrDefaultAsync <JToken>("SELECT '" + jsonInfo.JSONSpec + "'::jsonb"));
}
});
}
public async Task TestJSONWriting(
String connectionConfigFileLocation,
Type jsonGenerator
)
{
var generator = (SimpleJSONDataGenerator)Activator.CreateInstance(jsonGenerator);
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
pool.EnableJSONSupport();
await pool.UseConnectionAsync(async conn =>
{
var stmt = conn.VendorFunctionality.CreateStatementBuilder("SELECT ?");
foreach (var jsonInfo in generator.GenerateJSONValues())
{
var json = jsonInfo.Object;
stmt.SetParameterObjectWithType(0, json, typeof(JToken));
ValidateJSON(json, await conn.GetFirstOrDefaultAsync <JToken>(stmt));
}
});
}
public async Task TestNotReadingAllColumns(String connectionConfigFileLocation)
{
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
await pool.UseConnectionAsync(async conn =>
{
var iArgs = conn.PrepareStatementForExecution("SELECT * FROM( VALUES( 1, 2 ), (3, 4), (5, 6) ) AS tmp");
// First read is partial read
Assert.IsTrue(await iArgs.MoveNextAsync());
Assert.AreEqual(1, await iArgs.GetDataRow().GetValueAsync <Int32>(0));
// Second read just ignores columns
Assert.IsTrue(await iArgs.MoveNextAsync());
// Third read reads in opposite order
Assert.IsTrue(await iArgs.MoveNextAsync());
Assert.AreEqual(6, await iArgs.GetDataRow().GetValueAsync <Int32>(1));
Assert.AreEqual(5, await iArgs.GetDataRow().GetValueAsync <Int32>(0));
Assert.IsFalse(await iArgs.MoveNextAsync());
});
}
public async Task TestBatchedStatement(String connectionConfigFileLocation)
{
var pool = PgSQLConnectionPool.CreateOneTimeUseConnectionPool(GetConnectionCreationInfo(connectionConfigFileLocation));
const String FIRST = "first";
const String SECOND = "second";
const String THIRD = "third";
//const Int32 FIRST = 1;
//const Int32 SECOND = 2;
//const Int32 THIRD = 3;
await pool.UseConnectionAsync(async conn =>
{
await conn.ExecuteNonQueryAsync("CREATE TEMPORARY TABLE batch_test( id SERIAL, value TEXT, PRIMARY KEY (id) )");
var batchStmt = conn.CreateStatementBuilder("INSERT INTO batch_test(value) VALUES ( ? )");
batchStmt.SetParameterString(0, FIRST);
//batchStmt.SetParameterInt32( 0, FIRST );
//batchStmt.SetParameterString( 1, FIRST );
batchStmt.AddBatch();
batchStmt.SetParameterString(0, SECOND);
//batchStmt.SetParameterInt32( 0, SECOND );
//batchStmt.SetParameterString( 1, SECOND );
batchStmt.AddBatch();
batchStmt.SetParameterString(0, THIRD);
//batchStmt.SetParameterInt32( 0, THIRD );
//batchStmt.SetParameterString( 1, THIRD );
batchStmt.AddBatch();
await conn.ExecuteNonQueryAsync(batchStmt);
await AssertThatQueryProducesSameResults_IgnoreOrder(conn, "SELECT value FROM batch_test", FIRST, SECOND, THIRD);
});
}