Creating fixtures for models based on a view containing an IDENTITY column

[budiljak]

Fixtures for Rails tests cannot be created if an ActiveRecord model is created on a view instead of a table and the tables primary key is an IDENTITY column.
While creating fixtures for such a model the rails test environment needs to turn on IDENTITY_INSERT. This fails for views as it has to be executed on the underlying table.

The error message is:
IDENTITY_INSERT could not be turned OFF for table [xyz]

([xyz] would be the name of the view).

Expected behavior

Should call "SET IDENTITY_INSERT" on the underlying table.

Actual behavior

Calls "SET IDENTITY_INSERT" on the view the model is based on.

Solution

See def set_identity_insert(table_name, conn, enable)
(lib/active_record/connection_adapters/sqlserver/database_statements.rb line 303)

table_name is view name here.

[aidanharan]

@budiljak Could you provide a test case (https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/How-to-report-a-bug) to demonstrate the issue?

[budiljak]

Hi @aidanharan. As the bug is pretty obvious I thought you won't need the test case. But I understand how tiring it is to chase bugs that aren't bugs. Here's the testcase.

testcase.zip

Cheers,
Ben

[aidanharan]

@budiljak What version of the gem are you currently using? The fix is in the Rails 8 branch and wondering how far back you need it backported.

[budiljak]

@aidanharan I'm currently using 7.0.6.
Planning an upgrade to Rails 7.2, but this is going to take a while...

[aidanharan]

Rails v7.0.x is no longer supported. I have released versions of v7.1 and v7.2 with the fix.

[budiljak]

Rails v7.0.x is no longer supported. I have released versions of v7.1 and v7.2 with the fix.

@aidanharan Thanks. I could easily monkey patch the v7.0 with the code from the newer branches. That'll do until I can update to 7.2.

For anyone who wants to do the same, just edit the function set_identity_insert in
gems/activerecord-sqlserver-adapter-7.0.6/lib/active_record/connection_adapters/sqlserver/database_statements.rb

like this:

        def set_identity_insert(table_name, enable = true)
          table_name = view_table_name(table_name) if view_exists?(table_name)
          do_execute "SET IDENTITY_INSERT #{table_name} #{enable ? 'ON' : 'OFF'}"
        rescue Exception
          raise ActiveRecordError, "IDENTITY_INSERT could not be turned #{enable ? 'ON' : 'OFF'} for table #{table_name}"
        end

(Just insert the first line.)

Cheers,
Ben

[budiljak]

Hi @aidanharan.

I updated to Rails 7.2.2.1 and sqlserver-adapter 7.2.5, but the bug is still present.

Should I open a new ticket or can we use this one?

The monkey patch is also working with version 7.2.5. It could be the solution for a real patch...

Cheers,
Ben

[aidanharan]

@budiljak Could you create a PR that contains the fix and a test case for the issue? Thanks