-
-
Notifications
You must be signed in to change notification settings - Fork 3.6k
/
PostgresqlTests.cs
146 lines (126 loc) · 5.7 KB
/
PostgresqlTests.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using Xunit;
namespace Dapper.Tests
{
/// <summary>
/// If Docker Desktop is installed, run the following command to start a container suitable for the tests.
/// <code>
/// docker run -d -p 5432:5432 --name Dapper.Tests.PostgreSQL -e POSTGRES_DB=dappertest -e POSTGRES_USER=dappertest -e POSTGRES_PASSWORD=dapperpass postgres
/// </code>
/// </summary>
public class PostgresProvider : DatabaseProvider
{
public override DbProviderFactory Factory => Npgsql.NpgsqlFactory.Instance;
public override string GetConnectionString() =>
GetConnectionString("PostgesConnectionString", "Server=localhost;Port=5432;User Id=dappertest;Password=dapperpass;Database=dappertest");
}
public class PostgresqlTests : TestBase<PostgresProvider>
{
private Npgsql.NpgsqlConnection GetOpenNpgsqlConnection() => (Npgsql.NpgsqlConnection)Provider.GetOpenConnection();
private class Cat
{
public int Id { get; set; }
public string? Breed { get; set; }
public string? Name { get; set; }
}
private readonly Cat[] Cats =
{
new Cat() { Breed = "Abyssinian", Name="KACTUS"},
new Cat() { Breed = "Aegean cat", Name="KADAFFI"},
new Cat() { Breed = "American Bobtail", Name="KANJI"},
new Cat() { Breed = "Balinese", Name="MACARONI"},
new Cat() { Breed = "Bombay", Name="MACAULAY"},
new Cat() { Breed = "Burmese", Name="MACBETH"},
new Cat() { Breed = "Chartreux", Name="MACGYVER"},
new Cat() { Breed = "German Rex", Name="MACKENZIE"},
new Cat() { Breed = "Javanese", Name="MADISON"},
new Cat() { Breed = "Persian", Name="MAGNA"}
};
[FactPostgresql]
public void TestPostgresqlArrayParameters()
{
using var conn = GetOpenNpgsqlConnection();
var transaction = conn.BeginTransaction();
conn.Execute("create table tcat ( id serial not null, breed character varying(20) not null, name character varying (20) not null);");
conn.Execute("insert into tcat(breed, name) values(:Breed, :Name) ", Cats);
var r = conn.Query<Cat>("select * from tcat where id=any(:catids)", new { catids = new[] { 1, 3, 5 } });
Assert.Equal(3, r.Count());
Assert.Equal(1, r.Count(c => c.Id == 1));
Assert.Equal(1, r.Count(c => c.Id == 3));
Assert.Equal(1, r.Count(c => c.Id == 5));
transaction.Rollback();
}
[FactPostgresql]
public void TestPostgresqlListParameters()
{
using var conn = GetOpenNpgsqlConnection();
var transaction = conn.BeginTransaction();
conn.Execute("create table tcat ( id serial not null, breed character varying(20) not null, name character varying (20) not null);");
conn.Execute("insert into tcat(breed, name) values(:Breed, :Name) ", new List<Cat>(Cats));
var r = conn.Query<Cat>("select * from tcat where id=any(:catids)", new { catids = new List<int> { 1, 3, 5 } });
Assert.Equal(3, r.Count());
Assert.Equal(1, r.Count(c => c.Id == 1));
Assert.Equal(1, r.Count(c => c.Id == 3));
Assert.Equal(1, r.Count(c => c.Id == 5));
transaction.Rollback();
}
private class CharTable
{
public int Id { get; set; }
public char CharColumn { get; set; }
}
[FactPostgresql]
public void TestPostgresqlChar()
{
using var conn = GetOpenNpgsqlConnection();
var transaction = conn.BeginTransaction();
conn.Execute("create table chartable (id serial not null, charcolumn \"char\" not null);");
conn.Execute("insert into chartable(charcolumn) values('a');");
var r = conn.Query<CharTable>("select * from chartable");
Assert.Single(r);
Assert.Equal('a', r.Single().CharColumn);
transaction.Rollback();
}
[FactPostgresql]
public void TestPostgresqlSelectArray()
{
using var conn = GetOpenNpgsqlConnection();
var r = conn.Query<int[]>("select array[1,2,3]").ToList();
Assert.Single(r);
Assert.Equal(new[] { 1, 2, 3 }, r.Single());
}
[FactPostgresql]
public void TestPostgresqlDateTimeUsage()
{
using var conn = GetOpenNpgsqlConnection();
DateTime now = DateTime.UtcNow;
DateTime? nilA = now, nilB = null;
_ = conn.ExecuteScalar("SELECT @now, @nilA, @nilB::timestamp", new { now, nilA, nilB });
}
[AttributeUsage(AttributeTargets.Method, AllowMultiple = false)]
public class FactPostgresqlAttribute : FactAttribute
{
public override string? Skip
{
get { return unavailable ?? base.Skip; }
set { base.Skip = value; }
}
private static readonly string? unavailable;
static FactPostgresqlAttribute()
{
try
{
using (DatabaseProvider<PostgresProvider>.Instance.GetOpenConnection()) { /* just trying to see if it works */ }
}
catch (Exception ex)
{
unavailable = $"Postgresql is unavailable: {ex.Message}";
}
}
}
}
}