public async Task CleanupDbAsync(string tenant, string database)
{
string sql = @"DO
$$
DECLARE _schemas text[];
DECLARE _schema text;
DECLARE _sql text;
BEGIN
SELECT
array_agg(nspname::text)
INTO _schemas
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname NOT IN('information_schema', 'public');
IF(_schemas IS NOT NULL) THEN
FOREACH _schema IN ARRAY _schemas
LOOP
_sql := 'DROP SCHEMA IF EXISTS ' || _schema || ' CASCADE;';
EXECUTE _sql;
END LOOP;
END IF;
END
$$
LANGUAGE plpgsql;";
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
await Factory.ExecuteAsync(connectionString, database, sql).ConfigureAwait(false);
}
public static async Task CreateUserAsync(string tenant, int userId, UserInfo model)
{
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(tenant), tenant).GetDatabase())
{
db.BeginTransaction();
string encryptedPassword = EncryptPassword(model.Password);
var user = new User
{
Email = model.Email,
Password = encryptedPassword,
OfficeId = model.OfficeId,
RoleId = model.RoleId,
Name = model.Name,
Phone = model.Phone,
AuditTs = DateTimeOffset.UtcNow,
AuditUserId = userId
};
await db.InsertAsync("account.users", "user_id", true, user).ConfigureAwait(false);
db.CompleteTransaction();
}
}
public static async Task ChangePasswordAsync(string tenant, int userId, ChangePasswordInfo model)
{
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(tenant), tenant).GetDatabase())
{
string encryptedPassword = EncryptPassword(model.Email, model.Password);
await db.NonQueryAsync("UPDATE account.users SET password = @0 WHERE user_id=@1;", encryptedPassword, model.UserId, encryptedPassword).ConfigureAwait(false);
}
}
public async Task CleanupDbAsync(string tenant, string database)
{
string sql = @"SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @queries TABLE(id int identity, query nvarchar(500), done bit DEFAULT(0));
DECLARE @id int;
DECLARE @query nvarchar(500);
INSERT INTO @queries(query)
SELECT
'EXECUTE dbo.drop_schema ''' + sys.schemas.name + ''''+ CHAR(13) AS query
FROM sys.schemas
WHERE principal_id = 1
AND name != 'dbo'
ORDER BY schema_id;
WHILE(SELECT COUNT(*) FROM @queries WHERE done = 0) > 0
BEGIN
SELECT TOP 1
@id = id,
@query = query
FROM @queries
WHERE done = 0
EXECUTE(@query);
UPDATE @queries SET done = 1 WHERE id=@id;
END;";
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(sql, connection))
{
try
{
connection.Open();
var message = await command.ExecuteScalarAsync().ConfigureAwait(false);
if (message != null)
{
this.Notify(this, $"Could not completely clean database \"{tenant}\" due to dependency issues. Trying again.");
await CleanupDbAsync(tenant, database).ConfigureAwait(false);
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex.Message);
Console.ForegroundColor = ConsoleColor.White;
}
}
}
}
public bool HasSchema(string database, string schema)
{
const string sql = "SELECT COUNT(*) FROM pg_catalog.pg_namespace WHERE nspname=@0;";
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(database)).GetDatabase())
{
return(db.ExecuteScalar <int>(sql, schema).Equals(1));
}
}
public static void Add(ApprovedDomain tenant)
{
string database = TenantConvention.GetDbNameByConvention(tenant.DomainName);
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(database, database), database).GetDatabase())
{
var sql = new Sql("INSERT INTO account.installed_domains(domain_name, admin_email) SELECT @0, @1;", tenant.DomainName, tenant.AdminEmail);
db.Execute(sql);
}
}
public async Task CreateDbAsync(string tenant)
{
string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(tenant.ToLower()));
string database = Factory.GetMetaDatabase(tenant);
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
await Factory.ExecuteAsync(connectionString, tenant, sql).ConfigureAwait(false);
}
public void CreateDb(string tenant)
{
string sql = "CREATE DATABASE [{0}];";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(tenant.ToLower()));
string database = Factory.MetaDatabase;
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
Factory.Execute(connectionString, sql);
}
public void CreateDb(string tenant)
{
string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(tenant.ToLower()));
string database = Factory.MetaDatabase;
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
Factory.Execute(connectionString, sql);
}
public bool HasDb(string dbName)
{
const string sql = "SELECT COUNT(*) FROM pg_catalog.pg_database WHERE datname=@0;";
string database = Factory.MetaDatabase;
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
using (var db = DbProvider.Get(connectionString).GetDatabase())
{
return(db.ExecuteScalar <int>(sql, dbName).Equals(1));
}
}
public async Task <bool> HasDbAsync(string tenant, string database)
{
const string sql = "SELECT COUNT(*) FROM pg_catalog.pg_database WHERE datname=@0;";
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
using (var db = DbProvider.Get(connectionString, tenant).GetDatabase())
{
int awaiter = await db.ScalarAsync <int>(sql, tenant).ConfigureAwait(false);
return(awaiter.Equals(1));
}
}
public static void Add(ApprovedDomain tenant)
{
string database = DbConvention.GetDbNameByConvention(tenant.DomainName);
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(database)).GetDatabase())
{
dynamic poco = new ExpandoObject();
poco.domain_name = tenant.DomainName;
poco.admin_email = tenant.AdminEmail;
db.Insert("account.installed_domains", "domain_id", true, poco);
}
}
public static async Task CreateUserAsync(string tenant, ApprovedDomain domain)
{
if (string.IsNullOrWhiteSpace(domain.AdminEmail) || string.IsNullOrWhiteSpace(domain.BcryptedAdminPassword))
{
return;
}
var sql = new Sql("INSERT INTO account.users(email, password, office_id, role_id, name, phone)");
sql.Append("SELECT @0, @1, @2, @3, @4, @5", domain.AdminEmail, domain.BcryptedAdminPassword, 1, 9999, "", "");
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(tenant), tenant).GetDatabase())
{
await db.NonQueryAsync(sql).ConfigureAwait(false);
}
}
public async Task <bool> HasSchemaAsync(string tenant, string database, string schema)
{
const string sql = "SELECT COUNT(*) FROM pg_catalog.pg_namespace WHERE nspname=@0;";
using (var db = DbProvider.Get(FrapidDbServer.GetSuperUserConnectionString(tenant, database), tenant).GetDatabase())
{
int awaiter = await db.ScalarAsync <int>
(
sql,
new object[]
{
schema
}).ConfigureAwait(false);
return(awaiter.Equals(1));
}
}
public async Task RunSqlAsync(string tenant, string database, string fromFile)
{
fromFile = fromFile.Replace("{DbServer}", "SQL Server");
if (string.IsNullOrWhiteSpace(fromFile) || File.Exists(fromFile).Equals(false))
{
return;
}
string sql = File.ReadAllText(fromFile, Encoding.UTF8);
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
await this.RunScriptAsync(connection, sql).ConfigureAwait(false);
}
}
public async Task RunSqlAsync(string tenant, string database, string fromFile)
{
fromFile = fromFile.Replace("{DbServer}", "PostgreSQL");
if (string.IsNullOrWhiteSpace(fromFile) ||
File.Exists(fromFile).Equals(false))
{
return;
}
string sql = File.ReadAllText(fromFile, Encoding.UTF8);
//PetaPoco/NPoco Escape
//ORM: Remove this behavior if you change the ORM.
//sql = sql.Replace("@", "@@");
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
await Factory.ExecuteAsync(connectionString, tenant, database, sql).ConfigureAwait(true);
}
public async Task <bool> HasDbAsync(string tenant, string database)
{
const string sql = "SELECT COUNT(*) FROM master.dbo.sysdatabases WHERE name=@0;";
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
using (var db = DbProvider.Get(connectionString, tenant).GetDatabase())
{
int awaiter = await db.ExecuteScalarAsync <int>
(
sql,
new object[]
{
tenant
}).ConfigureAwait(false);
return(awaiter.Equals(1));
}
}
public async Task CleanupDbAsync(string tenant, string database)
{
string sql = @"DECLARE @sql nvarchar(MAX);
DECLARE @queries TABLE(id int identity, query nvarchar(500), done bit DEFAULT(0));
DECLARE @id int;
DECLARE @query nvarchar(500);
INSERT INTO @queries(query)
SELECT
'EXECUTE dbo.drop_schema ''' + sys.schemas.name + ''''+ CHAR(13) AS query
FROM sys.schemas
WHERE principal_id = 1
AND name != 'dbo'
ORDER BY schema_id;
WHILE(SELECT COUNT(*) FROM @queries WHERE done = 0) > 0
BEGIN
SELECT TOP 1
@id = id,
@query = query
FROM @queries
WHERE done = 0
EXECUTE(@query);
UPDATE @queries SET done = 1 WHERE id=@id;
END;";
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(sql, connection))
{
connection.Open();
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
}
public void RunSql(string database, string fromFile)
{
fromFile = fromFile.Replace("{DbServer}", "PostgreSQL");
if (string.IsNullOrWhiteSpace(fromFile) || File.Exists(fromFile).Equals(false))
{
return;
}
string sql = File.ReadAllText(fromFile, Encoding.UTF8);
//PetaPoco/NPoco Escape
//ORM: Remove this behavior if you change the ORM.
sql = sql.Replace("@", "@@");
Log.Verbose($"Running SQL {sql}");
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
Factory.Execute(connectionString, sql);
}
public void RunSql(string database, string fromFile)
{
fromFile = fromFile.Replace("{DbServer}", "SQL Server");
if (string.IsNullOrWhiteSpace(fromFile) || File.Exists(fromFile).Equals(false))
{
return;
}
string sql = File.ReadAllText(fromFile, Encoding.UTF8);
Log.Verbose($"Running SQL {sql}");
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
using (var sqlConnection = new SqlConnection(connectionString))
{
var svrConnection = new ServerConnection(sqlConnection);
var server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(sql);
}
}
