SQLServer database-roles schemas generated in migrations

[sundeveu]

Bug Report

Q	A
Version	3.4.5
Platform	SQLServer

Summary

It seems that SQLServerSchemaManager class incorrectly generating list of schemas available in database.
Problem found during generation doctrine:migrations:diff

Current behaviour

After generation doctrine:migration:diff even not changed "Entity" class migration file contains:

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SCHEMA db_accessadmin');
        $this->addSql('CREATE SCHEMA db_backupoperator');
        $this->addSql('CREATE SCHEMA db_datareader');
        $this->addSql('CREATE SCHEMA db_datawriter');
        $this->addSql('CREATE SCHEMA db_ddladmin');
        $this->addSql('CREATE SCHEMA db_denydatareader');
        $this->addSql('CREATE SCHEMA db_denydatawriter');
        $this->addSql('CREATE SCHEMA db_owner');
        $this->addSql('CREATE SCHEMA db_securityadmin');
        $this->addSql('CREATE SCHEMA dbo');
    }

How to reproduce

It will occur always.

Expected behaviour

All of these schemas shouldn't be listed.

Solution

"Database role" type schema names (type='R') should be excluded from query to prevent unnecessary generate migration-down entries for database-role schemas.

 src/Schema/SQLServerSchemaManager.php | 1 +
 1 file changed, 1 insertion(+)

diff --git a/src/Schema/SQLServerSchemaManager.php b/src/Schema/SQLServerSchemaManager.php
index e5c41cd0f..f07b24aa5 100644
--- a/src/Schema/SQLServerSchemaManager.php
+++ b/src/Schema/SQLServerSchemaManager.php
@@ -90,6 +90,7 @@ class SQLServerSchemaManager extends AbstractSchemaManager
 SELECT name
 FROM   sys.schemas
 WHERE  name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')
+AND    name NOT IN(SELECT name FROM sys.database_principals WHERE type='R')
 SQL,
         );
     }

and also:

 src/Platforms/SQLServerPlatform.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/Platforms/SQLServerPlatform.php b/src/Platforms/SQLServerPlatform.php
index e3fec78ee..ae6045715 100644
--- a/src/Platforms/SQLServerPlatform.php
+++ b/src/Platforms/SQLServerPlatform.php
@@ -1163,7 +1163,7 @@ class SQLServerPlatform extends AbstractPlatform
                 . ' use SQLServerSchemaManager::listSchemaNames() instead.',
         );
 
-        return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
+        return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys') AND name NOT IN(SELECT name FROM sys.database_principals WHERE type='R')";
     }
 

After patching migration looks like:

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SCHEMA dbo');
    }

Conclusion

It is not full solution because schema "dbo" also shouldn't be generated because it always exists in newly generated database but I can't find how to exclude it from schema comparison.

[OliverMensahDev]

Is the team considering participating in hacktoberfest this year?