Tables that are in schemas other than `dbo` are not properly reproduced in schema.rb

[Michoels]

Issue

I have a Rails app which uses tables that are spread across several schemas. The main tables are in dbo, but some tables are in external, sales, etc. We mostly use the schemas for access control.

I've been creating tables in other schemas like so:

create_table 'external.sales_charts', do |t|
end

The tables are correctly created. However, they are not properly represented in schema.rb (I can't use structure.sql because there are too many bugs)

Expected behavior

I would expect schema.rb to properly represent the schema-specific table like so:

create_table 'external.sales_charts', do |t|
end

Actual behavior

Instead, it strips the schema from the table name:

create_table 'sales_charts', do |t|
end

This breaks the entire approach of using separate schemas.

How to reproduce

This unit test reproduces the issue.

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "tiny_tds"
  gem "activerecord", "7.0.8"
  gem "activerecord-sqlserver-adapter", "7.0.5.1"
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(
  adapter:  "sqlserver",
  timeout:  5000,
  pool:     100,
  encoding: "utf8",
  database: "test_database",
  username: "sa",
  password: "yourStrong(!)Password",
  host:     "localhost",
  port:     1433,
)
ActiveRecord::Base.logger = Logger.new(STDOUT)

#####
# This fails when annotated with the `example` schema,
# and passes when no schema is specified
####
TABLE_NAME_WITH_SCHEMA = 'example.bug_tests' # <-- Change this

ActiveRecord::Schema.define do
  drop_table TABLE_NAME_WITH_SCHEMA rescue nil

  # Create the `example` schema
  execute <<-SQL
    IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'example')
    BEGIN
        EXEC('CREATE SCHEMA example')
    END
  SQL

  create_table TABLE_NAME_WITH_SCHEMA, force: true do |t|
    t.bigint :external_id
  end
end

class TestBugTest < Minitest::Test

  def test_schema_dump
    schema_filename = 'test_schema.rb'

    File.open(schema_filename, "w:utf-8") do |file|
      ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, file)
    end

    file_contents = File.read schema_filename
    assert_includes file_contents, "create_table \"#{TABLE_NAME_WITH_SCHEMA}\"" 
  end
end

Details

• Rails version: 7.0.8
• SQL Server adapter version: 7.0.5.1
• TinyTDS version: 2.1.7
• FreeTDS details: 1.4.6

Output of tsql -C

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.4.6
             freetds.conf directory: /opt/homebrew/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes

[Michoels]

Workaround for now is to manually edit schema.rb after every migration 😬.

I also need to manually inject the statement for creating the DB schema, like so:

# schema.rb
ActiveRecord::Schema[7.0].define(version: ****) do

  ActiveRecord::Base.connection.exec_query <<-SQL
    CREATE SCHEMA external
  SQL

  # Schema statements continue here...
end

This is necessary because schema.rb does not support raw SQL. We'll need to continue manually adding it until the problems in structure.sql are resolved.

[Michoels]

Bumping this