public void PqsqlCopyToTest14()
{
const int len = 1;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int4, b text, c text); " +
"insert into foo values (2, 'hallo pqsql 2', null);";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
CopyTimeout = 10,
SuppressSchemaQuery = true,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
try
{
copy.ReadBoolean();
}
catch (InvalidOperationException) {}
// try again with correct reader
var a0 = copy.ReadInt4();
Assert.AreEqual(2, a0);
var b0 = copy.ReadString();
Assert.AreEqual("hallo pqsql 2", b0);
var c0 = copy.IsNull();
Assert.IsTrue(c0);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.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 PqsqlCopyToTest10()
{
const int len = 2;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a bytea); " +
"insert into foo values (null); " +
"insert into foo values ('\\x707173716C'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadRaw();
Assert.IsNull(a0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadRaw();
CollectionAssert.AreEqual(new byte[] { 0x70, 0x71, 0x73, 0x71, 0x6C }, a1);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest11()
{
const int len = 1;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a text); " +
"insert into foo values ('hallo pqsql');";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadString();
Assert.AreEqual("hallo pqsql", a0);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest12()
{
const int len = 3;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int4, b text, c text); " +
"insert into foo values (2, 'hallo pqsql 2', null); " +
"insert into foo values (1, 'hallo pqsql 1', 'asd'); " +
"insert into foo values (3, 'hallo pqsql 3', null);";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Query = "select a,b from foo order by a asc; ",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadInt4();
Assert.AreEqual(1, a0);
var b0 = copy.ReadString();
Assert.AreEqual("hallo pqsql 1", b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadInt4();
Assert.AreEqual(2, a1);
var b1 = copy.ReadString();
Assert.AreEqual("hallo pqsql 2", b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadInt4();
Assert.AreEqual(3, a2);
var b2 = copy.ReadString();
Assert.AreEqual("hallo pqsql 3", b2);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest2()
{
const int len = 1;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int4, b int4, c int4); " +
"insert into foo values (null, 2, 3); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "c,a,b",
CopyTimeout = 10,
};
copy.Start();
var results = new int?[len];
while (copy.FetchRow())
{
for (int i = 0; i < len; i++)
{
int?result;
if (copy.IsNull())
{
result = null;
}
else
{
result = copy.ReadInt4();
}
if (i == 0)
{
// c
Assert.IsTrue(result.HasValue);
Assert.AreEqual(3, result.Value);
}
else if (i == 1)
{
// a
Assert.IsFalse(result.HasValue);
}
else if (i == 2)
{
// b
Assert.IsTrue(result.HasValue);
Assert.AreEqual(2, result.Value);
}
}
}
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest9()
{
const int len = 5;
// the last 8 ticks (800 nanoseconds) of MinValue can not be stored in postgres' interval type, because of
// its resolution to milliseconds.
var intervalMinValue = new TimeSpan(TimeSpan.MinValue.Ticks + 8);
// the last 7 ticks (700 nanoseconds) of MaxValue can not be stored in postgres' interval type, because of
// its resolution to milliseconds.
var intervalMaxValue = new TimeSpan(TimeSpan.MaxValue.Ticks - 7);
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a interval); " +
"insert into foo values (null); " +
"insert into foo values ('10675199 02:48:05.477580 ago'); " +
"insert into foo values ('10675199 02:48:05.477580'); " +
"insert into foo values ('10675199 02:48:05.477581 ago'); " +
"insert into foo values ('10675199 02:48:05.477581'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadInterval();
Assert.AreEqual(TimeSpan.MinValue, a0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadInterval();
Assert.AreEqual(intervalMinValue, a1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadInterval();
Assert.AreEqual(intervalMaxValue, a2);
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a3 '10675199 02:48:05.477581 ago'
var a3 = copy.ReadInterval();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a4 '10675199 02:48:05.477581'
copy.ReadInterval();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest8()
{
const int len = 8;
var dateMaxValue = DateTime.MaxValue.Date;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a date); " +
"insert into foo values (null); " +
"insert into foo values ('0001-01-01'); " +
"insert into foo values ('1970-01-01'); " +
"insert into foo values ('9999-12-31'); " +
"insert into foo values ('31 December, 1 BC'); " +
"insert into foo values ('10000-01-01'); " +
"insert into foo values ('-infinity'); " +
"insert into foo values ('infinity'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadDate();
Assert.AreEqual(DateTime.MinValue, a0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadDate();
Assert.AreEqual(DateTime.MinValue, a1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadDate();
Assert.AreEqual(DateTime.UnixEpoch, a2);
res = copy.FetchRow();
Assert.IsTrue(res);
var a3 = copy.ReadDate();
Assert.AreEqual(dateMaxValue, a3);
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a4 '31 December, 1 BC'
copy.ReadDate();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a5 '10000-01-01'
copy.ReadDate();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a6 '-infinity'
copy.ReadDate();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a7 'infinity'
copy.ReadDate();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest7()
{
const int len = 5;
var timeMinValue = DateTime.UnixEpoch;
var timeMaxValue = DateTime.UnixEpoch.AddDays(1);
var timeTzMinValue = new DateTimeOffset(1970, 1, 1, 0, 0, 0, new TimeSpan(14, 0, 0));
var timeTzMaxValue = new DateTimeOffset(1970, 1, 2, 0, 0, 0, new TimeSpan(-14, 0, 0));
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a time, b timetz); " +
"insert into foo values (null, null); " +
"insert into foo values ('00:00:00', '00:00:00+14'); " +
"insert into foo values ('24:00:00', '24:00:00-14'); " +
"insert into foo values (null, '00:00:00+14:01'); " +
"insert into foo values (null, '24:00:00-14:01'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadTime();
Assert.AreEqual(DateTime.MinValue, a0);
var b0 = copy.ReadTimeTZ();
Assert.AreEqual(DateTimeOffset.MinValue, b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadTime();
Assert.AreEqual(timeMinValue, a1);
var b1 = copy.ReadTimeTZ();
Assert.AreEqual(timeTzMinValue, b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadTime();
Assert.AreEqual(timeMaxValue, a2);
var b2 = copy.ReadTimeTZ();
Assert.AreEqual(timeTzMaxValue, b2);
res = copy.FetchRow();
Assert.IsTrue(res);
// a3
copy.ReadTime();
try
{
// b3 '00:00:00+14:01'
copy.ReadTimeTZ();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
// a4
copy.ReadTime();
try
{
// b4 '24:00:00-14:01'
copy.ReadTimeTZ();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest6()
{
const int len = 8;
// the highest representable System.DateTime tick in postgres is 9 less than
// System.DateTime.MaxValue.Ticks, because the resolution in postgres is to the millisecond whereas
// System.DateTime's resolution is to 100 nanoseconds. This means the last 900 nanoseconds before the year
// 10000 cannot be represented in postgres' timestamp.
var postgresDateTimeMaxValueTicks = DateTime.MaxValue.Ticks - 9;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a timestamp, b timestamptz); " +
"insert into foo values (null, null); " +
"insert into foo values ('0001-01-01 00:00:00.000000 UTC', '0001-01-01 00:00:00.000000 UTC'); " +
"insert into foo values ('1970-01-01 00:00:00.000000 UTC', '1970-01-01 00:00:00.000000 UTC'); " +
"insert into foo values ('9999-12-31 23:59:59.999999 UTC', '9999-12-31 23:59:59.999999 UTC'); " +
"insert into foo values ('31 December, 1 BC 23:59:59.999999', '31 December, 1 BC 23:59:59.999999'); " +
"insert into foo values ('10000-01-01 00:00:00.000000 UTC', '10000-01-01 00:00:00.000000 UTC'); " +
"insert into foo values ('-infinity', '-infinity'); " +
"insert into foo values ('infinity', 'infinity'); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, a0);
var b0 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, a1);
var b1 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.UnixEpoch, a2);
var b2 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.UnixEpoch, b2);
res = copy.FetchRow();
Assert.IsTrue(res);
var a3 = copy.ReadTimestamp();
Assert.AreEqual(new DateTime(postgresDateTimeMaxValueTicks), a3);
var b3 = copy.ReadTimestamp();
Assert.AreEqual(new DateTime(postgresDateTimeMaxValueTicks), b3);
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a4 '31 December, 1 BC 23:59:59.999999'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
try
{
// b4 '31 December, 1 BC 23:59:59.999999'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
try
{
// a5 '10000-01-01 00:00:00.000000 UTC'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
try
{
// b5 '10000-01-01 00:00:00.000000 UTC'
copy.ReadTimestamp();
Assert.Fail();
}
catch (ArgumentOutOfRangeException) {}
res = copy.FetchRow();
Assert.IsTrue(res);
// a6 '-infinity'
var a6 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, a6);
// b6 '-infinity'
var b6 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MinValue, b6);
res = copy.FetchRow();
Assert.IsTrue(res);
// a7 'infinity'
var a7 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MaxValue, a7);
// b7 'infinity'
var b7 = copy.ReadTimestamp();
Assert.AreEqual(DateTime.MaxValue, b7);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest5()
{
const int len = 3;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a float4, b float8); " +
"insert into foo values (null, null); " +
"insert into foo values (-3.40282347E+38, -1.7976931348623157E+308); " +
"insert into foo values (3.40282347E+38, 1.7976931348623157E+308); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadFloat4();
Assert.AreEqual(0, a0);
var b0 = copy.ReadFloat8();
Assert.AreEqual(0, b0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadFloat4();
Assert.AreEqual(float.MinValue, a1);
var b1 = copy.ReadFloat8();
Assert.AreEqual(double.MinValue, b1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadFloat4();
Assert.AreEqual(float.MaxValue, a2);
var b2 = copy.ReadFloat8();
Assert.AreEqual(double.MaxValue, b2);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest4()
{
const int len = 3;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int2, b int4, c int8); " +
"insert into foo values (null, null, null); " +
"insert into foo values (-32768, -2147483648, -9223372036854775808); " +
"insert into foo values (32767, 2147483647, 9223372036854775807); ";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b,c",
CopyTimeout = 10,
};
copy.Start();
var res = copy.FetchRow();
Assert.IsTrue(res);
var a0 = copy.ReadInt2();
Assert.AreEqual(0, a0);
var b0 = copy.ReadInt4();
Assert.AreEqual(0, b0);
var c0 = copy.ReadInt8();
Assert.AreEqual(0, c0);
res = copy.FetchRow();
Assert.IsTrue(res);
var a1 = copy.ReadInt2();
Assert.AreEqual(short.MinValue, a1);
var b1 = copy.ReadInt4();
Assert.AreEqual(int.MinValue, b1);
var c1 = copy.ReadInt8();
Assert.AreEqual(long.MinValue, c1);
res = copy.FetchRow();
Assert.IsTrue(res);
var a2 = copy.ReadInt2();
Assert.AreEqual(short.MaxValue, a2);
var b2 = copy.ReadInt4();
Assert.AreEqual(int.MaxValue, b2);
var c2 = copy.ReadInt8();
Assert.AreEqual(long.MaxValue, c2);
res = copy.FetchRow();
Assert.IsFalse(res);
copy.Close();
tran.Rollback();
}
public void PqsqlCopyToTest3()
{
const int len = 1;
PqsqlTransaction tran = mConnection.BeginTransaction();
mCmd.Transaction = tran;
mCmd.CommandText = "create temporary table foo (a int2, b int4, c int8, d boolean, e boolean, f float4, " +
"g float8, h text, i timestamp, j time, k timetz, l timetz, m date, n interval); " +
"insert into foo values (5, 1000001, 42949672950, true, false, 3.14, 3.14, 'hallo 1', " +
"TIMESTAMP '1999-01-08 04:05:06', '04:05:06.789', '04:05:06-08:00', '04:05:06+08:00', " +
"'1999-01-08', '3 4:05:06');";
mCmd.CommandType = CommandType.Text;
int affected = mCmd.ExecuteNonQuery();
Assert.AreEqual(len, affected);
var copy = new PqsqlCopyTo(mConnection)
{
Table = "foo",
ColumnList = "a,b,c,d,e,f,g,h,i,j,k,l,m,n",
CopyTimeout = 10,
};
copy.Start();
while (copy.FetchRow())
{
var a = copy.ReadInt2();
Assert.AreEqual(5, a);
var b = copy.ReadInt4();
Assert.AreEqual(1000001, b);
var c = copy.ReadInt8();
Assert.AreEqual(42949672950, c);
var d = copy.ReadBoolean();
Assert.IsTrue(d);
var e = copy.ReadBoolean();
Assert.IsFalse(e);
var f = copy.ReadFloat4();
Assert.AreEqual(3.14, f, 0.00001);
var g = copy.ReadFloat8();
Assert.AreEqual(3.14, g, 0.00001);
var h = copy.ReadString();
Assert.AreEqual("hallo 1", h);
var i = copy.ReadTimestamp();
Assert.AreEqual(new DateTime(1999, 1, 8, 4, 5, 6), i);
var j = copy.ReadTime();
Assert.AreEqual(new DateTime(1970, 1, 1, 4, 5, 6, 789), j);
var k = copy.ReadTimeTZ();
Assert.AreEqual(new DateTimeOffset(1970, 1, 1, 4, 5, 6, 0, new TimeSpan(-8, 0, 0)), k);
var l = copy.ReadTimeTZ();
Assert.AreEqual(new DateTimeOffset(1970, 1, 1, 4, 5, 6, 0, new TimeSpan(8, 0, 0)), l);
var m = copy.ReadDate();
Assert.AreEqual(new DateTime(1999, 1, 8), m);
var n = copy.ReadInterval();
Assert.AreEqual(new TimeSpan(3, 4, 5, 6), n);
}
copy.Close();
tran.Rollback();
}
