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

[aidanharan]

@Michoels Schema dumping was only added to Rails in version 7.1 (rails/rails#49164). I have included schema dumping in the adapters' 7.1 and 7.2 (main) branches. When you have upgraded your application to Rails 7.1 then you will be able to should be able to see the schema dumping in the future 7.1.5 release of the adapter.

[Michoels]

Excellent!
Will test this out.