public void PqsqlLargeObjectTest1()
{
PqsqlTransaction tran = mConnection.BeginTransaction();
PqsqlLargeObject lo = new PqsqlLargeObject(mConnection);
uint loid = lo.Create();
Assert.IsTrue(loid > 0);
lo.Open(loid, LoOpen.INV_READ | LoOpen.INV_WRITE);
Assert.AreEqual(0, lo.Position);
Assert.IsTrue(lo.CanSeek);
byte[] b = Encoding.ASCII.GetBytes("abc");
lo.Write(b, 0, b.Length);
lo.Flush();
Assert.AreEqual(3, lo.Position);
Assert.AreEqual(3, lo.Length);
lo.SetLength(2);
Assert.AreEqual(2, lo.Length);
Assert.AreEqual(2, lo.Position);
lo.Position = 0;
Assert.AreEqual(0, lo.Position);
lo.Close();
Assert.IsTrue(lo.Unlink() >= 0);
tran.Rollback();
}
public void PqsqlTypeRegistryTest1()
{
PqsqlTransaction tran = mConnection.BeginTransaction();
string old_tz = mConnection.TimeZone;
mCmd.CommandText = "set timezone to 'Asia/Hong_Kong';" +
"select localtimestamp, now(), now() at time zone 'Europe/Vienna', '2016-10-01 12:00:00' at time zone 'UTC', '2016-10-01 12:00:00' at time zone 'Europe/Vienna', utc_offset from pg_timezone_names where name = current_setting('TIMEZONE');";
mCmd.CommandType = CommandType.Text;
PqsqlDataReader reader = mCmd.ExecuteReader();
bool read = reader.Read();
Assert.IsFalse(read);
string new_tz = mConnection.TimeZone;
Assert.AreNotEqual(old_tz, new_tz);
bool hasnext = reader.NextResult();
Assert.IsTrue(hasnext);
read = reader.Read();
Assert.IsTrue(read);
DateTime now0 = reader.GetDateTime(0);
DateTime now1 = reader.GetDateTime(1);
DateTime now2 = reader.GetDateTime(2);
DateTime now3 = reader.GetDateTime(3);
DateTime now4 = reader.GetDateTime(4);
TimeSpan ts = reader.GetTimeSpan(5);
reader.Close();
DateTime nowutc0 = now0.ToUniversalTime();
TimeZoneInfo tzi_from_china_pgsql = TimeZoneInfo.CreateCustomTimeZone(new_tz, ts, new_tz, new_tz);
#if WIN32
var tz = "China Standard Time";
#else
var tz = "Asia/Shanghai";
#endif
TimeZoneInfo tzi_from_china_sys = TimeZoneInfo.FindSystemTimeZoneById(tz);
TimeSpan china_off = tzi_from_china_sys.GetUtcOffset(nowutc0);
TimeSpan local_off = TimeZoneInfo.Local.GetUtcOffset(nowutc0);
DateTimeOffset nowlocal0 = (nowutc0 + local_off - china_off).ToLocalTime();
DateTimeOffset dto_from_pgsql_to_sys = TimeZoneInfo.ConvertTime(now0, tzi_from_china_pgsql, tzi_from_china_sys);
DateTimeOffset dto_from_pgsql_to_localtime = TimeZoneInfo.ConvertTime(now0, tzi_from_china_pgsql, TimeZoneInfo.Local);
Assert.AreEqual(nowutc0, dto_from_pgsql_to_sys);
Assert.AreEqual(nowlocal0, dto_from_pgsql_to_localtime);
tran.Rollback();
}
public void PqsqlCommandBuilderTest3()
{
using (PqsqlConnection connection = new PqsqlConnection(connectionString))
using (PqsqlCommand command = connection.CreateCommand())
{
PqsqlTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
transaction.Commit(); // temp table must be visible in the next transaction
transaction = connection.BeginTransaction();
PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection)
{
SelectCommand =
{
Transaction = transaction
}
};
PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter);
// INSERT INTO "postgres"."pg_temp_2"."temptab" ("c0", "c1") VALUES (:p1, :p2)
PqsqlCommand inserter = builder.GetInsertCommand();
inserter.Parameters["p1"].Value = 1;
inserter.Parameters["p2"].Value = 2.1;
int inserted = inserter.ExecuteNonQuery();
Assert.AreEqual(1, inserted);
// UPDATE "postgres"."pg_temp_2"."temptab"
// SET "c0" = :p1, "c1" = :p2
// WHERE (("c0" = :p3) AND ((:p4 = 1 AND "c1" IS NULL) OR ("c1" = :p5)))
PqsqlCommand updater = builder.GetUpdateCommand();
updater.Parameters["p1"].Value = 2;
updater.Parameters["p2"].Value = 2.2;
updater.Parameters["p3"].Value = 1;
updater.Parameters["p4"].Value = 0;
updater.Parameters["p5"].Value = 2.1;
int updated = updater.ExecuteNonQuery();
Assert.AreEqual(1, updated);
// DELETE FROM "postgres"."pg_temp_2"."temptab"
// WHERE (("c0" = :p1) AND ((:p2 = 1 AND "c1" IS NULL) OR ("c1" = :p3)))
PqsqlCommand deleter = builder.GetDeleteCommand();
deleter.Parameters["p1"].Value = 2;
deleter.Parameters["p2"].Value = 0;
deleter.Parameters["p3"].Value = 2.2;
int deleted = deleter.ExecuteNonQuery();
Assert.AreEqual(1, deleted);
transaction.Rollback();
}
}
public void PqsqlCommandBuilderTest2()
{
using (PqsqlConnection connection = new PqsqlConnection(connectionString))
using (PqsqlCommand command = connection.CreateCommand())
{
PqsqlTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8)";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
transaction.Commit(); // temp table must be visible in the next transaction
transaction = connection.BeginTransaction();
PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection)
{
SelectCommand =
{
Transaction = transaction
},
};
adapter.RowUpdated += Adapter_RowUpdated;
PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Source);
adapter.Fill(ds, "temptab");
DataTable temptab = ds.Tables["temptab"];
DataRow row = temptab.NewRow();
row["c0"] = 123;
row["c1"] = 1.23;
temptab.Rows.Add(row);
adapter.Update(ds, "temptab");
command.CommandText = "select * from temptab";
command.CommandType = CommandType.Text;
using (PqsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Assert.AreEqual(123, reader.GetInt32(0));
Assert.AreEqual(1.23, reader.GetDouble(1));
}
}
transaction.Rollback();
}
}
public void PqsqlCopyFromTest1()
{
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo ( a int2, b int4, c int8 )";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(0, affected);
PqsqlCopyFrom copy = new PqsqlCopyFrom(mConnection)
{
Table = "foo",
ColumnList = "c,a,b",
CopyTimeout = 10
};
copy.Start();
for (short i = 9; i >= 0; i--)
{
copy.WriteInt8(i);
copy.WriteInt2(i);
copy.WriteInt4(i);
}
copy.End();
copy.Close();
mCmd.CommandText = "foo";
mCmd.CommandType = CommandType.TableDirect;
int value = 9;
foreach (IDataRecord rec in mCmd.ExecuteReader())
{
object[] o = new object[3];
rec.GetValues(o);
Assert.IsInstanceOfType(o[0], typeof(short));
Assert.AreEqual((short)value, o[0]);
Assert.IsInstanceOfType(o[1], typeof(int));
Assert.AreEqual(value, o[1]);
Assert.IsInstanceOfType(o[2], typeof(long));
Assert.AreEqual((long)value, o[2]);
value--;
}
Assert.AreEqual(-1, value);
tran.Rollback();
}
public void PqsqlCommandBuilderTest4()
{
using (PqsqlConnection connection = new PqsqlConnection(connectionString))
using (PqsqlCommand command = connection.CreateCommand())
{
PqsqlTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = "create temp table temptab (c0 int4 primary key, c1 float8, c2 timestamp);";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
transaction.Commit(); // temp table must be visible in the next transaction
transaction = connection.BeginTransaction();
PqsqlDataAdapter adapter = new PqsqlDataAdapter("select * from temptab", connection)
{
SelectCommand =
{
Transaction = transaction
},
};
adapter.RowUpdated += Adapter_RowUpdated;
PqsqlCommandBuilder builder = new PqsqlCommandBuilder(adapter);
DataTableMapping mapping = adapter.TableMappings.Add("Table", "temptab");
mapping.ColumnMappings.Add("c0", "id");
mapping.ColumnMappings.Add("c2", "time");
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
DataTable tab = ds.Tables[0];
Assert.AreEqual("id", tab.Columns[0].ColumnName);
Assert.AreEqual("c1", tab.Columns[1].ColumnName);
Assert.AreEqual("time", tab.Columns[2].ColumnName);
transaction.Rollback();
}
}
public void PqsqlConnectionTest5()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
PqsqlTransaction trans = connection.BeginTransaction();
Assert.AreEqual(IsolationLevel.ReadCommitted, trans.IsolationLevel, "wrong transaction isolation level");
trans.Rollback();
connection.Close();
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
}
public void PqsqlCommandTest6()
{
PqsqlTransaction t = mConnection.BeginTransaction();
PqsqlCommand cmd = mConnection.CreateCommand();
cmd.Transaction = t;
cmd.CommandText = "create or replace function pg_temp.test_out(p1 out text, i1 inout int, p2 out int, r inout refcursor) as $$begin $1 := 'p1 text'; $2:=$2*-4711; $3:=12345; open r for select * from ( values (1,2,3),(4,5,6),(7,8,9) ) X; end;$$ LANGUAGE plpgsql;";
cmd.CommandTimeout = 10;
cmd.CommandType = CommandType.Text;
int n = cmd.ExecuteNonQuery();
Assert.AreEqual(0, n);
PqsqlParameter p1 = new PqsqlParameter("p1", DbType.String)
{
Direction = ParameterDirection.Output,
Value = "p1_val"
};
const int p2_val = 4711;
PqsqlParameter p2 = new PqsqlParameter("i1", DbType.Int32, (object)p2_val)
{
Direction = ParameterDirection.InputOutput,
};
PqsqlParameter p3 = new PqsqlParameter("p2", DbType.Int32)
{
Direction = ParameterDirection.Output,
Value = 42
};
const string p4_val = "portal_name";
PqsqlParameter p4 = new PqsqlParameter
{
ParameterName = "r",
PqsqlDbType = PqsqlDbType.Refcursor,
Direction = ParameterDirection.InputOutput,
Value = p4_val
};
cmd.CommandText = "pg_temp.test_out";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
n = cmd.ExecuteNonQuery();
Assert.AreEqual(-1, n);
Assert.AreEqual("p1 text", p1.Value);
Assert.AreEqual(p2_val * -p2_val, p2.Value);
Assert.AreEqual(12345, p3.Value);
Assert.AreEqual(p4_val, p4.Value);
cmd.CommandText = string.Format("fetch all from {0}", p4.Value);
cmd.CommandType = CommandType.Text;
PqsqlDataReader r = cmd.ExecuteReader();
int i = 1;
while (r.Read())
{
Assert.AreEqual(i++, r.GetValue(0));
Assert.AreEqual(i++, r.GetValue(1));
Assert.AreEqual(i++, r.GetValue(2));
}
t.Rollback();
}
public void PqsqlCopyToTest1()
{
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int4, b int4, c int4); " +
"insert into foo values (1, 2, 3); " +
"insert into foo values (4, 5, 6); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(2, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "c,a,b",
CopyTimeout = 10,
};
copy.Start();
var i = 0;
while (copy.FetchRow())
{
var c = copy.ReadInt4();
var a = copy.ReadInt4();
var b = copy.ReadInt4();
if (i == 0)
{
Assert.AreEqual(1, a);
Assert.AreEqual(2, b);
Assert.AreEqual(3, c);
}
else if (i == 1)
{
Assert.AreEqual(4, a);
Assert.AreEqual(5, b);
Assert.AreEqual(6, c);
}
i++;
}
copy.Close();
tran.Rollback();
}
public void PqsqlDataReaderTest6()
{
const string qs = @"select ARRAY[0,1,2,3,42,null,4711]::_text, :p7, unnest(:p6), unnest(:p8) ;
select interval '20 days', interval '123 secs', interval '20 years 10 months', now(), :p4, timestamp 'infinity', timestamp '-infinity', date 'infinity', date '-infinity';
select 123.456::numeric, :p3::text ;
select 'NaN'::numeric, '-1234567898765432123456789.87654321'::numeric ;
select generate_series(1,1000000),generate_series(1,1000000);
select generate_series(1,1000000),generate_series(1,1000000);
select :p1,:p2::text ;
select extract(epoch from date_trunc('day',current_date - :p9 ))::integer " ;
const int p1_val = -1;
const int p2_val = 2;
const int p3_val = -3;
const double p4_val = 3.1415925;
decimal p5_val = decimal.Parse("123456789,87654321");
int[] p6_arr = { 1, 42, 4711, 0815 };
const string p7_0 = "1 string mit ü and ä sowie 0";
const string p7_1 = "2 string mit ü and ä sowie 🐨";
const string p7_2 = "42 string mit ü and ä sowie е́";
const string p7_3 = "0815 string mit ü and ä sowie П";
decimal[] p8_arr = { decimal.MinValue, 1.23M, 12.345M, -123.4567M, decimal.MaxValue };
const int p9_val = 47;
mCmd.CommandText = qs;
mCmd.CommandTimeout = 20;
PqsqlParameter p1 = mCmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = p1_val;
p1.DbType = DbType.Int32;
PqsqlParameter p2 = mCmd.CreateParameter();
p2.ParameterName = "p2";
p2.Value = p2_val;
p2.DbType = DbType.Int32;
PqsqlParameter p3 = mCmd.CreateParameter();
p3.ParameterName = "p3";
p3.Value = p3_val;
p3.DbType = DbType.Int32;
PqsqlParameter p4 = mCmd.CreateParameter();
p4.ParameterName = "p4";
p4.Value = p4_val;
p4.DbType = DbType.Double;
PqsqlParameter p5 = mCmd.CreateParameter();
p5.ParameterName = "p5";
p5.Value = p5_val;
p5.DbType = DbType.Decimal;
PqsqlParameter p6 = mCmd.CreateParameter();
p6.ParameterName = "p6";
p6.Value = p6_arr;
p6.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Int4;
PqsqlParameter p7 = mCmd.CreateParameter();
p7.ParameterName = "p7";
Array b = Array.CreateInstance(typeof(string), new int[] { 4 }, new int[] { -1 });
b.SetValue(p7_0, -1);
b.SetValue(p7_1, 0);
b.SetValue(p7_2, 1);
b.SetValue(p7_3, 2);
p7.Value = b;
p7.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Text;
PqsqlParameter p8 = mCmd.CreateParameter();
p8.ParameterName = "p8";
Array c = Array.CreateInstance(typeof(decimal), new int[] { 5 }, new int[] { -2 });
c.SetValue(p8_arr[0], -2);
c.SetValue(p8_arr[1], -1);
c.SetValue(p8_arr[2], 0);
c.SetValue(p8_arr[3], 1);
c.SetValue(p8_arr[4], 2);
p8.Value = c;
p8.PqsqlDbType = PqsqlDbType.Array | PqsqlDbType.Numeric;
PqsqlParameter p9 = mCmd.CreateParameter();
p9.ParameterName = "p9";
p9.Value = p9_val;
// let Pqsql guess the parameter type
mCmd.Parameters.Add(p1);
mCmd.Parameters.Add(p2);
mCmd.Parameters.Add(p3);
mCmd.Parameters.Add(p4);
mCmd.Parameters.Add(p5);
mCmd.Parameters.Add(p6);
mCmd.Parameters.Add(p7);
mCmd.Parameters.Add(p8);
mCmd.Parameters.Add(p9);
PqsqlTransaction t = mConnection.BeginTransaction();
mCmd.Transaction = t;
PqsqlDataReader r = mCmd.ExecuteReader();
int p6_rt = 0;
//int p8_rt = 0;
// select ARRAY[0,1,2,3,42,null,4711]::_text, :p7, unnest(:p6), unnest(:p8)
while (r.Read())
{
object o0 = r.GetValue(0);
object o1 = r.GetValue(1);
object o2 = r.GetValue(2);
object o3 = r.GetValue(3);
Array arr = (Array)o0;
Assert.AreEqual("0", arr.GetValue(1));
Assert.AreEqual("1", arr.GetValue(2));
Assert.AreEqual("2", arr.GetValue(3));
Assert.AreEqual("3", arr.GetValue(4));
Assert.AreEqual("42", arr.GetValue(5));
Assert.AreEqual(null, arr.GetValue(6));
Assert.AreEqual("4711", arr.GetValue(7));
arr = (Array)o1;
Assert.AreEqual(p7_0, arr.GetValue(1));
Assert.AreEqual(p7_1, arr.GetValue(2));
Assert.AreEqual(p7_2, arr.GetValue(3));
Assert.AreEqual(p7_3, arr.GetValue(4));
Assert.AreEqual(p6_arr[p6_rt++ % 4], o2);
// TODO decimal vs. double: decimal.MinValue cast to double does not work here
//Assert.AreEqual(p8_arr[p8_rt++], o3);
}
bool next = r.NextResult();
Assert.IsTrue(next);
// select interval '20 days', interval '123 secs', interval '20 years 10 months', now(), :p4, timestamp 'infinity', timestamp '-infinity', date 'infinity', date '-infinity'
while (r.Read())
{
TimeSpan s = r.GetTimeSpan(0);
Assert.AreEqual(TimeSpan.FromDays(20), s);
s = r.GetTimeSpan(1);
Assert.AreEqual(TimeSpan.FromSeconds(123), s);
s = r.GetTimeSpan(2);
Assert.AreEqual(TimeSpan.FromDays(7609), s);
DateTime d = r.GetDateTime(3);
TimeSpan diff = DateTime.UtcNow - d;
Assert.IsTrue(diff < TimeSpan.FromHours(1));
Assert.IsTrue(-diff < TimeSpan.FromHours(1));
double db = r.GetDouble(4);
Assert.AreEqual(p4_val, db);
d = r.GetDateTime(5);
Assert.AreEqual(DateTime.MaxValue, d);
d = r.GetDateTime(6);
Assert.AreEqual(DateTime.MinValue, d);
d = r.GetDateTime(7);
Assert.AreEqual(DateTime.MaxValue, d);
d = r.GetDateTime(8);
Assert.AreEqual(DateTime.MinValue, d);
}
next = r.NextResult();
Assert.IsTrue(next);
// select 123.456::numeric, :p3::text
while (r.Read())
{
decimal dec = r.GetDecimal(0);
Assert.AreEqual(123.456M, dec);
string str = r.GetString(1);
Assert.AreEqual(p3_val.ToString(), str);
}
t.Commit();
next = r.NextResult();
Assert.IsTrue(next);
// select 'NaN'::numeric, '-1234567898765432123456789.87654321'::numeric
while (r.Read())
{
double dou = r.GetDouble(0);
Assert.AreEqual(Double.NaN, dou);
dou = r.GetDouble(1);
Assert.AreEqual(decimal.ToDouble(-1234567898765432123456789.87654321M), dou);
}
next = r.NextResult();
Assert.IsTrue(next);
int comp = 1;
// select generate_series(1,1000000),generate_series(1,1000000)
while (r.Read())
{
int gen = r.GetInt32(0);
Assert.AreEqual(comp++, gen);
}
next = r.NextResult();
Assert.IsTrue(next);
comp = 1;
// select generate_series(1,1000000),generate_series(1,1000000)
while (r.Read())
{
int gen = r.GetInt32(0);
Assert.AreEqual(comp++, gen);
}
Assert.AreEqual(1000001, comp);
next = r.NextResult();
Assert.IsTrue(next);
comp = 0;
// select :p1,:p2::text
while (r.Read())
{
int i = r.GetInt32(0);
string st = r.GetString(1);
Assert.AreEqual(p1_val, i);
string p2s = Convert.ToString(p2_val);
Assert.AreEqual(p2s, st);
comp++;
}
Assert.AreEqual(1, comp);
next = r.NextResult();
Assert.IsTrue(next);
comp = 0;
// select extract(epoch from date_trunc('day',current_date - :p9 ))::integer
while (r.Read())
{
int i = r.GetInt32(0);
int unix = (int)DateTime.UtcNow.Subtract(new TimeSpan(p9_val, 0, 0, 0)).Date.Subtract(new DateTime(1970, 1, 1)).TotalSeconds;
Assert.AreEqual(unix, i);
comp++;
}
next = r.NextResult();
Assert.IsFalse(next);
r.Close();
r.Dispose();
}