/// <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;
}
}