public Schema SchemaRename(string schemaName, string newSchemaName)
{
try
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
if (String.IsNullOrWhiteSpace(newSchemaName))
{
throw new ArgumentNullException(nameof(newSchemaName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.SchemaRename(schemaName, newSchemaName));
var resp = new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
return(GetSchema(newSchemaName));
}
catch (PostgresException pex)
{
if (pex.SqlState == "3F000")
{
throw new SchemaDoesntExistsException(schemaName);
}
if (pex.SqlState == "42P06")
{
throw new SchemaAlreadyExistsException(newSchemaName);
}
throw;
}
}
public Schema CreateSchema(string schemaName, bool throwIfAlreadyExists)
{
try
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
//schemaName = schemaName.ToLower();
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.SchemaCreate(schemaName, throwIfAlreadyExists));
var resp = new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
return(GetSchema(schemaName));
}
catch (PostgresException pex)
{
if (pex.SqlState == "42P06")
{
throw new SchemaAlreadyExistsException(schemaName);
}
throw;
}
}
public void SchemaDrop(string schemaName, bool force, bool throwIfNotExists)
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
try
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.SchemaDrop(schemaName, force, throwIfNotExists));
var resp = new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
catch (PostgresException pex)
{
if (pex.SqlState == "2BP01")
{
throw new DependentObjectsException(pex.Detail);
}
if (pex.SqlState == "3F000")
{
throw new SchemaDoesntExistsException(schemaName);
}
throw;
}
}
public Database CreateDatabase(string database, bool throwIfAlreadyExists)
{
if (String.IsNullOrWhiteSpace(database))
{
throw new ArgumentNullException(nameof(database));
}
try
{
if (!DatabaseExists(database, false, throwIfAlreadyExists))
{
try
{
new DbExecuter(_configuration).ExecuteNonQuery(SQLStatements.DatabaseCreate(database));
}
catch (PostgresException pex)
{
if (throwIfAlreadyExists && pex.SqlState != "42P04")
{
throw;
}
}
}
return(GetDatabase(database));
}
catch (PostgresException pex)
{
if (pex.SqlState == "42P04")
{
throw new DatabaseAlreadyExistsException(database);
}
throw;
}
}
public bool DomainExists(string domainName)
{
if (String.IsNullOrWhiteSpace(domainName))
{
throw new ArgumentNullException(nameof(domainName));
}
return(new DbExecuter(ConnectionString).ExecuteScalar <bool>(SQLStatements.DomainExists(domainName)));
}
public void DropDatabaseConnections(string databaseName)
{
if (String.IsNullOrWhiteSpace(databaseName))
{
throw new ArgumentNullException(nameof(databaseName));
}
new DbExecuter(_configuration).ExecuteNonQuery(SQLStatements.DatabaseConnectionsDrop(databaseName));
}
public void ExtensionCreate(string extensionName, bool throwIfAlreadyExists)
{
if (String.IsNullOrWhiteSpace(extensionName))
{
throw new ArgumentNullException(nameof(extensionName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.ExtensionCreate(extensionName, throwIfAlreadyExists));
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
public void DomainDrop(string domainName, bool throwIfNotExists)
{
if (String.IsNullOrWhiteSpace(domainName))
{
throw new ArgumentNullException(nameof(domainName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.DomainDrop(domainName, throwIfNotExists));
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
public string[] TableList()
{
var l = new List <string>();
foreach (var exp in new DbExecuter(ConnectionString).ExecuteReader(SQLStatements.GetTables(ConnectionString.SchemaName)))
{
var obj = JSON.ToObject <Dictionary <string, object> >(exp);
l.Add(obj["table_name"].ToString());
}
return(l.ToArray());
}
public void DropDatabase(string databaseName, bool force, bool throwIfNotExists)
{
if (String.IsNullOrWhiteSpace(databaseName))
{
throw new ArgumentNullException(nameof(databaseName));
}
if (force)
{
DropDatabaseConnections(databaseName);
}
new DbExecuter(_configuration).ExecuteNonQuery(SQLStatements.DatabaseDrop(databaseName, throwIfNotExists));
}
public Schema TableDrop(string tableName, bool throwIfNotExists = false)
{
if (String.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
try
{
new DbExecuter(ConnectionString).ExecuteNonQuery(SQLStatements.RemoveTable(tableName, ConnectionString.SchemaName, throwIfNotExists));
}
catch (PostgresException ex) when(ex.SqlState == "42P01")
{
throw new TableDoesntExistsException(tableName);
}
return(this);
}
public bool DatabaseExists(string database, bool throwIfNotExists, bool throwIfExists)
{
if (String.IsNullOrWhiteSpace(database))
{
throw new ArgumentNullException(nameof(database));
}
var exists = new DbExecuter(_configuration).ExecuteScalar <bool>(SQLStatements.DatabaseExists(database));
if (!exists && throwIfNotExists)
{
throw new DatabaseDoesntExistsException(database);
}
if (exists && throwIfExists)
{
throw new DatabaseAlreadyExistsException(database);
}
return(exists);
}
public void DomainCreate(string domainName, string postgresTypeName, bool throwIfExists)
{
if (String.IsNullOrWhiteSpace(domainName))
{
throw new ArgumentNullException(nameof(domainName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.DomainCreate(domainName, postgresTypeName));
if (!throwIfExists)
{
if (!DomainExists(domainName))
{
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
}
else
{
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
}
public bool SchemaExists(string schemaName, bool throwIfNotExists, bool throwIfAlreadyExists)
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
var exists = new DbExecuter(ConnectionString).ExecuteScalar <bool>(SQLStatements.SchemaExists(schemaName));
if (!exists && throwIfNotExists)
{
throw new SchemaDoesntExistsException(schemaName);
}
if (exists && throwIfAlreadyExists)
{
throw new SchemaAlreadyExistsException(schemaName);
}
return(exists);
}
public IEnumerable <string> GetDatabaseList()
{
foreach (var expandableObject in new DbExecuter(_configuration).ExecuteReader(SQLStatements.DatabaseListAll()))
{
var obj = JSON.ToObject <Dictionary <string, object> >(expandableObject);
yield return(obj["datname"].ToString());
}
}
public List <Dictionary <string, object> > GetServerSetting()
{
var settings = new List <Dictionary <string, object> >();
foreach (var expandableObject in new DbExecuter(_configuration).ExecuteReader(SQLStatements.GetAllSettings()))
{
settings.Add(JSON.ToObject <Dictionary <string, object> >(expandableObject));
}
return(settings);
}
public IEnumerable <object> GetDatabaseConnections(string databaseName)
{
if (String.IsNullOrWhiteSpace(databaseName))
{
throw new ArgumentNullException(nameof(databaseName));
}
foreach (var expandableObject in new DbExecuter(_configuration).ExecuteReader <ExpandoObject>(SQLStatements.DatabaseConnectionsGet(databaseName)))
{
yield return(expandableObject);
}
}
public string[] SchemaList()
{
var l = new List <string>();
foreach (var sch in new DbExecuter(ConnectionString).ExecuteReader <Dictionary <string, string> >(SQLStatements.SchemaGetAll()))
{
l.Add(sch["schema_name"]);
}
return(l.ToArray());
}
public void DropTriggerFunction(string triggerFunctionName)
{
new DbExecuter(ConnectionString).ExecuteNonQuery(SQLStatements.DropTriggerFunction(triggerFunctionName));
}
public Dictionary <string, string>[] ExtensionList()
{
return(new DbExecuter(ConnectionString).ExecuteReader <Dictionary <string, string> >(SQLStatements.ExtensionList())?.ToArray());
}
// public bool EventTriggerExists(string triggerName)
// {
// var command = $"SELECT EXISTS(select 1 from pg_event_trigger where evtname = '{triggerName}');";
// return new DbExecuter(ConnectionString).ExecuteScalar<bool>(command);
// }
// public void EventTriggerDrop(string triggerName, bool force)
// {
// var command = $"DROP EVENT TRIGGER IF EXISTS \"{triggerName}\"";
// if (force)
// command = $"{command} CASCADE";
// new DbExecuter(ConnectionString).ExecuteNonQuery(command);
// }
// public void RegisterEventTrigger(bool overwriteIfExists = false)
// {
// var functionName = $"XA-Notify-SchemaEvent";
// if (!FunctionExists(functionName) || overwriteIfExists)
// {
// var function1 = $@"
//CREATE OR REPLACE FUNCTION ""{ConnectionString.SchemaName}"".""{functionName}""() RETURNS event_trigger AS $$
//DECLARE
// r RECORD;
// notification json;
// type text;
//BEGIN
// FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
// notification= json_build_object(
// 'action', upper(r.command_tag),
// 'schema', r.schema_name,
// 'identity', r.object_identity,
// 'type', upper(r.object_type)
// );
// PERFORM pg_notify('{functionName}', notification::text);
// END LOOP;
//END;
//$$ LANGUAGE plpgsql;
//";
// new DbExecuter(ConnectionString).ExecuteNonQuery(function1);
// }
// if (!FunctionExists($"{functionName}_dropped") || overwriteIfExists)
// {
// var function2 = $@"
//CREATE OR REPLACE FUNCTION ""{ConnectionString.SchemaName}"".""{functionName}_dropped""() RETURNS event_trigger AS $$
//DECLARE
// r RECORD;
// notification json;
// action text;
//BEGIN
// FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
// notification= json_build_object(
// 'action', 'DROP ' || upper(r.object_type),
// 'schema', r.schema_name,
// 'identity', r.object_identity,
// 'type', upper(r.object_type)
// );
// PERFORM pg_notify('{functionName}', notification::text);
// END LOOP;
//END;
//$$ LANGUAGE plpgsql;
//";
// new DbExecuter(ConnectionString).ExecuteNonQuery(function2);
// }
// var trigger1Exists = EventTriggerExists($"{functionName}_Trigger::{ConnectionString.SchemaName}");
// if (!trigger1Exists || overwriteIfExists)
// {
// if (trigger1Exists)
// EventTriggerDrop($"{functionName}_Trigger::{ConnectionString.SchemaName}", true);
// var trigger1 = $"CREATE EVENT TRIGGER \"{functionName}_Trigger::{ConnectionString.SchemaName}\" ON ddl_command_end EXECUTE PROCEDURE \"{ConnectionString.SchemaName}\".\"{functionName}\"();";
// new DbExecuter(ConnectionString).ExecuteNonQuery(trigger1);
// }
// var trigger2Exists = EventTriggerExists($"{functionName}_Trigger_dropped::{ConnectionString.SchemaName}");
// if (!trigger2Exists || overwriteIfExists)
// {
// if (trigger2Exists)
// EventTriggerDrop($"{functionName}_Trigger_dropped::{ConnectionString.SchemaName}", true);
// var trigger2 = $"CREATE EVENT TRIGGER \"{functionName}_Trigger_dropped::{ConnectionString.SchemaName}\" ON sql_drop EXECUTE PROCEDURE \"{ConnectionString.SchemaName}\".\"{functionName}_dropped\"();";
// new DbExecuter(ConnectionString).ExecuteNonQuery(trigger2);
// }
// }
#region Triggers
public bool TriggersForTriggerFunctionExists(string triggerFunctionName)
{
return(new DbExecuter(ConnectionString).ExecuteScalar <bool>(SQLStatements.TriggerExistsForTriggerFunction(triggerFunctionName)));
}
internal static TableDefinition<T> FromTable<T>(ITable table)
{
var td = TableDefinition.FromType<T>();
try
{
var columns = new DbExecuter(table.GetConnectionString()).ExecuteReader(SQLStatements.GetColumns(table.GetConnectionString().TableName, table.GetConnectionString().SchemaName)).ToArray();
foreach (var column in columns)
{
Column dbCol = column.ToObject<Column>();
var col = td.GetColumnByDbName(dbCol.DbName) ?? td.GetColumnByClrName(dbCol.DbName);
if (col == null)
{
dbCol.DotNetType = PgSqlTypeManager.GetDotNetType(dbCol.PgType);
td.AddColumn(dbCol);
}
else
{
col.DbName = dbCol.DbName;
col.CanBeNull = dbCol.CanBeNull;
col.DefaultValue = dbCol.DefaultValue;
col.Position = dbCol.Position;
col.IsPrimaryKey = dbCol.IsPrimaryKey;
col.MustBeUnique = dbCol.MustBeUnique;
}
}
}
catch (Exception e)
{
Logger.Error(e, "GetTableSchema");
throw;
}
return td;
}
public IEnumerable <Dictionary <string, string> > DomainList()
{
return(new DbExecuter(ConnectionString).ExecuteReader <Dictionary <string, string> >(SQLStatements.DomainList()));
}