public void PqsqlConnectionTest6()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
PqsqlCommand cmd = connection.CreateCommand();
Assert.AreEqual(CommandType.Text, cmd.CommandType, "wrong command type");
Assert.AreEqual(-1, cmd.CommandTimeout, "wrong command timeout");
Assert.AreEqual(string.Empty, cmd.CommandText, "wrong command text");
Assert.AreEqual(connection, cmd.Connection, "wrong command connection");
Assert.AreEqual(null, cmd.Transaction, "wrong command transaction");
cmd.CommandText = "select pg_terminate_backend(pg_backend_pid()); select pg_sleep(5);";
cmd.ExecuteNonQuery(); // must execute both statements
cmd.Cancel();
cmd.CommandText = "";
cmd.ExecuteNonQuery();
}
public void PqsqlConnectionTest4()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
IntPtr conn_attempt1 = connection.PGConnection;
int sock1 = PqsqlWrapper.PQsocket(conn_attempt1);
Assert.AreNotEqual(-1, sock1, "wrong socket");
// close the underlying socket without letting Pqsql and libpq know
int closed = CloseSocket(sock1);
Assert.AreEqual(0, closed, "closesocket failed");
connection.Close();
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
connection.Open();
IntPtr conn_attempt2 = connection.PGConnection;
ConnStatusType status = PqsqlWrapper.PQstatus(conn_attempt2);
Assert.AreEqual(ConnStatusType.CONNECTION_OK, status, "connection broken");
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
int sock2 = PqsqlWrapper.PQsocket(conn_attempt2);
// close the underlying socket without letting Pqsql and libpq know
closed = CloseSocket(sock2);
Assert.AreEqual(0, closed, "closesocket failed");
}
public void PqsqlConnectionTest9()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
string s = connection.GetErrorMessage();
Assert.AreEqual(string.Empty, s);
PqsqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "foobar command";
try
{
cmd.ExecuteNonQuery();
}
catch (PqsqlException exception)
{
// SYNTAX_ERROR = 16801924, // 42601 (syntax_error)
//Assert.AreNotSame(string.Empty, exception.Hint);
Assert.AreEqual("42601", exception.SqlState);
Assert.AreEqual(16801924, exception.ErrorCode);
}
s = connection.GetErrorMessage();
Assert.IsNotNull(s);
Assert.AreNotSame(string.Empty, s);
}
public void PqsqlConnectionTest8()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
DataTable connschema = connection.GetSchema();
Assert.Fail();
}
public void PqsqlTypeRegistryTest6()
{
Action[] actions = new Action[20];
// stress test user-defined type setup
for (int i = 0; i < 20; i++)
{
actions[i] = () =>
{
using (PqsqlConnection conn = new PqsqlConnection(mConnection.ConnectionString))
using (PqsqlCommand cmd = new PqsqlCommand("select 'hello world'::citext", conn))
using (PqsqlDataReader reader = cmd.ExecuteReader())
{
bool read = reader.Read();
Assert.IsTrue(read);
object helloWorld = reader.GetValue(0); // must access by GetValue, GetString verifies typoid
Assert.AreEqual("hello world", helloWorld);
read = reader.Read();
Assert.IsFalse(read);
}
};
}
using (PqsqlCommand check = new PqsqlCommand("select oid from pg_extension where extname='citext'", mConnection))
using (PqsqlCommand create = new PqsqlCommand("create extension citext", mConnection))
using (PqsqlTransaction t = mConnection.BeginTransaction())
{
object o = null;
try
{
check.Transaction = t;
o = check.ExecuteScalar();
if (o == null)
{
create.Transaction = t;
int aff = create.ExecuteNonQuery();
Assert.AreEqual(0, aff);
}
t.Commit();
Parallel.Invoke(actions);
}
finally
{
if (o == null)
{
using (PqsqlCommand drop = new PqsqlCommand("drop extension if exists citext", mConnection))
{
int aff = drop.ExecuteNonQuery();
Assert.AreEqual(0, aff);
}
}
}
}
}
public void PqsqlConnectionTest2()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
Assert.AreEqual(connectionString.Replace(" ", "").Replace("\t", "").Replace("\n", ""), connection.ConnectionString, "wrong connection string");
Assert.AreEqual(3, connection.ConnectionTimeout, "wrong connection timeout");
// closed connection with service file should give us empty database name
string dbname = connection.Database;
Assert.AreEqual(string.Empty, dbname, "wrong connection database");
string serverVersion = connection.ServerVersion;
Assert.IsFalse(string.IsNullOrEmpty(serverVersion));
Assert.AreEqual("-1", serverVersion);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
dbname = connection.Database;
Assert.AreNotEqual(string.Empty, dbname, "wrong connection database");
serverVersion = connection.ServerVersion;
Assert.IsFalse(string.IsNullOrEmpty(serverVersion));
if (serverVersion.Length >= 6) // expect 100000 or later
{
int version = Convert.ToInt32(serverVersion);
Assert.IsTrue(version >= 100000);
}
else if (serverVersion.Length >= 5) // expect 90400 .. 90699
{
int version = Convert.ToInt32(serverVersion);
Assert.IsTrue(version >= 90400);
Assert.IsTrue(version <= 90699);
}
else
{
Assert.Fail("wrong server version");
}
connection.Close();
dbname = connection.Database;
Assert.AreEqual(string.Empty, dbname, "wrong connection database");
serverVersion = connection.ServerVersion;
Assert.IsFalse(string.IsNullOrEmpty(serverVersion));
Assert.AreEqual("-1", serverVersion);
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
}
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 TestInitialize()
{
mConnection = new PqsqlConnection(connectionString);
// force UTC
using (var cmd = new PqsqlCommand("set timezone to 'UTC';", mConnection))
{
cmd.ExecuteNonQuery();
}
mCmd = mConnection.CreateCommand();
}
public void PqsqlConnectionStringBuilderTest1()
{
PqsqlConnectionStringBuilder builder = new PqsqlConnectionStringBuilder(connectionString);
builder[PqsqlConnectionStringBuilder.keepalives] = "1";
builder[PqsqlConnectionStringBuilder.keepalives_idle] = "23";
builder[PqsqlConnectionStringBuilder.keepalives_count] = "3";
builder[PqsqlConnectionStringBuilder.keepalives_interval] = "3";
string dataSource;
using (PqsqlConnection connection = new PqsqlConnection(builder))
{
// closed connection with service file should give us empty data source
Assert.IsTrue(string.IsNullOrEmpty(connection.DataSource));
using (PqsqlCommand cmd = new PqsqlCommand("show all", connection))
using (PqsqlDataReader r = cmd.ExecuteReader())
{
object value;
if (builder.TryGetValue(PqsqlConnectionStringBuilder.host, out value))
{
Assert.AreEqual(connection.DataSource, value);
dataSource = value.ToString();
}
else // no datasource specified
{
dataSource = connection.DataSource;
}
cmd.Cancel();
}
}
builder[PqsqlConnectionStringBuilder.host] = dataSource;
using (PqsqlConnection connection = new PqsqlConnection(builder))
using (PqsqlCommand cmd = new PqsqlCommand("show all", connection))
using (PqsqlDataReader r = cmd.ExecuteReader())
{
object value;
if (builder.TryGetValue(PqsqlConnectionStringBuilder.host, out value))
{
Assert.AreEqual(connection.DataSource, value);
}
else
{
Assert.Fail("host part is not available");
}
cmd.Cancel();
}
}
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 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 PqsqlConnectionTest7()
{
PqsqlConnection connection = new PqsqlConnection(connectionString);
bool opened = false;
bool closed = true;
connection.StateChange += (sender, args) =>
{
if (args.CurrentState == ConnectionState.Closed)
{
opened = false;
closed = true;
}
if (args.CurrentState == ConnectionState.Open)
{
opened = true;
closed = false;
}
};
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
Assert.AreEqual(true, opened);
Assert.AreEqual(false, closed);
try
{
PqsqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "select pg_terminate_backend(pg_backend_pid()); select pg_sleep(5);";
cmd.ExecuteNonQuery(); // must execute both statements
}
catch (Exception)
{
// ignored
connection.Close();
}
Assert.AreEqual(false, opened);
Assert.AreEqual(true, closed);
}
public void PqsqlConnectionStringBuilderTest3()
{
PqsqlConnectionStringBuilder builder = new PqsqlConnectionStringBuilder(string.Empty);
Assert.AreEqual(string.Empty, builder.ConnectionString);
Assert.AreEqual(0, builder.Count);
using (PqsqlConnection connection = new PqsqlConnection(builder))
{
try
{
connection.Open();
}
catch (PqsqlException)
{
// ignored, depends on server config whether empty connection string is valid
}
connection.Close();
}
}
public void PqsqlConnectionTest3()
{
PqsqlConnectionPool.Clear();
PqsqlConnection connection = new PqsqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
IntPtr conn_attempt1 = connection.PGConnection;
connection.Close();
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
// in order to simulate broken connections in the connection pool, we can step through this unit test and turn
// off postgresql before we open the connection again in the next step below:
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
IntPtr conn_attempt2 = connection.PGConnection;
Assert.AreEqual(conn_attempt1, conn_attempt2, "connection was not received from internal connection pool");
}
public void PqsqlConnectionTest1()
{
PqsqlConnection connection = new PqsqlConnection(string.Empty);
Assert.AreEqual(ConnectionState.Closed, connection.State, "wrong connection state");
Assert.AreEqual(string.Empty, connection.ConnectionString, "wrong connection string");
Assert.AreEqual(0, connection.ConnectionTimeout, "wrong connection timeout");
Assert.AreEqual(string.Empty, connection.Database, "wrong connection database");
connection.ConnectionString = connectionString;
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
connection.ChangeDatabase("postgres");
Assert.AreEqual("postgres", connection.Database, "wrong connection database");
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
ExecStatusType status = connection.Exec(null);
Assert.AreEqual(ExecStatusType.PGRES_FATAL_ERROR, status);
IntPtr res;
status = connection.Exec(null, out res);
Assert.AreEqual(ExecStatusType.PGRES_FATAL_ERROR, status);
Assert.AreEqual(IntPtr.Zero, res);
connection.Consume(res);
connection.Dispose();
connection.Dispose();
}
public void PqsqlConnectionTest10()
{
PqsqlConnectionPool.Clear();
PqsqlConnection connection = new PqsqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
IntPtr pgConn = connection.PGConnection;
int client_encoding = PqsqlWrapper.PQclientEncoding(pgConn);
Assert.AreEqual((int)PgEnc.PG_UTF8, client_encoding, "wrong client_encoding");
// setup client_encoding to sqlascii, and leave it like that
byte[] encoding = PqsqlUTF8Statement.CreateUTF8Statement("sqlascii");
client_encoding = PqsqlWrapper.PQsetClientEncoding(pgConn, encoding);
Assert.AreEqual(0, client_encoding, "could not set client_encoding");
client_encoding = PqsqlWrapper.PQclientEncoding(pgConn);
Assert.AreEqual(0, client_encoding, "wrong client_encoding");
connection.Close();
// now try to get the same connection again
connection = new PqsqlConnection(connectionString);
connection.Open();
Assert.AreEqual(ConnectionState.Open, connection.State, "wrong connection state");
pgConn = connection.PGConnection;
client_encoding = PqsqlWrapper.PQclientEncoding(pgConn);
Assert.AreEqual((int)PgEnc.PG_UTF8, client_encoding, "wrong client_encoding");
}
public void TestInitialize()
{
mConnection = new PqsqlConnection(connectionString);
mCmd = mConnection.CreateCommand();
}
public void TestInitialize()
{
mConnection = new PqsqlConnection(connectionString);
}
