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