public async Task ArrayParameterTest()
{
using (var connection = await PostgresServerInformation.Open())
using (var transaction = connection.BeginTransaction())
{
await CreateTempUsers(connection);
const string query = @"
SELECT *
FROM tempUser
LEFT JOIN tempUserInfo IN (@Ids)
WHERE tempUser.id IN (@Ids)";
var users = await ReadTempUsers(
connection, query, new {
Ids = new[] { 1, 2 }
});
Assert.AreEqual(2, users.Length);
var guyOne = users.Single(t => t.Id == 0);
var guyTwo = users.Single(t => t.Id == 1);
Assert.AreEqual(3, guyOne.Infos.Count);
Assert.AreEqual(2, guyTwo.Infos.Count);
}
}
public async Task BasicIntArrayTypes(bool useBinary)
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand(
"select '{10000, 10000, 10000, 10000}'::integer[]", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.IsTrue(await reader.ReadAsync(cancel));
var array = (int[])reader[0];
CollectionAssert.AreEqual(
new[] { 10000, 10000, 10000, 10000 },
array);
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
public async Task TestParameterizedQuery()
{
using (var connection = await PostgresServerInformation.Open())
using (var transaction = connection.BeginTransaction())
{
await CreateTempUsers(connection);
const string query = @"
SELECT *
FROM tempUser
LEFT JOIN tempUserInfo ON (tempUserInfo.user_id = tempUser.id)";
{
const string withWhere = query + @"
WHERE tempUser.id = @Id";
var users = await ReadTempUsers(
connection, withWhere, new {
Id = 1
});
Assert.AreEqual(1, users.Length);
Assert.AreEqual("guy two", users.Single().Name);
}
{
const string withWhere = query + @"
WHERE tempUser.name = @Name";
var users = await ReadTempUsers(
connection, withWhere, new {
Name = "guy two"
});
Assert.AreEqual(1, users.Length);
Assert.AreEqual("guy two", users.Single().Name);
var noResults = await ReadTempUsers(
connection, withWhere, new {
Name = "Invalid name"
});
Assert.AreEqual(0, noResults.Length);
var nullResults = await ReadTempUsers(
connection, withWhere, new {
Name = (string)null
});
Assert.AreEqual(0, nullResults.Length);
var escapeAttempts = await ReadTempUsers(
connection, withWhere, new {
Name = "\"'$FOOBVAR$$$"
});
Assert.AreEqual(0, escapeAttempts.Length);
}
}
}
public async Task BasicTestGuidTypes(bool useBinary)
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand(
"SELECT 'AC426679-CD6A-4571-A519-C4DD7691C63C'::uuid", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.IsTrue(await reader.ReadAsync(cancel));
var guid = (Guid)reader[0];
Assert.AreEqual(
Guid.Parse("AC426679-CD6A-4571-A519-C4DD7691C63C"),
guid);
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
public async Task BasicTestDateTypes(bool useBinary)
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand(
"SELECT '2001-09-27'::date", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.IsTrue(await reader.ReadAsync(cancel));
var date = (DateTime)reader[0];
Assert.AreEqual(2001, date.Year);
Assert.AreEqual(9, date.Month);
Assert.AreEqual(27, date.Day);
Assert.AreEqual(DateTimeKind.Utc, date.Kind);
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
public async Task TestDapperSelectOne()
{
using (var connection = await PostgresServerInformation.Open())
{
var one = await connection.QueryAsync <int>("SELECT 1");
CollectionAssert.AreEqual(new[] { 1 }, one.ToArray());
}
}
public async Task TestExecuteScalarAsync()
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand("SELECT 1", connection))
{
var one = await command.ExecuteScalarAsync(cancel);
Assert.AreEqual(1, one);
}
}
public async Task TestDapperSelectOneParameter()
{
using (var connection = await PostgresServerInformation.Open())
{
var one = await connection.QueryAsync <TheOne>(
"SELECT AOne FROM (SELECT 1 AS AOne) s WHERE AOne = @AOne", new {
AOne = 1
});
var results = one.Select(t => t.AOne).ToArray();
CollectionAssert.AreEqual(new[] { 1 }, results);
}
}
public async Task FloatingPointTest(bool useBinary, string floatFormat)
{
DebugLogger.Enabled = true;
var cancel = CancellationToken.None;
const decimal precision = 0.001m;
using (var connection = await PostgresServerInformation.Open())
{
await connection.SendPropertyAsync(cancel,
new PostgresPropertySetting(
PostgresPropertyName.ExtraFloatDigits,
"3"));
using (var command = new PostgresCommand(
$"SELECT 123456789.005::{floatFormat}, 500.0123456789::{floatFormat}", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.IsTrue(await reader.ReadAsync(cancel));
switch (floatFormat)
{
case "float4":
var val1 = reader.GetFloat(0);
var val2 = reader.GetFloat(1);
NumericAsserts.FloatEquals(123456789.005f, val1, precision);
NumericAsserts.FloatEquals(500.012f, val2, precision);
break;
case "float8":
var valb1 = reader.GetDouble(0);
var valb2 = reader.GetDouble(1);
NumericAsserts.FloatEquals(123456789.005, valb1, precision);
NumericAsserts.FloatEquals(500.012f, valb2, precision);
break;
default:
throw new ArgumentOutOfRangeException(floatFormat);
}
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
}
public async Task TestExecuteReaderAsync(bool useBinary)
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand(
"SELECT 0::int2, 1::int4, 2::int8, true, false, 4.6 as foobar, null", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.IsTrue(await reader.ReadAsync(cancel));
Assert.AreEqual(7, reader.FieldCount);
Assert.AreEqual(0, reader.GetInt16(0));
Assert.AreEqual((short)0, reader.GetValue(0));
Assert.AreEqual(1, reader.GetInt32(1));
Assert.AreEqual(1, reader.GetValue(1));
Assert.AreEqual(2, reader.GetInt64(2));
Assert.AreEqual(2L, reader.GetValue(2));
Assert.AreEqual(true, reader.GetBoolean(3));
Assert.AreEqual(true, reader.GetValue(3));
Assert.AreEqual(false, reader.GetBoolean(4));
Assert.AreEqual(false, reader.GetValue(4));
Assert.AreEqual(4.6m, reader.GetDecimal(5));
Assert.AreEqual(4.6m, reader.GetValue(5));
Assert.AreEqual(4.6m, reader["foobar"]);
Assert.AreEqual(DBNull.Value, reader.GetValue(6));
Assert.ThrowsException <IndexOutOfRangeException>(
() => reader[999]);
Assert.ThrowsException <IndexOutOfRangeException>(
() => reader["does not exist"]);
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
public async Task BasicTestTimeTypes(bool useBinary)
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand(
"SELECT '2001-09-27 23:00:00'::timestamp, '2002-10-28'::date at time zone 'PST'", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.Inconclusive("TODO: Write this test");
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
public async Task EnsureGetAllWorks()
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
{
var properties = await PostgresPropertySetting.GetAll(
true, connection, cancel);
Assert.IsTrue(properties.Count > 200);
var backslashQuote = properties.FirstOrDefault(
t => t.Name == PostgresProperties.BackslashQuote);
var possibleValues = new[] { "on", "off", "safe_encoding" };
CollectionAssert.Contains(possibleValues, backslashQuote.Value);
}
}
public async Task BasicTestNumericTypes(bool useBinary)
{
var cancel = CancellationToken.None;
using (var connection = await PostgresServerInformation.Open())
using (var command = new PostgresCommand(
"SELECT 500.5::numeric, 500.5::float4, 500.5::float8, 500.5::money", connection))
{
connection.QueryResultFormat = useBinary
? PostgresFormatCode.Binary
: PostgresFormatCode.Text;
var reader = await command.ExecuteReaderAsync(cancel);
Assert.IsTrue(await reader.ReadAsync(cancel));
Assert.Inconclusive("TODO: Write this test");
Assert.IsFalse(await reader.ReadAsync(cancel));
}
}
public async Task TestQuery()
{
using (var connection = await PostgresServerInformation.Open())
using (var transaction = connection.BeginTransaction())
{
await CreateTempUsers(connection);
const string query = @"
SELECT *
FROM tempUser
LEFT JOIN tempUserInfo ON (tempUserInfo.user_id = tempUser.id)";
var users = await ReadTempUsers(connection, query, null);
Assert.AreEqual(3, users.Length);
var guyOne = users.Single(t => t.Id == 0);
var guyTwo = users.Single(t => t.Id == 1);
var guyThree = users.Single(t => t.Id == 2);
Assert.AreEqual(3, guyOne.Infos.Count);
Assert.AreEqual(2, guyTwo.Infos.Count);
Assert.AreEqual(0, guyThree.Infos.Count);
Assert.AreEqual("guy one", guyOne.Name);
Assert.AreEqual("guy two", guyTwo.Name);
Assert.AreEqual("guy three", guyThree.Name);
Assert.AreEqual("Mars", guyOne.Location);
Assert.AreEqual("Jupiter", guyTwo.Location);
Assert.AreEqual("Venus", guyThree.Location);
Assert.IsTrue(guyOne.Infos.Any(t => t.Info == "info one"));
Assert.IsTrue(guyOne.Infos.Any(t => t.Info == "info two"));
Assert.IsTrue(guyOne.Infos.Any(t => t.Info == "info three"));
Assert.IsTrue(guyTwo.Infos.Any(t => t.Info == "info one"));
Assert.IsTrue(guyTwo.Infos.Any(t => t.Info == "info two"));
}
}
public async Task ArrayReaderTest()
{
using (var connection = await PostgresServerInformation.Open())
using (var transaction = connection.BeginTransaction())
{
const string schema = @"
CREATE TEMP TABLE tempArray (id int4, numbers integer[], texts text[][], texts3 text[][][]);
INSERT INTO tempArray
VALUES (0,
'{10000, 10000, 10000, 10000}',
'{{""meeting"", ""lunch""}, {""training"", ""presentation""}}',
'{{""meeting"", ""lunch"", null}, {""training"", ""presentation"", null}, {""training2"", ""presentation2"", null}}');
INSERT INTO tempArray
VALUES (1,
'{10001, 10002, 10003, 10004}',
'{{""meeting"", ""lunch""}, {""training"", ""presentation""}}', null);
INSERT INTO tempArray
VALUES (2,
'{10001, 10002, 10003, 10004}',
'{{""meeting"", ""lunch""}, {""training"", ""presentation""}}', null);
INSERT INTO tempArray
VALUES (3,
'{}',
'{{""meeting"", ""lunch""}, {null, null}}', null);
INSERT INTO tempArray
VALUES (4,
null,
null, null);";
await connection.ExecuteAsync(schema);
const string query = @"
SELECT *
FROM tempArray";
var data = (await connection
.QueryAsync <TempArray>(query)).ToArray();
Assert.AreEqual(5, data.Length);
var guy0 = data.Single(t => t.Id == 0);
var guy1 = data.Single(t => t.Id == 1);
var guy2 = data.Single(t => t.Id == 2);
var guy3 = data.Single(t => t.Id == 3);
var guy4 = data.Single(t => t.Id == 4);
void AssertNumbers(TempArray guy, int[] expected)
{
Assert.AreEqual(expected.Length, guy.Numbers.Length);
CollectionAssert.AreEqual(expected, guy.Numbers);
}
AssertNumbers(guy0, new[] { 10000, 10000, 10000, 10000 });
AssertNumbers(guy1, new[] { 10001, 10002, 10003, 10004 });
AssertNumbers(guy2, new[] { 10001, 10002, 10003, 10004 });
Assert.AreEqual(0, guy3.Numbers.Length);
Assert.AreEqual(null, guy4.Numbers);
}
}