public DbExecuter EndTransaction()
{
if (_transaction == null)
{
return(this);
}
_transaction = null;
_connection.CloseConnection();
return(this);
}
/// <summary>
/// Drops the specified dbs
/// </summary>
/// <param name="dbNames"></param>
protected virtual void DropDb(params string[] dbNames)
{
//wipe out the db name for the connection as need to connect to the service db!
var curentDbName = Dsc.DbName;
Dsc.DbName = string.Empty;
try
{
foreach (var dbName in dbNames)
{
ConsoleEx.Write($"Dropping db: {dbName}... ", ConsoleColor.DarkYellow);
//first cut the connection to the db if any
using (var conn = new NpgsqlConnection(Dsc.GetConnectionString(serviceDatabase: true)))
{
conn.Open();
var cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText =
$"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '{dbName}';";
cmd.ExecuteNonQuery();
conn.CloseConnection();
}
//it should be possible to drop the db now
using (var conn = new NpgsqlConnection(Dsc.GetConnectionString(serviceDatabase: true)))
{
conn.Open();
var cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = $"DROP DATABASE IF EXISTS {dbName}";
cmd.ExecuteNonQuery();
conn.CloseConnection();
}
ConsoleEx.Write("Done!" + Environment.NewLine, ConsoleColor.DarkGreen);
}
Console.WriteLine();
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
//restore previously set dbname
Dsc.DbName = curentDbName;
}
}
/// <summary>
/// Drops specified schemas
/// </summary>
/// <param name="schemaNames">k,v: schemaname,dbname</param>
protected virtual void DropSchema(Dictionary <string, string> schemaNames)
{
//cache whatever is the current db name, as it will be restored later; in order to drop schema, need to connect to a particular db!
var curentDbName = Dsc.DbName;
try
{
foreach (var schema in schemaNames)
{
Dsc.DbName = schema.Value;
ConsoleEx.Write($"Dropping schema: {schema.Key}... ", ConsoleColor.DarkYellow);
//FIXME - need to check if db exists, oherwise will fail...
using (var conn = new NpgsqlConnection(Dsc.GetConnectionString()))
{
conn.Open();
var cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = $"DROP SCHEMA IF EXISTS {schema.Key} CASCADE;";
cmd.ExecuteNonQuery();
conn.CloseConnection();
}
ConsoleEx.Write("Done!" + Environment.NewLine, ConsoleColor.DarkGreen);
ConsoleEx.WriteLine("INFO:", ConsoleColor.DarkMagenta);
ConsoleEx.WriteLine("With EF Core dropping schema is not enough! You will reset the migrations too. They should be located in the public schema.", ConsoleColor.DarkMagenta);
}
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
//restore previously set dbname
Dsc.DbName = curentDbName;
}
}
private void startListen()
{
lock (_listenerLock)
{
_npgsqlConnection?.CloseConnection();
_npgsqlConnection = new DbExecuter(_schema.ConnectionString).BuildNpgSqlConnetion();
_npgsqlConnection.Notification += _OnEvent;
try
{
_schema.CreateTriggerFunction(true);
lock (_listenOnTablesLock)
{
foreach (var table in _listenOnTables)
{
table.TriggerCreate("Notify-TableEvent", "Notify-TableEvent", true);
}
}
_npgsqlConnection.OpenConnection();
NpgsqlCommand prepareCommand = _npgsqlConnection.CreateCommand();
prepareCommand.CommandText = $"LISTEN \"Notify-TableEvent\";";
prepareCommand.ExecuteNonQuery();
prepareCommand.Dispose();
SetState(ListeningConnectionState.Connected);
while (true)
{
_npgsqlConnection.OpenConnection();
var timeout = _npgsqlConnection.Wait(TimeSpan.FromSeconds(3));
//var timeout = Task.Run(() =>
//{
// try
// {
// return !_npgsqlConnection.Wait(TimeSpan.FromSeconds(5));
// }
// catch (Exception)
// {
// return false;
// }
//}, _tokenSource.Token).GetAwaiter().GetResult();
if (!_tableNotificationSubject.HasObservers)
{
break;
}
if (_tokenSource.Token.IsCancellationRequested)
{
break;
}
if (timeout)
{
Logger.Debug(() => "Sending Keepalive Message...");
NpgsqlCommand keepAliveCommand = _npgsqlConnection.CreateCommand();
keepAliveCommand.CommandText = "SELECT 1";
keepAliveCommand.ExecuteNonQuery();
keepAliveCommand.Dispose();
}
}
NpgsqlCommand closeCommand = _npgsqlConnection.CreateCommand();
closeCommand.CommandText = $"UNLISTEN \"Notify-TableEvent\";";
closeCommand.ExecuteNonQuery();
closeCommand.Dispose();
SetState(ListeningConnectionState.Disconnected);
}
catch (Exception ex)
{
SetState(ListeningConnectionState.Error);
Logger.Error(() => $"[{Id}] {ex.Message}");
}
finally
{
_npgsqlConnection.CloseConnection();
_npgsqlConnection = null;
}
}
}
private void Listen()
{
_listeningConnection?.CloseConnection();
_connectionStateChangeSubject.OnNext(ListeningConnectionState.Connecting);
_listeningConnection = new DbExecuter(_connectionBuilder).BuildNpgSqlConnetion();
_listeningConnection.Notification += _OnEvent;
lock (_listenerLock)
{
//Console.WriteLine($"{Thread.CurrentThread.ManagedThreadId}: Listen({notificationChannel}) - LOCK");
bool ErrorOccured = false;
_tokenSource = new CancellationTokenSource();
try
{
_listeningConnection.OpenConnection();
NpgsqlCommand prepareCommand = _listeningConnection.CreateCommand();
prepareCommand.CommandText = $"LISTEN \"{_notifyName}\";";
prepareCommand.ExecuteNonQuery();
prepareCommand.Dispose();
_connectionStateChangeSubject.OnNext(ListeningConnectionState.Connected);
while (true)
{
_listeningConnection.OpenConnection();
var timeout = Task.Run(() =>
{
try
{
return(!_listeningConnection.Wait(TimeSpan.FromSeconds(10)));
}
catch (Exception)
{
return(false);
}
}, _tokenSource.Token).GetAwaiter().GetResult();
if (_tokenSource.Token.IsCancellationRequested)
{
break;
}
if (timeout)
{
//Console.WriteLine("Sending Keepalive Message...");
NpgsqlCommand keepAliveCommand = _listeningConnection.CreateCommand();
keepAliveCommand.CommandText = "SELECT 1";
keepAliveCommand.ExecuteNonQuery();
keepAliveCommand.Dispose();
}
}
//Console.WriteLine($"{Thread.CurrentThread.ManagedThreadId}: Listen({notificationChannel}) - AFTER WHILE");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Thread.Sleep(500);
ErrorOccured = true;
}
if (ErrorOccured)
{
_connectionStateChangeSubject.OnNext(ListeningConnectionState.Error);
}
else
{
_connectionStateChangeSubject.OnNext(ListeningConnectionState.Disconnected);
}
}
}
