SQLite database version requirements for Rails 5

[Yasuo Honda]

Hi, 

This is my first post to Rails core mailing list.

I'd like to propose that Rails 5 minimum version of SQLite 3.8 or higher.

( Here I am talking about SQLite database itself, not talking about sqlite3 gem. )

Usually I can open a pull request, but I need to know how to support SQLite 3.8 at Travis CI environment,

which can currently runs 3.7.11 by default or 3.7.15 which causes failures at ActiveRecord unit test.

* Background

Recently there are some issues reported. When more than 2 migrations migrated at the same time it fails with 

`ActiveRecord::StatementInvalid: SQLite3::SQLException: near ",": syntax error: INSERT INTO "schema_migrations" (version)`.

https://github.com/rails/rails/issues/24288

https://github.com/rails/rails/issues/24677

Since this commit will be available to Rails users who migrated to Rails 5. It has not backported to 4.2 or older version of Rails.

https://github.com/rails/rails/commit/42dd2336b31a8d98776d039a2b9fd7f834156a78

* Ubuntu releases and SQLite versions

- Ubuntu 12.04 LTS runs SQLite version 3.7.9, which does not support multiple values in one insert statement.

- Ubuntu 12.04 LTS + Travis CI PPA runs SQLite version 3.7.15.1

causes at two failures in NestedThroughAssociationsTest

which did not reproduce using SQLite 3.7.9 and 3.8.2

https://github.com/rails/rails/pull/24440

- Ubuntu 14.04 LTS runs SQLite version 3.8.2

All ActiveRecord unit tests passed without errors

Refer https://docs.travis-ci.com/user/ci-environment/#Virtualization-environments

Thanks,

--

Yasuo Honda

[Jeremy Daer]

On Thursday, April 21, 2016 at 3:24:28 PM UTC-7, Yasuo Honda wrote:
> I'd like to propose that Rails 5 minimum version of SQLite 3.8 or higher.
> ( Here I am talking about SQLite database itself, not talking about sqlite3 gem. )

Looks like OS X 10.9 Mavericks has SQLite 3.7.13, 10.10 Yosemite has 3.8.5, and 10.11 El Capitan has 3.8.10.2.

I think we're fairly safe to bump the version requirement.

On the other hand, it's pretty cheap/easy to support 3.7.x if we only do multi-insert if the db supports it:
```ruby
if connection.supports_multi_insert?
  …
```

Given that we've had multiple bug reports about it and how confusing it is to troubleshoot, that's probably the friendlier choice.

Best,
Jeremy

[Yasuo Honda]

Thanks for the suggestion. 

Agreed to handle multiple rows insert by implementing `supports_multi_insert?` method for each adapter.

not bumping minimum requirement version to 3.8. The most biggest reason is still some active platform Ubuntu 12.04 LTS and OS X 10.9 Mavericks will be "unsupported" which installs SQLite 3.7.9 by default.

Here are `supports_multi_insert?` return values for each adapters.

* SQLite: true if sqlite_version >= '3.7.11'

https://www.sqlite.org/releaselog/3_7_11.html

>> Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause.

* MySQL: true

Rails 5 requires MySQL 5.0 or higher, which supports multi row insert.

Refer: https://github.com/rails/rails/pull/23458

https://dev.mysql.com/doc/refman/5.0/en/insert.html

"13.2.5 INSERT Syntax"

* PostgreSQL: true

Rails 5 requires 9.1 or higher, which supports multi row insert.

Refer: https://github.com/rails/rails/pull/23434

PostgreSQL 9.1 supports

http://www.postgresql.org/docs/9.1/static/sql-insert.html

I'm working on a pull request for this.

Thanks,

--

Yasuo Honda