Non-default schemas are not used/respected

[benclark158]

I have found an issue where non-default schemas are not fully supported by commands such as flush and schemas are not migrated as normal tables. With inter connecting tables in non dbo schemas the flush fails to run (see below) and tables do not auto-create without adding custom migrations for creating schemas.

Software versions

• Django: 5.2
• mssql-django: 1.6
• python: 3.10.19
• SQL Server: 2017 and 2022
• OS: Windows and Linux

Table schema and Model

class ParentSchemaModel(models.Models):
    name = models.CharField(max_length=128)

    class Meta:
        db_table = '[test].[ParentSchemaModel]'

class ChildSchemaModel(models.Models):
    name = models.CharField(max_length=128)
    first = models.ForeignKey(FirstSchemaModel, on_delete=models.CASCADE)

    class Meta:
        db_table = '[test].[ChildSchemaModel]'

Database Connection Settings
{ "default": { "ENGINE": "mssql", "NAME": "test_server", "USER": "TestUser", "PASSWORD": "<password>", "HOST": "localhost\\SQLEXPRESS", "PORT": "", "OPTIONS": { "driver": "SQL Server Native Client 11.0", "extra_params": "Trusted_Connection=Yes", "query_timeout": 300 }, "TEST": { "NAME": "test_server", "DEPENDENCIES": [], "CHARSET": None, "COLLATION": True, "MIGRATE": True, "MIRROR": None }, "ATOMIC_REQUESTS": False, "AUTOCOMMIT": True, "CONN_MAX_AGE": 0, "CONN_HEALTH_CHECKS": False, "TIME_ZONE": None } }

Problem description and steps to reproduce

When using the Meta.db_table with a defined schema that is not dbo, the table is created and managed correctly. However flushing the table using python manage.py flush errors, reporting that:

[42S02] [Microsoft][SQL Server Native Client 11.0][sql Server]Cannot find the object "ChildSchemaModel" because it does not exist or you do not have permissions. (4902) (SQLExecDirectW)

When running python manage.py sqlflush it outputs the following:

ALTER TABLE [ChildSchemaModel] NOCHECK CONSTRAINT [test_name_of_foreign_key]
...
ALTER TABLE [ChildSchemaModel] CHECK CONSTRAINT [test_name_of_foreign_key]

There is also no DELETE FROM [ChildSchemaModel] or DELETE FROM [ParentSchemaModel] This means that neither table is being cleared and when there are constraints none of the models in that DB are being flushed, due to the error

Expected behavior and actual behavior

Creating models with different, non default (dbo) schemas should be supported and running flush should clear the tables as expected, rather than erroring, or not running for alternative schemas.

Error message/stack trace

This stack trace is from one of our production tables, rather than the test examples used above.

Traceback (most recent call last):
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 103, in _execute
    return self.cursor.execute(sql)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\mssql\base.py", line 679, in execute
    return self.cursor.execute(sql, params)
pyodbc.ProgrammingError: ('42S02', '[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find the object "clincode" because it does not exist or you do not have permissions. (4902) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\commands\flush.py", line 74, in handle
    connection.ops.execute_sql_flush(sql_list)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\base\operations.py", line 473, in execute_sql_flush
    cursor.execute(sql)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 79, in execute
    return self._execute_with_wrappers(
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 103, in _execute
    return self.cursor.execute(sql)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\mssql\base.py", line 679, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ('42S02', '[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find the object "clincode" because it does not exist or you do not have permissions. (4902) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Users\clarben2\Desktop\Git\api_lib\.\ehs\api\test\case\mixin.py", line 141, in setUpClass
    call_command("flush", database=db, verbosity=0, interactive=False)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\__init__.py", line 194, in call_command
    return command.execute(*args, **defaults)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "c:\Users\clarben2\Desktop\Git\api_lib\.\ehs\api\management\commands\flush.py", line 16, in handle
    return super().handle(*args, **options)
  File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\commands\flush.py", line 76, in handle
    raise CommandError(
django.core.management.base.CommandError: Database test_server couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.