Support for SQL Server on Rails 5.x, 6.0, 6.1, and 7.0

[JesseChavez]

Hi All,

I have done some work to support the SQL Server / Azure SQL, it is mostly based on the sqlserver arel visitor from the CRuby activerecord-sqlserver-adapter gem.

At the moment passes most of its tests and activerecord tests with few exceptions, the failing test are due SQL server specific ways to do things such as:

• Average respects the data type columns so the avg of a integer column the result is integer
• A column has been specified more than once in the order by list throws an error, this kind of scenario gets ignored by Postgres.
• transaction isolation level repeatable_read and read_committed protect the read with a lock.
• SQL Server can't update identity keys
• etc.

NOTES: To avoid the locking in read_committed we need to do:

"ALTER DATABASE [db_name] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE"

The activerecord tests run against my rails fork which contains specific fixes to run the test such as decimal max precision 38, quoting for SQL Server, etc. Please look at my commit in the rails fork.

https://github.com/JesseChavez/rails/tree/5-0-stable-dev

[headius]

This is excellent, thank you! Pinging @enebo @rdubya @kares to have a look.

I know @rdubya was working on SQL Server support also...hopefully we can merge these efforts and fast forward support for 5.1+.

[rdubya]

Hey @JesseChavez, I wish I had known you were working on this stuff (and that I would have pushed up what I had sooner.) I'll get my stuff up, but I've taken a different approach. I've been working on making it so that we can use the activerecord-sqlserver-adapter gem directly so we can eliminate the bulk of the code that is currently needed for the arjdbc mssql support. The maintainers of the gem have previously expressed willingness to make small changes to support the approach as long as they didn't have to deal with supporting JRuby themselves. At this point I have all but about 25 tests passing from their test suite, which runs the active record tests + their own mssql specific tests. Most of what is still failing is related to dates/times and some connection management stuff. I haven't pushed up what I have yet because I don't have a clean test environment for it yet, but I'll get what I have up so you can take a look at it.

[rdubya]

Just dropping a link to my WIP changes in here: https://github.com/jruby/activerecord-jdbc-adapter/pull/1008

[JesseChavez]

Hey @rdubya

We maintain a couple of dozens of app that are tightly coupled to the activerecord-jdbc-adapter gem, JNDI, JVM, Java libraries and tools, HikariCP, Tomcat, Jetty, PostgreSQL and SQL Server, for us is easy to bump the activerecord-jdbc-adapter gem and follow the standard rails upgrade process.

The Rails/JRuby/Java works well for us, we have the best of Java and JRuby, the only thing holding us back was the mssql support, I guess we should have worked earlier in the activerecord-jdbc-adapter gem.

The approach we took in this pull request is to start clean without all the baggage of the previous versions.

[rdubya]

Hey @JesseChavez, We've been talking it over in IRC/Matrix (https://matrix.to/#/!vyEDBdbmzqApWaugdd:matrix.org?via=matrix.org) and I think the plan at this point is to fork the activerecord-sqlserver-adapter gem and go with closer to my approach so that we can try to keep in sync with that gem but have the freedom to tweak things as needed. That will hopefully reduce the amount of maintenance work needed long term. Will this approach fit into your needs? Are you willing to contribute to that structure?

I'm not sure I understand your comment about things being tightly coupled, but I hope this will work for you since the activerecord-jdbc-adapter will still be used as the "driver" for the gem.

[rdubya]

@JesseChavez We've forked the activerecord-sqlserver-adapter codebase to create an activerecord-jdbcsqlserver-adapter. Can you take a look at it and see if it would work for your needs? It should make maintenance easier than with this approach and hopefully make it easier to also support 5.1, 5.2 and 6.0. You can see the changes to the forked code here: https://github.com/jruby/activerecord-sqlserver-adapter/compare/5-0-stable...jruby:5-0-stable-jdbc and running that branch with the branch for my PR in this project currently passes most of the test suite for the sqlserver gem (which is their own tests plus the ActiveRecord tests). My current result is: 4913 runs, 14185 assertions, 11 failures, 5 errors, 6 skips and it looks like some of what you have been fixing may fix some of the remaining failures. Would you be willing to contribute towards that effort? The bulk of the failures are around timestamp accuracy and most of the skips are things that we can't/don't support.

[JesseChavez]

Just for reference if there is anyone still interested on an Active Record SQL Server adapter the works with JRuby

There is a fork of the activerecord-jdbc-adapter with Rails 5.0, 5.1, 5.2, 6.0. and 6.1 support:

https://rubygems.org/gems/activerecord-jdbc-alt-adapter

It is strongly advised to read the README file of the forked repository for more information and differences compared to activerecord-sqlserver-adapter or activerecord-jdbc-adapter adapters.

platforms :jruby do
  gem 'activerecord-jdbc-alt-adapter', '~> 61.0.0'
  gem 'jdbc-mssql', '~> 0.9.0'
end

Some of our apps are already running in production and one of them is a huge ancient finance Rails app originally created with Rails 2.0 and JRuby.