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); } }