protected void _OpenConnection(ref NpgsqlConnection conn)
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (NpgsqlException e)
{
if (e.ErrorCode.Equals(CONNECTION_POOL_EXHAUSTED_CODE))
{
NpgsqlConnection.ClearAllPools();
}
throw e;
}
catch (Exception e)
{
throw e;
}
}
public void PooledConnections()
{
var totalCounter = Counters.NumberOfPooledConnections.DiagnosticsCounter;
var activeCounter = Counters.NumberOfActiveConnections.DiagnosticsCounter;
var freeCounter = Counters.NumberOfFreeConnections.DiagnosticsCounter;
var nonPooledConnString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
Pooling = false
}.ToString();
using (var pooledConn = new NpgsqlConnection(ConnectionString))
using (var nonPooledConn = new NpgsqlConnection(nonPooledConnString))
{
Assert.That(totalCounter !.RawValue, Is.Zero);
Assert.That(activeCounter !.RawValue, Is.Zero);
Assert.That(freeCounter !.RawValue, Is.Zero);
nonPooledConn.Open();
Assert.That(totalCounter.RawValue, Is.Zero);
Assert.That(activeCounter.RawValue, Is.Zero);
Assert.That(freeCounter.RawValue, Is.Zero);
pooledConn.Open();
Assert.That(totalCounter.RawValue, Is.EqualTo(1));
Assert.That(activeCounter.RawValue, Is.EqualTo(1));
Assert.That(freeCounter.RawValue, Is.Zero);
nonPooledConn.Close();
Assert.That(totalCounter.RawValue, Is.EqualTo(1));
Assert.That(activeCounter.RawValue, Is.EqualTo(1));
Assert.That(freeCounter.RawValue, Is.Zero);
pooledConn.Close();
Assert.That(totalCounter.RawValue, Is.EqualTo(1));
Assert.That(activeCounter.RawValue, Is.Zero);
Assert.That(freeCounter.RawValue, Is.EqualTo(1));
}
NpgsqlConnection.ClearAllPools();
Assert.That(totalCounter.RawValue, Is.Zero);
Assert.That(activeCounter.RawValue, Is.Zero);
Assert.That(freeCounter.RawValue, Is.Zero);
}
private void ResetConnection()
{
var cs = Connection.ConnectionString;
if (Transaction != null)
{
throw new FrameworkException("Can't reset connection inside transaction");
}
lock (sync)
{
try
{
Connection.Close();
Connection.Dispose();
}
catch (Exception ex)
{
LogFactory.Create("Postgres database layer - reset connection").Error(ex.ToString());
NpgsqlConnection.ClearAllPools();
}
Connection = new NpgsqlConnection(cs);
}
}
private void ResetConnection()
{
var cs = Connection.ConnectionString;
if (Transaction != null)
{
throw new FrameworkException("Can't reset connection inside transaction");
}
lock (sync)
{
try
{
Connection.Close();
Connection.Dispose();
}
catch (Exception ex)
{
TraceSource.TraceEvent(TraceEventType.Error, 5111, "{0}", ex);
NpgsqlConnection.ClearAllPools();
}
Connection = new NpgsqlConnection(cs);
DifferentConnection = true;
}
}
public void Setup()
{
NpgsqlConnection.ClearAllPools();
}
/// <summary>
/// Очистить пул приложений
/// </summary>
protected override void ClearPool()
{
NpgsqlConnection.ClearAllPools();
}
public void stopAccess()
{
NpgsqlConnection.ClearAllPools();
this.con.Close();
}
public override void TearDown()
{
base.TearDown();
// always kill connections to the db
NpgsqlConnection.ClearAllPools();
}
public void GlobalCleanup()
{
_noOpenCloseCmd.Connection?.Close();
NpgsqlConnection.ClearAllPools();
SqlConnection.ClearAllPools();
}
public void UpdateDataTable(DataTable table)
{
lock (objLock)
{
using (NpgsqlConnection _connectionInternal = new NpgsqlConnection(ConnectString))
{
try
{
if (_connectionInternal.State != ConnectionState.Open)
{
_connectionInternal.Open();
}
DataTable changes = table.GetChanges(DataRowState.Added);
if (changes != null)
{
DataRowCollection rows = changes.Rows;
DataColumnCollection cols = changes.Columns;
int colLength = cols.Count;
int rowLength = rows.Count;
string insert = @"INSERT INTO " + table.TableName + "(";
for (int i = 0; i < colLength; i++)
{
insert += cols[i].ColumnName + ",";
}
insert = insert.Substring(0, insert.Length - 1) + ")";
var transaction = _connectionInternal.BeginTransaction();
for (int j = 0; j < rowLength; j++)
{
DataRow row = rows[j];
string addstring = insert + " VALUES(";
for (int i = 0; i < colLength; i++)
{
DataColumn col = cols[i];
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
addstring += "'" + row[col] + "' , ";
}
else if (col.DataType == typeof(bool))
{
addstring += ((bool)row[col] ? 1 : 0) + " , ";
}
else if (col.DataType == typeof(DateTime))
{
addstring += string.IsNullOrWhiteSpace(row[col].ToString()) ? "null, " : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' , ";
}
else
{
addstring += (string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : row[col].ToString()) + " , ";
}
}
addstring = addstring.Substring(0, addstring.Length - 2) + ");";
NpgsqlCommand command = new NpgsqlCommand(addstring, _connectionInternal, transaction);
command.ExecuteNonQuery();
}
transaction.Commit();
table.AcceptChanges();
}
changes = table.GetChanges(DataRowState.Modified);
if (changes != null)
{
DataRowCollection rows = changes.Rows;
DataColumnCollection cols = changes.Columns;
DataColumn[] primary = changes.PrimaryKey;
int pl = primary.Length;
var names = new Dictionary <string, string>();
for (int i = 0; i < pl; i++)
{
var p = primary[i];
string name = p.ColumnName;
names.Add(name, "");
}
int colLength = cols.Count;
int rowLength = rows.Count;
var transaction = _connectionInternal.BeginTransaction();
for (int j = 0; j < rowLength; j++)
{
string update = @"UPDATE " + table.TableName + " SET ";
DataRow row = rows[j];
int added = 0;
for (int i = 0; i < colLength; i++)
{
var col = cols[i];
string name = col.ColumnName;
if (!names.ContainsKey(name))
{
added++;
string value = "";
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
value = "'" + row[col] + "' , ";
}
else if (col.DataType == typeof(bool))
{
value = ((bool)row[col] ? 1 : 0) + " , ";
}
else if (col.DataType == typeof(DateTime))
{
value = string.IsNullOrWhiteSpace(row[col].ToString()) ? "null, " : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' , ";
}
else
{
value = (string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : row[col].ToString()) + " , ";
}
update += name + " = " + value;
}
}
if (added > 0)
{
update = update.Substring(0, update.Length - 2) + " WHERE ";
for (int i = 0; i < pl; i++)
{
var col = primary[i];
string name = col.ColumnName;
string value = "";
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
value = "'" + row[col] + "'";
}
else if (col.DataType == typeof(bool))
{
value = ((bool)row[col] ? 1 : 0).ToString();
}
else if (col.DataType == typeof(DateTime))
{
value = string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' ";
}
else
{
value = (string.IsNullOrWhiteSpace(row[col].ToString()) ? "null" : row[col].ToString());
}
update += name + " = " + value + (i == pl - 1 ? ";" : " AND ");
}
update = update.Trim();
if (update.EndsWith(", ;"))
{
update = update.Replace(", ;", ";");
}
NpgsqlCommand command = new NpgsqlCommand(update, _connectionInternal, transaction);
command.ExecuteNonQuery();
}
}
transaction.Commit();
table.AcceptChanges();
}
changes = table.GetChanges(DataRowState.Deleted);
if (changes != null)
{
DataRowCollection rows = changes.Rows;
DataColumnCollection cols = changes.Columns;
DataColumn[] primary = changes.PrimaryKey;
int pl = primary.Length;
var names = new Dictionary <string, string>();
for (int i = 0; i < pl; i++)
{
var p = primary[i];
string name = p.ColumnName;
names.Add(name, "");
}
int colLength = cols.Count;
int rowLength = rows.Count;
var transaction = _connectionInternal.BeginTransaction();
for (int j = 0; j < rowLength; j++)
{
DataRow row = rows[j];
// if(row.RowState != DataRowState.Deleted)
{
string update = @"DELETE FROM " + table.TableName + " WHERE ";
for (int i = 0; i < pl; i++)
{
// var col = primary[i];
// string name = col.ColumnName;
// string value = "";
// // Console.WriteLine(" -----> REMOVE: " + col + " " + name);
// if (col.DataType == typeof(string) || col.DataType == typeof(char))
// value = "'" + row[col, DataRowVersion.Original] + "' , ";
// else if (col.DataType == typeof(bool))
// value = ((bool)row[col, DataRowVersion.Original] ? 1 : 0).ToString();
// else if (col.DataType == typeof(DateTime))
// value = string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null, " : "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' ";
// else
// value = (string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null" : row[col].ToString());
// update += name + " = " + value + (i == pl - 1 ? ";" : " AND ");
var col = primary[i];
string name = col.ColumnName;
string value = "";
if (col.DataType == typeof(string) || col.DataType == typeof(char))
{
value = "'" + row[col, DataRowVersion.Original] + "'";
}
else if (col.DataType == typeof(bool))
{
value = ((bool)row[col, DataRowVersion.Original] ? 1 : 0).ToString();
}
else if (col.DataType == typeof(DateTime))
{
value = string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null" : "'" + ((DateTime)row[col, DataRowVersion.Original]).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' ";
}
else
{
value = (string.IsNullOrWhiteSpace(row[col, DataRowVersion.Original].ToString()) ? "null" : row[col, DataRowVersion.Original].ToString());
}
update += name + " = " + value + (i == pl - 1 ? ";" : " AND ");
}
update = update.Trim();
if (update.EndsWith(", ;"))
{
update = update.Replace(", ;", ";");
}
NpgsqlCommand command = new NpgsqlCommand(update, _connectionInternal, transaction);
command.ExecuteNonQuery();
}
}
transaction.Commit();
table.AcceptChanges();
}
_connectionInternal.Close();
}
catch (Exception e)
{
NpgsqlConnection.ClearAllPools();
Console.WriteLine(e);
}
}
}
}
public void Cleanup()
{
NpgsqlConnection.ClearAllPools();
SqlConnection.ClearAllPools();
}
// Clear connection pools in case there are active connections that are pooled private static void ClearAllPools() => NpgsqlConnection.ClearAllPools();
public override void ClearConnectionPools(IConnectionInfo cxInfo)
{
NpgsqlConnection.ClearAllPools();
}
protected override void ReleaseAllPooledConnections()
{
NpgsqlConnection.ClearAllPools();
}
public async Task TestDatabaseSaving()
{
//TODO make this somehow singleton (with await xD)
PostgresProvider datahoarderMainUserProvider = new monolith.PostgresProvider("10.13.37.81", "datahoarder_superuser", "datahoarder_superuser", "postgres");
using var conn = await datahoarderMainUserProvider.NewConnection();
await conn.ExecuteAsync("SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'datahoarder_test' OR datname = 'datahoarder_template';"); // Drop Old Database Connections
await conn.ExecuteAsync("DROP DATABASE IF EXISTS datahoarder_test;");
await conn.ExecuteAsync("CREATE DATABASE datahoarder_test TEMPLATE datahoarder_template;");
NpgsqlConnection.ClearAllPools();
PostgresProvider postgresProvider = new monolith.PostgresProvider("10.13.37.81", "datahoarderfs", "datahoarderfs", "datahoarder_test");
monolith.Tracker.ContainerRegistry containerRegistry = new monolith.Tracker.ContainerRegistry(postgresProvider);
monolith.Tracker.FileRegistry fileRegistry = new monolith.Tracker.FileRegistry(postgresProvider);
monolith.Tracker.ChunkRegistry fileChunkRegistry = new monolith.Tracker.ChunkRegistry(postgresProvider);
monolith.Tracker.VersionRegistry fileVersionRegistry = new monolith.Tracker.VersionRegistry(postgresProvider);
var testContainer = new monolith.Tracker.Container {
Name = "alt.binaries.pictures.erotica.furry",
Creator = Guid.NewGuid().ToString(),
};
var registeredContainer = await containerRegistry.Register(testContainer);
var testFile = new monolith.Tracker.File {
ContainerId = registeredContainer,
Filename = $"testFile{ Guid.NewGuid() }.txt",
Owner = Guid.NewGuid().ToString(),
};
var registeredFile = await fileRegistry.Register(testFile);
var fetchedFile = await fileRegistry.Get(registeredFile);
Assert.Equal(testFile.ContainerId, fetchedFile.ContainerId);
Assert.Equal(testFile.Filename, fetchedFile.Filename);
Assert.Equal(testFile.Owner, fetchedFile.Owner);
// Testing Chunking Saving
const int testSizeMB = 10;
var testVersion = new monolith.Tracker.Version {
FileId = fetchedFile.Id,
Date = DateTime.Now,
Size = 1024 * 1024 * testSizeMB, // 10 MB
};
var registredVersion = await fileVersionRegistry.Register(testVersion);
System.Diagnostics.Trace.WriteLine("registred Version" + registredVersion.ToString());
var fetchedVersion = await fileVersionRegistry.Get(registredVersion);
Assert.Equal(testVersion.FileId, fetchedVersion.FileId);
Assert.Equal(testVersion.Date, fetchedVersion.Date, TimeSpan.FromMilliseconds(5));
Assert.Equal(testVersion.Size, fetchedVersion.Size);
for (int i = 0; i < testSizeMB; i++)
{
var testChunk = new monolith.Tracker.Chunk {
FileId = fetchedVersion.FileId,
Order = i,
Size = 1024 * 1024 * 1 // 1 MB
};
var registredChunk = await fileChunkRegistry.Register(testChunk);
var fetchedChunk = await fileChunkRegistry.Get(registredChunk);
Assert.Equal(fetchedChunk.FileId, testChunk.FileId);
Assert.Equal(fetchedChunk.Order, testChunk.Order);
Assert.Equal(fetchedChunk.Size, testChunk.Size);
}
//NpgsqlConnection.ClearAllPools();
}
public void CloseAllConnections()
{
NpgsqlConnection.ClearAllPools();
}
public XunitTests()
{
_tableName = "xxx";
NpgsqlConnection.ClearAllPools();
}
public void RemoveConexaoIdle()
{
NpgsqlConnection.ClearAllPools();
}
public override void ClosePool()
{
NpgsqlConnection.ClearAllPools();
}
internal static void ClearAllPools() => NpgsqlConnection.ClearAllPools();
public override void DropAllConnections()
{
NpgsqlConnection.ClearAllPools();
}
public static void ClearAllPools()
{
NpgsqlConnection.ClearAllPools();
}
public void ResetDatabase()
{
using (Log.InfoTraceMethodCall("ResetDatabase"))
{
var connectionString = config.Server.GetConnectionString(Zetbox.API.Helper.ZetboxConnectionStringKey);
Assert.That(connectionString.ConnectionString, Is.StringContaining("_test"), "test databases should be marked with '_test' in the connection string");
Log.InfoFormat("Current Directory=[{0}]", Environment.CurrentDirectory);
Log.InfoFormat("Using config from [{0}]", config.ConfigFilePath);
try
{
Log.Info("Restoring Database");
var cb = new NpgsqlConnectionStringBuilder(connectionString.ConnectionString);
var srcDB = cb.Database.Substring(0, cb.Database.Length - "_test".Length);
var destDB = cb.Database;
var userCmdString = "--username=sa --no-password";
var dumpFile = tmpService.CreateWithExtension(".zetbox.backup");
try
{
var exitCode = RunPgUtil("pg_dump", String.Format("--format c {0} --file={1} {2}", userCmdString, dumpFile, srcDB));
if (exitCode != 0)
{
throw new ApplicationException(String.Format("Failed to dump database (exit={0}), maybe you need to put your password into AppData\\Roaming\\postgresql\\pgpass.conf", exitCode));
}
var admin = new NpgsqlConnectionStringBuilder(connectionString.ConnectionString);
var dbName = admin.Database;
using (Log.InfoTraceMethodCall("DropCreateDatabase", string.Format("Recreating database {0}", dbName)))
{
admin.Database = "postgres"; // use "default" database to connect, when trying to drop "dbName"
schemaManager.Open(admin.ConnectionString);
if (schemaManager.CheckDatabaseExists(dbName))
{
schemaManager.DropDatabase(dbName);
}
schemaManager.CreateDatabase(dbName);
}
exitCode = RunPgUtil("pg_restore", String.Format("--format c {0} --dbname={2} {1}", userCmdString, dumpFile, destDB));
if (exitCode != 0)
{
throw new ApplicationException(String.Format("Failed to restore database (exit={0})", exitCode));
}
schemaManager.RefreshDbStats();
}
finally
{
// cleanup
File.Delete(dumpFile);
// After recreating the database, all connection pools should be cleard
NpgsqlConnection.ClearAllPools();
}
}
catch (ApplicationException)
{
throw;
}
catch (Exception ex)
{
Log.Error("Error while restoring database", ex);
throw;
}
}
}
