Example #1
0
        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);
        }
Example #2
0
        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();
            }
        }
Example #3
0
 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);
     }
 }
Example #4
0
        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;
                    }
                }
            }
        }
Example #5
0
        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));
            }
        }
Example #6
0
        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);
            }
        }
Example #7
0
        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);
        }
Example #8
0
        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);
        }
Example #9
0
        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);
        }
Example #10
0
        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));
            }
        }
Example #11
0
        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));
            }
        }
Example #12
0
        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);
            }
        }
Example #13
0
        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);
            }
        }
Example #14
0
        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));
            }
        }
Example #15
0
        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);
            }
        }
Example #16
0
        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);
        }
Example #17
0
        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));
            }
        }
Example #18
0
        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);
                }
            }
        }
Example #19
0
        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);
        }
Example #20
0
        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);
            }
        }